Taking hot backup of slave node in postgres (master-slave config with repmgr)

Question

I have a postgresql setup with master-slave configuration using repmgr. I'm struggling with taking a consistent snapshot backup (pg_start_backup) of the slave node, this is due the fact that the slave is in recovery, hence read-only mode.

I need both the master and the slave snapshots to speedup the recovery process (if taken only on one node, in case of recovery the slave needs to be rebuilt from scratch).

Any advice of how to take a consistent snapshot of slave node? (Master is no problem).

Currently these are the approaches I'm checking:

1. shutting down slave node and take a cold snapshot backup.

2. pg_start_backup on master and hopefully it will reflect consistent state on slave (with the WAL shipping) - any confirmation on this one?

3. stop replication, take hot backup of slave using regular methods and then starting replication again - as far as I tested, not possible because as the replication is stopped, each node becomes independent and has its own timeline, thus require the nodes to resync in order to function again in master-slave config.

4. make a switch over before taking the backup - don't like this option, don't think is a correct approach to make a switch-over each time I want to take a backup.

Anything I missed? Have you implemented it using different methods?

• Craig Ringer: PostgreSQL version? You should be able to pg_basebackup from a replica on the latest version at least.

Answer 1

At least on 9.3, you can run pg_basebackup against a hot standby replica, and that's the approach I would recommend. To do this, you must have postgresql.conf set appropriately:

max_wal_senders = 2
hot_standby = on

You didn't specify your version. If you're on a version that can't do a pg_basebackup from a standby you're pretty much out of luck - you need to do a logical dump, dump from the master, or stop the replica for the backup.