PostgreSQL 13 备份与恢复

basebackup 备份流程

1. 执行pg_start_backup 命令。
2. 执行物理文件备份。
3. 执行pg_stop_backup 命令。

pg_start_backup 流程

1. 强制将数据库进入full-page-wirte 模式。
2. 切换WAL segment file 文件。
3. 做检查点。
4. 创建backup_label file ： 此文件存在于 base 目录同一层。包含备份的基本信息。比如检查点的位置。

backup_label file

[postgres@node1 data]$ cat backup_label 
START WAL LOCATION: 0/4000028 (file 000000010000000000000004)
CHECKPOINT LOCATION: 0/4000060
BACKUP METHOD: pg_start_backup
BACKUP FROM: master
START TIME: 2022-11-09 16:05:28 CST
LABEL: backup
START TIMELINE: 1

pg_stop_backup 备份流程

1. 如果pg_start_backup强制更改了非整页写入模式，则将其重置为非整页写入模式。
2. 写一个备份结束的XLOG记录。
3. 切换WAL日志。
4. 创建备份历史文件–此文件包含备份标签文件的内容和执行pg_stop_backup的时间戳。
5. 删除备份标签文件–备份标签文件是从基本备份恢复所必需的，一旦复制，就不必在原始数据库群集中。

数据库恢复流程

数据库完全恢复

恢复到数据库故障前一刻。

基于时间点的恢复

PITR恢复流程概述如下：：
（1）为了找到重做点，PostgreSQL使用内部函数read-backup-label从备份标签文件中读取“检查点位置”的值。
（2）PostgreSQL从recovery.conf（12及以后版本为postgres.conf）中读取一些参数值；在本例中，restore_command和recovery_target_time。
（3）PostgreSQL开始从重做点重放WAL数据，可以很容易地从“检查点位置”的值中获取。
（4）恢复过程完成后，将在pg_xlog子目录（在版本10或更高版本的pg_wal子目录中）中创建时间线历史文件。

base_backup 命令使用

备份实验

开启归档

postgres>
--------------------input------------------------------
mkdir -p /home/postgres/arch
psql -U postgres -d testdb
testdb#
--------------------input------------------------------
ALTER SYSTEM SET archive_mode='on';
ALTER SYSTEM SET archive_command='test ! -f /home/postgres/arch/%f && cp %p /home/postgres/arch/%f';
\q
postgres>
--------------------input------------------------------
pg_ctl restart
psql -U postgres -d testdb
testdb#
--------------------input------------------------------
\! ls /home/postgres/arch
SELECT pg_switch_wal();
\! ls /home/postgres/arch

手动备份数据库

手动发起备份

testdb=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/3060F50
(1 row)

testdb=# \df pg_start_backup;
                                                        List of functions
   Schema   |      Name       | Result data type |                          Argument data types                           | Type 
------------+-----------------+------------------+------------------------------------------------------------------------+------
 pg_catalog | pg_start_backup | pg_lsn           | label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true | func
(1 row)

testdb=# select pg_start_backup('backup');
 pg_start_backup 
-----------------
 0/4000028

备份数据库集簇文件

cp $PGDATA /backup/

手动停止备份

select pg_stop_backup();

base_backup 备份

备份为tar 格式

 pg_basebackup  -U postgres  -D /home/postgres/backup1 -z  -l sakila  -Ft 

备份为目录格式

 pg_basebackup  -U postgres  -D /home/postgres/backup1   -l sakila  -Fp 

数据库恢复实验

base_backup 完全恢复

开启归档

PostgreSQL 备份与恢复#开启归档

备份整个数据库

