Ubuntu Manpage: pg_backup_ctl - Utility to backup and recover a running PostgresSQL database server

NAME

       pg_backup_ctl - Utility to backup and recover a running PostgresSQL database server

SYNOPSIS

       pg_backup_ctl [options...]

DESCRIPTION

       pg_backup_ctl  is  a  tool to simplify the steps needed to make a full transaction log archival Backup of
       PostgreSQL  clusters.  All   steps   performed   by   this   script   can   also   done   manually.   See
       http://www.postgresql.org/docs/9.2/interactive/continuous-archiving.html  for  details.  Furthermore this
       script implements several functions to prepare for backups using LVM-Snapshots.

       This script supports PostgreSQL 8.3 and above.

       While setup pg_backup_ctl will make some modifications to postgresql.conf specified in running_config. To
       work correctly 3 options will be set:
           - archive_command used to copy archived WAL files to archivedir/log/ directory
           - wal_level will be set to archive, if isn't already at archive or hot-standby.
           -  archive_mode  will  be  set  to on If these modifications aren't already done, the server needs to
       restart after setup to take over these changes.

       pg_backup_ctl should be run as postgres user (or any other user that  runs  the  postgresql  daemon).  In
       addition,  if the LVM features where used, the user needs the appropriate permissions (add sudo execution
       privileges for postgres to the following commands: lvcreate, lvremove, lvdisplay, mount, lvdisplay)

MODES

setup
Prepare server for transaction log archival and setup the environment.

This adjusts the server configuration file as required and reloads the server configuration.
However, since PostgreSQL 8.3 the script needs to set the archive_mode explicitly to on, forcing the
administrator to restart the PostgreSQL instance (if required). After a restart (assuming there is
database activity), you should be seeing files appearing in /var/lib/pgsql/backup/log.

basebackup
Perform a base backup of the currently running PostgreSQL cluster.

You should run this command from a cron job (mind the postgres user) once a night. (Once a week or
other intervals are also conceivable but will lead to huge recovery times for your data volume.)

You could replace this step by taking a file system snapshot. This might save space and time but
would otherwise be functionally equivalent. You can also alter the script accordingly. Look into
the line that calls the tar program.

lvmbasebackup
Perform a base backup using LVM snapshot. (Requires -L, -M, -n, -N)

create-lvmsnapshot
Create an LVM snapshot for an external backup command like bacula. (Requires -L, -M, -n, -N)

rsyncbackup
Perform a basebackup with rsync.

This command requires rsync accessible via PATH. Backups performed with rsyncbackup will save disk
space between multiple runs for unchanged files, since they are just hardlinked. See the --link-dest
parameter of the rsync command.

streambackup
Perform a streaming basebackup.

This command requires PostgreSQL 9.1 and above and pg_basebackup accessible via PATH. The server
should be configured to allow streaming replication connections.

remove-lvmsnapshot
Remove an LVM snapshot created with create-lvmsnapshot.

restore basebackup
Restores the specified basebackup into the specified directory by the -D parameter.

The directory must already exists and be empty. If the directory, wich is specified as target
location, isn't empty pg_backup_ctl will refuse it as target.

The destination directory will also contain a generated recovery.conf, suitable to start a PostgreSQL
instance for recovery immediately.

It is still possible to do the recovery process completely manually. The recovery process is detailed
in the documentation.

currentbackup
Backup the current WAL file. This command should be called from a cronjob.

cleanup [ filename | xlog | +[1-9]* ]
Remove old WAL files after new base backup. This command sould also run by a cron job.

One typically runs this command from a cron job once a minute (whatever the desired backup frequency)
your data volume will cause log segments to fill up on the order of minutes anyway, so this step can
then be omitted.

This will only clean up old log segments. Old base backups have to be removed manually.

It is possible to specify the base backup filename filename, wich WAL files should be kept at least
or the WAL file xlog. If a positive number greater than zero is specified, the cleanup command will
treat it as its retention policy and keep at least this number of base backup files. Please note that
the latter form of the cleanup command will delete all outdated base backups as well whereas the two
forms of cleanup invoke with filename will delete the WAL files only.

If no argument is specified, cleanup will remove all WAL files except those wich are required by the
latest base backup.

ls[+]
Lists available base backups and there size in the current archive. When issued with +, the ls
command will examine the WAL archive and the minimum WAL segment file, required to use the backup to
perform a full recovery.

OPTIONS

The following command-line options control actions done by pg_backup_ctl.

-A archivedir
Directory with will contain the backup-files. This parameter is required at all actions. It will also
contain configuration and history files needed for all actions, except setup.

-D datadir
PostgresSQL data directory. If PostgreSQL is running this parameter isn't used and will come from
PostgreSQL running_configuration.

-T TABLESPACE
Target directory for tablespace location during restore (replaces the original symlinks in the base
backup, but places all tablespaces into one directory).

-m
Take care of old archive log files, archive these files before deleting them.

-z
Compression. Use gzip to compress archived WAL segments.

-L lvm-size
Determines the buffer size for an LVM snapshot.

-M volume
LVM volume identifier to create the snapshot on. Needed for LVM-Backup.

-n name
LVM snapshot volume name. Needed for LVM-Backup. The backup_lable will be named after it.

-N lvm-data-dir
PostgreSQL DATADIR relative to partition (i.e the path to DATADIR inside the LVM snapshot).

-o mount-options
Additional options passed to LVM snapshot mount.

-h hostname
Server hostname that running PostgreSQL. Specifies the host name of the machine on which the server
is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.
(See pgsql(1) for details)

-p port
Specifies the TCP port or local Unix domain socket file extension on which the server is listening
for connections. (See pgsql(1) for details)

-M username
User name to connect as.

EXAMPLES

       To  setup  the  environment  and  PostgreSQL  configuration  settings  for  further  cluster  backups  at
       /mnt/backup/pgsql:

           $ pg_backup_ctl -A /mnt/backup/pgsql setup

       To do a base backup (Note: setup the environment is required):

           $ pg_backup_ctl -A /mnt/backup/pgsql basebackup

       To do a streaming base backup backup (Note: setup the environment and configuring the PostgreSQL database
       server to allow streaming replication is required):

           $ pg_backup_ctl -A /mnt/backup/pgsql streambackup

       To do a base backup backup with rsync (Note: setup the environment is required):

           $ pg_backup_ctl -A /mnt/backup/pgsql rsyncbackup

       To copy the current log segment(s):

           $ pg_backup_ctl -A /mnt/backup/pgsql currentbackup

       To show up the backups that are available:

           $ pg_backup_ctl -A /mnt/backup/pgsql ls+

       To  recover  a  base backup (e.g. basebackup_2013-01-04T1517.tar.gz) to the directory /recover/pgsql (the
       server may not run):

           $ pg_backup_ctl -A /mnt/backup/pgsql -D /recovery/pgsql restore basebackup_2013-01-04T1517.tar.gz

           $ pg_ctl start -D /recovery/pgsql

CAVEATS

       pg_backup_ctl internally protects itself against concurrent execution with the flock command  line  tool.
       This  places  a  lock file into the archive directory, which will hold an exclusive lock on it to prevent
       another pg_backup_ctl to concurrently modify the archive. This doesn't work on network  filesystems  like
       SMBFS  or  CIFS,  especially  when  mounted from a Windows(tm) server. In this case you should use the -l
       option to place the lockfile into a directory on a local filesystem.  Older  distributions  doesn't  have
       the flock command line tool, but it's possible to just comment out the locking subscripts.