暂无图片
暂无图片
3
暂无图片
暂无图片
暂无图片

PostgreSQL运维—pg_rman安装及使用说明

原创 李先生 2022-02-17
2962

PostgreSQL运维—pg_rman安装及使用说明


PostgreSQL运维—pg_rman安装及使用说明

pg_rman 说明

pg_rman 是 PostgreSQL 的在线备份和恢复工具。类似oracle 的 rman

pg_rman 项目的目标是提供一种与 pg_dump 一样简单的在线备份和 PITR 方法。此外,它还为每个数据库集群维护一个备份目录。用户可以使用一个命令维护旧备份,包括存档日志。

今天我们来学习一下pg_rman 工具的安装及使用。

pg_rman安装

软件下载

安装包下载地址: 墨天轮地址:https://www.modb.pro/download/438831 gitlb地址:https://github.com/ossc-db/pg_rman

安装准备

解压压缩包

[postgres@lyp plug-in]$ ls -lrt pg_rman-master.zip -rw-r--r--. 1 postgres postgres 148651 Feb 17 20:04 pg_rman-master.zip [postgres@lyp plug-in]$ unzip pg_rman-master.zip Archive: pg_rman-master.zip 59b3bca1338d3fe48eba2395b81ec69de534eabf creating: pg_rman-master/ creating: pg_rman-master/.github/ .......... inflating: pg_rman-master/validate.c inflating: pg_rman-master/xlog.c [postgres@lyp plug-in]$

要编译并安装“pg_rman”,请阅读文件“README.md”,然后按照其中包含的指示和建议进行编译安装pg_rman。

安装软件

[postgres@lyp pg_rman-master]$ make gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o backup.o backup.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o catalog.o catalog.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o data.o data.c data.c: In function1mfigure_out_segno鈥data.c:1270:8: warning: variable1mscanned鈥t but not used [-Wunused-but-set-variable] int scanned; ^ gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o delete.o delete.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o dir.o dir.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o init.o init.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o parray.o parray.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o pg_rman.o pg_rman.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o restore.o restore.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o show.o show.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o util.o util.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o validate.o validate.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o xlog.o xlog.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o pgsql_src/pg_ctl.o pgsql_src/pg_ctl.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o pgut/pgut.o pgut/pgut.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -I/opt/pgsql14.1/include -lm -I. -I./ -I/opt/pgsql14.1/include/server -I/opt/pgsql14.1/include/internal -D_GNU_SOURCE -c -o pgut/pgut-port.o pgut/pgut-port.c gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 backup.o catalog.o data.o delete.o dir.o init.o parray.o pg_rman.o restore.o show.o util.o validate.o xlog.o pgsql_src/pg_ctl.o pgut/pgut.o pgut/pgut-port.o -L/opt/pgsql14.1/lib -Wl,--as-needed -Wl,-rpath,'/opt/pgsql14.1/lib',--enable-new-dtags -L/opt/pgsql14.1/lib -lpgcommon -lpgport -L/opt/pgsql14.1/lib -lpq -lpgcommon -lpgport -lz -lreadline -lpthread -lrt -ldl -lm -o pg_rman [postgres@lyp pg_rman-master]$ [postgres@lyp pg_rman-master]$ make install /usr/bin/mkdir -p '/opt/pgsql14.1/bin' /usr/bin/install -c pg_rman '/opt/pgsql14.1/bin' [postgres@lyp pg_rman-master]$

pg_rman使用

帮助说明

[postgres@lyp pg_rman-master]$ pg_rman --help pg_rman manage backup/recovery of PostgreSQL database. 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 Common Options: -D, --pgdata=PATH location of the database storage area -A, --arclog-path=PATH location of archive WAL storage area -S, --srvlog-path=PATH location of server log storage area -B, --backup-path=PATH location of the backup storage area -c, --check show what would have been done -v, --verbose show what detail messages -P, --progress show progress of processed files Backup options: -b, --backup-mode=MODE full, incremental, or archive -s, --with-serverlog also backup server log files -Z, --compress-data compress data backup with zlib -C, --smooth-checkpoint do smooth checkpoint before backup -F, --full-backup-on-error switch to full backup mode if pg_rman cannot find validate full backup on current timeline NOTE: this option is only used in --backup-mode=incremental or archive. --keep-data-generations=NUM keep NUM generations of full data backup --keep-data-days=NUM keep enough data backup to recover to N days ago --keep-arclog-files=NUM keep NUM of archived WAL --keep-arclog-days=DAY keep archived WAL modified in DAY days --keep-srvlog-files=NUM keep NUM of serverlogs --keep-srvlog-days=DAY keep serverlog modified in DAY days --standby-host=HOSTNAME standby host when taking backup from standby --standby-port=PORT standby port when taking backup from standby Restore options: --recovery-target-time time stamp up to which recovery will proceed --recovery-target-xid transaction ID up to which recovery will proceed --recovery-target-inclusive whether we stop just after the recovery target --recovery-target-timeline recovering into a particular timeline --recovery-target-action action the server should take once the recovery target is reached --hard-copy copying archivelog not symbolic link Catalog options: -a, --show-all show deleted backup too Delete options: -f, --force forcibly delete backup older than given DATE Connection options: -d, --dbname=DBNAME database to connect -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port -U, --username=USERNAME user name to connect as -w, --no-password never prompt for password -W, --password force password prompt Generic options: -q, --quiet don't show any INFO or DEBUG messages --debug show DEBUG messages --help show this help, then exit --version output version information, then exit Read the website for details. <http://github.com/ossc-db/pg_rman> Report bugs to <http://github.com/ossc-db/pg_rman/issues>. [postgres@lyp pg_rman-master]$

初始化备份目录

[postgres@lyp ~]$ mkdir backup [postgres@lyp ~]$ cd backup/ [postgres@lyp backup]$ pwd /home/postgres/backup [postgres@lyp backup]$ pg_rman init --backup-path=/home/postgres/backup INFO: ARCLOG_PATH is set to '/home/postgres/pgdata14/archive' INFO: SRVLOG_PATH is set to '/home/postgres/pgdata14/log' [postgres@lyp backup]$

备份恢复

全备

测试数据
postgres=# create database mydb1; CREATE DATABASE postgres=# \c mydb1 You are now connected to database "mydb1" as user "postgres". mydb1=# create table t1 (id1 int,name varchar(20)); CREATE TABLE mydb1=# insert into t1 select n,'test'||n from generate_series(1,5) n; INSERT 0 5 mydb1=# select * from t1; id1 | name -----+------- 1 | test1 2 | test2 3 | test3 4 | test4 5 | test5 (5 rows) mydb1=#
全备
[postgres@lyp backup]$ pg_rman backup --backup-mode=full --backup-path=/home/postgres/backup INFO: copying database files INFO: copying archived WAL files INFO: backup complete INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. [postgres@lyp backup]$ pg_rman validate --backup-path=/home/postgres/backup INFO: validate: "2022-02-17 22:36:02" backup and archive log files by CRC INFO: backup "2022-02-17 22:36:02" is valid [postgres@lyp backup]$ pg_rman show --backup-path=/home/postgres/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2022-02-17 22:36:02 2022-02-17 22:36:04 FULL 108MB 1 OK [postgres@lyp backup]$

增量备份

测试数据
mydb1=# insert into t1 select n,'test'||n from generate_series(6,10) n; INSERT 0 5 mydb1=# select * from t1; id1 | name -----+-------- 1 | test1 2 | test2 3 | test3 4 | test4 5 | test5 6 | test6 7 | test7 8 | test8 9 | test9 10 | test10 (10 rows) mydb1=#
增量备份
[postgres@lyp backup]$ pg_rman backup --backup-mode=incremental --backup-path=/home/postgres/backup INFO: copying database files INFO: copying archived WAL files INFO: backup complete INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. [postgres@lyp backup]$ pg_rman validate --backup-path=/home/postgres/backup INFO: validate: "2022-02-17 22:36:37" backup and archive log files by CRC INFO: backup "2022-02-17 22:36:37" is valid [postgres@lyp backup]$ pg_rman show --backup-path=/home/postgres/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2022-02-17 22:36:37 2022-02-17 22:36:39 INCR 33MB 1 OK 2022-02-17 22:36:02 2022-02-17 22:36:04 FULL 108MB 1 OK [postgres@lyp backup]$

恢复

测试数据
mydb1=# \c postgres You are now connected to database "postgres" as user "postgres". postgres=# drop database mydb1; DROP DATABASE postgres=# \l mydb1 List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ------+-------+----------+---------+-------+------------------- (0 rows) postgres=#
恢复全备
[postgres@lyp backup]$ pg_rman show --backup-path=/home/postgres/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2022-02-17 22:36:37 2022-02-17 22:36:39 INCR 33MB 1 OK 2022-02-17 22:36:02 2022-02-17 22:36:04 FULL 108MB 1 OK [postgres@lyp backup]$ pg_rman restore --backup-path=/home/postgres/backup --recovery-target-time='2022-02-17 22:36:04' ERROR: PostgreSQL server is running HINT: Please stop PostgreSQL server before executing restore. [postgres@lyp backup]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@lyp backup]$ pg_rman restore --backup-path=/home/postgres/backup --recovery-target-time='2022-02-17 22:36:04' INFO: the recovery target timeline ID is not given INFO: use timeline ID of current database cluster as recovery target: 1 INFO: calculating timeline branches to be used to recovery target point INFO: searching latest full backup which can be used as restore start point INFO: found the full backup can be used as base in recovery: "2022-02-17 22:36:02" INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: "2022-02-17 22:36:02" backup and archive log files by SIZE INFO: backup "2022-02-17 22:36:02" is valid INFO: restoring database files from the full mode backup "2022-02-17 22:36:02" INFO: searching incremental backup to be restored INFO: searching backup which contained archived WAL files to be restored INFO: backup "2022-02-17 22:36:02" is valid INFO: restoring WAL files from backup "2022-02-17 22:36:02" INFO: backup "2022-02-17 22:36:37" is valid INFO: restoring WAL files from backup "2022-02-17 22:36:37" INFO: restoring online WAL files and server log files INFO: create pg_rman_recovery.conf for recovery-related parameters. INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf INFO: generating recovery.signal INFO: removing standby.signal if exists to restore as primary INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman. [postgres@lyp backup]$ pg_ctl start waiting for server to start....2022-02-17 22:38:31.898 CST [120634] LOG: redirecting log output to logging collector process 2022-02-17 22:38:31.898 CST [120634] HINT: Future log output will appear in directory "log". done server started [postgres@lyp backup]$
测试数据
[postgres@lyp ~]$ psql psql (14.1) Type "help" for help. postgres=# \l mydb1 List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------+----------+----------+-------------+-------------+------------------- mydb1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (1 row) postgres=# \c mydb1 You are now connected to database "mydb1" as user "postgres". mydb1=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row) mydb1=# mydb1=# select * from t1; id1 | name -----+------- 1 | test1 2 | test2 3 | test3 4 | test4 5 | test5 (5 rows) mydb1=#

SAVEPOINT CPT1

增量恢复
[postgres@lyp backup]$ pg_rman show --backup-path=/home/postgres/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2022-02-17 21:53:35 2022-02-17 21:53:37 INCR 33MB 1 OK 2022-02-17 21:52:47 2022-02-17 21:52:49 FULL 116MB 1 OK [postgres@lyp backup]$ pg_rman restore --backup-path=/home/postgres/backup --recovery-target-time='2022-02-17 21:53:37' ERROR: PostgreSQL server is running HINT: Please stop PostgreSQL server before executing restore. [postgres@lyp backup]$ pg_ctl stop waiting for server to shut down.... done server stopped [postgres@lyp backup]$ pg_rman restore --backup-path=/home/postgres/backup --recovery-target-time='2022-02-17 21:53:37' INFO: the recovery target timeline ID is not given INFO: use timeline ID of current database cluster as recovery target: 1 INFO: calculating timeline branches to be used to recovery target point INFO: searching latest full backup which can be used as restore start point INFO: found the full backup can be used as base in recovery: "2022-02-17 21:52:47" INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: "2022-02-17 21:52:47" backup and archive log files by SIZE WARNING: backup file "/home/postgres/backup/20220217/215247/arclog/00000001000000000000006E" vanished WARNING: backup "2022-02-17 21:52:47" is corrupted INFO: restoring database files from the full mode backup "2022-02-17 21:52:47" INFO: searching incremental backup to be restored INFO: validate: "2022-02-17 21:53:35" backup and archive log files by SIZE WARNING: backup file "/home/postgres/backup/20220217/215335/arclog/000000010000000000000070" vanished WARNING: backup "2022-02-17 21:53:35" is corrupted INFO: restoring database files from the incremental mode backup "2022-02-17 21:53:35" INFO: searching backup which contained archived WAL files to be restored INFO: restoring online WAL files and server log files INFO: create pg_rman_recovery.conf for recovery-related parameters. INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf INFO: generating recovery.signal INFO: removing standby.signal if exists to restore as primary INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman. [postgres@lyp backup]$ pg_ctl start waiting for server to start....2022-02-17 22:02:42.469 CST [117788] LOG: redirecting log output to logging collector process 2022-02-17 22:02:42.469 CST [117788] HINT: Future log output will appear in directory "log". stopped waiting pg_ctl: could not start server Examine the log output. [postgres@lyp backup]$
日志
2022-02-17 22:40:45.889 CST [120857] LOG: starting PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit 2022-02-17 22:40:45.889 CST [120857] LOG: listening on IPv4 address "0.0.0.0", port 5433 2022-02-17 22:40:45.889 CST [120857] LOG: listening on IPv6 address "::", port 5433 2022-02-17 22:40:45.892 CST [120857] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2022-02-17 22:40:45.895 CST [120859] LOG: database system was interrupted; last known up at 2022-02-17 22:36:37 CST 2022-02-17 22:40:45.904 CST [120859] LOG: starting point-in-time recovery to 2022-02-17 22:36:39+08 cp: cannot stat ‘/home/postgres/pgdata14/archive/000000010000000000000007’: No such file or directory 2022-02-17 22:40:45.910 CST [120859] LOG: invalid checkpoint record 2022-02-17 22:40:45.910 CST [120859] FATAL: could not locate required checkpoint record 2022-02-17 22:40:45.910 CST [120859] HINT: If you are restoring from a backup, touch "/home/postgres/pgdata14/recovery.signal" and add required recovery options. If you are not restoring from a backup, try removing the file "/home/postgres/pgdata14/backup_label". Be careful: removing "/home/postgres/pgdata14/backup_label" will result in a corrupt cluster if restoring from a backup. 2022-02-17 22:40:45.910 CST [120857] LOG: startup process (PID 120859) exited with exit code 1 2022-02-17 22:40:45.910 CST [120857] LOG: aborting startup due to startup process failure 2022-02-17 22:40:45.912 CST [120857] LOG: database system is shut down

