Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 1E4C76330C6 for ; Thu, 3 Jun 2010 12:19:44 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 30563-02 for ; Thu, 3 Jun 2010 15:19:37 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from master.phlo.org (master.phlo.org [213.47.213.180]) by mail.postgresql.org (Postfix) with ESMTP id DD390632978 for ; Thu, 3 Jun 2010 12:19:32 -0300 (ADT) Received: (qmail 4989 invoked by uid 0); 3 Jun 2010 15:19:30 -0000 Received: from d91-128-59-49.cust.tele2.at (HELO [192.168.1.2]) (fgp@[91.128.59.49]) (envelope-sender ) by imap.phlo.org (qmail-ldap-1.03) with SMTP for ; 3 Jun 2010 15:19:30 -0000 Subject: Re: PITR Recovery Question Mime-Version: 1.0 (Apple Message framework v1078) Content-Type: text/plain; charset=us-ascii From: Florian Pflug In-Reply-To: <001201cb0321$df429d40$9dc7d7c0$@com> Date: Thu, 3 Jun 2010 17:19:30 +0200 Cc: Content-Transfer-Encoding: quoted-printable Message-Id: <0CC7BE82-C693-46EF-AE02-2E4E524F5527@phlo.org> References: <001201cb0321$df429d40$9dc7d7c0$@com> To: X-Mailer: Apple Mail (2.1078) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.011 tagged_above=-5 required=5 tests=BAYES_40=-0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201006/175 X-Sequence-Number: 163805 Hi, I'll try to answer your questions below, but in the future please post = questions concerning the usage and administration of postgres to = pgsql-general or pgsql-admin. This list focus is the development of new = features and bugfixes. On Jun 3, 2010, at 15:37 , Gnanakumar wrote: > PITR SETUP DETAILS > We've 2 drives. Primary drive (pgsql/data/ directory resides) is 400 = GB and > secondary drive (WAL archive) is 30 GB. All WAL archives are written = to > secondary drive. >=20 > Base backup taken on: Aug03, 2009 > WAL archive drive become full (100% use) on: Sep05, 2009 >=20 > Because this WAL archive drive has become full, all WAL archive = segments to > be archived are accumulated into pg_xlog/ directory itself. = Eventually, 9 > months (as of today from Sep05, 2009) of WAL archives are residing in > pg_xlog/ directory. This is by design. WAL logs are only removed from pg_xlog once they have = been archived successfully. Since your archive_command fails due to the = disk being full, they remain in pg_xlog. Once you enlarge the filesystem = holding the WAL archive they should be copied and subsequently removed = from pg_xlog. Note that you'd usually take a new base backup once in a while to limit = the number of WAL segments you need to retain. You can take a base = backup while postgres is running by issuing pg_start_backup() before you = start the copy and pg_stop_backup() after it finished. Apart from = creating additional IO load, doing so won't interfere with normal query = execution in any way. > My question is, in case if I would like to perform recovery process as = it is > in this situation, will this work out? That is, I'm seeing/finding = out > whether recovery process would perform successfully anywhere between = the > date range Aug03, 2009 (my base backup date) and as of today - Jun03, = 2009. > Reason I'm asking this is still all my WAL archives are residing in = pg_xlog/ > directory. For PITR, you'll obviously need the WAL segment starting from the time = your base backup started up until the point you want to recover to. If = some of those WAL segments still reside in pg_xlog, you'll either need = to teach your restore_command to fetch them from there. Note that you = cannot recover "in reverse". To recover up to a certain point in time = you always need to start from a base backup taken *before* that time. best regards, Florian Pflug