Seeking datacenter PITR backup suggestions

On Aug 17, 2007, at 5:48 PM, Joey K. wrote:

We have several web applications with Pg 8.2.x running on isolated
servers (~25). The database size on each machines (du -h pgdata) is
~2 GB. We have been using nightly filesystem backup (stop pg, tar
backup to ftp, start pg) and it worked well.

We would like to move to PITR backups since the database size will
increase moving forward and our current backup method might
increase server downtimes.

We have a central ftp backup server (yes, ftp :-) which we would
like to use for weekly full and daily incremental PITR backups.

After reading the docs, PITR is still fuzzy. Our ideas for backup
are (do not worry about the syntax),

** START **

tmpwal = "/localhost/tmp" # tmp space on server 1 for storing wal
files before ftp
Configure $pgdata/postgresql.conf archive_command = "cp %p $tmpwal/%f"

Why not just FTP WAL files directly?

Day 1:
% psql pg_start_backup(); tar pgdata.tar --exclude pg_xlog/ pgdata
% psql pg_stop_backup()
% ftp put pgdata.tar ftpserver:/server1/day1/pgdata
% ftp put $tmpwal/* ftpserver:/server1/day1/wal
% rm -f $tmpwal/* pgdata.tar

The last 2 are a race condition... you could easily lose a WAL file
that way.

Keep in mind that that pgdata.tar is 100% useless unless you also
have the WAL files that were created during the backup. I generally
recommend to folks that they keep two base copies around for that
reason.

Day 2:
% ftp put $tmpwal/* ftpserver:/server1/day2/wal
% rm -f $tmpwal/*

Day 3:
...
...

Day 7:
% rm -f $tmpwal/*
Start over

Recovery on server1 (skeleton commands),
% rm -f $tmpwal/*
% mv pgdata pgdata.hosed
% ftp get ftpbackup:/server1/day1/pgdata.tar .
% tar -xvf pgdata.tar
% ftp get ftpbackup:/server1/day1/wal/* $tmpwal
% ftp get ftpbackup:/server1/day2/wal/* $tmpwal
.....
.....
% cp -r pgdata.hosed/pg_xlog pgdata/
% echo "cp $tmpwal/%f %p" > pgdata/recovery.conf
% start pg (recovery begins)

** END **

Assumptions:
a. After pg_stop_backup(), Pg immediately recycles log files and
hence wal logs can be copied to backup. This is a clean start.
b. New wal files since (a) are incremental backups

We are not sure if WAL log filenames are unique and possibly
overwrite older wal files during recovery.

I'm seeking suggestions from others with experience performing
PostgreSQL PITR backups from multiple servers to a central backup
server.

In general, your handling of WAL files seems fragile and error-prone.
I think it would make far more sense to just FTP them directly, and
not try and get fancy with different directories for different days.
*when* a WAL file was generated is meaningless until you compare it
to a base backup to see if that WAL file is required for the base
backup, useful (but not required) to the base backup, or useless for
the base backup.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

In general, your handling of WAL files seems fragile and error-prone....

Indeed. I would recommend simply using rsync to handle pushing the
files. I see several advantages:

1. Distributed load - you aren't copying a full-day of files all at once.

2. Very easy to set-up - you can use it directly as your archive_command
if you wish.

3. Atomic. Rsync copies new data to a temporary location that will only
be moved into place when the transfer is complete. The destination
server will never see a partial file. Depending on the FTP client/server
combo, you will likely end up with a partial file in the event of
communication failure.

4. Much more up-to-the-minute recovery data.

In your scenario, what about using "cp -l" (or "ln") instead? Since the
hard-link it is only creating a new pointer, it will be very fast and
save a bunch of disk IO on your server and it doesn't appear that the
tempdir is for much other than organizing purposes anyway.

I'm setting up some test machines to learn more about PITR and warm
backups and am considering a two-stage process using "cp -l" to add the
file to the list needing transfer and regular rsync to actually move the
files to the destination machine. (The destination machine will be over
a WAN link so I'd like to avoid having PG tied up waiting for each rsync
to complete.)

Cheers,
Steve

"Steve Crawford" <scrawford@pinpointresearch.com> writes:

4. Much more up-to-the-minute recovery data.

In your scenario, what about using "cp -l" (or "ln") instead? Since the
hard-link it is only creating a new pointer, it will be very fast and
save a bunch of disk IO on your server and it doesn't appear that the
tempdir is for much other than organizing purposes anyway.

Postgres tries to reuse WAL files. Once the archive_command completes it
believes it is safe to reuse the old file without deleting it. That will do
nasty things if you've used ln as your archive command.

I'm setting up some test machines to learn more about PITR and warm
backups and am considering a two-stage process using "cp -l" to add the
file to the list needing transfer and regular rsync to actually move the
files to the destination machine. (The destination machine will be over
a WAN link so I'd like to avoid having PG tied up waiting for each rsync
to complete.)

In theory the only thing that the archive command blocks is the reuse of WAL
log files. So as long as the command is running if Postgres has used up all
its existing WAL files it will have to create new ones which does have some
performance cost. But otherwise it's unaffected.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com

Gregory Stark wrote:

In your scenario, what about using "cp -l" (or "ln") instead?....

Postgres tries to reuse WAL files. Once the archive_command completes it
believes it is safe to reuse the old file without deleting it. That will do
nasty things if you've used ln as your archive command.

Um, OK. I won't try that. Thanks. That really could be nasty as I
imagine that it would cause either corruption or failures on the standby
server that might be very hard to track down.

In theory the only thing that the archive command blocks is the reuse of WAL
log files....

What happens if PG server crashes during a WAL transfer? Does it still
know the file needs to be transferred or is doing a fast copy to a local
(same machine or machine on local network) from which files are
transferred over a slow-link a safer process?

Also, in the (rare) case that PG needs to be restarted, will the restart
block while waiting for all current log transfers to complete?

Cheers,
Steve

On Tue, Aug 28, 2007 at 11:05:52AM -0700, Steve Crawford wrote:

In your scenario, what about using "cp -l" (or "ln") instead? Since the
hard-link it is only creating a new pointer, it will be very fast and
save a bunch of disk IO on your server and it doesn't appear that the
tempdir is for much other than organizing purposes anyway.

Note that that will only work if you're creating the link on the same
filesystem, and having /tmp and your data in the same filesystem isn't
such a hot idea.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Tue, Aug 28, 2007 at 08:31:10PM +0100, Gregory Stark wrote:

"Steve Crawford" <scrawford@pinpointresearch.com> writes:

4. Much more up-to-the-minute recovery data.

In your scenario, what about using "cp -l" (or "ln") instead? Since the
hard-link it is only creating a new pointer, it will be very fast and
save a bunch of disk IO on your server and it doesn't appear that the
tempdir is for much other than organizing purposes anyway.

Postgres tries to reuse WAL files. Once the archive_command completes it
believes it is safe to reuse the old file without deleting it. That will do
nasty things if you've used ln as your archive command.

I thought that was specifically disabled when PITR was enabled? Or do we
just do a rename rather than an unlink ond creating a new file?
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

Decibel! <decibel@decibel.org> writes:

On Tue, Aug 28, 2007 at 08:31:10PM +0100, Gregory Stark wrote:

Postgres tries to reuse WAL files. Once the archive_command completes it
believes it is safe to reuse the old file without deleting it. That will do
nasty things if you've used ln as your archive command.

I thought that was specifically disabled when PITR was enabled? Or do we
just do a rename rather than an unlink ond creating a new file?

No. The only difference is we don't recycle the file until the
archive_command says it's done with it.

The archive_command must actually physically copy the data someplace
else, and must not return success until it's sure the copy is good.
Perhaps the docs are not sufficiently clear on the point?

regards, tom lane

On Tue, Aug 28, 2007 at 09:54:23PM -0400, Tom Lane wrote:

Decibel! <decibel@decibel.org> writes:

On Tue, Aug 28, 2007 at 08:31:10PM +0100, Gregory Stark wrote:

Postgres tries to reuse WAL files. Once the archive_command completes it
believes it is safe to reuse the old file without deleting it. That will do
nasty things if you've used ln as your archive command.

I thought that was specifically disabled when PITR was enabled? Or do we
just do a rename rather than an unlink ond creating a new file?

No. The only difference is we don't recycle the file until the
archive_command says it's done with it.

The archive_command must actually physically copy the data someplace
else, and must not return success until it's sure the copy is good.
Perhaps the docs are not sufficiently clear on the point?

Yeah... I think that's a big gotcha waiting to smack someone. I'd
actually make the mention <strong> so that hopefully no one can miss
it... or do we have an official method for putting warnings in the docs?

"Because WAL segment files are renamed and not re-created from scratch,
it is critical that the archive command actually copy files, not move
or hard-link them."
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)

On Tue, 2007-08-28 at 21:04 -0500, Decibel! wrote:

On Tue, Aug 28, 2007 at 09:54:23PM -0400, Tom Lane wrote:

Perhaps the docs are not sufficiently clear on the point?

Yeah... I think that's a big gotcha waiting to smack someone. I'd
actually make the mention <strong> so that hopefully no one can miss
it... or do we have an official method for putting warnings in the docs?

"Because WAL segment files are renamed and not re-created from scratch,
it is critical that the archive command actually copy files, not move
or hard-link them."

I'll shortly be writing a doc patch to clarify a few points and to
explain new possibilities, such as Koichi Suzuki's work.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com