Category Archives: PostgreSQL

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
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

PostgreSQL DBA – How to START/STOP PostgreSQL 13 ON RHEL 7

How to START/STOP PostgreSQL 13 ON RHEL 7

Table of Contents
___________________________________________________________________________________________________

0. Overview
1. How to check Postgres server status?
2. How to Stop Postgres server?
3. How to Start Postgres server?
4. How to Restart Postgres server?
5. How to Reload Postgres server?
___________________________________________________________________________________________________


0. Overview

As root user:

service postgresql-13.service status
service postgresql-13.service start
service postgresql-13.service restart
service postgresql-13.service reload
service postgresql-13.service stop

OR 

systemctl stop postgresql-13.service
systemctl start postgresql-13.service
systemctl restart postgresql-13.service
systemctl status postgresql-13.service
systemctl reload postgresql-13.service

As Postgres user use pg_ctl command

/usr/pgsql-13/bin/pg_ctl stop -D /var/lib/pgsql/13/data
/usr/pgsql-13/bin/pg_ctl start -D /var/lib/pgsql/13/data
/usr/pgsql-13/bin/pg_ctl restart -D /var/lib/pgsql/13/data
/usr/pgsql-13/bin/pg_ctl status -D /var/lib/pgsql/13/data
/usr/pgsql-13/bin/pg_ctl reload -D /var/lib/pgsql/13/data

Restart or reload is required when changes are made to the postgresql.conf file.

Reload simply reloads the configuration in-memory. Restart replaces the entire instance.


1. How to check Postgres server status?

[root@rac1 ~]# systemctl list-units|grep postgresql
  postgresql-13.service     loaded active running   PostgreSQL 13 database server
[root@rac1 ~]# 

---- OR -----

[root@rac1 ~]# service postgresql-13.service status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-03-15 17:57:05 +08; 53min ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 969 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 991 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           ├─ 991 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─1053 postgres: logger
           ├─1178 postgres: checkpointer
           ├─1179 postgres: background writer
           ├─1180 postgres: walwriter
           ├─1181 postgres: autovacuum launcher
           ├─1182 postgres: stats collector
           ├─1183 postgres: logical replication launcher
           └─2282 postgres: postgres postgres [local] idle

Mar 15 17:57:03 rac1.rajasekhar.com systemd[1]: Starting PostgreSQL 13 database server...
Mar 15 17:57:04 rac1.rajasekhar.com postmaster[991]: 2021-03-15 17:57:04.155 +08 [991] LOG:  redirecting log output to logging collector process
Mar 15 17:57:04 rac1.rajasekhar.com postmaster[991]: 2021-03-15 17:57:04.155 +08 [991] HINT:  Future log output will appear in directory "log".
Mar 15 17:57:05 rac1.rajasekhar.com systemd[1]: Started PostgreSQL 13 database server.
[root@rac1 ~]#

--- OR ---

[root@rac1 ~]# ps -ef | grep postgres
postgres   991     1  0 17:57 ?        00:00:00 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
postgres  1053   991  0 17:57 ?        00:00:00 postgres: logger
postgres  1178   991  0 17:57 ?        00:00:00 postgres: checkpointer
postgres  1179   991  0 17:57 ?        00:00:00 postgres: background writer
postgres  1180   991  0 17:57 ?        00:00:00 postgres: walwriter
postgres  1181   991  0 17:57 ?        00:00:00 postgres: autovacuum launcher
postgres  1182   991  0 17:57 ?        00:00:00 postgres: stats collector
postgres  1183   991  0 17:57 ?        00:00:00 postgres: logical replication launcher
root      2201  2143  0 17:58 pts/0    00:00:00 su - postgres
postgres  2202  2201  0 17:58 pts/0    00:00:00 -bash
postgres  2281  2202  0 17:58 pts/0    00:00:00 psql
postgres  2282   991  0 17:58 ?        00:00:00 postgres: postgres postgres [local] idle
root      5744  5275  0 18:55 pts/1    00:00:00 grep --color=auto postgres
[root@rac1 ~]#

OR

[postgres@rac1 ~]$ cd /usr/pgsql-13/bin/
[postgres@rac1 bin]$ ./pg_ctl status -D /var/lib/pgsql/13/data/
pg_ctl: server is running (PID: 991)
/usr/pgsql-13/bin/postgres "-D" "/var/lib/pgsql/13/data/"
[postgres@rac1 bin]$


2. How to Stop Postgres server?

[postgres@rac1 ~]$ cd /usr/pgsql-13/bin/
[postgres@rac1 bin]$ ./pg_ctl stop -D /var/lib/pgsql/13/data/
waiting for server to shut down.... done
server stopped
[postgres@rac1 bin]$

[root@rac1 ~]# ps -ef | grep postgres
root      6718  2143  0 19:07 pts/0    00:00:00 grep --color=auto postgres
[root@rac1 ~]#

--- OR ----

[root@rac1 ~]# service postgresql-13.service stop
Redirecting to /bin/systemctl stop postgresql-13.service
[root@rac1 ~]#
[root@rac1 ~]# service postgresql-13.service status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Mon 2021-03-15 19:07:03 +08; 5min ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 991 ExecStart=/usr/pgsql-13/bin/postmaster -D ${PGDATA} (code=exited, status=0/SUCCESS)
  Process: 969 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 991 (code=exited, status=0/SUCCESS)

Mar 15 17:57:03 rac1.rajasekhar.com systemd[1]: Starting PostgreSQL 13 database server...
Mar 15 17:57:04 rac1.rajasekhar.com postmaster[991]: 2021-03-15 17:57:04.155 +08 [991] LOG:  redirecting log output to logging collector process
Mar 15 17:57:04 rac1.rajasekhar.com postmaster[991]: 2021-03-15 17:57:04.155 +08 [991] HINT:  Future log output will appear in directory "log".
Mar 15 17:57:05 rac1.rajasekhar.com systemd[1]: Started PostgreSQL 13 database server.
[root@rac1 ~]#


