Re: pg_start_backup - backups

Thanks Kevin and Ian for your replies. Your information confirms
activity I was noticing, but without indepth knowledge of what goes on
"behind the current" I was left to speculation - a situation I am
extremely uncomfortable with. I have read and re-read the existing
documentation on backup and restore for postgresql and I believe we have
all of the pieces in place. You both have been a big help.

Thanks,
David

-----Original Message-----
From: Kevin Grittner [mailto:Kevin(dot)Grittner(at)wicourts(dot)gov]
Sent: Monday, April 11, 2011 10:47 AM
To: Ian Lea; David Roland
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] pg_start_backup - backups

"David Roland" <david(dot)roland(at)soapware(dot)com> wrote:

> changes to the data folder continue even after the PG_START_BACKUP
> command has been issued. This implies to me that the contents of any
> copy of the data folder may be unreliable. i.e. the copy may not
> reflect the state of the data folder either before the copy started or

> after the copy has finished. It may reflect the state of the data
> folder in some transient form.

Right.

> Assuming this is true, is the copy still usable for restoration?

Yes.

> If so, how does PostgreSQL get the data folder to a stable state? Is
> it by the use of the WAL files that may be created during the backup
> process and the restore.config file?

Exactly. Simplifying somewhat:

- The pg_start_backup causes the WAL position to be remembered. You
will need to start WAL replay at this point.

- Every significant change to a page is WAL-logged.

- Changes after you record the restart point may or may not be in
the base backup.

- After the base backup copying is complete, pg_stop_backup records
the WAL position. You will need to replay WAL *at least* to this
point to have a consistent database.

- WAL replay will bring every page modified after the "start" to a
valid state, whether or not any or all direct modifications to
that page made it into the base backup.

- This actually goes beyond page fix-ups to file creation and
deletion, etc.

-Kevin