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 Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform