When you go to restore from a give base file system backup you need to know the start WAL file that you need and the end WAL file that you need. (You will most likely have many files beyond the "stop" file but you must have at least up to the "stop" file for the restore to work.
Now if you try to restore but you don't have the "stop" WAL file postges will die on recovery and tell you that it can't recover forward far enough to make the backup consistent. But I wanted to know the easiest way to verify if you indeed had the necessary files without having to actually do a restore and have postgres tell you if it succeeded or not.
Perhaps no one understood me because the answer I was looking for was too obvious. But what I really wanted to know was how do you know what the "stop" file is. It informs you of the start file all over the place when doing the base backups but I thought I would have to do something clever to figure out the stop file on my own. But luckily I don't. The backup history file has too lines like this:
START WAL LOCATION: 0/88F21D0C (file 000000010000000000000088) STOP WAL LOCATION: 0/88F21D50 (file 000000010000000000000088)It was clear to me from the docs how to figure out what the start file is but the end file was a mystery until I actually created a backup history file and looked in it. The only place I can find in the Online Backup instructions where this is indicated is this paragraph:
"To make use of this backup, you will need to keep around all the WAL segment files generated during and after the file system backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need to have to make use of the backup. For example, if the starting WAL file is 0000000100001234000055CD the backup history file will be named something like 0000000100001234000055CD.007C9330.backup. (The second number in the file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the file system backup and the WAL segment files used during the backup (as specified in the backup history file), all archived WAL segments with names numerically less are no longer needed to recover the file system backup and may be deleted. However, you should consider keeping several backup sets to be absolutely certain that you can recover your data. Keep in mind that only completed WAL segment files are archived, so there will be delay between running pg_stop_backup and the archiving of all WAL segment files needed to make the file system backup consistent."
Reading it now it seems obvious that the file would contain not only the start WAL file but also the Stop WAL file but when going over the directions the first time it did not pick up on it. And it left me thinking I would have to use some hack to figure it out if I ever wanted to test a base backup. It would have been less confusing to me if it just said right in the docs: "The backup history file contains both the start WAL file name and the Stop WAL file name" or something like that just to make it perfectly clear.
Now that I know this I can extract that filename from the backup history file, check to see if it has been archived and copy it somewhere if it hasn't been archived yet. I'm pretty sure that I can assume that all files before the stop file have already been archived. So once I backup the stop file I can be positive that the base backup I just made will be valid when I try to restore from it.
This lessens my need for the "get current WAL file" functionality in this context. It will still be nice to have in the context of backing it up every five minutes or so in case a WAL file takes a long time to fill up.
Anyway I would have been less confused if the docs had made it more clear that the name of the stop wal file was in the backup history file.
Rick On Jan 30, 2006, at 10:20 PM, Bruce Momjian wrote:
Yes, I think copying it while it is being written is safe.---------------------------------------------------------------------- -----Rick Gigger wrote:Yes! Thanks you! That is exactly what I was looking for. So I take it that this means that it is save to copy the current in use WAL file even as it is being written to? And it also means that if I copy it with my physical file system backup then I should have the last file that I need to restore from that physical backup? So if I write my own backup_latest_WAL_file.sh script (I think I found one on the list from Simon Riggs) then I can do what I need to do before those todo items get done? Or will I need to wait till postgres gives me the ability to safely copy the file? On Jan 30, 2006, at 11:13 AM, Bruce Momjian wrote:Unfortunately, I think I understand your question. :-) These TODO items are what you need: * Point-In-Time Recovery (PITR) o Allow point-in-time recovery to archive partially filled write-ahead logs [pitr] Currently only full WAL files are archived. This means that the most recent transactions aren't available for recovery in case of a disk failure. This could be triggered by a user command or a timer. o Automatically force archiving of partially-filled WAL files when pg_stop_backup() is called or the server is stopped Doing this will allow administrators to know more easily when the archive contains all the files needed for point-in- time recovery. I will try to push to have them done for 8.2.-------------------------------------------------------------------- ------- Rick Gigger wrote:I guess my email wasn't all that clear. I will try to rephrase. I am moving from using the old style pg_dump for backups to usingincrementals and want to make sure I understand the process before Igo about writing a bunch of scritps. To me setting up incremental backup consists of the following components: 1) Setting up the WAL archiving. This one is trivial. 2) Doing physical dumps of the $PGDATA directory. This one is once again trivial. 3) Knowing which physical dumps are Good and Not Good. For a given physical dump D there is are WAL archive files Dstart and Dend forwhich you much have Dstart and Dend and all files in between. If youhave all those files then the physical dump is Good. If you don't have them then the dump is worthless to you.4) Knowing which dumps and which archive files can be deleted. Thisdepends on a number of factors. a) How far back do you want to be able to do PITR b) How much space do you have / want to use for PITR c) Which physical dumps are Good and which are Not Good. (see #3) Now I think I have a pretty good plan here except for #3 (and so #4 then also suffers).Just as an example lets say I'm not concerned so much with PITR as Iam recovering from a db crash. I've got all the backups files saved to my backup db server so I can failover to it if my primary db server dies. I just want to make sure I've got one physical dumpthat is good. (This is not my actual situation but it illustrated mypoint better.)Now when I do a physical dump it is not a Good dump. That is I don'thave the end archive file necessary to recover from that physical dump. That is to say that when I call pg_backup_start() then copy $PGDATA then call pg_backup_stop() postgres might be on say WAL archive file #5. Once the physical dump is completed WAL archive file #5 hasn't been archived yet. I only have up to #4. So if Idelete my old physical dumps and all I've got is this most recent oneand my database crashes before #5 gets archived then I am hosed. I have no good physical backups to start from.My main question is about the best way to figure out when a physicaldump is Good.One strategy is to always keep around lots of physical dumps. If youkeep around 100 dumps you can be pretty sure that in the space oftime that those physical dumps take place that at least one WAL file was archived. In fact if you keep 2 physical dumps you can be fairly certain of this. If not then you really need to space our your dumpsmore. Is this making sense at this point? The problem is that the WAL archiving is triggered by postgres and the rate at which the db is updated. The physical dumps aretriggered by cron and on a purely time based schedule. So in theoryif you had the physical dumps happening once a day but for some odd reason no one updated the database for 4 days then all of a sudden you'd have 2 physical backups and neither of them are good. If you're db crashes during that time you are hosed. Maybe I am arguing a point that is just stupid because this willnever happen in real life. But in that it is my backups system thatI will be using to recover from complete and total disaster I just want to have all my bases covered. So my ideas on how to determine if a physical dump is Good are as follows.1) When you do the physical backup (after dumping the $PGDATA dir butbefore calling pg_stop_backup() ) determine the current WAL archive file. Mark somewhere in the backed up physical dump the last file needed for the dump to be considered good. Then your deletion scripts can look at the WAL archive files you have and the last onerequired for the dump to be Good and determine if the dump is Good ornot.2) After doing the physical dump but before calling pg_stop_backup()just copy the current WAL file to the physical dump. If that file later gets archived then the restore commands overwrites yourpartially completed one so it doesn't hurt but you know that when you call pg_stop_backup() that that physical dump is good. (Is it ok tocopy the current WAL file while it is still in use?) Is anyone taking one of these or any other precautions to make sure they've got a good physical dump or does everyone just keep a whole bunch of dumps around, and then actually restore the dump to see if it is good and if not go back to a previous dump? I hope that makes more sense. Thanks, Rick On Jan 27, 2006, at 3:33 AM, Richard Huxton wrote:Rick Gigger wrote:Um, no you didn't read my email at all. I am aware of all of thatand it is clearly outlined in the docs. My email was about a specific detail in the process. Please read it if you want to know what my actual question was.I'm not sure your email is quite right as regards the process. You need: 1. the filesystem backup 2. the WAL file indicated in the history-file 3. all the WAL files later than that to get up to "now".If you don't want to replay up to "now" then you will not need someof the more recent WAL files. You can't afford to throw them away though since you've got a rolling backup system running and the whole point is so you can recover to any point you like. You can however throw away any WAL files older than that indicated in the history file for your current filesystem-backup. You can then only restore from that point in time forward. There is no "last one" in the WAL set unless you know the time you want to restore to. Indeed, the "last one" might not be "full" yet and therefore archived if you want to restore to 10 seconds ago. Or am I mis-understanding your email too? -- Richard Huxton Archonet Ltd---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------TIP 4: Have you searched our list archives? http://archives.postgresql.org-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road+ Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------TIP 4: Have you searched our list archives? http://archives.postgresql.org
---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
