Infrastructure at your Service

I believe most people use pg_ctl nowadays to start, stop, or restart a PostgreSQL instance. You can do all these tasks without using pg_ctl, but you’ll notice in a minute that pg_ctl really is your friend. Even if you’re using pg_ctl already, is is good to know what happens in the background and this is the scope of this post.

For a moment, let’s imagine there is not pg_ctl: How can you start a PostgreSQL instance without it? The most simple solution to that is this:

[email protected]:/home/postgres/ [pgdev] postgres -D /u02/pgdata/DEV/
2021-05-14 15:00:47.309 CEST - 1 - 2793 -  - @ LOG:  redirecting log output to logging collector process
2021-05-14 15:00:47.309 CEST - 2 - 2793 -  - @ HINT:  Future log output will appear in directory "pg_log".

The downside with this is, that PostgreSQL will run in the foreground and shutdown as soon as the session ends or you abort the command:

[email protected]:/home/postgres/ [pgdev] CRTL-C
[email protected]:/home/postgres/ [pgdev] psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

Does this behavior make sense? It might not make sense when you start PostgreSQL manually, but it does make sense if:

  • You want to start PostgreSQL with systemd and you’re using Type=notify
  • You run PostgreSQL in a container. In this case you do not want to detach, as the container is considered dead when the postmaster dies.

There are probably other cases, but for me those are the two important ones.

If you want to start PostgreSQL in the background you would need to do something like this:

[email protected]:/home/postgres/ [pgdev] postgres -D /u02/pgdata/DEV/ >/var/tmp/pg.log 2>&1 &
[1] 2806
[email protected]:/home/postgres/ [pgdev] psql
psql (14devel)
Type "help" for help.

Nothing is wrong with that, but how do you stop PostgreSQL without using pg_ctl? The answer are signals. Way back in 2012 I’ve written a small post about signals and I think it is still worth reading. In very much the same way as written in this old post, you can control PostgreSQL by sending signals to the postmaster. For shutting down in “fast” mode, this is:

[email protected]:/home/postgres/ [pgdev] head -1 /u02/pgdata/DEV/postmaster.pid 
2806
[email protected]:/home/postgres/ [pgdev] kill -SIGINT 2806
[email protected]:/home/postgres/ [pgdev] psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?
[1]+  Done                    postgres -D /u02/pgdata/DEV/ > /var/tmp/pg.log 2>&1

The other signals you can use for shutting down, are SIGTERM(smart) and SIGQUIT(immediate). pg_ctl is doing nothing different in the background, but of course it is much easier to use the switches of pg_ctl, than to remember the signals all the time. Here is a fragment of the source code from pg_ctl, where you can see the mapping of the signals to the shutdown modes:

        if (strcmp(modeopt, "s") == 0 || strcmp(modeopt, "smart") == 0)
        {
                shutdown_mode = SMART_MODE;
                sig = SIGTERM;
        }
        else if (strcmp(modeopt, "f") == 0 || strcmp(modeopt, "fast") == 0)
        {
                shutdown_mode = FAST_MODE;
                sig = SIGINT;
        }
        else if (strcmp(modeopt, "i") == 0 || strcmp(modeopt, "immediate") == 0)
        {
                shutdown_mode = IMMEDIATE_MODE;
                sig = SIGQUIT;
        }

The same is true for starting up: pg_ctl is starting PostgreSQL in the background automatically and you do not need to care about this:

[email protected]:/home/postgres/ [pgdev] pg_ctl start
waiting for server to start....2021-05-14 15:18:07.159 CEST - 1 - 2856 -  - @ LOG:  redirecting log output to logging collector process
2021-05-14 15:18:07.159 CEST - 2 - 2856 -  - @ HINT:  Future log output will appear in directory "pg_log".
 done
server started

There is another signal you can use: SIGHUP. When you change a configuration in postgresql.conf, postgresql.auto.conf or pg_hba.conf you need to tell the postmaster about that change. Given the parameter can be changed online, or a change in pg_hba.conf (which can always be done online) you can tell the postmaster to re-read it’s configuration files:

[email protected]:/home/postgres/ [pgdev] head -1 /u02/pgdata/DEV/postmaster.pid 
2856
[email protected]:/home/postgres/ [pgdev] kill -SIGHUP 2856

Take a look at the log file and you’ll see the exact same messages, as if you would have used “pg_ctl reload”:

2021-05-14 15:21:02.623 CEST - 8 - 2856 -  - @ LOG:  received SIGHUP, reloading configuration files

Instead of using pg_terminate_backend to terminate a process, you can send a signal instead:

[email protected]:/home/postgres/ [pg14] ps -ef | grep psql
postgres  2908  2289  0 15:30 pts/1    00:00:00 psql
postgres  3086  2918  0 15:31 pts/0    00:00:00 grep psql
[email protected]:/home/postgres/ [pg14] kill -SIGTERM 2908
-- the session of the backend reports:
psql (14devel)
Type "help" for help.

[local]:5432 [email protected]=# Terminated

You see, pg_ctl and pg_terminate_backend are your friends, as it is more clear what happens if you can use something descriptive, instead of sending signals directly.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure