Thread: You cannot do PITR with streaming replication - true?
I want to have a master-slave setup mainly for backups (but a hot read replica would be an added bonus). I have been using WAL replication earlier using Skytools walmgr utility. After upgrading to 9.1, I have explored streaming replication and it works nicely. However, here are some points which I need a confirmation about: 1. If we are using streaming replication, stopping and starting a slave server will always require a base backup from the primary. This is not the case with WAL replication if the WAL archives are being continuously shipped. (Base backups could be costly and slow if the involve hundreds of GB of data..) 2. In streaming replication, after the catchup phase, there are no WALs saved on the slave and therefore not available for replay. So, PITR will not be possible. Therefore, if what we need is a backup facility it's better to go with WAL replication (log shipping) instead of streaming replication because streaming replication is like RAID as Bruce Momjian says. Is that a correct conclusion? Or is it that I am missing something here? Is it an issue with walmgr3 rather than PostgreSQL? Thanks in advance. Regards, sayeed -- View this message in context: http://postgresql.1045698.n5.nabble.com/You-cannot-do-PITR-with-streaming-replication-true-tp5720050.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thu, Aug 16, 2012 at 11:11 AM, sayeed <sayeed.anjum@gmail.com> wrote: > I want to have a master-slave setup mainly for backups (but a hot read > replica would be an added bonus). > > I have been using WAL replication earlier using Skytools walmgr utility. > After upgrading to 9.1, I have explored streaming replication and it works > nicely. However, here are some points which I need a confirmation about: > > 1. If we are using streaming replication, stopping and starting a slave > server will always require a base backup from the primary. This is not the > case with WAL replication if the WAL archives are being continuously > shipped. (Base backups could be costly and slow if the involve hundreds of > GB of data..) No. As long as the WAL is still around on the master, the slave can be restarted. Look at the parameter wal_keep_segments to keep extra WAL around on the master for scenarios like this. Also, if you have a log archive available, you can put *both* streaming replication *and* a restore_command in your recovery.conf. That way, postgresql will use the archive to catch up, and then switch to streaming once it's there. > 2. In streaming replication, after the catchup phase, there are no WALs > saved on the slave and therefore not available for replay. So, PITR will not > be possible. Streaming replication doesn't keep a copy of the archive, that's correct. For that, you use archive_command. > Therefore, if what we need is a backup facility it's better to go with WAL > replication (log shipping) instead of streaming replication because > streaming replication is like RAID as Bruce Momjian says. Is that a correct > conclusion? One does not exclude the other. Probably the best thing in most scenarios is to use WAL archiving (primarily for backups) and streaming replication (for up-to-the-second replication) both. Backups can switch to get the very last segment from the replication slave if necessary, and replication can use the WAL archive if necessary - so they complement each other. > Or is it that I am missing something here? Is it an issue with walmgr3 > rather than PostgreSQL? I don't know walmgr3, but my guess is what you're missing is that replication isn't either streaming or file based, but it can be both at the same time. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Thanks Magnus for the really fast response. Your inputs have helped removed my doubts. I now went back and checked my slave server and the WALs files are being shipped and the streaming replication is also working. I will try a PITR test this weekend... I have set wal_keep_segments to 3 but maybe I will bump in to 30 or some such bigger number. On a related note, I am see the following log entries: 2012-08-14 11:28:34 IST LOG: streaming replication successfully connected to primary 2012-08-14 18:35:13 IST FATAL: could not receive data from WAL stream: SSL error: sslv3 alert unexpected message 2012-08-14 18:35:18,444 8691 INFO 000000010000002D0000002E: not found (ignored) 2012-08-14 18:35:18 IST LOG: record with zero length at 2D/2E488200 2012-08-14 18:35:18,551 8693 INFO 000000010000002D0000002E: not found (ignored) 2012-08-14 18:35:18 IST LOG: streaming replication successfully connected to primary 2012-08-16 12:56:07 IST FATAL: could not receive data from WAL stream: SSL error: sslv3 alert unexpected message 2012-08-16 12:56:13,086 8753 INFO 000000010000002D000000CB: not found (ignored) 2012-08-16 12:56:13 IST LOG: invalid magic number 0000 in log file 45, segment 203, offset 13246464 2012-08-16 12:56:13,192 8755 INFO 000000010000002D000000CB: not found (ignored) 2012-08-16 12:56:13 IST LOG: streaming replication successfully connected to primary My followup question is, do such errors affect the integrity of the slave? Is the slave in a consistent state after such errors occur? I notice that the corresponding WAL files are available on the slave though... -- View this message in context: http://postgresql.1045698.n5.nabble.com/You-cannot-do-PITR-with-streaming-replication-true-tp5720050p5720054.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 08/16/2012 02:45 AM, sayeed wrote: > Thanks Magnus for the really fast response. Given the nature of your questions, you may want to take a look at http://www.pgbarman.org/ Cheers, Steve