联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
通过试验验证pg_rman的基础使用
创建测试环境
模拟创建新表空间,新数据库,新用户,新schema,并且创建表插入测试数据，这样的环境下,pg_rman 备份还原效果
[root@localhost ~]# psql Password: psql.bin (10.4) Type "help" for help. postgres=# CREATE USER u_xifenfei WITH postgres-# LOGIN postgres-# SUPERUSER postgres-# CREATEDB postgres-# CREATEROLE postgres-# INHERIT postgres-# REPLICATION postgres-# CONNECTION LIMIT -1 postgres-# PASSWORD 'xifenfei'; CREATE ROLE postgres=# CREATE TABLESPACE tbs_xifenfei postgres-# OWNER u_xifenfei postgres-# LOCATION '/opt/PostgreSQL/10/tbs_xifenfei'; CREATE TABLESPACE postgres=# CREATE DATABASE db_xifenfei postgres-# WITH postgres-# OWNER = u_xifenfei postgres-# ENCODING = 'UTF8' postgres-# TABLESPACE = tbs_xifenfei postgres-# CONNECTION LIMIT = -1; CREATE DATABASE postgres=# \q [root@localhost ~]# psql -U u_xifenfei Password for user u_xifenfei: psql.bin (10.4) Type "help" for help. postgres=# \q [root@localhost ~]# psql -U u_xifenfei -d db_xifenfei Password for user u_xifenfei: psql.bin (10.4) Type "help" for help. db_xifenfei=# db_xifenfei=# db_xifenfei=# create schema u_xifenfei; CREATE SCHEMA db_xifenfei=# create table t_xifenfei as select * from pg_tables; SELECT 69 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 69 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 138 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 276 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 552 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 1104 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 2208 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 4416 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 8832 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 17664 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 35328 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 70656 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 141312 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 282624 db_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 565248 db_xifenfei=# select count(*) from t_xifenfei; count --------- 1130496 (1 row) db_xifenfei=#
第一次全备数据库
[root@localhost backup]# pg_rman backup --backup-mode=full \ [root@localhost backup]# --backup-path=/backup -d db_xifenfei -U u_xifenfei -h 127.0.0.1 Password for user u_xifenfei: 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.
模拟继续插入数据
t_xifenfei表一共有数据2260992条
b_xifenfei=# insert into t_xifenfei select * from t_xifenfei; INSERT 0 1130496 db_xifenfei=# select count(*) from t_xifenfei; count --------- 2260992 (1 row)
查看全备情况
[root@localhost 10]# pg_rman show --backup-path=/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2018-08-05 10:47:29 2018-08-05 10:47:43 FULL 611MB 1 DONE
备份归档日志
[root@localhost 10]# pg_rman backup --backup-mode=archive \ [root@localhost 10]# --backup-path=/backup -d db_xifenfei -U u_xifenfei -h 127.0.0.1 Password for user u_xifenfei: INFO: copying archived WAL files INFO: backup complete INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
检查全备和归档备份情况
[root@localhost 10]# pg_rman show --backup-path=/backup ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2018-08-05 10:54:51 2018-08-05 10:56:07 ARCH 620MB 1 DONE 2018-08-05 10:47:29 2018-08-05 10:47:43 FULL 611MB 1 DONE
停掉pg
[root@localhost data]# su - postgres Last login: Sun Aug 5 02:19:57 EDT 2018 on pts/1 -bash-4.2$ source pg_env.sh -bash-4.2$ pg_ctl stop waiting for server to shut down.... done server stopped -bash-4.2$ ps -ef|grep postgres root 39902 30494 0 11:05 pts/0 00:00:00 su - postgres postgres 39903 39902 0 11:05 pts/0 00:00:00 -bash postgres 40021 39903 0 11:06 pts/0 00:00:00 ps -ef postgres 40022 39903 0 11:06 pts/0 00:00:00 grep --color=auto postgres
删除原库并创建相关目录
注意:对应的空间目录权限为700,所有者和组为postgres
[root@localhost 10]# pwd /opt/PostgreSQL/10 [root@localhost 10]# mv data data_bak [root@localhost 10]# mv tbs_xifenfei tbs_xifenfei_bak [root@localhost 10]# mkdir data [root@localhost 10]# mkdir tbs_xifenfei [root@localhost 10]# chmod 700 data [root@localhost 10]# chmod 700 tbs_xifenfei [root@localhost 10]# chown postgres:postgres data [root@localhost 10]# chown postgres:postgres tbs_xifenfei
还原数据库
-bash-4.2$ pg_rman restore --backup-path=/backup WARNING: pg_controldata file "/opt/PostgreSQL/10/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: "2018-08-05 10:47:29" INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: "2018-08-05 10:47:29" backup and archive log files by SIZE INFO: backup "2018-08-05 10:47:29" is valid INFO: restoring database files from the full mode backup "2018-08-05 10:47:29" INFO: searching incremental backup to be restored INFO: searching backup which contained archived WAL files to be restored INFO: backup "2018-08-05 10:47:29" is valid INFO: restoring WAL files from backup "2018-08-05 10:47:29" INFO: restoring online WAL files and server log files INFO: generating recovery.conf INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started.
启动pg
-bash-4.2$ pg_ctl start waiting for server to start....2018-08-05 11:23:40.190 EDT [40855] LOG: listening on IPv4 address "0.0.0.0", port 5432 2018-08-05 11:23:40.190 EDT [40855] LOG: listening on IPv6 address "::", port 5432 2018-08-05 11:23:40.193 EDT [40855] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2018-08-05 11:23:40.200 EDT [40855] LOG: redirecting log output to logging collector process 2018-08-05 11:23:40.200 EDT [40855] HINT: Future log output will appear in directory "log". done server started -bash-4.2$ ps -ef|grep postgres root 40328 21806 0 11:14 pts/3 00:00:00 su - postgres postgres 40329 40328 0 11:14 pts/3 00:00:00 -bash postgres 40855 1 0 11:23 pts/3 00:00:00 /opt/PostgreSQL/10/bin/postgres postgres 40856 40855 0 11:23 ? 00:00:00 postgres: logger process postgres 40857 40855 57 11:23 ? 00:00:16 postgres: startup process waiting for 000000010000000000000025 postgres 40859 40855 0 11:23 ? 00:00:00 postgres: checkpointer process postgres 40860 40855 7 11:23 ? 00:00:02 postgres: writer process postgres 40862 40855 0 11:23 ? 00:00:00 postgres: stats collector process postgres 40892 40329 0 11:24 pts/3 00:00:00 ps -ef postgres 40893 40329 0 11:24 pts/3 00:00:00 grep --color=auto postgres -bash-4.2$
验证数据库还原结果
-bash-4.2$ psql -U u_xifenfei -d db_xifenfei Password for user u_xifenfei: psql.bin (10.4) Type "help" for help. db_xifenfei=# select count(*) from t_xifenfei; count --------- 2260992 (1 row) db_xifenfei=#
破坏环境之前表条数和还原之后完全匹配,证pg_rman在功能上备份恢复没有问题