On Thu, Aug 16, 2012 at 11:11 AM, sayeed <sayeed.anjum@xxxxxxxxx> 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
