Re: You cannot do PITR with streaming replication - true?

On Thu, Aug 16, 2012 at 11:11 AM, sayeed <sayeed(dot)anjum(at)gmail(dot)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/