sslug-teknik team mailing list archive

[Donald J Axel <donax@xxxxxx>]

On Thu, 30 Jul 2009 13:02:52 +0000 (UTC)
jesper@xxxxxxxx (Jesper Krogh) wrote:

> 'select pg_start_backup("label")'

Det står minsandten på side 432! Chapter-24 Backup and Restore
i min postgresql-8.3-A4.pdf.

Her er noget om "archiving" med pg_start_backup. Den forudsætter en
hulens masse andre ting, selv om den er "relativt simpel", så jeg
tror at man i de fleste tilfælde vil være bedre tjent med 
pg_dumpall(1).

========================================================================


http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html

 The procedure for making a base backup is relatively simple:

   1.

      Ensure that WAL archiving is enabled and working.
   2.

      Connect to the database as a superuser, and issue the command:

      SELECT pg_start_backup('label');

      where label is any string you want to use to uniquely
identify this backup operation. (One good practice is to use the
full path where you intend to put the backup dump file.)
pg_start_backup creates a backup label file, called backup_label,
in the cluster directory with information about your backup.

      It does not matter which database within the cluster you
connect to to issue this command. You can ignore the result
returned by the function; but if it reports an error, deal with
that before proceeding.

      pg_start_backup can take a long time to finish. This is
because it performs a checkpoint, and the I/O required for a
checkpoint will be spread out over a significant period of time, by
default half your inter-checkpoint interval (see the configuration
parameter checkpoint_completion_target). Usually this is what you
want because it minimizes the impact on query processing. If you
just want to start the backup as soon as possible, execute a
CHECKPOINT command (which performs a checkpoint as quickly as
possible) and then immediately execute pg_start_backup. Then there
will be very little for pg_start_backup's checkpoint to do, and it
won't take long.
   3.

      Perform the backup, using any convenient file-system-backup
tool such as tar or cpio. It is neither necessary nor desirable to
stop normal operation of the database while you do this.
   4.

      Again connect to the database as a superuser, and issue the
command:

      SELECT pg_stop_backup();

      This terminates the backup mode and performs an automatic
switch to the next WAL segment. The reason for the switch is to
arrange that the last WAL segment file written during the backup
interval is immediately ready to archive.
   5.

      Once the WAL segment files used during the backup are
archived, you are done. The file identified by pg_stop_backup's
result is the last segment that needs to be archived to complete
the backup. Archival of these files will happen automatically,
since you have already configured archive_command. In many cases,
this happens fairly quickly, but you are advised to monitor your
archival system to ensure this has taken place so that you can be
certain you have a complete backup. 

Some backup tools that you might wish to use emit warnings or
errors if the files they are trying to copy change while the copy
proceeds. This situation is normal, and not an error, when taking a
base backup of an active database; so you need to ensure that you
can distinguish complaints of this sort from real errors. For
example, some versions of rsync return a separate exit code for
"vanished source files", and you can write a driver script to
accept this exit code as a non-error case. Also, some versions of
GNU tar return an error code indistinguishable from a fatal error
if a file was truncated while tar was copying it. Fortunately, GNU
tar versions 1.16 and later exit with 1 if a file was changed
during the backup, and 2 for other errors.

It is not necessary to be very concerned about the amount of time
elapsed between pg_start_backup and the start of the actual backup,
nor between the end of the backup and pg_stop_backup; a few
minutes' delay won't hurt anything. (However, if you normally run
the server with full_page_writes disabled, you might notice a drop
in performance between pg_start_backup and pg_stop_backup, since
full_page_writes is effectively forced on during backup mode.) You
must ensure that these steps are carried out in sequence without
any possible overlap, or you will invalidate the backup.

Be certain that your backup dump includes all of the files
underneath the database cluster directory
(e.g., /usr/local/pgsql/data). If you are using tablespaces that do
not reside underneath this directory, be careful to include them as
well (and be sure that your backup dump archives symbolic links as
links, otherwise the restore will mess up your tablespaces).

You can, however, omit from the backup dump the files within the
pg_xlog/ subdirectory of the cluster directory. This slight
complication is worthwhile because it reduces the risk of mistakes
when restoring. This is easy to arrange if pg_xlog/ is a symbolic
link pointing to someplace outside the cluster directory, which is
a common setup anyway for performance reasons.

To make use of the backup, you will need to keep around all the WAL
segment files generated during and after the file system backup. To
aid you in doing this, the pg_stop_backup function creates a backup
history file that is immediately stored into the WAL archive area.
This file is named after the first WAL segment file that you need
to have to make use of the backup. For example, if the starting WAL
file is 0000000100001234000055CD the backup history file will be
named something like 0000000100001234000055CD.007C9330.backup. (The
second part of the file name stands for an exact position within
the WAL file, and can ordinarily be ignored.) Once you have safely
archived the file system backup and the WAL segment files used
during the backup (as specified in the backup history file), all
archived WAL segments with names numerically less are no longer
needed to recover the file system backup and can be deleted.
However, you should consider keeping several backup sets to be
absolutely certain that you can recover your data.

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.

Since you have to keep around all the archived WAL files back to
your last base backup, the interval between base backups should
usually be chosen based on how much storage you want to expend on
archived WAL files. You should also consider how long you are
prepared to spend recovering, if recovery should be necessary -- the
system will have to replay all those WAL segments, and that could
take awhile if it has been a long time since the last base backup. 




-- 
Donald Axel <donax@xxxxxx>