Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EC5396502A9 for ; Wed, 27 Aug 2008 10:52:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 85117-03 for ; Wed, 27 Aug 2008 10:52:25 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.154]) by postgresql.org (Postfix) with ESMTP id 07171650109 for ; Wed, 27 Aug 2008 10:52:07 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so1852009fgg.23 for ; Wed, 27 Aug 2008 06:52:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=4bwotxkpewLGWY72cPuh+mbhHxIxXf2gtqkb1pZ7vRA=; b=YKlLAQRdmB22xzz4GSruMNHaxJqx3PoPNyMt6ed6CcgLHbaJG/0mbJZtVvdbxusgii dk34RAShfGSIhe4+GCIYZUjp6JtXFvHFn6Qw+yEcj19q3CxQog7Yze/4MsqOyCFspRWC D83bkcwZ4U7+sRNQKkvQyB0cIONnFTOa/fB4E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=OhiFsMW1do/uJzphZCqSoWrr0Fq9mzF4t1DXPVRQYZnKRxHLVumJCMa4r+brP12bZv I7mQohbwec5mS6GFycfVGm2oVG5BZtrGcMyFFni5A7bL0BdPg81C9d66D9rfFamkBIIl vtNrbM9EfnelQSEjZhoQrwUTa/dlYfi/sGnhQ= Received: by 10.86.4.14 with SMTP id 14mr88908fgd.20.1219845123136; Wed, 27 Aug 2008 06:52:03 -0700 (PDT) Received: by 10.86.23.1 with HTTP; Wed, 27 Aug 2008 06:52:03 -0700 (PDT) Message-ID: <4abad0eb0808270652g3c65caefg2ba065d63830a9d9@mail.gmail.com> Date: Wed, 27 Aug 2008 08:52:03 -0500 From: "Michael Nolan" To: "Merlin Moncure" Subject: Re: [GENERAL] PITR - base backup question Cc: "Richard Broersma" , "pgsql-general@postgresql.org" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_57506_25562746.1219845123127" References: <396486430808261553k6687500q19f4117b6c71ae3f@mail.gmail.com> <396486430808261804l93855f1gf86d92541a80141@mail.gmail.com> <4abad0eb0808270618w785f34f1pcb2c8d9b062f2d42@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200808/195 X-Sequence-Number: 30040 ------=_Part_57506_25562746.1219845123127 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On Wed, Aug 27, 2008 at 8:32 AM, Merlin Moncure 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 ------=_Part_57506_25562746.1219845123127 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline


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
------=_Part_57506_25562746.1219845123127--