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

Re: PITR Base Backup on an idle 8.1 server

From: Marco Colombo <pgsql(at)esiway(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: PITR Base Backup on an idle 8.1 server
Date: 2007-05-31 16:32:12
Message-ID: 465EF88C.1020203@esiway.net (view raw or flat)
Thread:
Lists: pgsql-general
Greg Smith wrote:
[...]
> -Find something harmless I can execute in a loop that will generate WAL 
> activity, run that until the segment gets archived.  Haven't really 
> thought of something good to use for that purpose yet.

Some time ago I started a thread about taking on-the-fly backups at file 
level on idle servers. Problem was much the same of yours. After posting 
that, I'm doing some research on my own (in spare time) now.

Currently, I'm using the following procedure:

1) create a backup lockfile
2) issue pg_start_backup()
3) tar the data directory, excluding pg_xlog
4) issue pg_stop_backup()
5) tar pg_xlog
6) remove the lockfile

Meanwhile, a "fake" WAL archiving is active, which does pretty nothing.

archive_command = 'test ! -f /var/lib/pgsql/backup_lock </dev/null'

Under normal condition (no backup running) this will trick PG into 
thinking that segments get archived. If I'm not mistaken, PG should 
behave exactly as if no archive_command is configured, and recycle them 
ASAP. This saves me the burden of taking care of the archiving at all.

Should a WAL segment fill up during the backup (unlikely as it is, since 
the system is mostly idle AND the tar completes withing a minute - but 
it's still possible), the test command would report failure in archiving 
the segment, and PG would keep it around in pg_xlog, ready to be tar'ed 
  at step 5 (mind you - this is speculation since I had no time to 
actually test it).

So it ends up with two tar archives: one is the datafiles backup, the 
other the wal segments. As an optimization, I should exclude WAL 
segments older that the lockfile in step 5), since I know they are older 
than the backup.

What I really should do now is kill -STOP the tar at step 3), start some 
big write activity and see what exaclty happens to the WAL segment when 
it fills up and PG tries to archive it.

Restore would be done the usual way, extracting both the archives, maybe 
adding WAL segments from the crashed pg_xlog. Whether I need to 
configure a fake restore command I have still to find out.

Hope it helps,
.TM.

In response to

Responses

pgsql-general by date

Next:From: lawpoopDate: 2007-05-31 16:46:47
Subject: collision in serial numbers after INSERT?
Previous:From: Frank WittigDate: 2007-05-31 14:48:57
Subject: Re: warm standby server stops doing checkpoints after a while

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