From: Michael Nolan <htfoot@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Richard Broersma <richard.broersma@gmail.com>
Cc: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Cc: pgsql-admin@postgresql.org
Subject: Re: [GENERAL] PITR - base backup question
Date: Wed, 27 Aug 2008 08:52:03 -0500
Message-ID: <4abad0eb0808270652g3c65caefg2ba065d63830a9d9@mail.gmail.com> (raw)
In-Reply-To: <b42b73150808270632t4823d522i42576a2e8022383c@mail.gmail.com>
References: <396486430808261553k6687500q19f4117b6c71ae3f@mail.gmail.com>
<b42b73150808261738j605e1bchc1935f81d695fb08@mail.gmail.com>
<396486430808261804l93855f1gf86d92541a80141@mail.gmail.com>
<b42b73150808270512y2867c937h1710512d6010d3c8@mail.gmail.com>
<4abad0eb0808270618w785f34f1pcb2c8d9b062f2d42@mail.gmail.com>
<b42b73150808270632t4823d522i42576a2e8022383c@mail.gmail.com>
On Wed, Aug 27, 2008 at 8:32 AM, Merlin Moncure <mmoncure@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: htfoot@gmail.com, mmoncure@gmail.com, richard.broersma@gmail.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] PITR - base backup question
In-Reply-To: <4abad0eb0808270652g3c65caefg2ba065d63830a9d9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox