PostgreSQL運維—pg_rman安裝及使用說明

PostgreSQL運維—pg_rman安裝及使用說明

pg_rman 說明

pg_rman 是 PostgreSQL 的在線備份和恢復工具。類似oracle 的 rman

pg_rman 項目的目標是提供一種與 pg_dump 一樣簡單的在線備份和 PITR 方法。此外，它還為每個數(shù)據(jù)庫集群維護一個備份目錄。用戶可以使用一個命令維護舊備份，包括存檔日志。

今天我們來學習一下pg_rman 工具的安裝及使用。

pg_rman安裝

軟件下載

安裝包下載地址：
墨天輪地址：http://www.sunline.cc/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鈥data.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]$ 

備份恢復

全備

測試數(shù)據(jù)

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]$ 

增量備份

測試數(shù)據(jù)

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]$

恢復

測試數(shù)據(jù)

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]$ 

測試數(shù)據(jù)

[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與之前的備份已經(jīng)不在同一時間線上。恢復默認只沿著基礎(chǔ)備份建立時時間線恢復而不會切換到新的時間線，所以建議在恢復之后，立即對數(shù)據(jù)庫做一個全庫的備份。

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 ~]$ 

恢復之后, 數(shù)據(jù)庫可能處于 read-only狀態(tài),此時可以用超戶執(zhí)行select pg_wal_replay_resume(); 或者在啟動數(shù)據(jù)庫實例前在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初始化之后會在備份目錄中產(chǎn)生 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份全備數(shù)量

KEEP_DATA_DAYS=30，數(shù)據(jù)保留30天，

KEEP_ARCLOG_DAYS=60，WAL日志保留60天

KEEP_SRVLOG_DAYS=180，LOG日志保留180天數(shù)

總結(jié)建議

• 在做備份操作時，建議使用參數(shù)

-C, --smooth-checkpoint do smooth checkpoint before backup （備份前做檢查）

-Z, --compress-data compress data backup with zlib （使用zlib壓縮數(shù)據(jù)備份）

• 在做恢復操作時，建議使用參數(shù)

–hard-copy copying archivelog not symbolic link （copy 歸檔日志，而不是link軟連接）

• 備份目錄不能放在數(shù)據(jù)目錄中

否則每備份都會將之前的備份再備份一遍，從而導致備份文件異常增大