pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about PITR backup
3+ messages / 3 participants
[nested] [flat]
* Question about PITR backup
@ 2012-06-08 10:43 sgm <sgm277@yahoo.com.cn>
0 siblings, 2 replies; 3+ messages in thread
From: sgm @ 2012-06-08 10:43 UTC (permalink / raw)
To: pgsql-admin
Dear admin,
I have a question about PITR backup in a single server, the method is make a base backup, and backup the WAL archive log(eg, every day at 11:30 pm). But if the OS' harddisk is broken(eg,14:00 pm),the system can't start, we have to recover the database on a another server using the base backup and the WAL backup,so in this case suppose that we only can restore the database to yesterday's 11:30 pm state, am I right?
How can we recover the database to 14:00 pm? We are missing the newest pg_xlog.
I want to backup the pg_xlog folder every minute by crontab but my concern is that the data inconsistent, because the lastest log in pg_xlog is being updated all the time, am I right? Any suggestions?
Very appreciated your answer.
Thanks,
Song
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about PITR backup
@ 2012-06-08 18:20 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: sgm <sgm277@yahoo.com.cn>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2012-06-08 18:20 UTC (permalink / raw)
To: pgsql-admin; sgm <sgm277@yahoo.com.cn>
sgm <sgm277@yahoo.com.cn> wrote:
> I have a question about PITR backup in a single server, the method
> is make a base backup, and backup the WAL archive log(eg, every
> day at 11:30 pm). But if the OS' harddisk is broken(eg,14:00
> pm),the system can't start, we have to recover the database on a
> another server using the base backup and the WAL backup,so in this
> case suppose that we only can restore the database to yesterday's
> 11:30 pm state, am I right?
You can't recover to a point past your last available WAL record.
> I want to backup the pg_xlog folder every minute by crontab but my
> concern is that the data inconsistent, because the lastest log in
> pg_xlog is being updated all the time, am I right? Any
> suggestions?
Use streaming replication? Or at the very least, set a short
archive_timeout value and copy from the archive target location
frequently.
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Question about PITR backup
@ 2012-06-08 19:30 René Romero Benavides <ichbinrene@gmail.com>
parent: sgm <sgm277@yahoo.com.cn>
1 sibling, 0 replies; 3+ messages in thread
From: René Romero Benavides @ 2012-06-08 19:30 UTC (permalink / raw)
To: pgsql-admin
Am 08.06.2012 05:43, schrieb sgm:
> Dear admin,
>
> I have a question about PITR backup in a single server, the method is
> make a base backup, and backup the WAL archive log(eg, every day at
> 11:30 pm). But if the OS' harddisk is broken(eg,14:00 pm),the system
> can't start, we have to recover the database on a another server using
> the base backup and the WAL backup,so in this case suppose that we
> only can restore the database to yesterday's 11:30 pm state, am I right?
>
> How can we recover the database to 14:00 pm? We are missing the newest
> pg_xlog.
>
> I want to backup the pg_xlog folder every minute by crontab but my
> concern is that the data inconsistent, because the lastest log in
> pg_xlog is being updated all the time, am I right? Any suggestions?
>
Yes, the wal segments are updated constantly. You'll have a consistent
state in your data files up to the time when the last checkpoint
happened. Should a crash occur the server will replay the log segments
in the WAL files starting from the one that was marked during the last
checkpoint recorded.
So,provided all the WAL files generated by the server after the last
checkpoint are available, it will always be able to bring the data
files to a consistent state. Those WAL files are always kept in the
pg_xlog directory.
Though I'm also in favor of leveraging a streaming replication
configuration as it's more reliable and the window for data loss is very
small.
--
pglearn.blogspot.mx:postgresql recipes <http://pglearn.blogspot.mx/;
*Twitter*.You might consider to follow *@sqlhotfix*
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-06-08 19:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-08 10:43 Question about PITR backup sgm <sgm277@yahoo.com.cn>
2012-06-08 18:20 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-08 19:30 ` René Romero Benavides <ichbinrene@gmail.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