Re: PITR Base Backup on an idle 8.1 server

On Fri, 1 Jun 2007, Marco Colombo wrote:

> If you need *both* a "full backup" *and* PITR, just add a real cp to the
> archive_command above. The important part is to return failure during the
> backup process, I think.

You seem to have worked out a way for your application to do a base backup
in a fashion that you're happy with. The way you're grabbing files
directly from the xlog directory only works because your commit workload
is so trivial that you can get away with it, and because you haven't then
tried to apply future archive logs. In the general case, circumventing
the archiving when the backup is going on won't guarantee everything is
ordered just right for PITR to work correctly. I consider what you're
doing a bad idea that you happen to be comfortable with the ramifications
of, and given the circumstances I understand how you have ended up with
that solution.

I would highly recommend you consider switching at some point to the
solution Simon threw out:

> create table xlog_switch as
> select '0123456789ABCDE' from generate_series(1,1000000);
> drop table xlog_switch;

as the best way to solve this problem (thanks, Simon; that's what I was
looking for but didn't quite have figured out yet). With that as a
working way to force a segment change on an idle server without actually
impacting the data, you should reconsider doing your PITR backup
properly--where you never touch anything in the xlog directory and instead
only work with what the archive_command is told.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD