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.

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?

share|improve this question
    
PostgreSQL version? You should be able to pg_basebackup from a replica on the latest version at least. –  Craig Ringer Jan 15 at 3:41
add comment

1 Answer

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.

share|improve this answer
add comment

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.