Re: Pgbasebackup help

On 12/3/15 12:59 AM, Yelai, Ramkumar IN BLR STS wrote:
> What I wanted to achieve is simple copy of Data folder. I can't shutdown the database during the backup and unable to use file system copy of data folder as it creates inconsistency and don't want to use pg_dump.
>
> Hence I decided to use Pg_basebackup for copying the base backup and don't want to replay the wal.

Replaying WAL is *not* optional. Each restore will have to replay at
least one WAL segment to become consistent, depending on write volume
during the backup.

> Anyway, pg_basebackup put checkpoint before copying the data folder. For me it is enough to restore till checkpoint.

This won't work - the database keeps running and making changes after
the checkpoint.

> I saw this link http://blog.veritech.io/2014/10/automated-backup-for-postgresql-cluster.html.
>
> In this link also, I have not seen they have enabled archive_mode. Archive mode is not necessary as long as you streaming the your wal files to pg_xlog.

These instructions are for bringing up a replica. Even if this is OK
for your purposes, it still would not get you a database at time T3.
You are supposing that because this method does not use archiving that

> Also, even if I have all wal files , how do I restore till time T3. I am analyzing at pgbackrest to know how to restore backup till time T3.

To restore to time T3 you would select a backup that ended *before* T3
then using point-in-time recovery to play forward to T3.

That should be explained pretty clearly in the user guide - if there's
something you don't understand then it would be helpful to know so I can
improve the guide.

--
-David
david(at)pgmasters(dot)net