Thread: Am I missing something about the output of pg_stop_backup()?
So, I'm working on a script that does PITR and basing it off the one here: http://archives.postgresql.org/pgsql-admin/2006-03/msg00337.php (BTW, thanks for posting that, Rajesh.) My frustration comes from the output format of pg_stop_backup(). Specifically, it outputs a string like this: 550/E788D70 ...which I then need to tokenize in order to find which archived wal files I'll need to keep as part of the backup. For instance, that output above will result in a WAL file named like so: 00000001000005500000000E.00788D70.backup That's easy enough to parse when using fixed positions, but sometimes pg_stop_backup won't always give me a string with the same number of characters. In fact, I suspect the output can be up to 23 characters (including the /) if I've commided enough transactions.... and that's before the first 1 in the WAL filename presumably increments past 1. At which point, I'm not sure what I'd see. "2/ABC/12345678"? I guess my questions boil down to: 1. Why not delimit the tokens it's obviously trying to represent with a delimiter, instead of just mashing the second two tokens together? 2. Why use / to delimit the first two tokens? That's unhelpfully the character usually used for pattern matching, and so needs to be escaped. 3. Am I missing an obvious way to map this function output to the generated WAL filname? 4. Actually.... why doesn't pg_stop_backup() just output the WAL filename that was used?
Ben wrote: > So, I'm working on a script that does PITR and basing it off the one here: > > http://archives.postgresql.org/pgsql-admin/2006-03/msg00337.php > > (BTW, thanks for posting that, Rajesh.) > > My frustration comes from the output format of pg_stop_backup(). > Specifically, it outputs a string like this: > > 550/E788D70 > > ...which I then need to tokenize in order to find which archived wal > files I'll need to keep as part of the backup. For instance, that output > above will result in a WAL file named like so: > > 00000001000005500000000E.00788D70.backup > 4. Actually.... why doesn't pg_stop_backup() just output the WAL > filename that was used? Have you looked in the "backup history file": http://www.postgresql.org/docs/8.2/static/continuous-archiving.html#BACKUP-BASE-BACKUP "The backup history file is just a small text file. It contains the label string you gave to pg_start_backup, as well as the starting and ending times and WAL segments of the backup. If you used the label to identify where the associated dump file is kept, then the archived history file is enough to tell you which dump file to restore, should you need to do so." -- Richard Huxton Archonet Ltd
On Tue, 10 Jul 2007, Richard Huxton wrote: > Have you looked in the "backup history file": > http://www.postgresql.org/docs/8.2/static/continuous-archiving.html#BACKUP-BASE-BACKUP > > "The backup history file is just a small text file. It contains the label > string you gave to pg_start_backup, as well as the starting and ending times > and WAL segments of the backup. If you used the label to identify where the > associated dump file is kept, then the archived history file is enough to > tell you which dump file to restore, should you need to do so." Yeah, I saw that, but looking for the most recent backup history file and blindly assuming that's what I want, regardless of what pg_stop_backup() told me, seems like it might be problematic in some random corner cases. For instance, in the case when the backup history file from the previous backup is, for some reason, still around, but the backup history file from the current run has yet to be written, then I'd backup more WAL files than I need. That's hardly a critical error, but the general method strikes me as far from bulletproof and there may well be another corner case I'm not thinking of right now that *would* be a critical error. I was hoping I could parse the output of pg_stop_backup() more cleanly than it seems possible to do..... but, well, for reasons unclear to me, it's not so clean to do it right.
On Tue, 10 Jul 2007, Ben wrote: >> "The backup history file is just a small text file. It contains the label >> string you gave to pg_start_backup, as well as the starting and ending >> times and WAL segments of the backup. > For instance, in the case when the backup history file from the previous > backup is, for some reason, still around, but the backup history file > from the current run has yet to be written, then I'd backup more WAL > files than I need. You should give each backup a unique label (normally today's date suffices) and confirm that the history file you find has that label in it. That's how you prevent this class of problem. If you don't find the label you expect in the history, abort the whole thing because your backup didn't happen correctly. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
Ah, perfect, that's what I was looking for. Thanks! On Tue, 10 Jul 2007, Greg Smith wrote: > On Tue, 10 Jul 2007, Ben wrote: > >>> "The backup history file is just a small text file. It contains the label >>> string you gave to pg_start_backup, as well as the starting and ending >>> times and WAL segments of the backup. > >> For instance, in the case when the backup history file from the previous >> backup is, for some reason, still around, but the backup history file from >> the current run has yet to be written, then I'd backup more WAL files than >> I need. > > You should give each backup a unique label (normally today's date suffices) > and confirm that the history file you find has that label in it. That's how > you prevent this class of problem. If you don't find the label you expect in > the history, abort the whole thing because your backup didn't happen > correctly. > > -- > * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >
On Tue, 2007-07-10 at 12:44 -0700, Ben wrote: > ...which I then need to tokenize in order to find which archived wal > files I'll need to keep as part of the backup. For instance, that > output above will result in a WAL file named like so: Try using pg_xlogfile_name() http://www.postgresql.org/docs/8.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP-TABLE -- Simon Riggs EnterpriseDB http://www.enterprisedb.com