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

Re: [HACKERS] Point in Time Recovery

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Kirkwood <markir(at)coretech(dot)co(dot)nz>
Cc: Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: [HACKERS] Point in Time Recovery
Date: 2004-07-22 02:39:15
Message-ID: 2110.1090463955@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackerspgsql-patches
Mark Kirkwood <markir(at)coretech(dot)co(dot)nz> writes:
> Here is one for the 'idiot proof' category:
> 1) initdb and set archive_command
> 2) shutdown
> 3) do a backup
> 4) startup and run some transactions
> 5) shutdown and remove PGDATA
> 6) restore backup
> 7) startup

> Obviously this does not work as the backup is performed with the 
> database shutdown.

Huh?  It works fine.

The bit you may be missing is that if you blow away $PGDATA including
pg_xlog/, you won't be able to recover past whatever you have in your WAL
archive area.  The archive is certainly not going to include the current
partially-filled WAL segment, and it might be missing a few earlier
segments if the archival process isn't speedy.  So you need to keep
those recent segments in pg_xlog/ if you want to recover to current time
or near-current time.

I'm becoming more and more convinced that we should bite the bullet and
move pg_xlog/ to someplace that is not under $PGDATA.  It would just
make things a whole lot more reliable, both for backup and to deal with
scenarios like yours above.  I tried to talk Bruce into this on the
phone the other day, but he wouldn't bite.  I still think it's a good
idea though.  It would
(1) eliminate the problem that a tar backup of $PGDATA would restore
    stale copies of xlog segments, because the tar wouldn't include
    pg_xlog in the first place.
(2) eliminate the problem that a naive "rm -rf $PGDATA" would blow away
    xlog segments that you still need.

A possible compromise is that we should strongly suggest that pg_xlog
be pushed out to another place and symlinked if you are going to use
WAL archiving.  That's already considered good practice for performance
if you have a separate disk spindle to put WAL on.  It'll just have
to be good practive for WAL archiving too.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2004-07-22 03:06:22
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Tom LaneDate: 2004-07-22 02:14:24
Subject: Re: Sorting out acl fixes

pgsql-admin by date

Next:From: Bruce MomjianDate: 2004-07-22 03:06:22
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Mark KirkwoodDate: 2004-07-22 01:51:04
Subject: Re: [HACKERS] Point in Time Recovery

pgsql-patches by date

Next:From: Bruce MomjianDate: 2004-07-22 03:06:22
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Bruce MomjianDate: 2004-07-22 02:33:40
Subject: Re: autovacuum integration attempt #3

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