postgresql> rm -rf /home/postgres/backup1/*
 pg_basebackup  -U postgres  -D /home/postgres/backup1 -z  -l sakila  -Ft 

业务模拟

postgresql> psql -U postgres -d testdb 
create table t01 (id int);
insert into t01 values(1);
insert into t01 values(2);
insert into t01 select *from t01;
begin;
insert into t01 select *from t01;

主机断电，导致$PGDATA/base损坏

kill -9  `ps -ef|grep bin/postgres |grep -v grep |awk '{print $2}'`

恢复base 表空间

rm  -rf $PGATA/base
tar -xvf  /home/postgres/backup/base.tar.gz  -C $PGDATA

指定数据库恢复参数

postgres> cat >> $PGDATA/postgresql.conf  << EOF
restore_command = 'cp /home/postgres/arch/%f %p'
recovery_target_timeline = 'latest'
EOF
postgres>  touch $PGDATA/recovery.signal

启动数据库，自动恢复，验证数据完整性。

postgres> pg_ctl start -D $PGDATA
postgres> psql -d testdb -c "table t01" 

删除参数

postgres> vi $PGDATA/postgresql.conf 

删除 restore_command 和recovery_target_timeline这两个参数，防止下次数据恢复的时候会有参数冲突。

base_backup 基于时间点恢复

开启归档

PostgreSQL 备份与恢复#开启归档

备份整个数据库

postgresql> rm -rf /home/postgres/backup1/*
 pg_basebackup  -U postgres  -D /home/postgres/backup1 -z  -l sakila  -Ft 

业务模拟

postgresql> psql -U postgres -d testdb 
drop table t01;
create table t01 (id int);
insert into t01 values(1);
insert into t01 values(2);
insert into t01 select *from t01;
select now();
drop table t01;

--------------------------------------output:-------------------------------
              now              
-------------------------------
2022-11-09 19:03:13
drop table t02;
create table t02(id int);
insert into t02 values(1);

停止数据库

pg_ctl stop
rm -rf $PGDATA/*

恢复base 表空间

tar -zxf  /home/postgres/backup1/base.tar.gz  -C $PGDATA

指定数据库恢复参数

postgres> vi $PGDATA/postgresql.conf 
--------------------input------------------------------
restore_command = 'cp /home/postgres/arch/%f %p'
recovery_target_time='2022-11-09 19:03:13' 

postgres>  touch $PGDATA/recovery.signal

启动数据库，自动恢复，验证数据完整性。

postgres> pg_ctl start -D $PGDATA

删除参数

postgres> vi $PGDATA/postgresql.conf 

恢复数据库

postgresql> select pg_wal_replay_resume();

查看数据是否恢复

postgresql> psql -U postgres -d testdb 
\d 

       List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t01  | table | postgres

删除 restore_command 和recovery_target_timeline这两个参数，防止下次数据恢复的时候会有参数冲突。

执行其他不完全恢复

recovery_target = 'immediate' :这个参数指定恢复应该在达到一个一致状态后尽快结束，即尽早结束。在从一个在线备份中恢复时，这意味着备份结束的那个点。

recovery_target_name (string) :指定（pg_create_restore_point()所创建）的已命名的恢复点，进行恢复。

recovery_target_time (timestamp) ：这个参数指定按时间戳恢复。

recovery_target_xid (string) ：这个参数指定按事务 ID进行恢复。

recovery_target_lsn (pg_lsn) ：这个参数指定按继续进行的预写日志位置的LSN进行恢复。

逻辑备份和还原

备份数据库

备份单表

pg_dump -U postgres  -d testdb -f /tmp/table.dmp -Fc -t public.emp

备份多表

pg_dump -U postgres  -d testdb -f /tmp/table.dmp -Fc -t public.emp -t  public.store

模式

pg_dump -Upostgres  -d testdb -f /tmp/public.dmp -Fc -n public

数据库

pg_dump -Upostgres  -d testdb -f /tmp/dir1 -Fd -j 2
ls -ltr /backup/dump/dir1/

只备份定义

pg_dump -Upostgres -dtestdb -Fp -s -f /tmp/ddl.sql

恢复数据库

文本格式恢复

#文本格式恢复
$ psql testdb postgres <  /tmp/testdb.sql

恢复某一张表

pg_restore -Usystem  -dtestdb /tmp/table.dmp -Fc -t emp

恢复某一个模式

pg_restore -Usystem -dtestdb /tmp/schema01.dmp -Fc -g schema01 -G schema02

#查看二进制文件中的内容
$ pg_restore -l testdb.dmp 

#从二进制格式文件生成一个文本格式文件
pg_restore dm_bk/testdb.dmp -f dm_bk/testdb2.sql

#二进制文件恢复，把dump文件中的数据导入到testdb数据库中
$ pg_restore -d testdb dm_bk/testdb.dmp 

使用TOC 文件选择性恢复

1）创建逻辑备份

pg_dump -Upostgres  -d testdb -f /tmp/testdb.dmp -Fc 

2）根据二进制备份文件生成 toc 文件

pg_restore -l -f /tmp/testdb.toc /tmp/testdb.dmp 

3）修改 toc 文件，用‘;’号注释掉不用还原的内容（bank表）:

postgres> vi /tmp/testdb.toc
# 注释t01 创建表结构和 数据。注意需要注释两条信息。

4）以 toc 文件列表做恢复

postgres> createdb testdb2
pg_restore -U postgres -d testdb2 -F c -L /tmp/testdb.toc /tmp/testdb.dmp

使用pg_dump 快速迁移

配置两个数据库之间的信任关系（必须）

host    all             all             192.168.18.0/24         trust

两库迁移

1、在数据库之间进行某个数据库迁移（Fp文本格式)

pg_dump -h node1 -p 1922 -d testdb   -Fp | psql -h node2 -p 1922 testdb

2、在数据库之间进行某个数据库迁移（Fc二进制格式)

pg_dump -h node1 -p 1922 -d testdb   -Fc   |  pg_restore -h node2 -p 1922 -d testdb 

3、在数据库之间进行某个数据库迁移（Ft二进制格式)

pg_dump -h node1 -p 1922 -d testdb   -Ft   |  pg_restore -h node2 -p 1922 -d testdb -Ft -c

4、迁移所有的数据库到目标数据库

pg_dumpall -h node1 -p 1922 -U postgres |psql -h node2 -p 5432 -U postgres 

PG_RMAN

PG_RMAN 编译和安装

官方地址

ossc-db/pg_rman: Backup and restore management tool for PostgreSQL (github.com)

下载并安装和编译

# chown postgres:postgres /soft/pg_rman*.tar.gz 
postgres>
tar -zxf /soft/pg_rman-*.tar.gz -C /soft
cd /soft/pg_rman-*/
make
make install

