Server Control - PostgreSQL 9 Administration - page 73

A restart is just a stop 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 pulling some tricks to make that restart happen faster.
First, the stop performed needs to be a fast stop. If we do a default or "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 them 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 faster if we issue a normal checkpoint first, as the
shutdown checkpoint will have much less work to do. So, flush all 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.
I've written a utility named pg_cacheutils to record the contents of the database cache prior
to shutdown. This can then be used to prime or warm the cache again immediately after restart.
psql -c "select pg_cache_save('mycache')"
Then, you can issue a database server stop using immediate mode, so that we stop quickly
and start up again cleanly as follows:.
pg_ctl -D datadir -m immediate restart
One we're up, we can connect and warm the cache again as follows:
psql -c "select pg_cache_warm('mycache')"
It's not magic, so it will still take some time to be at full speed.
How it works...
pg_cache_save() will save the set of disk blocks in a table named mycache . That allows
you to have several sets of caches for various purposes if you need them. If you don't supply
a name, it will just use saved_cache .