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 after time 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 standby server), 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@xxxxxxxxxxxxx] Sent: Friday, December 04, 2015 6:26 PM To: Yelai, Ramkumar IN BLR STS; pgsql-general@xxxxxxxxxxxxxx Subject: 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@xxxxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
