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/