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 function 鈥1mfigure_out_segno鈥[01mdata.c:1270:8: warning: variable 鈥1mscanned鈥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软连接）
- 备份目录不能放在数据目录中
否则每备份都会将之前的备份再备份一遍，从而导致备份文件异常增大
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
