PG_BACKUP_CTL(1) | pg_backup_ctl | PG_BACKUP_CTL(1) |
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
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
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
create-lvmsnapshot
rsyncbackup
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
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
restore basebackup
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
cleanup [ filename | xlog | +[1-9]* ]
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[+]
OPTIONS¶
The following command-line options control actions done by pg_backup_ctl.
-A archivedir
EXAMPLES¶
To setup the environment and PostgreSQL configuration settings for further cluster backups at /mnt/backup/pgsql:
To do a base backup (Note: setup the environment is required):
To do a streaming base backup backup (Note: setup the environment and configuring the PostgreSQL database server to allow streaming replication is required):
To do a base backup backup with rsync (Note: setup the environment is required):
To copy the current log segment(s):
To show up the backups that are available:
To recover a base backup (e.g. basebackup_2013-01-04T1517.tar.gz) to the directory /recover/pgsql (the server may not run):
$ 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.
SEE ALSO¶
pg_dump(1), pgsql(1), pg_basebackup(1), flock(1)
2015-10-11 | PostgreSQL Tools |