Re: [GENERAL] PITR - base backup question

On Wed, Aug 27, 2008 at 8:32 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> > 3. Shut down the Postgresql server running on the backup server, if any
> > pg_ctl stop
> > (Use 'ps ax' to make sure the server is stopped.)
>
> probably pg_ctl -m fast stop or -m immediate...since we are overwriting it.

Good point, but sometimes there are tasks running on the backup server
(recently I did a test of a revised procedure that took 5 days to run.)
I'll probably update the cookbook guide to deal with that possibility.

>
>
> > 5. Delete the entire contents of the /disk1/postgres/data directory
> tree.
> > MAKE ABSOLUTELY SURE YOU ARE IN THE /disk1/postgres/data directory!
> >
> > cd /disk1/postgres/data
> > pwd
> > rm -rf *
>
> why not just rm -rf /disk1/postgres/data?

I prefer the visual confirmation that I am indeed in the right directory
rather than risk a typo in the longer rm command.

> 6. Restore the tar file for the low-level backup from the live server
> >
> > tar xvf /usr/local/pgsql/tardir/pg_part1.tar
> >
> > (This restore may take 2-3 hours)
> >
> > 7. Remove the PostgreSQL log file and the WAL files that were restored
> > from the tar archive
>
> I prefer to exclude WAL files from the original tar, and recreate the
> folders here (being careful to chown them to postgres account). Every
> little bit helps.

Another good point.

>
> > 12. Go to the directory where the WAL files have been archived on
> > the server and remove all files older than the file matching the
> > last .backup file. The fastest way to do this is as follows:
> >
> > cd /usr/local/pgsql/archivedir
> > ls -1 > files
>
> This is a nice touch. With a little bash-fu you could do a find |
> xargs rm and list/kill the files in one pass. In the standby setups
> I've done I usually script the whole process, a prep on the main and a
> startup on the standby.

The scripts to create the system level backups and copy them to the backup
servers and to rsync the WAL files are both cron jobs.

I've considered writing a script to do all the restore tasks on the backup
server, but I figure if someone other than me ever has to do it, for example
if there is a problem when I'm unavailable, that person will probably want
to watch the entire process carefully, since he or she will be far less
familiar with the configuration.

As disk space permits, I actually keep TWO sets of the tar archive base
files, the most recent one and the one from the previous week. That way I
could go back further for a PITR recovery up to some incident. I've never
needed to do that and I hope I never do, but it's nice to have that
capability just in case.
--
Mike Nolan