pg_rman 1.3

documentation in Japanese
  1. Name
  2. Synopsis
  3. Description
  4. Examples
  5. Options
  6. Way to pass options
  7. Restrictions
  8. Details
  9. External Scripts
  10. Download
  11. Installation
  12. Requirements
  13. See Also

Name

pg_rman -- manages backup and recovery of PostgreSQL.

Synopsis

pg_rman [ OPTIONS ] { init |
                      backup |
                      restore |
                      show [ DATE | detail ] |
                      validate [ DATE ] |
                      delete DATE |
                      purge }

pg_rman has the features below:

DATE is the start time of the target backup in ISO-format (YYYY-MM-DD HH:MI:SS). Prefix match is used to compare DATE and backup files.

$ pg_rman show 2009-12 # show backups in a month of December 2009
$ pg_rman validate     # validate all unvalidated backups

pg_rman supports the following commands. See also Options for details of OPTIONS.

Description

pg_rman is a utility program to backup and restore PostgreSQL database. It takes a physical online backup of whole database cluster, archive WALs, and server logs.

pg_rman supports getting backup from standby-site with PostgreSQL 9.0 later, also supports storage snapshot backup.

Initialize a backup catalog

First, you need to create "a backup catalog" to store backup files and their metadata.

$ pg_rman init -B <a backup catalog path>

It is recommended to setup log_directory, archive_mode and archive_command in postgresql.conf before initialize the backup catalog. If the variables are initialized, pg_rman can adjust the configuration file to the setting. In this case, you have to specify the database cluster path for PostgreSQL. Please specify it in PGDATA environmental variable or -D/--pgdata option.

Backup

The mode of backup can be one of the following types.

Pg_rman also can backup PostgreSQL server log files.

Validate backup data

It is necessary to validate the data backuped by pg_rman. Pg_rman uses file size check and CRC for validation.

It is recommended to verify backup files as soon as possible after backup. Unverified backup cannot be used in restore nor in incremental backup.

View backup information

The show command outputs backup lists.

$ pg_rman show
 =====================================================================
 StartTime            EndTime              Mode    Size   TLI  Status
 =====================================================================
 2015-03-27 11:43:31  2015-03-27 11:43:32  INCR    54kB     1  OK
 2015-03-27 11:43:19  2015-03-27 11:43:20  INCR    69kB     1  OK
 2015-03-27 11:43:04  2015-03-27 11:43:06  INCR   151kB     1  OK
 2015-03-27 11:42:56  2015-03-27 11:42:57  INCR    96kB     1  OK
 2015-03-27 11:34:55  2015-03-27 11:34:58  FULL  5312kB     1  OK

show detail command shows more detail information.

$ pg_rman show detail
 ======================================================================================================================
 StartTime            EndTime              Mode    Data  ArcLog  SrvLog   Total  Compressed  CurTLI  ParentTLI  Status
 ======================================================================================================================
 2015-03-27 11:43:31  2015-03-27 11:43:32  INCR    16kB    33MB    ----    54kB        true       1          0  OK
 2015-03-27 11:43:19  2015-03-27 11:43:20  INCR   148kB    33MB    ----    69kB        true       1          0  OK
 2015-03-27 11:43:04  2015-03-27 11:43:06  INCR   173kB    83MB    ----   151kB        true       1          0  OK
 2015-03-27 11:42:56  2015-03-27 11:42:57  INCR   173kB    50MB    ----    96kB        true       1          0  OK
 2015-03-27 11:34:55  2015-03-27 11:34:58  FULL    28MB    83MB    ----  5312kB        true       1          0  OK

The fields are:

And more, when you specify the date in “Start” field, you can see the detail information of the backup.

$ pg_rman show '2011-11-27 19:15:45'
# configuration
BACKUP_MODE=FULL
WITH_SERVERLOG=false
COMPRESS_DATA=false
# result
TIMELINEID=1
START_LSN=0/08000020
STOP_LSN=0/080000a0
START_TIME='2011-11-27 19:15:45'
END_TIME='2011-11-27 19:19:02'
RECOVERY_XID=1759
RECOVERY_TIME='2011-11-27 19:15:53'
TOTAL_DATA_BYTES=1242420184
READ_DATA_BYTES=25420184
READ_ARCLOG_BYTES=32218912
WRITE_BYTES=242919520
BLOCK_SIZE=8192
XLOG_BLOCK_SIZE=8192
STATUS=OK

Restore

Pg_rman restore the backuped data into target database cluster path.

