PostgreSQL Database Creation-1

PostgreSQL provides two ways of creating a new database:
1.Create database Using Command-Line Mode
2.creating database using sql command

1.Create database Using Command-Line Mode
Connect To the database :

[postgres@r1 ~]$ cd /opt/PostgreSQL/9.3/bin/
[postgres@r1 bin]$./psql -p 5432 -d postgres -U postgres
Password for user postgres: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
postgres=#  

First we will check that  how many database is their in postgres server:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

Create "teachdb" using command line mode:

[root@r1 ~]# su - postgres
[postgres@r1 ~]$ cd /opt/PostgreSQL/9.3/bin/
[postgres@r1 bin]$ ./create
createdb    createlang  createuser  
[postgres@r1 bin]$ ./createdb teachdb
Password: 
[postgres@r1 bin]$ 

Check the created "teachdb" listed or not using "\l":

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 teachdb   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Connect the newly created "teachdb" database:

[postgres@r1 ~]$ cd /opt/PostgreSQL/9.3/bin/
[postgres@r1 bin]$ ./psql -p 5432 -d teachdb -U postgres
Password for user postgres: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
teachdb=# 

• If You want to access postgres utility at linux home without going utility path (/opt/PostgreSQL/9.3/bin/) you need to set .bash_prifile
• later we will learn  or next chapter how to setup ".bash_profile" for postgres

You can view the version of postgres using "PG_VERSION" file from data directory:

[postgres@r1 data]$ pwd
/opt/PostgreSQL/9.3/data
[postgres@r1 data]$ cat PG_VERSION
9.3

creating db with specific user using shell:

[postgres@r1 ~]$ createdb -O u1 account
Password: 
[postgres@r1 ~]$ 

Drop "teachdb" database :

when you drop database no session should not be connected

[postgres@r1 ~]$ drop
dropdb    droplang  dropuser  
[postgres@r1 ~]$ dropdb teachdb
Password: 
dropdb: database removal failed: ERROR:  database "teachdb" is being accessed by other users
DETAIL:  There is 1 other session using the database.

Solution:

After session closed easily we can drop database 

[postgres@r1 ~]$ dropdb teachdb
Password: 
[postgres@r1 ~]$ 

2.creating database using sql command:
Check the Version of postgres using "sql command" mode:

teachdb=# select version ();
                                                 version                                                
---------------------------------------------------------------------------------------------------------
 PostgreSQL 9.3.14 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 32-bit
(1 row)

Synopsis:

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace ]
           [ CONNECTION LIMIT [=] connlimit ] ]

To create a new database:

postgres=# CREATE DATABASE teachlax;
CREATE DATABASE

To create a database sales owned by user "u1" with a default tablespace of tbs1:

postgres=# create user u1 with password 'u1';                                   
CREATE ROLE

postgres=# CREATE DATABASE sales OWNER u1 TABLESPACE tbs1;
CREATE DATABASE

To create a database music which supports the ISO-8859-1 character set:

CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;

In this example, the TEMPLATE template0 clause would only be required if template1's encoding is not ISO-8859-1. Note that changing encoding might require selecting new LC_COLLATE and LC_CTYPE settings as well.

Droping a database:

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sales     | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 teachlax  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
(5 rows)

postgres=# drop database teachlax;
DROP DATABASE

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sales     | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres      
    +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

change database  owner  from "U1" to "U2":

postgres=# create user u2 with password 'u2';
CREATE ROLE
postgres=# alter database sales owner to u2;
ALTER DATABASE

Set  the Default tablespace :

postgres=# alter database account set default_tablespace=tbs1;
ALTER DATABASE

postgres=# \db
          List of tablespaces
    Name    |  Owner   |    Location    
------------+----------+----------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs1       | postgres | /home/postgres
(3 rows)

List the database using sql prompt:

postgres=# select datname from pg_database;
  datname  
-----------
 template1
 template0
 postgres
 sales
 musicdb
 account
(6 rows)