Re: How does the WAL work? Need to recovery the database to a specific point of time - Mailing list pgsql-admin

From Matheus de Oliveira
Subject Re: How does the WAL work? Need to recovery the database to a specific point of time
Date
Msg-id CAJghg4+zFjJS3OoPMWTOiygvKKx+X4BHqD79g+Hj1bXXiVh57Q@mail.gmail.com
Whole thread Raw
In response to How does the WAL work? Need to recovery the database to a specific point of time  ("Wang, Mary Y" <mary.y.wang@boeing.com>)
List pgsql-admin


On Tue, Apr 22, 2014 at 12:40 PM, Wang, Mary Y <mary.y.wang@boeing.com> 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.


If you haven't setup incremental backups (basebackup + archiving) you can't use PITR feature now. You really should have done such setup before.

 

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? 


Yes. It should not exactly be "updated", but "created". And you do that not in the production system, but in the one you want to do the recover.
 

2.       What specific settings should I set? 


There are plenty, for PITR you will need at least restore_command and one of recovery_*. See [1].

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?

 


If the file exists when you start PostgreSQL, it will read and use it. So it is basically the existence of the file that will make PG use.

I recommend you reading through the documentation for "Continuous Archiving and Point-in-Time Recovery (PITR)" at [2]. After reading all there, you can come back here if you still have doubts.
 

I am running on Postgres 8.4.11 and RHEL 6.4.

 


You should upgrade ASAP to 8.4.21 and plan an upgrade to a more recent version (like 9.3), as 8.4 will be out of support soon. See [3].
 

Thanks

Mary Wang


[1] http://www.postgresql.org/docs/current/static/recovery-target-settings.html
[2] http://www.postgresql.org/docs/current/static/continuous-archiving.html
[3] http://www.postgresql.org/support/versioning/

--
Matheus de Oliveira
Analista de Banco de Dados
Dextra Sistemas - MPS.Br nível F!
www.dextra.com.br/postgres

pgsql-admin by date:

Previous
From: Steve Crawford
Date:
Subject: Re: How does the WAL work? Need to recovery the database to a specific point of time
Next
From: Simon Riggs
Date:
Subject: Re: How does the WAL work? Need to recovery the database to a specific point of time