Viswamitra's blog

I feel PostgreSQL is  very simple and easy to use database. Today I am learning the methods of stopping the database using the pg_ctl and kill command. Both pg_ctl and kill command work in the same manner to stop the database. 

There are three methods to stop the postgres database.

Before learning the methods , we need to know the command and options.

This is postgres command used to start and stop the database. This command also have many other features. Below command options are useful for stopping the database.

-m : This option is used to provide the “shutdown signal” to the database server. We can also give only first letter of the signal to this option.
-D : Cluster data directory location. If we export PGDATA variable , we no need this option. I have already set the variable so I am not using this option.

This is linux command used to stop the process at OS level. 

-s : Specify the “signal type”

Both the commands use the signal type. Below are the type of signals we pass to the commands to stop the database. 

This is equal to SIGTERM of kill command. The server disallows new connections, but lets existing sessions end their work normally. If the server is in online backup mode, it additionally waits until online backup mode is no longer active. While the backup mode is active , server allow connections for super user so that he can kill the backup operation.

[postgres@node1 data]$ pg_ctl -m smart stop
waiting for server to shut down…. done
server stopped

(OR)

[postgres@node1 data]$ kill -s SIGTERM `head -1 /opt/PostgreSQL/9.5/data/postmaster.pid`
[postgres@node1 data]$ echo $?
0

We can observe the below info in the log file of postgres..

2016-02-06 17:57:05 IST LOG:  received smart shutdown request
2016-02-06 17:57:05 IST LOG:  autovacuum launcher shutting down
2016-02-06 17:57:05 IST LOG:  shutting down
2016-02-06 17:57:05 IST LOG:  database system is shut down

NOTE : Mode is case sensitive , need give mode only in small letters.

fast : (SIGINT for kill command)

This is the Fast Shutdown mode. The server disallows new connections and sends all existing server processes SIGTERM, which will cause them to abort their current transactions and exit promptly. It then waits for all server processes to exit and finally shuts down. If the server is in online backup mode, backup mode will be terminated, rendering the backup useless.

[postgres@node1 data]$ pg_ctl -m fast stop
waiting for server to shut down…. done
server stopped

(OR)

[postgres@node1 data]$ kill -s SIGINT `head -1 /opt/PostgreSQL/9.5/data/postmaster.pid`
[postgres@node1 data]$ echo $?
0

We can observe the below info in the log file of postgres..

2016-02-06 18:25:11 IST LOG:  received fast shutdown request
2016-02-06 18:25:11 IST LOG:  aborting any active transactions
2016-02-06 18:25:11 IST LOG:  autovacuum launcher shutting down
2016-02-06 18:25:11 IST LOG:  shutting down
2016-02-06 18:25:11 IST LOG:  database system is shut down

immediate : (SIGQUIT for kill command)

This will terminate the server processes abruptly which causes the recovery from WAL logs at the time of startup.

[postgres@node1 data]$ pg_ctl -m immediate stop
waiting for server to shut down…. done
server stopped

(OR)

[postgres@node1 data]$ kill -s SIGQUIT `head -1 /opt/PostgreSQL/9.5/data/postmaster.pid`
[postgres@node1 data]$ echo $?
0

We can observe the below info in the log file of postgres.

2016-02-06 18:33:02 IST LOG:  received immediate shutdown request
2016-02-06 18:33:02 IST WARNING:  terminating connection because of crash of another server process
2016-02-06 18:33:02 IST DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2016-02-06 18:33:02 IST HINT:  In a moment you should be able to reconnect to the database and repeat your command.

References :

http://www.postgresql.org/docs/9.5/interactive/server-shutdown.html 

http://www.cyberciti.biz/faq/unix-kill-command-examples/