在 基于时间点之后,那么此时DB与之前的备份已经不在同一时间线上。恢复默认只沿着基础备份建立时时间线恢复而不会切换到新的时间线,所以建议在恢复之后,立即对数据库做一个全库的备份。

ROLLBACK CPT1

再次全备

[postgres@lyp ~]$ pg_rman backup --backup-mode=full --backup-path=/home/postgres/backup INFO: copying database files ERROR: query failed: ERROR: recovery is in progress HINT: pg_walfile_name_offset() cannot be executed during recovery. query was: SELECT * from pg_walfile_name_offset(pg_start_backup($1, $2, $3)) [postgres@lyp ~]$

恢复之后, 数据库可能处于 read-only状态,此时可以用超户执行select pg_wal_replay_resume(); 或者在启动数据库实例前在postgresql.conf中添加recovery_target_action=‘promote’

postgres=# select pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 row) postgres=# select * from t1; id1 | name -----+------- 1 | test1 2 | test2 3 | test3 4 | test4 5 | test5 (5 rows) postgres=# insert into t1 select n,'test'||n from generate_series(6,10) n; INSERT 0 5 postgres=#

pg_rman.ini

pg_rman初始化之后会在备份目录中产生 pg_rman.ini 文件

[postgres@lyp backup]$ more pg_rman.ini ARCLOG_PATH='/home/postgres/pgdata14/archive' SRVLOG_PATH='/home/postgres/pgdata14/log' [postgres@lyp backup]$

可以编辑该文件并添加备份策略

KEEP_DATA_GENERATIONS=4
KEEP_DATA_DAYS=30
KEEP_ARCLOG_DAYS=35
KEEP_SRVLOG_DAYS=180

KEEP_DATA_GENERATIONS=4,保留4份全备数量

KEEP_DATA_DAYS=30,数据保留30天,

KEEP_ARCLOG_DAYS=60,WAL日志保留60天

KEEP_SRVLOG_DAYS=180,LOG日志保留180天数

总结建议

  • 在做备份操作时,建议使用参数

-C, --smooth-checkpoint do smooth checkpoint before backup (备份前做检查)

-Z, --compress-data compress data backup with zlib (使用zlib压缩数据备份)

  • 在做恢复操作时,建议使用参数

–hard-copy copying archivelog not symbolic link (copy 归档日志,而不是link软连接)

  • 备份目录不能放在数据目录中

否则每备份都会将之前的备份再备份一遍,从而导致备份文件异常增大

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论