PostgreSQL Restart – Restarting PostgreSQL Quickly

postgresqlpostgresql-9.1

I'm reading over the postgresql admin cookbook. Some of the database server parameters require a restart and with a busy database, you may need to restart quickly. There are a number of things to do to speed up restart: 1) issue a normal checkpoint before shutdown checkpoint, 2) flush all dirty shared_buffers to
disk, 3) record the contents of the database cache prior
to shutdown and then warm the cache again immediately after restart. Therefore, the book seems to recommend:

psql -c "CHECKPOINT"
psql -c "select pg_cache_save('mycache')"
pg_ctl -D datadir -m immediate restart
psql -c "select pg_cache_warm('mycache')"

It also showed this method to:

pg_ctl -D datadir restart -m fast

Which one should I use when restarting a busy database which gets inserts very often? One of the above or is there a better method?

Best Answer

If your clients cope well with being disconnected, and don't lose data, you should probably use the first process: Force a checkpoint, then perform an immediate restart. That'll get the server back up and running faster than a fast restart will, at the cost of greater disruption to currently active clients.

Client applications will get errors when they next run an SQL statement. Well written applications will throw away the database connection and retry, so the user won't notice/see anything.

Less well written applications will report an error to the user.

Truly badly written applications will silently lose data (if they swallow exceptions) or perform only part of an operation (if it isn't properly using transactions). These clients will also have problems under other circumstances, and must be fixed.

Related Question