Re: Consistent file-level backup of pg data directory

On Tue, Jan 7, 2014 at 10:58 AM, <gator_ml(at)yahoo(dot)de> 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
>

Why under a different name? That sounds dangerous to me.

> - call pg_stop_backup()
> - the run the regular backup procedure
>

You have this order backwards. You can only tell postgres that you are
done doing the backup once you are done doing the backup.

> 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.
>

Does this mean you weren't actually archiving the xlog when you turned
archiving on?

What do you do with the backup_label? If you don't restore it to its
original name, then postgres will just think it is recovering from a power
failure or something like that, not a media failure, so it will not even
try to access the archived logs.

> In practice, this procedure also seems to work as expected.
>

If no checkpoints occurred during the backup (or no more than one of them
did), then your method might accidentally work. Or it could silently
corrupt your database in hard to detect ways.

> 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!
>

It is pretty much a disaster. Do you really need rsync? Otherwise, use
pg_basebackup, it is easier to get right.

Cheers,

Jeff