Skip site navigation (1) Skip section navigation (2)

Re: Question about PITR backup

From: René Romero Benavides <ichbinrene(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Question about PITR backup
Date: 2012-06-08 19:30:32
Message-ID: 4FD252D8.2010103@gmail.com (view raw or flat)
Thread:
Lists: 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 *(at)sqlhotfix*

In response to

pgsql-admin by date

Next:From: Lonni J FriedmanDate: 2012-06-08 19:30:48
Subject: Re: pg_basebackup blocking all queries with horrible performance
Previous:From: René Romero BenavidesDate: 2012-06-08 19:13:21
Subject: Re: Creating a Read Only profile in 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group