postgresql之pg_rman测试

操作系统版本：

[postgres@emgs ~]$ cat etc/redhat-release
CentOS Linux release 7.5.1804 (AltArch)
[postgres@emgs ~]$
复制

数据库版本：

[postgres@emgs ~]$ psql -V
psql (PostgreSQL) 12.2
[postgres@emgs ~]$
[postgres@emgs ~]$ psql -p 5433
psql (12.2)
Type "help" for help.
postgres=#
复制

软件下载：
https://github.com/ossc-db/pg_rman

软件部署

软件解压：
[root@emgs tools]# tar xzvf pg_rman-1.3.9-pg12.tar.gz
安装依赖包：
[root@emgs tools]# yum install zlib-devel
已经安装过：
Package zlib-devel-1.2.7-18.el7.aarch64 already installed and latest version
编译安装
[postgres@emgs pg_rman-1.3.9-pg12]$ 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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/include/internal  -D_GNU_SOURCE   -c -o data.o data.c
data.c: In function ‘figure_out_segno’:
data.c:1268:8: warning: variable ‘scanned’ set 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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/include -lm -I. -I./ -I/opt/pgsql/include/server -I/opt/pgsql/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/pgsql/lib   -Wl,--as-needed -Wl,-rpath,'/opt/pgsql/lib',--enable-new-dtags  -L/opt/pgsql/lib -lpgcommon -lpgport -L/opt/pgsql/lib -lpq -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm -o pg_rman
[postgres@emgs pg_rman-1.3.9-pg12]$ exit

[root@emgs pg_rman-1.3.9-pg12]# source home/postgres/.bash_profile
[root@emgs pg_rman-1.3.9-pg12]# make install
/usr/bin/mkdir -p '/opt/pgsql/bin'
/usr/bin/install -c  pg_rman '/opt/pgsql/bin'
[root@emgs pg_rman-1.3.9-pg12]#

[postgres@emgs ~]$ make installcheck
make: *** No rule to make target `installcheck'.  Stop.
[postgres@emgs ~]$
复制

准备工作

1、开启pg归档和参数设置

echo "logging_collector = on" >> $PGDATA/postgresql.conf
echo "wal_level = replica" >> $PGDATA/postgresql.conf
echo "archive_mode = on" >> $PGDATA/postgresql.conf
echo "archive_command = 'cp %p home/postgres/pg_wal_archives/%f'" >> $PGDATA/postgresql.conf

2、测试归档

[postgres@emgs pg_wal_archives]$ psql -p5432
psql (12.2)
Type "help" for help.
postgres=# select pg_switch_wal();
pg_switch_wal
---------------
0/20000F0
(1 row)
postgres=#
3、rman初始化

rm -rf pg_rman_bakcup pg_wal_archives pg_rman_log
mkdir pg_rman_bakcup pg_wal_archives pg_rman_log
 

export BACKUP_PATH=/home/postgres/pg_rman_bakcup
[postgres@emgs ~]$ pg_rman init -B home/postgres/pg_rman_bakcup
INFO: ARCLOG_PATH is set to '/home/postgres/pg_wal_archives'
INFO: SRVLOG_PATH is set to '/opt/pgsql/data/pg_log'
[postgres@emgs ~]$

$ ll
total 16
drwx------ 4 postgres postgres 4096 Apr  9 16:21 backup
-rw-rw-r-- 1 postgres postgres   84 Apr  9 16:21 pg_rman.ini
-rw-rw-r-- 1 postgres postgres   40 Apr  9 16:21 system_identifier
drwx------ 2 postgres postgres 4096 Apr  9 16:21 timeline_history
$
复制

全备

export BACKUP_PATH=/home/postgres/pg_rman_bakcup

[postgres@emgs pg_rman_bakcup]$ pg_rman backup --backup-mode=full --with-serverlog -C -P -p 5432
INFO: copying database files
Processed 1320 of 1320 files, skipped 0
INFO: copying archived WAL files
Processed 5 of 5 files, skipped 0
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@emgs pg_rman_bakcup]$


5、验证归档


[postgres@emgs pg_rman_bakcup]$ pg_rman validate
INFO: validate: "2020-04-09 17:43:44" backup, archive log files and server log files by CRC
INFO: backup "2020-04-09 17:43:44" is valid
6、查看备份


[postgres@emgs pg_rman_bakcup]$ pg_rman validate
INFO: validate: "2020-04-09 17:43:44" backup, archive log files and server log files by CRC
INFO: backup "2020-04-09 17:43:44" is valid
[postgres@emgs pg_rman_bakcup]$ pg_rman show
=====================================================================
StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2020-04-09 17:43:44  2020-04-09 17:43:46  FULL    89MB     1  OK
[postgres@emgs pg_rman_bakcup]$
复制

增量备份

tc=# insert into tc_test1 values(22);
INSERT 0 1
tc=# select * from tc_test1;
id
----
11
22
22
(3 row)

export BACKUP_PATH=/home/postgres/pg_rman_bakcup
[postgres@emgs ~]$ pg_rman backup -b incremental --with-serverlog -C -P -Z -p 5432
INFO: copying database files
Processed 1321 of 1321 files, skipped 1273
INFO: copying archived WAL files
Processed 9 of 9 files, skipped 5
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@emgs pg_rman_bakcup]$

[postgres@emgs pg_rman_bakcup]$ pg_rman show
=====================================================================
StartTime           EndTime              Mode    Size   TLI  Status
=====================================================================
2020-04-09 17:48:11  2020-04-09 17:48:16  INCR    85kB     1  DONE
2020-04-09 17:43:44  2020-04-09 17:43:46  FULL    89MB     1  OK
[postgres@emgs pg_rman_bakcup]$
复制

恢复

[postgres@emgs pg_rman_bakcup]$ pg_rman restore --arclog-path=/home/postgres/pg_wal_archives  --srvlog-path=/opt/pgsql/data/pg_log
WARNING: pg_controldata file "/opt/pgsql/data/global/pg_control" does not exist
INFO: the recovery target timeline ID is not given
INFO: use timeline ID of latest full backup 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: "2020-04-09 17:43:44"
INFO: copying online WAL files and server log files
INFO: clearing restore destination
INFO: validate: "2020-04-09 17:43:44" backup, archive log files and server log files by SIZE
INFO: backup "2020-04-09 17:43:44" is valid
INFO: restoring database files from the full mode backup "2020-04-09 17:43:44"
INFO: searching incremental backup to be restored
INFO: searching backup which contained archived WAL files to be restored
INFO: backup "2020-04-09 17:43:44" is valid
INFO: restoring WAL files from backup "2020-04-09 17:43:44"
INFO: restoring online WAL files and server log files
INFO: add recovery related options to postgresql.conf
INFO: generating recovery.signal
INFO: restore complete
HINT: Recovery will start automatically when the PostgreSQL server is started.
[postgres@emgs pg_rman_bakcup]$
复制

检查数据

[postgres@emgs data]$ pg_ctl start
waiting for server to start....2020-04-09 17:53:47.113 CST [31796] LOG:  starting PostgreSQL 12.2 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit
2020-04-09 17:53:47.113 CST [31796] LOG:  listening on IPv6 address "::1", port 5432
2020-04-09 17:53:47.113 CST [31796] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2020-04-09 17:53:47.113 CST [31796] LOG:  could not bind IPv4 address "127.0.0.1": Address already in use
2020-04-09 17:53:47.113 CST [31796] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2020-04-09 17:53:47.118 CST [31796] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2020-04-09 17:53:47.126 CST [31796] LOG:  redirecting log output to logging collector process
2020-04-09 17:53:47.126 CST [31796] HINT:  Future log output will appear in directory "log".
done
server started
 
[postgres@emgs data]$ psql -d tc
psql (12.2)
Type "help" for help.
tc=# \d
          List of relations
Schema |   Name   | Type  |  Owner  
--------+----------+-------+----------
public | tc_test1 | table | postgres
(1 rows)
tc=# select * from tc_test1;
id
----
11
22
22
(3 rows)
复制

本公众号长期关注于数据库技术以及性能优化，故障案例分析，K8S技术知识分享，工作心得等主题，欢迎扫码关注。