Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wcd7r-0007mh-95 for pgsql-admin@arkaria.postgresql.org; Tue, 22 Apr 2014 16:00:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Wcd7q-0004Ds-Is for pgsql-admin@arkaria.postgresql.org; Tue, 22 Apr 2014 16:00:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wcd7o-0004C3-MQ for pgsql-admin@postgresql.org; Tue, 22 Apr 2014 16:00:48 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Wcd7g-0003Va-Cz for pgsql-admin@postgresql.org; Tue, 22 Apr 2014 16:00:48 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id C50F2E00EC83; Tue, 22 Apr 2014 09:00:37 -0700 (PDT) Message-ID: <53569225.7060605@pinpointresearch.com> Date: Tue, 22 Apr 2014 09:00:37 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.4.0 MIME-Version: 1.0 To: "Wang, Mary Y" , "pgsql-admin@postgresql.org" Subject: Re: How does the WAL work? Need to recovery the database to a specific point of time References: <27F6554ED189174ABAA2EDB71E1350951AE59CBF@XCH-PHX-202.sw.nos.boeing.com> In-Reply-To: <27F6554ED189174ABAA2EDB71E1350951AE59CBF@XCH-PHX-202.sw.nos.boeing.com> Content-Type: multipart/alternative; boundary="------------090401070005080005080405" X-Pg-Spam-Score: -2.5 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. --------------090401070005080005080405 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 04/22/2014 08:40 AM, Wang, Mary Y wrote: > > Hi All, > > I'd like to recovery the database to a specific point of time. I see > some WAL files in the pg_xlog directories. > > I read some blogs, and some have indicated that a recovery.conf needs > to be updated. I do not see a recovery.conf file in the $PG_DATA > directory ( I see pg_hba.conf and postgresql.conf). > > 1. Do I need to create a recovery.conf file? > > 2. What specific settings should I set? > > 3. How do I ensure that postgres will read the recovery.conf file? > > 4. What is the start/stop postgres command to use when working with a > recovery.conf file? > > I am running on Postgres 8.4.11 and RHEL 6.4. > > Point-in-time recovery is not something that can typically be performed arbitrarily "after the fact" - you need to have established the necessary backup and archiving in advance. This capability is essentially a useful side-effect of the process through which PostgreSQL recovers from an inconsistent state following a power-failure or other disruptive event by replaying the appropriate WAL files. Point-in-time recovery assumes you have both a *file-system* backup (not a pg_dump) of the PostgreSQL data files that was completed prior to the point in time to which you wish to recover *and* all the write-ahead log (WAL) files created from when the file-system was backed-up and the point to which you wish to recover. This is typically set up through a combination of scheduled file-system backups and archiving of WAL files. WAL files are not stored indefinitely but rather are rotated and optionally archived based on your configuration settings. If you have the base file-system backup and all the required WAL files, have a look at http://www.postgresql.org/docs/8.4/static/continuous-archiving.html Cheers, Steve --------------090401070005080005080405 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit
On 04/22/2014 08:40 AM, Wang, Mary Y wrote:

Hi All,

 

I’d like to recovery the database to a specific point of time. I see some WAL files in the pg_xlog directories.

I read some blogs, and some have indicated that a recovery.conf needs to be updated.  I do not see a recovery.conf file in the $PG_DATA directory ( I see pg_hba.conf and postgresql.conf). 

 

1.       Do I need to create a recovery.conf file? 

2.       What specific settings should I set? 

3.       How do I ensure that postgres will read the recovery.conf file? 

4.       What is the start/stop postgres command to use when working with a recovery.conf file?

 

I am running on Postgres 8.4.11 and RHEL 6.4.

 



Point-in-time recovery is not something that can typically be performed arbitrarily "after the fact" - you need to have established the necessary backup and archiving in advance.

This capability is essentially a useful side-effect of the process through which PostgreSQL recovers from an inconsistent state following a power-failure or other disruptive event by replaying the appropriate WAL files.

Point-in-time recovery assumes you have both a *file-system* backup (not a pg_dump) of the PostgreSQL data files that was completed prior to the point in time to which you wish to recover *and* all the write-ahead log (WAL) files created from when the file-system was backed-up and the point to which you wish to recover. This is typically set up through a combination of scheduled file-system backups and archiving of WAL files.

WAL files are not stored indefinitely but rather are rotated and optionally archived based on your configuration settings.

If you have the base file-system backup and all the required WAL files, have a look at http://www.postgresql.org/docs/8.4/static/continuous-archiving.html

Cheers,
Steve
--------------090401070005080005080405--