Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Database Creation-1

PostgreSQL provides two ways of creating a new database:
1.Create database Using postgresql utility
2.creating database using sql command

1.Create database Using postgresql utility
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=#  
check the postgresql database list using command line utility \l
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 database using createdb postgresql utility
[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 database list  whether created or not using \l or pg_database views
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
  • how to setup ".bash_profile" for accessing postgresql utility.
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 using sql command:
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)


Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL