pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow does the WAL work? Need to recovery the database to a specific point of time
4+ messages / 4 participants
[nested] [flat]
* How does the WAL work? Need to recovery the database to a specific point of time
@ 2014-04-22 15:40 Wang, Mary Y <mary.y.wang@boeing.com>
0 siblings, 3 replies; 4+ messages in thread
From: Wang, Mary Y @ 2014-04-22 15:40 UTC (permalink / raw)
To: pgsql-admin
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.
Thanks
Mary Wang
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How does the WAL work? Need to recovery the database to a specific point of time
@ 2014-04-22 16:00 Steve Crawford <scrawford@pinpointresearch.com>
parent: Wang, Mary Y <mary.y.wang@boeing.com>
2 siblings, 0 replies; 4+ messages in thread
From: Steve Crawford @ 2014-04-22 16:00 UTC (permalink / raw)
To: Wang, Mary Y <mary.y.wang@boeing.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How does the WAL work? Need to recovery the database to a specific point of time
@ 2014-04-22 16:05 Matheus de Oliveira <matioli.matheus@gmail.com>
parent: Wang, Mary Y <mary.y.wang@boeing.com>
2 siblings, 0 replies; 4+ messages in thread
From: Matheus de Oliveira @ 2014-04-22 16:05 UTC (permalink / raw)
To: Wang, Mary Y <mary.y.wang@boeing.com>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How does the WAL work? Need to recovery the database to a specific point of time
@ 2014-04-22 16:07 Simon Riggs <simon@2ndQuadrant.com>
parent: Wang, Mary Y <mary.y.wang@boeing.com>
2 siblings, 0 replies; 4+ messages in thread
From: Simon Riggs @ 2014-04-22 16:07 UTC (permalink / raw)
To: Wang, Mary Y <mary.y.wang@boeing.com>; +Cc: pgsql-admin
On 22 April 2014 16:40, 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.
>
> 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 supported, though you have to have done some
planning beforehand. The settings and commands you'll use depend upon
the exact nature of your problem and the backups and other file copies
you possess.
http://www.postgresql.org/docs/devel/static/continuous-archiving.html
etc
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-04-22 16:07 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-22 15:40 How does the WAL work? Need to recovery the database to a specific point of time Wang, Mary Y <mary.y.wang@boeing.com>
2014-04-22 16:00 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-04-22 16:05 ` Re: How does the WAL work? Need to recovery the database to a specific point of time Matheus de Oliveira <matioli.matheus@gmail.com>
2014-04-22 16:07 ` Re: How does the WAL work? Need to recovery the database to a specific point of time Simon Riggs <simon@2ndQuadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox