Re: Consistent file-level backup of pg data directory

From: Rajeev rastogi <rajeev(dot)rastogi(at)huawei(dot)com>
To: "gator_ml(at)yahoo(dot)de" <gator_ml(at)yahoo(dot)de>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Consistent file-level backup of pg data directory
Date: 2014-01-08 03:56:57
Message-ID: BF2827DCCE55594C8D7A8F7FFD3AB7713DDB6648@SZXEML508-MBX.china.huawei.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 08 January 2014, Gator wrote:

> Hi,
>
> On my site, we run rsync-based backups of all servers to a central
> backup server every night. Unfortunately, reading the postgres
> documentation I could not find any direct way how to ensure that the
> state of the postgres data directory stored on the backup server is in
> a consistent state.
>
> It seems to me, that using (abusing;-) the "Continuous Archiving" it
> should be possible to get what i need. Specifically, I came up with the
> following strategy:
> - set wal_level=archive
> - call pg_start_backup()
> - keep a copy "backup_label" under a different name
> - call pg_stop_backup()
> - the run the regular backup procedure
>
> As far as I can see, the result of this should be that I get a
> consistent snapshot of the data directory at the time of the
> pg_stop_backup call. After restoring the backup somewhere, I only need
> to create a "recovery.conf" file (containing
> restore_command='/bin/true' because restore_command is
> mandatory) and everything should be fine.
>
> In practice, this procedure also seems to work as expected.
> Is there any hidden pitfall I overlooked? Or is there maybe a better
> way not mentioned in the postgres documentation how to get the data
> into a consistent state for a file level backup?
>
> Any Feedback would be appreciated!
>
> Regards,
> Peter
>
>

You can better try to use pg_basebackup (if you are not very particular about rsync), which will be
easy to use and will give you consistent backup.
You will be required to make configuration changes of all servers.
Check for PostgreSQL documentation for more detail.

Thanks and Regards,
Kumar Rajeev Rastogi

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Jayadevan M 2014-01-08 07:11:14 table design and data type choice
Previous Message Scott Marlowe 2014-01-08 01:31:18 Re: Sudden slow down and spike in system CPU causes max_connections to get exhausted