PostgreSQL server should be stopped before restore. In addition, do not erase a original database cluster, because pg_rman has to check the timeline ID or data checksum status from it. Restore command will save unarchived transaction log and delete all database files. You can retry recovery until a new backup is taken. After restoring files, pg_rman create recovery.conf in $PGDATA. The conf file contains parameters to recovery, and you can also modify the file if needed.

It is recommended to take a full backup as soon as possible after recovery is succeeded.

If --recovery-target-timeline is not specified, the last checkpoint’s TimeLineID in control file ($PGDATA/global/pg_control) will be a restore target. If pg_control is not present, TimeLineID in the full backup used by the restore will be a restore target.

When specifying --recovery-target-time, make sure to specify a timestamp greater than (or equal to) the EndTime of the full backup that you want to use as the base.

Delete backups

The delete command deletes all backup files before the specified date not required by other incremental backups. Incremental backups depend on earlier validated full backup.

The following example deletes unneeded backup files to recovery at 12:00 11, September 2009.

$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2015-07-30 13:31:08  2015-07-30 13:31:10  FULL    14MB    15  OK
2015-07-30 13:30:37  2015-07-30 13:30:38  ARCH    53kB    15  OK
2015-07-30 13:30:32  2015-07-30 13:30:33  ARCH    26kB    15  OK
2015-07-30 13:30:11  2015-07-30 13:30:13  FULL    14MB    15  OK
2015-07-30 13:28:10  2015-07-30 13:28:11  INCR   120kB    14  OK
2015-07-30 13:27:45  2015-07-30 13:27:46  INCR    54kB    14  OK
2015-07-30 13:27:32  2015-07-30 13:27:33  INCR    54kB    14  OK
2015-07-30 13:27:25  2015-07-30 13:27:26  INCR    54kB    14  OK
2015-07-30 13:24:02  2015-07-30 13:24:04  FULL    14MB    14  OK

$ pg_rman delete 2015-07-30 13:30:30
WARNING: cannot delete backup with start time "2015-07-30 13:30:11"
DETAIL: This is the latest full backup necessary for successful recovery.
INFO: delete the backup with start time: "2015-07-30 13:28:10"
INFO: delete the backup with start time: "2015-07-30 13:27:45"
INFO: delete the backup with start time: "2015-07-30 13:27:32"
INFO: delete the backup with start time: "2015-07-30 13:27:25"
INFO: delete the backup with start time: "2015-07-30 13:24:02"

$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2015-07-30 13:31:08  2015-07-30 13:31:10  FULL    14MB    15  OK
2015-07-30 13:30:37  2015-07-30 13:30:38  ARCH    53kB    15  OK
2015-07-30 13:30:32  2015-07-30 13:30:33  ARCH    26kB    15  OK
2015-07-30 13:30:11  2015-07-30 13:30:13  FULL    14MB    15  OK

Remove deleted backups

Though delete command removes actual data from file system, there remains some catalog information of deleted backups. In order to remove this, execute purge command.

$ pg_rman show -a
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2015-07-30 13:31:08  2015-07-30 13:31:10  FULL    14MB    15  OK
2015-07-30 13:30:37  2015-07-30 13:30:38  ARCH    53kB    15  OK
2015-07-30 13:30:32  2015-07-30 13:30:33  ARCH    26kB    15  OK
2015-07-30 13:30:11  2015-07-30 13:30:13  FULL    14MB    15  OK
2015-07-30 13:28:10  2015-07-30 13:28:11  INCR   120kB    14  DELETED
2015-07-30 13:27:45  2015-07-30 13:27:46  INCR    54kB    14  DELETED
2015-07-30 13:27:32  2015-07-30 13:27:33  INCR    54kB    14  DELETED
2015-07-30 13:27:25  2015-07-30 13:27:26  INCR    54kB    14  DELETED
2015-07-30 13:24:02  2015-07-30 13:24:04  FULL    14MB    14  DELETED

$ pg_rman purge
INFO: DELETED backup "2015-07-30 13:28:10" is purged
INFO: DELETED backup "2015-07-30 13:27:45" is purged
INFO: DELETED backup "2015-07-30 13:27:32" is purged
INFO: DELETED backup "2015-07-30 13:27:25" is purged
INFO: DELETED backup "2015-07-30 13:24:02" is purged

