How to Stop PostgreSQL Database Server Manually in Linux

Introduction

Basically, it is quite easy to stop the PostgreSQL database server. But depends on the process for starting the PostgreSQL database server, it will decide how to stop it. There are several way for stopping PostgreSQL database server. Depending on how to start the service. Either manually by using ‘postgres’ or ‘pg_ctl’. Starting the PostgreSQL database server using ‘postgres’ exist in the article with the title of ‘How to Start PostgreSQL Database Server manually using postgres command in Linux’ in this link. Another one, starting it using ‘pg_ctl’ exist in the article with the title of ‘How to Start PostgreSQL Database Server manually in Linux’ in this link.

 

Stopping PostgreSQL Database Server Manually in Linux

Actually, manually stopping PostgreSQL database server in this context means directly execute the command which is directly relate to it to stop. In this context, it either by using ‘postgres’ or ‘pg_ctl’. So, whenever the service of PostgreSQL database server is active and running by executing ‘postgres’ or ‘pg_ctl’ command, just directly stop that command to stop the service.

Stopping PostgreSQL Database Server running using ‘postgres’ using Ctrl+Z

Running PostgreSQL database server using ‘postgres’ basically will persist the command line interface and block any other commands after to be executed. It exist as in the following execution :

[pgsql@10 ~]$ /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data/
2021-06-17 09:37:52.777 EDT [89289] LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2021-06-17 09:37:52.778 EDT [89289] LOG:  listening on IPv6 address "::1", port 5432
2021-06-17 09:37:52.778 EDT [89289] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-06-17 09:37:52.929 EDT [89289] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-06-17 09:37:53.141 EDT [89290] LOG:  database system was shut down at 2021-06-17 09:30:49 EDT
2021-06-17 09:37:53.147 EDT [89289] LOG:  database system is ready to accept connections

Stopping the service of the PostgreSQL database server which starts using the default postgres command execution as in the above one is very easy. Just type Ctrl+Z to terminate the process as follows :

[pgsql@10 ~]$ /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data/
2021-06-17 09:37:52.777 EDT [89289] LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2021-06-17 09:37:52.778 EDT [89289] LOG:  listening on IPv6 address "::1", port 5432
2021-06-17 09:37:52.778 EDT [89289] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-06-17 09:37:52.929 EDT [89289] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-06-17 09:37:53.141 EDT [89290] LOG:  database system was shut down at 2021-06-17 09:30:49 EDT
2021-06-17 09:37:53.147 EDT [89289] LOG:  database system is ready to accept connections
^Z
[1]+  Stopped                 /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data/
[pgsql@10 ~]$

Stopping PostgreSQL Database Server running using ‘postgres’ using kill command

Remember that the above way to start and to stop the PostgreSQL database server is not the best practice to do it. Because the process is not terminated completely, It is still running in the background. So, after stopping by typing Ctrl+Z, the login process will not possible anymore, but the process is still exist. Just check the process and try to login in :

[pgsql@10 ~]$ ps -ef | grep postgres
pgsql      89600   89569  0 10:58 pts/0    00:00:00 /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data/
pgsql      89602   89600  0 10:58 ?        00:00:00 postgres: checkpointer
pgsql      89603   89600  0 10:58 ?        00:00:00 postgres: background writer
pgsql      89604   89600  0 10:58 ?        00:00:00 postgres: walwriter
pgsql      89605   89600  0 10:58 ?        00:00:00 postgres: autovacuum launcher
pgsql      89606   89600  0 10:58 ?        00:00:00 postgres: stats collector
pgsql      89607   89600  0 10:58 ?        00:00:00 postgres: logical replication launcher
pgsql      89609   89569  0 10:58 pts/0    00:00:00 grep --color=auto postgres
[pgsql@10 ~]$

Basically, the above command for checking the process of running PostgreSQL database server. It is directly continue on the process after stopping the PostgreSQL database server. In the end, although the process is stop, the process is still running in the background. Although the process is stop, the worst part is that the login process is stuck and cannot move forward into the login prompt as follows :

[pgsql@10 ~]$ psql -Upgsql postgres

So, basically the login process hangs without nothing further although it is listed in the current available process :

[pgsql@10 ~]$ ps -ef | grep postgres
pgsql      91519   89569  0 22:14 pts/0    00:00:00 /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data/
pgsql      91521   91519  0 22:14 ?        00:00:00 postgres: checkpointer
pgsql      91522   91519  0 22:14 ?        00:00:00 postgres: background writer
pgsql      91523   91519  0 22:14 ?        00:00:00 postgres: walwriter
pgsql      91524   91519  0 22:14 ?        00:00:00 postgres: autovacuum launcher
pgsql      91525   91519  0 22:14 ?        00:00:00 postgres: stats collector
pgsql      91526   91519  0 22:14 ?        00:00:00 postgres: logical replication launcher
pgsql      91527   89726  0 22:15 pts/2    00:00:00 psql -Upgsql postgres
pgsql      91529   89569  0 22:15 pts/0    00:00:00 grep --color=auto postgres
[pgsql@10 ~]$

Two lines from the bottop inform that there is a login process as follows :

pgsql      91527   89726  0 22:15 pts/2    00:00:00 psql -Upgsql postgres

So, it is not an effective way to stop the process. After typing Ctrl+Z, there is an additional process for stopping the PostgreSQL database server completely. It is to run the ‘kill’ command as follows :

