PostgreSQL Database Creation

• CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. Thus that database is the “template” from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This behavior allows site-local modifications to the standard set of objects in databases. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra action being taken when those databases are created.
• There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By instructing CREATE DATABASE to copy template0 instead of template1, you can create a “virgin” user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a virgin database to ensure that one recreates the correct contents of the dumped database, without conflicting with objects that might have been added to template1 later on.
• Another common reason for copying template0 instead of template1 is that new encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does. This is because template1 might contain encoding-specific or locale-specific data, while template0 is known not to.

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 postgresPassword for user postgres: psql.bin (9.3.14)Type "help" for help.No entry for terminal type "xterm";using dumb terminal settings.postgres=#
Copy

check the postgresql database list using command line utility \l

postgres=# \lList 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)
Copy

create teachdb database using createdb postgresql utility

[root@r1 ]# su - postgres[postgres@r1 ]$ cd /opt/PostgreSQL/9.3/bin/[postgres@r1 bin]$ ./createcreatedbcreatelang  createuser  [postgres@r1 bin]$ ./createdb teachdbPassword: [postgres@r1 bin]$
Copy

check the database list  whether  teachdb created or not using \l or pg_database views

postgres=# \lList 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)
Copy

Connect the newly created “teachdb” database:

[postgres@r1 ]$ cd /opt/PostgreSQL/9.3/bin/[postgres@r1 bin]$ ./psql -p 5432 -d teachdb -U postgresPassword for user postgres: psql.bin (9.3.14)Type "help" for help.No entry for terminal type "xterm";using dumb terminal settings.teachdb=#
Copy

• If You want to access postgres utility at linux home without going utility path (/opt/PostgreSQL/9.3/bin/) you have to setup .bash_prifile first

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_VERSION9.3
Copy

To create the database demo using the postmaster on host eden, port 5000:

createdb -p 5000 -h eden demo
Copy

Drop “teachdb” database :

when you drop database no session should not be connected

[postgres@r1 ]$ dropdropdbdroplang  dropuser  [postgres@r1 ]$ dropdb teachdbPassword: dropdb: database removal failed: ERROR:  database "teachdb" is being accessed by other usersDETAIL:  There is 1 other session using the database.
Copy

Solution:

After session closed easily we can drop database .

REVOKE CONNECT ON DATABASE teachdb FROM public;
Copy

(and possibly other users/roles; see \l+ in psql)

You can then terminate all connections to this db except your own:

SELECT pg_terminate_backend(pg_stat_activity.pid)FROM pg_stat_activityWHERE pg_stat_activity.datname = 'teachdb';
Copy

Note: On older versions pid was called procpid so you’ll have to deal with that.

Since you’ve revoked CONNECT rights, whatever was trying to auto-connect should no longer be able to do so.

You’ll now be able to drop the DB.

This won’t work if you’re using superuser connections for normal operations, but if you’re doing that you need to fix that problem first.

[postgres@r1 ]$ dropdb teachdbPassword: [postgres@r1 ]$
Copy

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)
Copy

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 ] ]
Copy

To create a new database using sql command:

postgres=# CREATE DATABASE teachlax;CREATE DATABASE
Copy

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

postgres=# create user u1 with password 'u1'; CREATE ROLEpostgres=# CREATE DATABASE sales OWNER u1 TABLESPACE tbs1;CREATE DATABASE
Copy

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

CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;
Copy

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=# \lList 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 DATABASEpostgres=# \lList 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)
Copy

change database  owner  from “U1” to “U2”:

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

Set  the Default tablespace :

postgres=# alter database account set default_tablespace=tbs1;ALTER DATABASEpostgres=# \db  List of tablespacesName|  Owner |Location------------+----------+---------------- pg_default | postgres |  pg_global  | postgres |  tbs1 | postgres | /home/postgres(3 rows)
Copy

Listing  database using sql query:

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

create a database coping template0 in sql:

postgres=# create database temp0copy template template0;CREATE DATABASE
Copy

create a database coping template0 in shell:

[postgres@r1 ]$ createdb -T template0 temp0copy2Password: [postgres@r1 ]$
Copy

Listing database using command line:

[postgres@r1 ]$ psql -lPassword: List of databasesName|  Owner | Encoding | Collate |Ctype| Access privileges ------------+----------+----------+-------------+-------------+----------------------- account| u1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |  musicdb| postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |  postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |  sales  | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |  temp0copy  | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |  temp0copy2 | 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(8 rows)
Copy

• It is possible to create additional template databases, and indeed one can copy any database in a cluster by specifying its name as the template for CREATE DATABASE. It is important to understand, however, that this is not (yet) intended as a general-purpose “COPY DATABASE” facility.
• The principal limitation is that no other sessions can be connected to the source database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; during the copy operation, new connections to the source database are prevented.

Two useful flags exist in pg_database for each database: 

1. datistemplate
2. datallowconn

datistemplate:

• datistemplate can be set to indicate that a database is intended as a template for CREATE DATABASE.
• If this flag is set(t), the database can be cloned by any user with CREATEDB privileges;
• if it is not set(f), only superusers and the owner of the database can clone it.

datallowconn :

• If datallowconn is false, then no new connections to that database will be allowed (but existing sessions are not terminated simply by setting the flag false). The template0 database is normally marked datallowconn = false to prevent its modification.
• If true => new connections not disturbed.
• Both template0 and template1 should always be marked with datistemplate = true.

postgres=#  select datname, datistemplate, datallowconn from pg_database;  datname | datistemplate | datallowconn ------------+---------------+-------------- template1  | t | t template0  | t | f postgres | f | t sales  | f | t musicdb| f | t account| f | t temp0copy  | f | t temp0copy2 | f | t(8 rows)
Copy

Notes: template1 and template0 do not have any special status beyond the fact that the name template1 is the default source database name for CREATE DATABASE.
Example For  one could drop template1 and recreate it from template0 without any ill effects. This course of action might be advisable if one has carelessly added a bunch of junk in template1. (To delete template1, it must have pg_database.datistemplate = false.)

The postgres database is also created when a database cluster is initialized. This database is meant as a default database for users and applications to connect to. It is simply a copy of template1 and can be dropped and recreated if necessary.
Database Configuration:
PostgreSQL server provides a large number of run-time configuration variables. You can set database-specific default values for many of these settings.
For example, if for some reason you want to disable the GEQO optimizer for a given database, you’d ordinarily have to either disable it for all databases or make sure that every connecting client is careful to issue SET geqo TO off;. To make this setting the default within a particular database, you can execute the command:

ALTER DATABASE mydb SET geqo TO off;
Copy

This will save the setting (but not set it immediately). In subsequent connections to this database it will appear as though SET geqo TO off; had been executed just before the session started. Note that users can still alter this setting during their sessions; it will only be the default. To undo any such setting, use ALTER DATABASE dbname RESET varname;.
Destroying a Database
Databases are destroyed with the command DROP DATABASE:

DROP DATABASE name;
Copy

• Only the owner of the database, or a superuser, can drop a database. Dropping a database removes all objects that were contained within the database. The destruction of a database cannot be undone.
• You cannot execute the DROP DATABASE command while connected to the victim database. You can, however, be connected to any other database, including the template1 database. template1 would be the only option for dropping the last user database of a given cluster.

For convenience, there is also a shell program to drop databases, dropdb:

dropdb dbname
Copy

(Unlike createdb, it is not the default action to drop the database with the current user name.)

postgres=# select pg_database_size('account'); pg_database_size ------------------  6349316(1 row)postgres=# select pg_size_pretty(pg_database_size('account')); pg_size_pretty ---------------- 6201 kB(1 row)