Обсуждение: Backup and failover process
PostgreSQL: 8.2
I am about to change my backup and failover procedure from dumping a full file SQL dump of our data every so many minutes to using WAL files. Could someone review the below strategy to identify if this strategy has any issues?
1) On the primary server, all WAL files will be written to a backup directory. Once a night I will delete all of the WAL files on the primary server from the backup directory. I will create a full file SQL dump of the database and put it into the same backup folder that the WAL files are put in. The backup directory will be rsynced to the failover server. This will cause the failover server to delete all of the WAL files it has copies of each night.
2) On the primary server, I will then check periodically with cron during the day to see if there is a new WAL file. If there is a new WAL file I will then copy it to the fail over server.
3) At the end of the day I will repeat step #1.
In the event of a failure a script is ran that converts the failover server to the primary server. After starting PostgreSQL the server would load the full file SQL dump. The server would then apply all of the WAL files it has in the backup directory.
Is there any problems with the process I am considering? My only concern is in step one. If I create a full file SQL dump how do I know that some of the transactions have not already been applied by the first WAL file that is created each night? What will happen if I try to restore from the first WAL file? Will PostgreSQL some how know that some of the transactions have already been applied from the first WAL file? Will it just ignore those transactions? Or will PostgreSQL just fail to reload the WAL file?
Thanks,
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
My e-mail address has changed to lance@illinois.edu
>>> "Campbell, Lance" <lance@illinois.edu> wrote: > PostgreSQL: 8.2 > I am about to change my backup and failover procedure from dumping a full > file SQL dump of our data every so many minutes You're currently running pg_dump every so many minutes? > to using WAL files. Be sure you have read (and understand) this section of the docs: http://www.postgresql.org/docs/8.2/interactive/backup.html -Kevin
Kevin, I have read this documentation. I still does not answer my basic question. What happens if you take an SQL snapshot of a database while creating WAL archives then later restore from that SQL snapshot and apply those WAL files? Will there be a problem if the transactions within the newest WAL file after the SQL snapshot was taken cause problems when they are applied? I would assume yes but I wanted to check if there was some type of timestamp that would prevent an issue from occurring? Thanks, -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] Sent: Tuesday, July 15, 2008 12:24 PM To: Campbell, Lance; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Backup and failover process >>> "Campbell, Lance" <lance@illinois.edu> wrote: > PostgreSQL: 8.2 > I am about to change my backup and failover procedure from dumping a full > file SQL dump of our data every so many minutes You're currently running pg_dump every so many minutes? > to using WAL files. Be sure you have read (and understand) this section of the docs: http://www.postgresql.org/docs/8.2/interactive/backup.html -Kevin
>>> "Campbell, Lance" <lance@illinois.edu> wrote: > I have read this documentation. > I wanted to check if there was some type of timestamp My previous email omitted the URL I meant to paste: http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html#RECOVERY-CONFIG-SETTINGS -Kevin
>>> "Campbell, Lance" <lance@illinois.edu> wrote: > What happens if you take an SQL snapshot of a database while > creating WAL archives then later restore from that SQL snapshot and > apply those WAL files? What do you mean by "an SQL snapshot of a database"? WAL files only come into play for backup techniques which involve file copies, not dumps done using SQL commands (like pg_dump). > Will there be a problem if the transactions > within the newest WAL file after the SQL snapshot was taken cause > problems when they are applied? Point In Time Recovery (PITR) backup techniques allow you to restore to a specified point in time, so you could restore up to the point immediately before the problem transactions. > I would assume yes but I wanted to > check if there was some type of timestamp that would prevent an issue > from occurring? Take another look at this section -- in particular, recovery_target_time. I hope this helps. -Kevin
On Tue, Jul 15, 2008 at 11:08:27AM -0500, Campbell, Lance wrote: > 1) On the primary server, all WAL files will be written to a backup directory. Once a night I will delete all ofthe WAL files on the primary server from the backup directory. I will create a full file SQL dump of the database andput it into the same backup folder that the WAL files are put in. The backup directory will be rsynced to the failoverserver. This will cause the failover server to delete all of the WAL files it has copies of each night. > 2) On the primary server, I will then check periodically with cron during the day to see if there is a new WAL file. If there is a new WAL file I will then copy it to the fail over server. > 3) At the end of the day I will repeat step #1. I think your outline sounds rather fragile. Moreover, I don't understand why you're planning to delete WAL files from the target server. It seems to me you'd be better off using pg_standby along with some helper applications. (Command Prompt has a tool we use for this, and I believe it's been released, but I'll have to check.) A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
Is this a correct understanding? When restoring using archiving, it is only possible to restore to a database using WAL files if the database you are restoring to was created before the first WAL file you wish to apply was created. So based on the above, if one were to create a backup of a database using pg_dump and then at a later time restore the database using the SQL backup, any WAL files that were created after the pg_dump was created would not work because the database would see itself as existing after the WAL files. Is this correct? -----Original Message----- From: Evan Rempel [mailto:erempel@uvic.ca] Sent: Tuesday, July 15, 2008 9:46 PM To: Campbell, Lance Subject: Re: [ADMIN] Backup and failover process You can not mix WAL recovery/restore and pg_dump restores. To restore a pg_dump, you require a fully functioning postgresql server, which makes its own WAL files. After the restore of the pg_dump, you can not interject the WAL archive files. The WAL archive files can only be used to roll-forward from known checkpoints (known by the "recovery mode" internal to postgresql. You can use pg_dump to get specific snapshots of databases, but you can not roll transactions forward from the pg_dump using WAL files. We use pg_dump to get daily snapshots of databases so that if a user accidentally breaks their database, we can upon request, restore to the previous nights backup. We also use filesystem backups and WAL files to allow us to recover to any point in time given a disaster. It would be VERY nice to use filesystem backups and WAL files to recover a SINGLE database, but that is currently on my wish list. Evan Rempel. Campbell, Lance wrote: > Kevin, > I have read this documentation. I still does not answer my basic > question. What happens if you take an SQL snapshot of a database while > creating WAL archives then later restore from that SQL snapshot and > apply those WAL files? Will there be a problem if the transactions > within the newest WAL file after the SQL snapshot was taken cause > problems when they are applied? I would assume yes but I wanted to > check if there was some type of timestamp that would prevent an issue > from occurring? > > Thanks, > > -----Original Message----- > From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] > Sent: Tuesday, July 15, 2008 12:24 PM > To: Campbell, Lance; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Backup and failover process > >>>> "Campbell, Lance" <lance@illinois.edu> wrote: >> PostgreSQL: 8.2 >> I am about to change my backup and failover procedure from dumping a > full >> file SQL dump of our data every so many minutes > > You're currently running pg_dump every so many minutes? > >> to using WAL files. > > Be sure you have read (and understand) this section of the docs: > > http://www.postgresql.org/docs/8.2/interactive/backup.html > > -Kevin >
Got it. Thanks a bunch. Your last email put it all together. Thanks, -----Original Message----- From: Evan Rempel [mailto:erempel@uvic.ca] Sent: Wednesday, July 16, 2008 10:22 AM To: Campbell, Lance Subject: Re: [ADMIN] Backup and failover process postgres does not use "time" to determine if a WAL file contains transactions that come before or after a pg-dump. It uses transaction numbers. pg_dump does NOT dump the transaction numbers. When reloading from a pg-dump file, the instance of postgresql that you are loading into will generate all of the transaction numbers, and they will NOT be appropriate for using the WAL files. You will not be able to copy WALL files into the xlog directory and roll them into a different instance of postgresql. To make use of the WAL files, you MUST have the filesystem level restore of the entire postgresql cluster/instance, not just the pg_dump of a single database. In my opinion, to get reliable point in time failover, you need one of 1. The system administrators provide failover as part of thier postgres offering 2. You need assistance from the system administrators to set up failover 3. You need to administer the machines yourself. Just my $0.02 Evan. Campbell, Lance wrote: > Evan, > During failover my idea was to load my database from a nightly dump > created from pg_dump. Then apply all of the WAL files from when the > full backup was created by pg_dump. > > Are you saying that the database would not be able to load the WAL > files? > > I thought I might have to start PostgreSQL in standard mode. Restore > from the pg_dump. Then start it using archiving. Then restore using > WAL files. Does this not work? > > Thanks, > > -----Original Message----- > From: Evan Rempel [mailto:erempel@uvic.ca] > Sent: Tuesday, July 15, 2008 9:46 PM > To: Campbell, Lance > Subject: Re: [ADMIN] Backup and failover process > > You can not mix WAL recovery/restore and pg_dump restores. To restore a > pg_dump, you > require a fully functioning postgresql server, which makes its own WAL > files. After the > restore of the pg_dump, you can not interject the WAL archive files. > > The WAL archive files can only be used to roll-forward from known > checkpoints (known by > the "recovery mode" internal to postgresql. > > You can use pg_dump to get specific snapshots of databases, but you can > not roll transactions > forward from the pg_dump using WAL files. > > We use pg_dump to get daily snapshots of databases so that if a user > accidentally > breaks their database, we can upon request, restore to the previous > nights backup. > > We also use filesystem backups and WAL files to allow us to recover to > any point in > time given a disaster. > > It would be VERY nice to use filesystem backups and WAL files to recover > a SINGLE database, > but that is currently on my wish list. > > Evan Rempel. > > > Campbell, Lance wrote: >> Kevin, >> I have read this documentation. I still does not answer my basic >> question. What happens if you take an SQL snapshot of a database > while >> creating WAL archives then later restore from that SQL snapshot and >> apply those WAL files? Will there be a problem if the transactions >> within the newest WAL file after the SQL snapshot was taken cause >> problems when they are applied? I would assume yes but I wanted to >> check if there was some type of timestamp that would prevent an issue >> from occurring? >> >> Thanks, >> >> -----Original Message----- >> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov] >> Sent: Tuesday, July 15, 2008 12:24 PM >> To: Campbell, Lance; pgsql-admin@postgresql.org >> Subject: Re: [ADMIN] Backup and failover process >> >>>>> "Campbell, Lance" <lance@illinois.edu> wrote: >>> PostgreSQL: 8.2 >>> I am about to change my backup and failover procedure from dumping a >> full >>> file SQL dump of our data every so many minutes >> >> You're currently running pg_dump every so many minutes? >> >>> to using WAL files. >> >> Be sure you have read (and understand) this section of the docs: >> >> http://www.postgresql.org/docs/8.2/interactive/backup.html >> >> -Kevin >> > > -- Evan Rempel erempel@uvic.ca Senior Programmer Analyst 250.721.7691 Computing Services University of Victoria