[pgsql@10 ~]$ kill -9 91519

After checking the list of process again, the PostgreSQL database server is already stop completely. It is not running anymore s the following command execution for checking the process :

[pgsql@10 ~]$ ps -ef | grep postgres
pgsql      91553   89569  0 22:23 pts/0    00:00:00 grep --color=auto postgres
[1]+  Killed                  /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data/
[pgsql@10 ~]$

Furthermore, the login process using psql command is suddenly terminated as follows :

[pgsql@10 ~]$ psql -Upgsql postgres
psql: error: could not connect to server: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
[pgsql@10 ~]$

 

Stopping PostgreSQL Database Server running using ‘pg_ctl’

Actually, running PostgreSQL database server using ‘pg_ctl’ by default is more practical. It will redirect the process back to the command line interface after it finish. It exist as in the following execution :

[pgsql@10 ~]$ /opt/postgresql-12.0/app/bin/pg_ctl -D /opt/postgresql-12.0/data/ start
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2021-06-17 23:16:04.411 EDT [91707] LOG:  starting PostgreSQL 12.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2021-06-17 23:16:04.411 EDT [91707] LOG:  listening on IPv6 address "::1", port 5432
2021-06-17 23:16:04.411 EDT [91707] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-06-17 23:16:04.416 EDT [91707] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-06-17 23:16:04.433 EDT [91708] LOG:  database system was interrupted; last known up at 2021-06-17 22:15:00 EDT
2021-06-17 23:16:05.253 EDT [91708] LOG:  database system was not properly shut down; automatic recovery in progress
2021-06-17 23:16:05.256 EDT [91708] LOG:  redo starts at 0/16453D0
2021-06-17 23:16:05.256 EDT [91708] LOG:  invalid record length at 0/1645408: wanted 24, got 0
2021-06-17 23:16:05.256 EDT [91708] LOG:  redo done at 0/16453D0
2021-06-17 23:16:05.273 EDT [91707] LOG:  database system is ready to accept connections
 done
server started
[pgsql@10 ~]$

Run the following command to check the process as follows :

[pgsql@10 ~]$ ps -ef | grep postgres
pgsql      91730       1  0 23:18 ?        00:00:00 /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data
pgsql      91732   91730  0 23:18 ?        00:00:00 postgres: checkpointer
pgsql      91733   91730  0 23:18 ?        00:00:00 postgres: background writer
pgsql      91734   91730  0 23:18 ?        00:00:00 postgres: walwriter
pgsql      91735   91730  0 23:18 ?        00:00:00 postgres: autovacuum launcher
pgsql      91736   91730  0 23:18 ?        00:00:00 postgres: stats collector
pgsql      91737   91730  0 23:18 ?        00:00:00 postgres: logical replication launcher
pgsql      91739   89569  0 23:18 pts/0    00:00:00 grep --color=auto postgres
[pgsql@10 ~]$

Furthermore, the login process to PostgreSQL Command Console Interface also is a success as follows :

[pgsql@10 ~]$ psql -Upgsql postgres
psql (12.0)
Type "help" for help.
postgres=#

The login process to the PostgreSQL Command Console Interface also available in the listing of the process as follows :

[pgsql@10 ~]$ ps -ef | grep postgres
pgsql      91730       1  0 23:18 ?        00:00:00 /opt/postgresql-12.0/app/bin/postgres -D /opt/postgresql-12.0/data
pgsql      91732   91730  0 23:18 ?        00:00:00 postgres: checkpointer
pgsql      91733   91730  0 23:18 ?        00:00:00 postgres: background writer
pgsql      91734   91730  0 23:18 ?        00:00:00 postgres: walwriter
pgsql      91735   91730  0 23:18 ?        00:00:00 postgres: autovacuum launcher
pgsql      91736   91730  0 23:18 ?        00:00:00 postgres: stats collector
pgsql      91737   91730  0 23:18 ?        00:00:00 postgres: logical replication launcher
pgsql      91740   89569  0 23:19 pts/0    00:00:00 psql -Upgsql postgres
pgsql      91741   91730  0 23:19 ?        00:00:00 postgres: pgsql postgres [local] idle
pgsql      91743   89726  0 23:19 pts/2    00:00:00 grep --color=auto postgres
[pgsql@10 ~]$

This is actually the correct and the best practice for starting PostgreSQL database server manually. So, in order to stop it, just execute ‘pg_ctl’ with different operation parameter. Just add ‘stop’ parameter as follows :

[pgsql@10 ~]$ /opt/postgresql-12.0/app/bin/pg_ctl -D /opt/postgresql-12.0/data/ stop
waiting for server to shut down....2021-06-17 23:17:40.242 EDT [91707] LOG:  received fast shutdown request
2021-06-17 23:17:40.246 EDT [91707] LOG:  aborting any active transactions
2021-06-17 23:17:40.248 EDT [91707] LOG:  background worker "logical replication launcher" (PID 91714) exited with exit code 1
2021-06-17 23:17:40.248 EDT [91709] LOG:  shutting down
2021-06-17 23:17:40.266 EDT [91707] LOG:  database system is shut down
 done
server stopped
[pgsql@10 ~]$

In order to check if the process is already stop, just execute the following command :

[pgsql@10 ~]$ ps -ef | grep postgres
pgsql      91760   89569  0 23:22 pts/0    00:00:00 grep --color=auto postgres
[pgsql@10 ~]$