Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 26E3F9FB337 for ; Thu, 11 Oct 2007 11:37:17 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 29799-01 for ; Thu, 11 Oct 2007 11:37:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id 6E7CB9F9C9D for ; Thu, 11 Oct 2007 11:37:13 -0300 (ADT) Received: (qmail invoked by alias); 11 Oct 2007 14:37:10 -0000 Received: from unknown (EHLO [192.168.25.163]) [89.244.186.255] by mail.gmx.net (mp039) with SMTP; 11 Oct 2007 16:37:10 +0200 X-Authenticated: #608320 X-Provags-ID: V01U2FsdGVkX1/JLtIjbdjJPtuTnypFNL2rGw0NfPF211LfGrdQU8 PqP25wDioLh0Ui Subject: Re: Consistent PITR backup From: Thomas Karcher To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <13019.1192112569@sss.pgh.pa.us> References: <20071011093908.GA7988@arche.mine.nu> <13019.1192112569@sss.pgh.pa.us> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-vekpesqD+UJH0GpK/Wm1" Date: Thu, 11 Oct 2007 16:37:05 +0200 Message-Id: <1192113425.9842.2.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.10.3 X-Y-GMX-Trusted: 0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/92 X-Sequence-Number: 27006 --=-vekpesqD+UJH0GpK/Wm1 Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Hi Tom, > > 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. > If the database is busy, you might never see an instant where there are > no .ready files. Certainly it's possible that this approach will > archive many more segments than you need. I see - but the risk for never seeing an instant without .ready files is relatively slow, I hope. And compared to the risk of not having a consistent backup, I could live with having backed up more WAL files than I actually would have needed. > > Is that right? Or is there an easier way to do it? > I believe the intended way to do this is to look at pg_stop_backup's > return value to determine the last segment you need to archive. Thank you - I saw that, but I didn't have a clue about how to determine the WAL filename in which this segment is to be found ... Thank you & regards, Thomas --=-vekpesqD+UJH0GpK/Wm1 Content-Type: application/pgp-signature; name=signature.asc Content-Description: Dies ist ein digital signierter Nachrichtenteil -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQBHDjURiMyIQtYO79wRAmG0AKDJwZ3tMKtZ/JGkOFRFmjV6APTvJQCfUKKl Fchyxt8m+LwALavmd1mQO3E= =HS9L -----END PGP SIGNATURE----- --=-vekpesqD+UJH0GpK/Wm1--