Thread: Am I missing something about the output of pg_stop_backup()?

Am I missing something about the output of pg_stop_backup()?

From
Ben
Date:
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?

Re: Am I missing something about the output of pg_stop_backup()?

From
Richard Huxton
Date:
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.

Re: Am I missing something about the output of pg_stop_backup()?

From
Greg Smith
Date:
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
>

Re: Am I missing something about the output ofpg_stop_backup()?

From
"Simon Riggs"
Date:
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