Detailed explanation of the use of physical backup and online backup tool pg_rman (9)

Pg_rman tool is a backup tool for online backup, physical backup and recovery of PostgreSQL database. Can achieve full backup, incremental backup.

Full quantity: full

Increment: incremental

Archiving: archive

Usage: pg_rman OPTION init pg_rman OPTION backup pg_rman OPTION restore pg_rman OPTION show [DATE] pg_rman OPTION show detail [DATE] pg_rman OPTION validate [DATE] pg_rman OPTION delete DATE pg_rman OPTION purge command option:-D,-- path to pgdata=PATH data storage directory-A,-- path to arclog-path=PATH archive wal (pre-written) logs-S -- the path where the srvlog-path=PATH database server stores logs. -B,-- the path to the backup-path=PATH backup data store. -c,-- check check-v,-- verbose displays detailed messages-P,-- progress shows progress backup options for processed files:-b,-- backup-mode=MODE complete, add, archive-s -- with-serverlog backup server log files-Z,-- compress-data uses zlib compressed data backup-C,-- smooth-checkpoint does a smooth checkpoint before backup-F,-- full-backup-on-error switches to full backup mode Note: this option is only used for-- backup-mode = incremental or archive-- keep-data-generations=NUM preserves NUM generation full data backup-- keep-data-days=NUM maintains sufficient data backup to restore to N days ago-- keep-arclog-files=NUM keeps NUM archived WAL logs-- keep-arclog-days=DAY keeps archived WAL modified within DAY days-- keep-srvlog-files=NUM Keep NUM server logs-- keep-srvlog-days=DAY modifies server logs within DAY days-- standby host when standby-host=HOSTNAME backups from standby-- alternate port restore option when standby-port=PORT backups from standby:-- timestamp that recovery-target-time restore will continue-- recovery-target-xid transaction ID Restore will continue-recovery-target-inclusive if we stop after the restore target-recovery-target-timeline restore to a specific time-hard-copy replicates archivelog instead of symbolic link directories option:-a displays deleted backup deletion option:-f Force deletion of backup connections older than the specified date option:-d -- dbname=DBNAME connection specified library-h,-- host=HOSTNAME database hostname / IP address-p,-- port=PORT database port-U,-- username=USERNAME database user name-w,-- no-password never prompts for password-W,-- password force password prompt generic option:-Q -- quiet does not display any INFO or DEBUG information. -- debug views debug information.

Start using the pg_rman tool:

Create a backup directory

# mkdir / database-backup/postgresql-backup/ {fullbackup,walbackup Pg_srvlog}-p # chown-R postgres.postgres / database-backup/postgresql-backup/ set environment variable: $vim .profile export BACKUP_PATH=/database-backup/postgresql-backup/fullbackup export ARCLOG_PATH=/database-backup/postgresql-backup/walbackup export SRVLOG_PATH=/database-backup/postgresql-backup/pg_srvlog $source .profile # modify postgresql.conf profile immediately: $vim data/postgresql .conf archive_mode = on archive_command = 'DATE= `date +% Y% m% d` DIR= "/ database-backup/postgresql-backup/walbackup/"; (test-d $DIR | | mkdir-p $DIR) & & cp% p $DIR/%f' save and exit. Pg_rman init initialization $pg_rman init INFO: ARCLOG_PATH is set to'/ database-backup/postgresql-backup/walbackup' INFO: SRVLOG_PATH is set to'/ database-backup/postgresql-backup/pg_srvlog'pg_rman full backup $pg_rman backup--backup-mode=full-- with-serverlog-- progressINFO: copying database filesProcessed 1298 of 1298 files, skipped 0INFO: copying archived WAL filesProcessed 3 of 3 files Skipped 0INFO: copying server log filesINFO: backup completeINFO: Please execute 'pg_rman validate' to verify the files are correctly copied.pg_rman check backup set pg_rman backups must all be verified Otherwise, recovery and incremental backups cannot be performed. $pg_rman validateINFO: validate: "2018-10-14 14:55:34" backup, archive log files and server log files by CRCINFO: backup "2018-10-14 14:55:34" is valid

Pg_rman lists backup sets

$pg_rman show

$ls-l / database-backup/postgresql-backup/fullbackup/ total 20 drwx- 3 postgres postgres 4096 Oct 14 14:55 20181014 drwx- 4 postgres postgres 4096 Oct 14 14:53 backup-rw-rw-r-- 1 postgres postgres 119 Oct 14 14:53 pg_rman.ini-rw-rw-r-- 1 postgres postgres 40 Oct 14 14:53 system_identifier drwx- 2 postgres postgres 4096 Oct 14 14:53 timeline Historypg_rman incremental backup: incremental backup is based on the update time timeline of the file system. Incremental backups must have a corresponding full library backup. $pg_rman backup--backup-mode incremental-progress-compress-data

Pg_rman validate INFO: validate: "2018-10-14 15:59:57" backup and archive log files by CRC INFO: backup "2018-10-14 15:59:57" is valid $pg_rman show

Pg_rman delete backup set

While $pg_rman delete-f '2018-10-14 15 delete the backup with start time: "2018-10-14 15:59:57" INFO: delete the backup with start time: "2018-10-14 14:55:34" deletes the incremental backup, the full backup has also been deleted. $pg_rman show = = StartTime EndTime Mode Size TLI Status =

Note: you must create a full backup before you can create an incremental backup.

Pg_rman restore restore simulation database erroneously deleted: $/ etc/init.d/postgresql stop $ps-ef | grep-I postmaster | grep-v grep $cd data $ls-l

$mv data data-bak # takes the directories between them as backups. $mkdir data # create a data directory. $chmod 700. / data

Restore using pg_rman restore:

$pg_rman restore

$cd $PGDATA$ ls-l

$cat recovery.conf # recovery.conf generated by pg_rman 1.3.7 restore_command ='cp / database-backup/postgresql-backup/walbackup/%f% p 'recovery_target_timeline =' 1' start PostgreSQL database service: $/ etc/init.d/postgresql start $ps-ef | grep-I postgres

When restoring, note:

Starting after restore will encounter the following error: invalid primary checkpoint record invalid secondary checkpoint record could not locate a valid checkpoint record can only reset wal at this time and cancel the recovery model $pg_resetwal-- f $PGDATA $mv $PGDATA/recovery.conf $PGDATA/recovery.done archiving wal using pg_rman backups is achieved through soft links. It is recommended to add-- hard-copy.

Incremental backup and full backup, backup restore has been completed.

It is important to note that:

Full and incremental backups during physical backup can be backed up online (non-downtime backup).

But when you restore, you need to stop the machine to restore.

Postgres user's environment variable profile content: PATH=$PATH:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin:/services/install/postgresql10/bin/ export LD_LIBRARY_PATH=/services/install/postgresql10/lib:$LD_LIBRARY_PATH export LD_LIBRARY_PATH=/services/install/python3.6/lib:$LD_LIBRARY_PATH Export PGDATA PATH export BACKUP_PATH=/database-backup/postgresql-backup/fullbackup export ARCLOG_PATH=/database-backup/postgresql-backup/walbackup export SRVLOG_PATH=/database-backup/postgresql-backup/pg_srvlog

Reference:

Https://github.com/ossc-db/pg_rman/tree/master

Http://ossc-db.github.io/pg_rman/index.html