Faster shutdowns with checkpoint

Have you ever restarted your PostgreSQL cluster and wondered why it's taking so long?  The cause of this is generally one of these:

• You didn't include the "-m fast" flag (v9.4 and earlier), and sessions are still connected
• A large update query is rolling back
• There are a large number of dirty blocks, which are not yet written to disk

You can cover off the first two by:

• Using "pg_ctl -m fast"
• Checking in pg_stat_activity for long-running queries

But to ensure you minimise your downtime when restarting a PostgreSQL cluster, then consider forcing a "checkpoint" beforehand.

The normal process of a shutdown is:

1. Stop any new connections
2. Disconnect any existing connections, and rollback transactions (if "-m fast" is used)
3. Write all dirty blocks to disk
4. Shutdown the database cleanly, and restart

The 3rd step can sometimes take a long time if you've got a lot of dirty buffers.  Maybe you've got a very long checkpoint_timeout set, or possibly your server is just very busy.

If you run a manual "CHECKPOINT" immediately prior to running the shutdown, then you can sometimes drastically shorten the time a restart takes and keep your users happy:

postgres@localhost[default:5432] ~/$ psql

psql (10.1)

Type "help" for help.

postgres=# CHECKPOINT;

CHECKPOINT

postgres=# \q

postgres@localhost[default:5432] ~/$ pg_ctl restart -m fast

waiting for server to shut down.... done

server stopped

waiting for server to start....LOG:  listening on IPv4 address "0.0.0.0", port 5432

LOG:  listening on IPv6 address "::", port 5432

LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"

LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

LOG:  redirecting log output to logging collector process

HINT:  Future log output will appear in directory "log".

 done

server started


You can combine this all into a single step by running:

postgres@localhost[default:5432] ~/$ psql -c CHECKPOINT && pg_ctl restart -m fast
CHECKPOINT
waiting for server to shut down.... done
server stopped
waiting for server to start....2018-01-11 09:53:21.419 GMT [8919] LOG:  listening on IPv4 address "0.0.0.0", port 5432
LOG:  listening on IPv6 address "::", port 5432
LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
LOG:  redirecting log output to logging collector process
HINT:  Future log output will appear in directory "log".
 done

server started