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.

Context:

Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the master. The standby's recovery.conf doesn't contain a 'restore_command' entry (for restoring from WAL journal files), but does contain a 'primary_conninfo' string (for Streaming Replication).

Question:

If I start the standby again after a day of changes on the master. Will it "catch up" (eventually come into a state which mirrors the master) using only Streaming Replication? Or do I have to enable WAL file archiving and let it apply files archived during the outage to ensure currency?

I've checked the WAL archiving/streaming replication doc here, and it says that you don't have to enable both WAL archiving and Streaming Replication, but it is unclear whether or not catch-up will happen without WAL file archiving being enabled.

Thanks!

share|improve this question

migrated from stackoverflow.com Jan 11 '12 at 19:59

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 8 down vote accepted

Yes, it will catch up, using streaming only, if (and only if), the number of WAL segments generated since the last update on the standby is less than the value of wal_keep_segments in postgresql.conf. This is covered in this section of the documentation: Replication

share|improve this answer
1  
This answer is correct, but it highlights the problem. If you do ever pass wal_keep_segments, your replication is dead. Setting up file-based replication is not optional if you want a system that will survive a long disconnection from the master and catch back up. –  Greg Smith Sep 15 '12 at 6:48

No, I set up an instance of streaming replication and it fell out of sync somehow, I was unable to get it working again until I did a manual rsync of the WAL archives.

share|improve this answer

at standby node,you can set restore_command on recovery.conf and then copy masters pg_xlog (which missing on standby) files to folder which restore_command points. You can easily find which xlog files missing by starting startup node and typing

ps aux | grep postgres

you will see there "waiting for 000000020000005200000025" or something like this, that tells you which pg_xlog you should start copying from master to standby's restore_command path.

if you enable wal_archiving, it will start archive from the moment you set-up.

share|improve this answer
    
I understand that using file-based WAL archiving and a giving the standby a restore_command to load WAL files, I can ensure that it catches up. That's not my question, though; I want to know whether or not the standby will catch up if I use only streaming replication (no WAL file shipping, just the replication stream as specified in 'primary_conninfo'). –  Zac B Jan 13 '12 at 14:48
    
no. postgres doesnt do that. you should copy log files if there is a delay on your replication. –  sftsz Jan 16 '12 at 11:51

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.