Running pg_dump on a hot standby server?

Question

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?

Answer 1

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.

Answer 2

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().

Answer 3

Disclaimer: Only works up to PG12, PG13+ uses replication slots

If you pause the replication during the backup, (This is a good idea for preserve integrity and consistence), you may edit some lines in your master postgresql:

How much time is delaying your backup habitually. Keep sure that the master node preserve the whole x_log files necessary to resume the replication. You can do it in postgresql.conf editing

wal_keep_segments = 32      # in logfile segments, 16MB each; 0 disables

If you dont modify this and your backup process is too long, is probably that the master node erase the xlog files before send them to the slave.