current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

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?

share|improve this question
    
Depends a lot on how well behaved your clients are when they lose their connections. Do they just retry politely? –  Craig Ringer Mar 16 '14 at 0:03
    
@CraigRinger it's for a website. –  JohnMerlino Mar 16 '14 at 15:15

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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