Tag Archives: How To Create New Database With Template in PostgreSQL using psql?

PostgreSQL DBA – How to Create Database in PostgreSQL?

PostgreSQL DBA – How to Create Database in PostgreSQL?

Table of Contents
___________________________________________________________________________________________________

0. How to Find Create Database Syntax in PostgreSQL?
1. How to List All Databases in PostgreSQL using psql?
2. How To Create New Database in PostgreSQL using psql?
3. How To Create New Database With Owner in PostgreSQL using psql?
4. How To Create New Database With Tablespace in PostgreSQL using psql?
5. How To Create New Database With Encoding in PostgreSQL using psql?
6. How To Create New Database With Template in PostgreSQL using psql?
7. How to Create New Database With Binary Owner in PostgreSQL using Command Line?
8. How to Connect to Database in PostgreSQL using psql?
9. How to Find the Version of Database in PostgreSQL?
10. How to Exit from PostgreSQL?
___________________________________________________________________________________________________


0. How to Find Create Database Syntax in PostgreSQL?

[postgres@rac1 ~]$ psql
psql (13.2)
Type "help" for help.

postgres=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/13/sql-createdatabase.html

postgres=#


1. How to List All Databases in PostgreSQL using psql?

postgres=# \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8117 kB | pg_default |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 8117 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7969 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7969 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(4 rows)

postgres=#

postgres=# select oid,datname from pg_database;
  oid  |  datname
-------+-----------
 14172 | postgres
 16384 | orcl
     1 | template1
 14171 | template0
(4 rows)

postgres=#


select * from pg_database;


2. How To Create New database in PostgreSQL using psql?

postgres=# create database test;
CREATE DATABASE
postgres=#
postgres=# SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, datname FROM pg_database where datname='test';
      modification      | datname
------------------------+---------
 2021-03-15 21:30:27+08 | test <----
(1 row)

postgres=#


3. How To Create New database With Owner in PostgreSQL using psql?

postgres=# create user vishnu with password 'vishnu';
CREATE ROLE
postgres=# CREATE DATABASE test2 OWNER vishnu;
CREATE DATABASE
postgres=#
postgres=# \l+ test2
                                               List of databases
 Name  | Owner  | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
-------+--------+----------+-------------+-------------+-------------------+---------+------------+-------------
 test2 | vishnu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7969 kB | pg_default |
(1 row)

postgres=#


4. How To Create New database With Tablespace in PostgreSQL using psql?

[root@rac1 ~]# mkdir -p /u01/postgres/data/test3_tbs
[root@rac1 ~]# chown -R postgres:postgres /u01/postgres
[root@rac1 ~]# chmod -R 750 /u01/postgres

postgres=# CREATE TABLESPACE test3_tbs LOCATION '/u01/postgres/data/test3_tbs';
CREATE TABLESPACE
postgres=#

postgres=# CREATE DATABASE test3 TABLESPACE test3_tbs;
CREATE DATABASE
postgres=# select datname from pg_database where datname='test3';
 datname
---------
 test3 <----
(1 row)

postgres=#


5. How To Create New database With Encoding in PostgreSQL using psql?

postgres=# CREATE DATABASE test4 ENCODING 'UTF8';
CREATE DATABASE
postgres=#
postgres=# \l+ test4
                                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
-------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
 test4 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7969 kB | pg_default |
(1 row)

postgres=#


6. How To Create New database With Template in PostgreSQL using psql?

postgres=# \l+ template1
                                                                List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |            Description
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+------------------------------------
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7969 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            |
(1 row)

postgres=#

postgres=# CREATE DATABASE test5 TEMPLATE template1;
CREATE DATABASE
postgres=#
postgres=# \l+ test5
                                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
-------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
 test5 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7969 kB | pg_default |
(1 row)

postgres=#


7. How to Create New Database With Binary Owner in PostgreSQL using Command Line?

[postgres@rac1 ~]$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[postgres@rac1 ~]$
[postgres@rac1 ~]$ which createdb
/bin/createdb
[postgres@rac1 ~]$
[postgres@rac1 ~]$ createdb test6
[postgres@rac1 ~]$

postgres=# \l+ test6
                                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access privileges |  Size   | Tablespace | Description
-------+----------+----------+-------------+-------------+-------------------+---------+------------+-------------
 test6 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                   | 7969 kB | pg_default |
(1 row)

postgres=#


8. How to Connect to Database in PostgreSQL using psql?

[postgres@rac1 ~]$ id
uid=26(postgres) gid=26(postgres) groups=26(postgres) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
[postgres@rac1 ~]$ psql
psql (13.2)
Type "help" for help.

postgres=#
postgres=#  SELECT current_database();
 current_database
------------------
 postgres <-----
(1 row)

postgres=#
postgres=# \c test2    <---- to connect to selective database
You are now connected to database "test2" as user "postgres".
test2=#
test2=# SELECT current_database();
 current_database
------------------
 test2 <----
(1 row)

test2=#

--- OR ---

[postgres@rac1 ~]$ psql orcl
psql (13.2)
Type "help" for help.

orcl=#  <---- Now we connected to database "orcl"


9. How to Find the Version of Database in PostgreSQL using psql?

test2=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

test2=#

-- OR --

[postgres@rac1 ~]$ psql
psql (13.2)  <-----
Type "help" for help.

postgres=#


10. How to Exit from PostgreSQL?

test2=# \q  <---- To quit from Postgres prompt.
[postgres@rac1 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/