设置环境变量

cat >> ~/.bash_profile << EOF
export BACKUP_PATH=/home/postgres/backup/rman
export ARCLOG_PATH=/home/postgres/arch
EOF
source ~/.bash_profile

初始化pg_rman

[postgres@node1 pg_rman-1.3.14-pg12]$ pg_rman init
--------------------------------------output:-------------------------------

INFO: ARCLOG_PATH is set to '/home/postgres/arch'
INFO: SRVLOG_PATH is set to '/usr/local/pg12.2/data/log'

PG_RAMN备份

PG_RMAN 全库备份

 pg_rman backup -b full  -C -P  

验证备份有效性

 pg_rman validate

查看备份

pg_rman show

增量备份

pg_rman backup --backup-mode=incremental 

PG_RMAN 完全恢复

1. 实例崩溃

kill -9 `ps -ef|grep bin/postgres |grep -v grep |awk '{print $2}'`

rm  -rf $PGDATA/base

2. 恢复数据库文件。

pg_rman restore

3. 启动数据库。

pg_ctl start

4. 前滚redo log

select pg_wal_replay_resume();

PG_RMAN 基于时间点恢复

开启归档

PostgreSQL 备份与恢复#开启归档

备份整个数据库

postgresql>  pg_rman backup -b full  -C -P  

业务模拟

1. 误删除表

postgresql> psql -U postgres -d testdb 
drop table t01;
create table t01 (id int);
insert into t01 values(1);
insert into t01 values(2);
insert into t01 select *from t01;
select now();
drop table t01;

--------------------------------------output:-------------------------------
             now              
------------------------------
 2022-11-09 20:52:19.63061+08
drop table t02;
create table t02(id int);
insert into t02 values(1);

1. 清除数据文件

pg_ctl stop 
rm  -rf $PGDATA/base

2. 恢复数据库文件。

pg_rman restore --recovery-target-time='2022-11-09 20:52:19'  --recovery-target-action=promote

3. 启动数据库。

pg_ctl start