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.

Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask.

I would like to run a nightly backup job (via pg_dumpall) from a hot standby server running streaming replication, to avoid putting that load on the primary. I've only seen mention of some gotchas people have run into, e.g. here and here, but very little guidance. It's okay if the backup lags behind the primary slightly, as long as it's consistent (which it should be).

My questions are:

  1. Do I really want to do this, or should the backup be done on the primary server? Why?

  2. When doing a dump on the standby, what settings do I need and procedure should I use to do that correctly? e.g. must I stop replication for the duration of the backup?

share|improve this question
    
I would expect that if your replication keeps the standby database in a consistent state, your backup will be consistent. As the pg_dump documentation states: "It makes consistent backups even if the database is being used concurrently." pg_dumpall runs the former for each database. –  dezso Dec 17 '12 at 19:43

2 Answers 2

up vote 5 down vote accepted

AFAIK, running pg_dump on a hot standby is one of the major things standbys are useful for. It's perfectly safe, though it isn't perfectly reliable - the dumps can fail if the standby aborts the transaction when it's falling too far behind the master.

The only thing you really need to watch is to make sure the standby is current and is keeping up. If the standby lost its connection to master and fell too far behind, you don't want to be merrily backing up a three week out-of-date standby.

You will need to allow the standby to fall quite far behind the master during the backup, since it'll otherwise have to cancel your pg_dump transaction in order to continue replaying WAL. See the documentation on hot standby, particularly the "handling query conflicts" section, and the max_standby_archive_delay and max_standby_streaming_delay parameters.

Note that the master must be willing to keep enough WAL archives to allow the slave to catch up again.

share|improve this answer
  1. We do backup on standby, it is perfectly fine.
  2. To avoid cancelled statement conflict during backup on standby system, you need to pause replication on standby using SELECT pg_xlog_replay_pause();, then run your backup, once it is finished run SELECT pg_xlog_replay_resume(); to resume replication. Keep in mind that running above commands will cause recovery lag on the slave, which might be quite large, depending on you database size. Also, take into account the space WAL segments will take, as they will not be replayed on slave during the pause.

You may find some other useful administractive functions in documentation. For instance, check if the server is actually in recovery, prior to pausing it: SELECT pg_is_in_recovery().

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.