$ pg_rman show -a
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2015-07-30 13:31:08  2015-07-30 13:31:10  FULL    14MB    15  OK
2015-07-30 13:30:37  2015-07-30 13:30:38  ARCH    53kB    15  OK
2015-07-30 13:30:32  2015-07-30 13:30:33  ARCH    26kB    15  OK
2015-07-30 13:30:11  2015-07-30 13:30:13  FULL    14MB    15  OK

Standby-site Backup

If you use replication feature on PostgreSQL 9.0 later, you can get backup from standby-site.

You should specify different options from usual use for getting backup from standby-site. In detail, you should specify the database cluster on standby-site by -D/--pgdata option. And you should specify information on master-site by connection options (-d/--dbname, -h/--host, -p/--port). In addition, you should specify information to connect standby-site by standby connection options (--standby-host, --standby-port).

$ pg_rman init -B <a backup catalog path> -D <(the database cluster path(on standby-site)>

Here shows an example with the below environment.

Then, the backup from standby site can be done with the below command:

$ pg_rman backup --pgdata=/home/postgres/pgdata_sby --backup-mode=full --host=master --standby-host=localhost --standby-port=5432

Examples

In this example, let's consider about PostgreSQL server with the following configurations.

postgres=# SHOW log_directory ;
 log_directory
---------------
 pg_log
(1 row)

postgres=# SHOW archive_command ;
              archive_command
--------------------------------------------
 cp %p /home/postgres/arc_log/%f
(1 row)

And the PGDATA and BACKUP_PATH are set as environment variables.

$ echo $PGDATA
/home/postgres/pgdata
$ echo $BACKUP_PATH
/home/postgres/backup

Initialize a backup catalog.

$ pg_rman init
INFO: ARCLOG_PATH is set to '/home/postgres/arclog'
INFO: SRVLOG_PATH is set to '/home/postgres/pgdata/pg_log'

By this, the configuration file for pg_rman, named pg_rman.init, is created. All the commands of pg_rman load configurations from this file as default.

For this example, we use the following configurtaions.

$ cat $BACKUP_PATH/pg_rman.ini
ARCLOG_PATH = /home/postgres/arclog
SRVLOG_PATH = /home/postgres/pgdata/pg_log

BACKUP_MODE = F
COMPRESS_DATA = YES
KEEP_ARCLOG_FILES = 10
KEEP_DATA_GENERATIONS = 3
KEEP_SRVLOG_FILES = 10

Then, do a backup. It should be start from a full backup. Here, we will also take server log files.

$ pg_rman backup --backup-mode=full --with-serverlog --progress
INFO: copying database files
Processed 1760 of 1760 files, skipped 0
NOTICE:  pg_stop_backup complete, all required WAL segments have been archived
INFO: copying archived WAL files
Processed 21 of 21 files, skipped 0
INFO: copying server log files
Processed 1 of 1 files, skipped 0
INFO: backup complete
HINT: Please execute 'pg_rman validate' to verify the files are correctly copied.
INFO: start deleting old archived WAL files from ARCLOG_PATH (keep files = 10)
INFO: delete "0000000E0000000100000092"
INFO: delete "0000000E0000000100000092.00000028.backup"
INFO: delete "0000000E0000000100000093"
INFO: start deleting old server files from SRVLOG_PATH (keep files = 10)
INFO: start deleting old backup (keep generations = 3)
INFO: does not include the backup just taken

Check the result by show command.

$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2015-07-30 13:36:38  2015-07-30 13:36:41  FULL    15MB    15  DONE

The status of the backup we have just taken is DONE. This is because we does not do validate yet. So, do validate command next.

$ pg_rman validate
INFO: validate: "2015-07-30 13:36:38" backup, archive log files and server log files by CRC
INFO: backup "2015-07-30 13:36:38" is valid

$ pg_rman show
=====================================================================
 StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2015-07-30 13:36:38  2015-07-30 13:36:41  FULL    15MB    15  OK

Now the status has been changed to OK.

Let's try to restore the backup data. Before try to do it, PostgreSQL server should be stopped.

$ pg_ctl stop -m immediate
$ pg_rman restore

The pg_rman has created recovery.conf in $PGDATA by restore command. If necessary, modify it as you wanted. In this example, we use this without modifications and will try to do PITR to latest database status.

$ cat $PGDATA/recovery.conf
# recovery.conf generated by pg_rman 1.2.11
restore_command = 'cp /home/postgres/arclog/%f %p'
recovery_target_timeline = '1'
$ pg_ctl start

Options

pg_rman accepts the following command line parameters. Some of them can be also specified as environment variables.

Common options

As a general rule, paths for data location need to be specified as absolute paths; relative paths are not allowed.