Re: Pgbasebackup help - Mailing list pgsql-general

Thanks David,

This helped me to understand the WAL importance.

Finally, we decided to use "stream" option to copy the WAL file during the backup as mentioned in the help.

Enabled this options in postgres.conf

wal_level = hot_standby
max_wal_senders = 2

Though I get the information, which are archived during the backup process. I myself deleting archived records the
aftertime T3, while restoring the backup by using our sql procedures.  

Since I am not using archive recovery or standy replica (I am restoring the data folder in to the same server not in
standbyserver), I can't use the recovery.conf options to recover till Time T3.  Hence I forcefully deleted as mentioned
earlier.

Please let me know If any way to replay the WAL till Time T3 then I am interested to use it.

Thanks once again.

Regards,
Ramkumar.

-----Original Message-----
From: David Steele [mailto:david@pgmasters.net]
Sent: Friday, December 04, 2015 6:26 PM
To: Yelai, Ramkumar IN BLR STS; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
usefile 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,
dependingon 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
streamingthe 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
databaseat 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
restorebackup 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
forwardto T3. 

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

--
-David
david@pgmasters.net