Re: Pgbasebackup help

HI,

Thanks David.

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.

Anyway, pg_basebackup put checkpoint before copying the data folder. For me it is enough to restore till 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.

By using this option in pg_basebackup and backup will become standalone backup.

--xlog-method = stream
"Includes the required transaction log files (WAL files) in the backup. This will include all transaction logs generated during the backup. If this option is specified, it is possible to start a postmaster directly in the extracted directory without the need to consult the log archive, thus making this a completely standalone backup."

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.

Our private DBMS backup does not have any data after Time T3, hence I don't want postgresql backup contains any transaction after Time T3.

Please let me know is my understand correct? And am I doing the correct way to backup.

Regards,
Ramkumar.

-----Original Message-----
From: David Steele [mailto:david(at)pgmasters(dot)net]
Sent: Thursday, December 03, 2015 1:27 AM
To: pgsql-general(at)postgresql(dot)org; Yelai, Ramkumar IN BLR STS
Subject: Re: [GENERAL] 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