Thread: Streaming replication failed to start scenarios
Hi all, I have been testing streaming replication in windows with postgres 9.1.1. For few scenario's I haven't found a solution. Please advice me. 1. Precautions before promoting standby server to primary manually considering the dead primary server ?? 2. How could we ensure the standby has received all transactions sent by primary till the point primary server is dead. (Meaning the dead primary and standby server are exactly same, so that the dead primary comes back it can be turned to standby without any issues). 3. When the dead primary is switchedto standby the streaming is not happening due to current_wal_location is ahead in the standby server is ahead of wal_sent_location. In this case how can I start streaming without taking a fresh base backup from current primary ?? 4. When the dead primary comes back the DB still accepts data and it goes to out of sync with the current primary and streaming won't start. Is there any solution for this case ?? Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
chinnaobi wrote: > I have been testing streaming replication in windows with postgres 9.1.1. > For few scenario's I haven't found a solution. Please advice me. > > 1. Precautions before promoting standby server to primary manually > considering the dead primary server ?? You don't have to take any precautions; the standby can be promoted as soon as "consistent recovery state reached" appears in the log. > 2. How could we ensure the standby has received all transactions sent by > primary till the point primary server is dead. (Meaning the dead primary and > standby server are exactly same, so that the dead primary comes back it can > be turned to standby without any issues). If the primary is truy dead, there is no way (unless you use synchronous replication, then it is always the case). You can use pg_last_xlog_receive_location() on the standby to see the last replayed transaction ID and pg_last_xact_replay_timestamp() for the timestamp. If the primary is still there, you can use txid_current() to get the current transaction ID. > 3. When the dead primary is switchedto standby the streaming is not > happening due to current_wal_location is ahead in the standby server is > ahead of wal_sent_location. In this case how can I start streaming without > taking a fresh base backup from current primary ?? The parenthesis in your previous question and this question suggest that you missed out on one thing: When the standby is promoted, it starts a new time line, so it is on a different time line from the old master. The old primary cannot be turned to a standby without a new base backup (possibly via rsync to speed up things). This time line switch is a mechanism that prevents precisely the problems you are anticipating. > 4. When the dead primary comes back the DB still accepts data and it goes to > out of sync with the current primary and streaming won't start. Is there any > solution for this case ?? You should lock out connections to the old primary, perhaps via pg_hba.conf. Yours, Laurenz Albe
Hey Laurenz Albe, Thank you for the quick reply.. > 3. When the dead primary is switchedto standby the streaming is not > happening due to current_wal_location is ahead in the standby server is > ahead of wal_sent_location. In this case how can I start streaming without > taking a fresh base backup from current primary ?? >>The parenthesis in your previous question and this question suggest >>that you missed out on one thing: >>When the standby is promoted, it starts a new time line, so it >>is on a different time line from the old master. The old >>primary cannot be turned to a standby without a new base backup >>(possibly via rsync to speed up things). >>This time line switch is a mechanism that prevents precisely >>the problems you are anticipating. But taking 200 GB to 500 GB DB base backup is impossible for me. Is there any other solution ?? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519p5728557.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
chinnaobi wrote: >> When the standby is promoted, it starts a new time line, so it >> is on a different time line from the old master. The old >> primary cannot be turned to a standby without a new base backup >> (possibly via rsync to speed up things). > But taking 200 GB to 500 GB DB base backup is impossible for me. Is there > any other solution ?? But you must take backups anyway, right? Using rsync should make the backup much faster if nothing much has happened since failover. Yours, Laurenz Albe
Hi Laurenz Albe, I have tested using cygwin rsync in windows 2008 R2, just after restart the server. for 10 GB it took nearly 5 minutes to sync, for 50 GB it took nearly 30 minutes, -- too long Though there were no big changes. My requirement is something less than 5 minutes. I am doing high availability. In my setup there is WAL archiving enabled as well. Is there any way I can speedup the configuring dead primary server to standby ?? Regards, Reddy -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519p5729302.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
chinnaobi wrote: > I have tested using cygwin rsync in windows 2008 R2, just after restart the > server. > > for 10 GB it took nearly 5 minutes to sync, > for 50 GB it took nearly 30 minutes, -- too long Though there were no big > changes. > > My requirement is something less than 5 minutes. I am doing high > availability. In my setup there is WAL archiving enabled as well. > > Is there any way I can speedup the configuring dead primary server to > standby ?? Faster network, faster disks :^( Why is it time critical to build a new standby? Yours, Laurenz Albe
Well.. There should be a standby server ready to server as a primary if the current primary goes down right ?? But the dead primary is yet in the mode of configuring stage or probably failed to be a standby server. In my high availability cluster there are only two servers. --Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519p5729310.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi Laurenz Albe, Correct me If am wrong in doing the high availability for postgresql with two servers win windows postgres 9.1 S1 -Primary S2 -Standby S1 -dead, S2- becomes primary S1-comes back and This should be turned to a new standby --Am I wrong ??--This step is most time taking one, Which I am facing ?? -Reddy. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Streaming-replication-failed-to-start-scenarios-tp5728519p5729399.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Oct 22, 2012, at 6:57 AM, chinnaobi wrote: > Hi Laurenz Albe, > > I have tested using cygwin rsync in windows 2008 R2, just after restart the > server. > > for 10 GB it took nearly 5 minutes to sync, > for 50 GB it took nearly 30 minutes, -- too long Though there were no big > changes. > > My requirement is something less than 5 minutes. I am doing high > availability. In my setup there is WAL archiving enabled as well. > > Is there any way I can speedup the configuring dead primary server to > standby ?? If it's a planned switch, and not a crash, take a look at this thread: http://archives.postgresql.org/pgsql-general/2012-08/msg00083.php If it's a crash, then the only way to bring your crashed node back up as a slave to your new master is with another fullcopy from that master node.