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 an environment with pair of postgresql servers set up to do WAL streaming replication.

I also have a test environment and periodically I want to snapshot the production DBMS and restore that onto the test server.

Right now I do:

ID=db-$(date +%F_%T)
pg_basebackup --format=tar --pgdata=- --xlog --gzip --label=$ID

and then restore that archive on the test server.

Unfortunately that makes things really slow down on the production server so I'd like to switch that to pulling the clone from the replication slave.

But I can't - the error I get is:

pg_basebackup: could not connect to server: FATAL: recovery is still in progress, can't accept WAL streaming connections

What do I need to do to accomplish my goal of not killing performance on the master when I need to clone it?


Running on Ubuntu 12.04 x86_64, postgresql server 9.1.9-0ubuntu12.04

share|improve this question

2 Answers 2

Chained replication is supported on PostgreSQL 9.2.

9.1 does not support chained replication. You will need to either take the base backup from the master, or:

  • Stop the replica
  • Copy the replica manually
  • Start the replica back up
share|improve this answer

i use rsync on the standby.

 1. A=$(psql -h <master> -tAc "select pg_start_backup('label')")
 2. rsync -rptgoDLK --delete $PGDATA/ <standby>:9.2/main --exclude postmaster.pid
 3. B=$(psql -h <master> -tAc "select pg_stop_backup()")
 4. With pg_xlogfile_name('$A') and pg_xlogfile_name('$B') i decide, which WAL-files i have to copy.
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.