Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id F32F59FB2C3 for ; Thu, 11 Oct 2007 07:03:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 63292-09 for ; Thu, 11 Oct 2007 07:03:16 -0300 (ADT) X-Greylist: delayed 00:23:58.731137 by SQLgrey-1.7.5 Received: from secure.wh4f.de (secure.wh4f.de [81.169.136.22]) by postgresql.org (Postfix) with ESMTP id 6725A9FA5E4 for ; Thu, 11 Oct 2007 07:03:20 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by secure.wh4f.de (Postfix) with ESMTP id D079B668038 for ; Thu, 11 Oct 2007 11:39:16 +0200 (CEST) Received: from secure.wh4f.de ([127.0.0.1]) by localhost (wh4f.de [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id DMlodke-+p0j for ; Thu, 11 Oct 2007 11:39:16 +0200 (CEST) Received: from arche.mine.nu (unknown [89.244.186.255]) by secure.wh4f.de (Postfix) with ESMTP id 2ABCC668036 for ; Thu, 11 Oct 2007 11:39:16 +0200 (CEST) Received: from localhost (localhost [127.0.0.1]) by arche.mine.nu (Postfix) with ESMTP id 6911BC901A702 for ; Thu, 11 Oct 2007 11:39:11 +0200 (CEST) X-Virus-Scanned: Virus and Spam crusher at arche.mine.nu Received: from arche.mine.nu ([127.0.0.1]) by localhost (arche.mine.nu [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id DuKoVec87tbN for ; Thu, 11 Oct 2007 11:39:09 +0200 (CEST) Received: by arche.mine.nu (Postfix, from userid 1000) id 652EFC901A701; Thu, 11 Oct 2007 11:39:08 +0200 (CEST) Date: Thu, 11 Oct 2007 11:39:09 +0200 From: Thomas Karcher To: pgsql-admin@postgresql.org Subject: Consistent PITR backup Message-ID: <20071011093908.GA7988@arche.mine.nu> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="qDbXVdCdHGoSgWSk" Content-Disposition: inline User-Agent: Mutt/1.5.16 (2007-06-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/85 X-Sequence-Number: 26999 --qDbXVdCdHGoSgWSk Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Dear list, in the documentation (http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html) is written that after doing the filesystem based backup, I have to invoke pg_stop_backup() which triggers an xlog switch, and in order to have a consistent backup, I need to have the switched WAL file included in my backup. Unfortunately, there is a little problem for me with that: The archive_command isn't actually doing the backup but just copying the WAL files to a specific directory on the database server. The actual backup then comes later and fetches them to the backup server. It is not wished that there is a more or less persistent connection to the backup server and that PG itself is doing the actual backup. So when I'm doing a full PITR backup, I have to include the last WAL file that was switched in pg_stop_backup(), but the archiver is as far as I understood more or less asynchronous, so I can't easily be sure that the archiver really copied the last WAL file to my archive directory. I had a look into the source code and found an undocumented (I suppose ...) feature: The xlog switch creates a xxxx.ready file in the pg_xlog/archive_status directory, and as soon as the archiver finds a file like this, it copies the corresponding WAL file to the archive directory. My idea is now: When doing a consistent PITR backup, extend the documented steps between 4 (pg_stop_backup()) and 5 (wait for archived WAL file) with a busy wait on pg_xlog/archive_status/*.ready files - as soon as no .ready files are there anymore, the archiver has done its work, and I can safely backup the archived WAL files in order to have a really consistent backup. Is that right? Or is there an easier way to do it? Thanks & best regards, Thomas --qDbXVdCdHGoSgWSk Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHDe88iMyIQtYO79wRAhbCAJ9zzWPcSywaycz6InOUTOh2RnisdgCgltTO 3pkRtApgt/mNNNycT0EXDw8= =h3pZ -----END PGP SIGNATURE----- --qDbXVdCdHGoSgWSk--