Re: Standalone Hot Backups - Mailing list pgsql-admin

On Wed, Aug 21, 2013 at 09:35:23PM +0400, Sergey Arlashin wrote:
> Hi!
> I'm reading this article http://www.postgresql.org/docs/current/static/continuous-archiving.htm
>
> In section 'Making a Base Backup Using the Low Level API' is said that once one has WAL archiving set up it is ok to
omitpg_xlog folder from the backup dump: 
>
> > "You can, however, omit from the backup dump the files within the cluster's pg_xlog/ subdirectory. This slight
adjustmentis worthwhile because it reduces the risk of mistakes when restoring. This is easy to arrange if pg_xlog/ is
asymbolic link pointing to someplace outside the cluster directory, which is a common setup anyway for performance
reasons.You might also want to exclude postmaster.pid and postmaster.opts, which record information about the running
postmaster,not about the postmaster which will eventually use this backup. (These files can confuse pg_ctl.)" 
>
> But in section related to making standalone hot backups there is no information about it.
>
> There is only the following set of commands:
>
> touch /var/lib/pgsql/backup_in_progress
> psql -c "select pg_start_backup('hot_backup');"
> tar -cf /var/lib/pgsql/backup.tar /var/lib/pgsql/data/
> psql -c "select pg_stop_backup();"
> rm /var/lib/pgsql/backup_in_progress
> tar -rf /var/lib/pgsql/backup.tar /var/lib/pgsql/archive/
>
> I only can see that the backup folder is being archived with pg_xlog.
>
>
> So, the question is - is it ok to omit pg_xlog folder from backup dump while making standalone hot backups or not ?

In order to use a base backup as a standalone hot backup, you need
the WAL files which were generated between the time that you started
the backup and the time it finished. These files are generated in the
pg_xlog directory and are automatically removed when no longer required.

In the first case, you will have them from your archive and therefore
you don't need the pg_xlog directory. In the second case, it's assumed
that you've not got archiving set up and the paragraph before the one you
quoted describes the use of the file /var/lib/pgsql/backup_in_progress
to turn archiving on temporarily; the last command in the sequence adds
the files from the temporary archive to the base backup in the tar file.
So you don't actually need the pg_xlog directory and it would be OK for
the base backup to have excluded it.

Short answer: it is OK to omit pg_xlog, because as part of a standalone
hot backup you will always have the WAL files from another source.

Richard

--
Richard Poole                 http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services