5

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?

2
  • Depends a lot on how well behaved your clients are when they lose their connections. Do they just retry politely? Commented Mar 16, 2014 at 0:03
  • @CraigRinger it's for a website. Commented Mar 16, 2014 at 15:15

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.