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

Re: [HACKERS] Point in Time Recovery

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mark Kirkwood <markir(at)coretech(dot)co(dot)nz>,Simon Riggs <simon(at)2ndquadrant(dot)com>, pgsql-admin(at)postgresql(dot)org
Subject: Re: [HACKERS] Point in Time Recovery
Date: 2004-07-22 03:06:22
Message-ID: 200407220306.i6M36MU11209@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackerspgsql-patches
I think we should push the partially complete WAL file to the archive
location before shutdown.  I talked to you or Jan about it and you (or
Jan) wouldn't bite either, but I think when someone shuts down, they
assume they have things fully archived and can recover fully with a
previous backup and the archive files.

When you are running and finally fill up the WAL file it would then
overwrite the one in the archive but I think that is OK.  Maybe we would
need to give it a special file extension so we only use it when we don't
have a full version.

---------------------------------------------------------------------------

Tom Lane wrote:
> 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

In response to

Responses

pgsql-hackers by date

Next:From: Mark KirkwoodDate: 2004-07-22 03:12:21
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Tom LaneDate: 2004-07-22 02:39:15
Subject: Re: [HACKERS] Point in Time Recovery

pgsql-admin by date

Next:From: Mark KirkwoodDate: 2004-07-22 03:12:21
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Tom LaneDate: 2004-07-22 02:39:15
Subject: Re: [HACKERS] Point in Time Recovery

pgsql-patches by date

Next:From: Mark KirkwoodDate: 2004-07-22 03:12:21
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Tom LaneDate: 2004-07-22 02:39:15
Subject: Re: [HACKERS] Point in Time Recovery

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