Re: How does the WAL work? Need to recovery the database to a specific point of time

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