PostgreSQL备份还原

备份工具	安装方式	备份类型	备份特点
pg_dump	自带	逻辑	可备份指定库
pg_dumpall	自带	逻辑	只能全库备份
pg_basebackup	自带	物理	全库物理备份，不清理归档
pgbackrest	独立安装	物理	可指定物理备份，可清理归档

1、pg_dump

创建备份存放目录

mkdir -p /app/pgsql/backup/pg_dump

备份单个库

pg_dump -d work_base >/app/pgsql/backup/pg_dump/work_base.sql

还原单个库

psql </app/pgsql/backup/pg_dump/work_base.sql

备份整个库

pg_dump > /app/pgsql/backup/pg_dump/full.sql

恢复整个库

psql </app/pgsql/backup/pg_dump/full.sql

2、pg_dumpall

创建备份存放目录

mkdir -p /app/pgsql/backup/pg_dumpall

备份全库

pg_dumpall > /app/pgsql/backup/pg_dumpall/all_dbdata.sql

恢复全库

psql postgres < /app/pgsql/backup/pg_dumpall/all_dbdata.sql

3、pg_basebackup

创建备份文件目录

mkdir /app/pgsql/backup/pg_basebackup

执行备份

-D 指定备份文件的存储位置
-Ft 备份文件打个包
-Pv 输出备份的详细信息
-U 用户名（要拥有备份的权限）
-h ip地址  -p 端口号
-R 复制写配置文件

pg_basebackup -D /app/pgsql/backup/pg_basebackup -Ft -Pv

查看备份文件

[postgres@msp-app02 pg_basebackup]$ ll
total 55604
-rw------- 1 postgres postgres   226492 Jan 29 16:10 backup_manifest
-rw------- 1 postgres postgres 39925760 Jan 29 16:10 base.tar
-rw------- 1 postgres postgres 16780288 Jan 29 16:10 pg_wal.tar

模拟数据库数据丢失，先停止主节点postgresql服务，然后删除data目录下的所有内容

pg_ctl -D /app/pgsql/data/ -l /app/pgsql/log/start.log stop

将之前备份的两个文件准备好，一个base.tar，一个pg_wal.tar
第一步：将base.tar中的内容，全部解压到data目录下

tar -xvf base.tar -C /app/pgsql/data/

第二步：将pg_wal.tar中的内容，全部解压到pg_wal目录下

tar -xvf pg_wal.tar -C /app/pgsql/data/pg_wal/

第三步：修改postgresql.auto.conf文件，指定归档文件的存储位置，以及恢复的方式

vim /app/pgsql/data/postgresql.auto.conf

#添加以下内容
restore_command = 'cp /app/pgsql/data/pg_wal/%f %p'
recovery_target = 'immediate'

第四步：启动postgresql

pg_ctl -D /app/pgsql/data/ -l /app/pgsql/log/start.log start

第五步：查看数据是否恢复成功

#登录psql
psql -U postgres -d postgres

#切换到work_base库
\c wok_base;

#查看test_table表数据
select * from test_table;

work_base=# select * from test_table;
  id  |  value   
------+----------
    1 | test1
    2 | test2
    3 | test3
    4 | test4
    5 | test5
    6 | test6
    7 | test7
    8 | test8
--More--				#数据恢复成功

第六步：重新将旧的 primary 节点以 standby 的身份加入到集群中
删除postgresql.auto.conf新添加的配置

vim /app/pgsql/data/postgresql.auto.conf

#删除以下内容
restore_command = 'cp /app/pgsql/data/pg_wal/%f %p'
recovery_target = 'immediate'

#重启生效
pg_ctl -D /app/pgsql/data/ -l /app/pgsql/log/start.log restart

#关闭postgresql服务
pg_ctl -D /app/pgsql/data/ -l /app/pgsql/log/start.log stop

#测试是否能重新加入集群
repmgr node rejoin -f /etc/repmgr/conf/repmgr.conf -h msp-app02 -U repmgr -d repmgr --dry-run --force-rewind

#加入集群
repmgr node rejoin -f /etc/repmgr/conf/repmgr.conf -h msp-app02 -U repmgr -d repmgr --force-rewind

查看集群信息

[postgres@msp-app03 ~]$ repmgr -f /etc/repmgr/conf/repmgr.conf cluster show
 ID | Name      | Role    | Status    | Upstream  | Location  | Priority | Timeline | Connection string
 ----+-----------+---------+-----------+-----------+-----------+----------+----------+----------------------
  1  | msp-app01 | standby |   running | msp-app02 | location1 | 100      | 6        | host=msp-app01 user=r
  2  | msp-app02 | primary | * running |           | location1 | 100      | 6        | host=msp-app02 user=r
  3  | msp-app03 | witness | * running | msp-app02 | location1 | 0        | n/a      | host=msp-app03 user=r