DBA Hub

📋Steps in this guide1/14

PostgreSQL DBA – How to Create Database in PostgreSQL?

PostgreSQL DBA – How to Create Database in PostgreSQL?

postgresql configurationintermediate
by PostgreSQL
15 views
1

Overview

PostgreSQL DBA – How to Create Database in PostgreSQL? 0. How to Find Create Database Syntax in PostgreSQL? psql \h CREATE DATABASE

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[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=#
2

Section 2

1. How to List All Databases in PostgreSQL using psql? \l+ select oid,datname from pg_database; select * from pg_database;

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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;
3

Section 3

2. How To Create New database in PostgreSQL using psql? create database test; SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, datname FROM pg_database where datname='test'; test <----

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
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=#
4

Section 4

3. How To Create New database With Owner in PostgreSQL using psql? create user vishnu with password 'vishnu'; CREATE DATABASE test2 OWNER vishnu; \l+ test2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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=#
5

Section 5

test2 4. How To Create New database With Tablespace in PostgreSQL using psql? mkdir -p /u01/postgres/data/test3_tbs chown -R postgres:postgres /u01/postgres

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[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=#
6

Section 6

chmod -R 750 /u01/postgres CREATE TABLESPACE test3_tbs LOCATION '/u01/postgres/data/test3_tbs'; CREATE DATABASE test3 TABLESPACE test3_tbs; select datname from pg_database where datname='test3'; 5. How To Create New database With Encoding in PostgreSQL using psql?
7

Section 7

CREATE DATABASE test4 ENCODING 'UTF8'; \l+ test4 test4 6. How To Create New database With Template in PostgreSQL using psql?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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=#
8

Section 8

\l+ template1 CREATE DATABASE test5 TEMPLATE template1; \l+ test5 test5 7. How to Create New Database With Binary Owner in PostgreSQL using Command Line?

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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=#
9

Section 9

7. How to Create New Database With Binary Owner in PostgreSQL using Command Line? id which createdb createdb test6 \l+ test6

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[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=#
10

Section 10

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
[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"
11

Section 11

SELECT current_database(); \c test2 <---- to connect to selective database You are now connected to database "test2" as user "postgres". test2 <---- --- OR --- psql orcl
12

Section 12

orcl =# <---- Now we connected to database "orcl" 9. How to Find the Version of Database in PostgreSQL using psql? select version(); PostgreSQL 13.2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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=#
13

Section 13

-- OR -- (13.2) <----- 10. How to Exit from PostgreSQL? \q <---- To quit from Postgres prompt.

Code/Command (click line numbers to comment):

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

Section 14

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. 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.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!