How to do it…

It's now become a habit in many recipes in this chapter that they be presented in two forms: one with systemd and one without. This may look repetitive or boring, but it's unavoidable because the introduction of a new system does not automatically eliminate all existing alternatives, or migrate old installations to new ones.

Like before, the you can find further systemd details, including details on service unit names, in the previous recipe, Starting the database server manually, of this chapter.

A PostgreSQL server managed by systemd can be restarted in fast mode by issuing the following command:

sudo systemctl restart SERVICEUNIT

As before, change SERVICEUNIT to the appropriate service unit name—for example, postgresql@11-main for a PostgreSQL 10 cluster running in Debian or Ubuntu.

If systemd is not available, then you can use the following syntax:

pg_ctlcluster 11 main restart -m fast

The basic command to restart the server is the following one:

pg_ctl -D datadir restart -m fast

A restart is just a stop that's going to be followed by a start, so it sounds very simple. In many cases, it will be simple, but there are times when you'll need to restart the server while it is fairly busy. That's when we need to start performing some tricks to make that restart happen quicker.

First, the stop performed needs to be a fast stop. If we do a default or a smart stop, then the server will just wait for everyone to finish. If we do an immediate stop, then the server will crash, and we will need to crash-recover the data, which will be slower overall.

The running database server has a cache full of data blocks, many of which are dirty. PostgreSQL is similar to other database systems in that it does a shutdown checkpoint before it closes. This means that the startup that follows will be quick and clean. The more work the checkpoint has to do, the longer it will take to shut down.

The actual shutdown will happen much quicker if we issue a normal checkpoint first, as the shutdown checkpoint will have much less work to do. So, flush all the dirty shared buffers to disk with the following command, issued by a database superuser:

psql -c "CHECKPOINT"

The next consideration is that once we restart, the database cache will be empty again and will need to refresh itself. The larger the database cache, the longer it takes for the cache to get warm again, and 30 to 60 minutes is not uncommon before returning to full speed. So, what was a simple restart can actually have a large business impact if handled badly.