3. How to Start Postgres server?

[postgres@rac1 ~]$ cd /usr/pgsql-13/bin/
[postgres@rac1 bin]$ ./pg_ctl start -D /var/lib/pgsql/13/data/
waiting for server to start....2021-03-15 19:14:17.815 +08 [7443] LOG:  redirecting log output to logging collector process
2021-03-15 19:14:17.815 +08 [7443] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@rac1 bin]$

--- OR ---- 

[root@rac1 ~]# service postgresql-13.service start
Redirecting to /bin/systemctl start postgresql-13.service
[root@rac1 ~]#
[root@rac1 ~]# service postgresql-13.service status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-03-15 19:15:14 +08; 3s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 7523 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 7529 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           ├─7529 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─7533 postgres: logger
           ├─7535 postgres: checkpointer
           ├─7536 postgres: background writer
           ├─7537 postgres: walwriter
           ├─7538 postgres: autovacuum launcher
           ├─7539 postgres: stats collector
           └─7540 postgres: logical replication launcher

Mar 15 19:15:14 rac1.rajasekhar.com systemd[1]: Starting PostgreSQL 13 database server...
Mar 15 19:15:14 rac1.rajasekhar.com postmaster[7529]: 2021-03-15 19:15:14.193 +08 [7529] LOG:  redirecting log output to logging collector process
Mar 15 19:15:14 rac1.rajasekhar.com postmaster[7529]: 2021-03-15 19:15:14.193 +08 [7529] HINT:  Future log output will appear in directory "log".
Mar 15 19:15:14 rac1.rajasekhar.com systemd[1]: Started PostgreSQL 13 database server.
[root@rac1 ~]#


4. How to Restart Postgres server?

-w, --wait             wait until operation completes (default)
  
[postgres@rac1 ~]$ cd /usr/pgsql-13/bin/
[postgres@rac1 bin]$ ./pg_ctl -w restart -D /var/lib/pgsql/13/data/
waiting for server to shut down.... done
server stopped
waiting for server to start....2021-03-15 19:22:58.920 +08 [8017] LOG:  redirecting log output to logging collector process
2021-03-15 19:22:58.920 +08 [8017] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@rac1 bin]$

--- OR ----

[root@rac1 ~]# service postgresql-13.service restart
Redirecting to /bin/systemctl restart postgresql-13.service
[root@rac1 ~]#
[root@rac1 ~]# service postgresql-13.service status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-03-15 19:52:26 +08; 3s ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 10439 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 10445 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           ├─10445 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─10449 postgres: logger
           ├─10451 postgres: checkpointer
           ├─10452 postgres: background writer
           ├─10453 postgres: walwriter
           ├─10454 postgres: autovacuum launcher
           ├─10455 postgres: stats collector
           └─10456 postgres: logical replication launcher

Mar 15 19:52:26 rac1.rajasekhar.com systemd[1]: Starting PostgreSQL 13 database server...
Mar 15 19:52:26 rac1.rajasekhar.com postmaster[10445]: 2021-03-15 19:52:26.856 +08 [10445] LOG:  redirecting log output to logging collector process
Mar 15 19:52:26 rac1.rajasekhar.com postmaster[10445]: 2021-03-15 19:52:26.856 +08 [10445] HINT:  Future log output will appear in directory "log".
Mar 15 19:52:26 rac1.rajasekhar.com systemd[1]: Started PostgreSQL 13 database server.
[root@rac1 ~]#


5. How to Reload Postgres server?

[postgres@rac1 bin]$ ./pg_ctl reload -D /var/lib/pgsql/13/data
server signaled
[postgres@rac1 bin]$

--- OR --- 

[root@rac1 ~]# service postgresql-13.service reload
Redirecting to /bin/systemctl reload postgresql-13.service
[root@rac1 ~]#
[root@rac1 ~]# service postgresql-13.service status
Redirecting to /bin/systemctl status postgresql-13.service
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2021-03-15 19:52:26 +08; 11min ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 11218 ExecReload=/bin/kill -HUP $MAINPID (code=exited, status=0/SUCCESS)
  Process: 10439 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 10445 (postmaster)
   CGroup: /system.slice/postgresql-13.service
           ├─10445 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─10449 postgres: logger
           ├─10451 postgres: checkpointer
           ├─10452 postgres: background writer
           ├─10453 postgres: walwriter
           ├─10454 postgres: autovacuum launcher
           ├─10455 postgres: stats collector
           └─10456 postgres: logical replication launcher

Mar 15 19:52:26 rac1.rajasekhar.com systemd[1]: Starting PostgreSQL 13 database server...
Mar 15 19:52:26 rac1.rajasekhar.com postmaster[10445]: 2021-03-15 19:52:26.856 +08 [10445] LOG:  redirecting log output to logging collector process
Mar 15 19:52:26 rac1.rajasekhar.com postmaster[10445]: 2021-03-15 19:52:26.856 +08 [10445] HINT:  Future log output will appear in directory "log".
Mar 15 19:52:26 rac1.rajasekhar.com systemd[1]: Started PostgreSQL 13 database server.
Mar 15 19:53:40 rac1.rajasekhar.com systemd[1]: Reloaded PostgreSQL 13 database server.
Mar 15 20:03:49 rac1.rajasekhar.com systemd[1]: Reloaded PostgreSQL 13 database server.
[root@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
WhatsApp : +65-94693551
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/