Re: [ADMIN] [GENERAL] PITR - base backup question
Date: Wed, 27 Aug 2008 06:58:33 -0700 From: [EMAIL PROTECTED] Subject: Re: [ADMIN] [GENERAL] PITR - base backup question To: pgsql-general@postgresql.org; [EMAIL PROTECTED]; [EMAIL PROTECTED] --- On Tue, 8/26/08, Richard Broersma wrote: From: Richard Broersma Subject: [GENERAL] PITR - base backup question To: pgsql-general@postgresql.org , [EMAIL PROTECTED] Date: Tuesday, August 26, 2008, 10:53 PM From the following link: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP Step 3 says to perform the back up. Does this mean a File System Backup of the Data directory?\ It is a file system backup, we use PITR and every time we need a new checkpoint we create a TAR file of the postgresql home directory OR Does this mean performing a pg_dumpall and backing up the dump file? is a file system backup of he data directory -- Regards, Richard Broersma Jr. -- Sent via pgsql-admin mailing list ([EMAIL PROTECTED]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PITR - base backup question
On Tue, Aug 26, 2008 at 9:04 PM, Richard Broersma [EMAIL PROTECTED] wrote: On Tue, Aug 26, 2008 at 5:38 PM, Merlin Moncure [EMAIL PROTECTED] wrote: If you ever want to mess around with log shipping I strongly suggest you go through the motions of setting up a warm standby vi the pg_standby utility and practice popping the standby out of recovery. Thanks for the reply! I want to be certain that I understand the point you are making: Is setting up a warm standby server good pratice for gaining experience in PITR or do you mean that PITR is a good starting point for setting up a warm standby server? setting up a warm standby is good for understanding pitr. A warm standby just 'lays on top' of pitr and you should learn how to do it. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PITR - base backup question
I have what I have sometimes called a 'tepid spare' backup. Once a week I copy the physical files over to another system (actually to two of them) and every few hours I make sure the archived WAL log files are in sync (using rsync.) Anyway, here's the cookbook guide I wrote for updating one of the backup servers, which I have used several times, so I don't have to rediscover the steps each time. I think it has sufficient detail that someone other than me could follow them. Obviously it would have to be changed to apply to your file organization scheme. Enjoy! -- Mike Nolan How to restore the PostgreSQL low level backup tar files and WAL files to do a point-in-time recovery (PITR) files on a backup server. This note will explain how to perform a point-in-time recovery of the PostgreSQL database using the low-level backup files and the archived WAL (write-ahead log) files. (These steps should work on either server, with any differences in actual file locations dealt with using symbolic links.) The low level backup files and WAL log files should already be present on the backup servers. The low level tar files files are copied when they are created (usually early on a Tuesday morning each week) and the WAL log files are rsync'ed to both servers every few hours so that both backup servers should have files enabling them to be restored to a state that is no more than a few hours out of sync with the live database server. (In the future, we hope to keep one of the backup servers in full real-time synchronization with the live server, using a different technique.) The steps given below will generally take 3-5 hours to run, depending on how many archived log files need to be processed when the PostgreSQL server is restarted. These instructions assume some familiarity with Unix/Linux system administration tools, including the vi editor, and with database administration procedures, though not necessarily detailed knowledge of PostgreSQL. For more details, see chapter 23 of the PostgreSQL documentation, especially section 23.3. The documentation for version 8.2 is at http://www.postgresql.org/docs/8.2/static/backup.html 1. su to root on the backup server that the restore will be performed on 2. su to postgres (su - postgres) 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.) 4. cd to /disk1/postgres/data and copy two files to /tmp cd /disk1/postgres/data cp pg_hba.conf /tmp cp recovery.done /tmp 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 * 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 rm log.out cd pg_xlog rm 00* (do not remove the archive_status subdirectory) 8. Copy the pg_hba.conf file back from the /tmp directory cd /disk1/postgres/data cp /tmp/pg_hba.conf . (This is necessary to keep this database server configured for restricted access. If more expansive access is needed the restored pg_hba.conf file may be more appropriate.) 9. Copy the recovery configuration file from the /tmp directory (changing the file name) cp /tmp/recovery.done recovery.conf (This file may be edited to do a point-in-time recovery other than to the end of the PITR log files, see the PostgreSQL documentation for details.) 10. Delete the entire contents of the /disk2/postgres/data directory tree. MAKE ABSOLUTELY SURE YOU ARE IN THE /disk2/postgres/data directory! cd /disk2/postgres/data pwd rm -rf * 11. Restore the 2nd tar archive tar xvf /usr/local/pgsql/tardir/pg_part2.tar (This restore may take around a half hour) 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 will produce a file listing all files in the archivedir directory. Now edit that file (using vi) to take out all lines after the latest 'backup' file (including the file just ahead of that backup file, since it is actually the first file that needs to be processed. (Note: These ones after the latest backup file are the files we're NOT going to delete, so we don't want those file names in this text file.) For example, the directory list may look like this: 00010096000A 00010096000B 00010096000C 00010096000C.004027C0.backup 00010096000D There will
Re: [GENERAL] PITR - base backup question
On Wed, Aug 27, 2008 at 9:18 AM, Michael Nolan [EMAIL PROTECTED] wrote: I have what I have sometimes called a 'tepid spare' backup. Once a week I copy the physical files over to another system (actually to two of them) and every few hours I make sure the archived WAL log files are in sync (using rsync.) I have a couple of comments...see below: 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. 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? 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. 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. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PITR - base backup question
On Wed, Aug 27, 2008 at 8:32 AM, Merlin Moncure [EMAIL PROTECTED] 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
Re: [GENERAL] PITR - base backup question
--- On Tue, 8/26/08, Richard Broersma [EMAIL PROTECTED] wrote: From: Richard Broersma [EMAIL PROTECTED] Subject: [GENERAL] PITR - base backup question To: pgsql-general@postgresql.org pgsql-general@postgresql.org, [EMAIL PROTECTED] Date: Tuesday, August 26, 2008, 10:53 PM From the following link: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP Step 3 says to perform the back up. Does this mean a File System Backup of the Data directory? OR Does this mean performing a pg_dumpall and backing up the dump file? is a file system backup of he data directory -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PITR - base backup question
On Wed, Aug 27, 2008 at 9:52 AM, Michael Nolan [EMAIL PROTECTED] wrote: 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. Here are some basic facts of life about PITR/log shipping. It is a disaster prevention feature. Here's the scenario: You are going to depend upon it on 3 a.m. early saturday morning three years in the future, only you are not going to be there. A drive just went out on the main, but instead of degrading the entire backplane went amber. You are going to be in Barbados on your honeymoon, with no connectivity to the outside world (your blushing bride made you leave the laptop at home). The guy doing the switchover to the standby is the only one that could be gotten a hold of, he still hasn't gotten over the 12 hour bender from Friday. He's never really understood why your company took your advice and went with PostgreSQL instead of SQL Server, is cranky, and doesn't like you that much. He secretly hopes the standby wont come up and barely knows how to use a console. write the script. test it. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PITR - base backup question
On Tue, Aug 26, 2008 at 6:53 PM, Richard Broersma [EMAIL PROTECTED] wrote: From the following link: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP Step 3 says to perform the back up. Does this mean a File System Backup of the Data directory? OR Does this mean performing a pg_dumpall and backing up the dump file? File system backup. You tar up the database folder except pg_xlog (and maybe pg_log). You then expand it on the other side (making sure to set up pg_xlog properly). If you ever want to mess around with log shipping I strongly suggest you go through the motions of setting up a warm standby vi the pg_standby utility and practice popping the standby out of recovery. It will give you a good understanding of the process. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PITR - base backup question
On Tue, Aug 26, 2008 at 5:38 PM, Merlin Moncure [EMAIL PROTECTED] wrote: If you ever want to mess around with log shipping I strongly suggest you go through the motions of setting up a warm standby vi the pg_standby utility and practice popping the standby out of recovery. Thanks for the reply! I want to be certain that I understand the point you are making: Is setting up a warm standby server good pratice for gaining experience in PITR or do you mean that PITR is a good starting point for setting up a warm standby server? -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
