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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
