Re: pg_start_backup - backups

> First of all, thanks so much for the reply. To be clear, as stated in my
> original post, if a user is interacting with the PostgreSQL database,
> 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. Assuming this is true, is the copy still usable for
> restoration? 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?

The copy is not useful by itself, but is when used in conjunction with
the WAL archives.

It is all explained, much better than I could do it, in
http://www.postgresql.org/docs/9.0/interactive/continuous-archiving.html

There are other backup strategies too.
http://www.postgresql.org/docs/9.0/interactive/backup.html

--
Ian.

> -----Original Message-----
> From: Ian Lea [mailto:ian(dot)lea(at)gmail(dot)com]
> Sent: Friday, April 08, 2011 3:55 AM
> To: David Roland
> Cc: pgsql-admin(at)postgresql(dot)org
> Subject: Re: [ADMIN] pg_start_backup - backups
>
>> Attempting to run a backup with the database online. I first issue
>> 'PG_START_BACKUP', then copy the contents of the postgresql data
>> folder, then issue 'PG_STOP_BACKUP'. The database service remains
>> running during the copy. If queries continue to hit the database after
>
>> the PG_START_BACKUP command is issued changes to the data folder are
>> possible, which can cause copy errors (for instance, a file not found
>> exception). The file copy method first creates a string array of the
>> data folder contents (all folders and
>> files) and then processes the array, copying one file at a time. What
>> am I doing wrong?
>
> You need to use a program to do the copying that can accept changes to
> files as it runs.  rsync is a common choice and has the massive
> advantage that it only takes changes and is therefore, after the first
> run, quick.
>
> See
> http://www.postgresql.org/docs/9.0/static/continuous-archiving.html#BACK
> UP-BASE-BACKUP
> or use your favourite search engine to find more info.
>
>
> --
> Ian.
>