Re: Pgbasebackup help

On 11/30/15 6:28 AM, Yelai, Ramkumar IN BLR STS wrote:
> Hi All,
>
> I need some help in postgresql base backup.
>
> We are currently using multiple DBMS in our project and postgresql is
> one of them. Our private DBMS keeps the online data and postgresql keeps
> online as well as historical data.
>
> At present, we are doing the backup/restore process for our project. So
> we planned to use Pg_basebackup instead of pg_dump.
>
> Below is the backup steps.
>
> Time-T1 = Start the backup of private DBMS.
> Time-T2 = Finished the private backup DBMS.
> Time-T3 = Start the pg_basebackup.
> Time-T4 = End the Pg_basebackup.
>
> Here the requirement is we don’t want to restore the data after
> Time-T3. But when I followed this approach
> _https://opensourcedbms.com/dbms/point-in-time-recovery-pitr-using-pg_basebackup-with-postgresql-9-2/_,
> I am still getting the information’s archived from Time-T3 to TimeT4.
>
> Seems, WAL archives are holding all the transactions, which are happened
> between Time T3 – Time T4.

This is the expected behavior. The WAL generated during the backup must
be replayed to make the database consistent so T4 is the earliest you
can possibly stop recovery.

> Also, I don’t want enable archive_mode = on as it needs to maintain
> archives files.

As it turns out, archiving would be the solution to your problem. If
you were archiving you could restore a *previous* backup and then replay
WAL to exactly T3. There might be some jitter from clock differences
but it should have the desired effect.

I've done this to have development database reasonably in sync with each
other and in practice it works quite well.

> So I decided the enable only these parameters.
>
> Postgresql.conf
> ---------------------------------
> wal_level = hot_standby
> max_wal_senders = 1
>
> And added replication permissions for the current user in pg_hba.conf.
>
> It does, what I need it. In the backup I did not have the data between
> T3-T4.

There's not not enough detail here for me to make out what you are
doing. Is there still a pg_basebackup going on or are you just copying
files?

If archive_mode is not enabled then wal_level = hot_standby is likely
ignored.

What you end up with may start, but I doubt it's consistent. I don't
see how you could use pg_basebackup without archiving and end up at T3
with a consistent cluster.

Here's a tutorial I wrote for pgBackRest that covers point in time
recovery and goes into a bit more detail than the article you cited:

http://www.pgbackrest.org/user-guide.html#pitr

This method requires archive_mode to be enabled, which I believe is the
correct way to achieve the desired result.

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