PG(Postgres)数据库不停机在线备份

摘要

1、PG数据库停用服务备份；无需停服，在线备份
2、PG数据库备份数据还原

介绍

1、备份方式：

1）PG数据库可以停止服务拷贝Data目录、或使用迁移工具进行备份或迁移，这种方式快且简单，但需要停止服务。
2）PG数据库可以不停止服务进行基础备份，再加WAL日志备份达到不停服全量备份。

2、备份还原工具

pg_dump：逻辑备份工具，单个数据库备份
pg_dumpall：逻辑备份工具，集群数据库备份（用户下的多个数据库）
pg_basebackup：物理备份工具，集群数据库备份

psql：交互命令行工具，也可以用于逻辑还原sql文件
pg_restore：逻辑还原工具，还原pg_dump生成的除了SQL文件的其他格式备份

使用

1、离线备份（停止服务）

1.1、备份

导出数据库到"postgres.tar"文件

pg_dump -U postgres -F t -f postgres.tar postgres

1.2、恢复

创建"backup"数据库，将备份的数据恢复到"backup"数据库中

pg_restore -U postgres -d backup postgres.tar

2、在线备份(不停服务)

说明：
离线备份全部数据等同于这里的基准数据。
在线备份包含3中类型的数据：基准数据、归档数据（归档WAL）、未归档数据（当前WAL），这三种数据叠加就是数据库的最新数据。

例：每周日全量备份，其余6天增量部分
基准备份：周日全量备份
归档数据（归档WAL）：周一至现在的几分钟或几秒钟前
未归档数据（当前WAL）：当前数据库正在工作的数据

2.1、备份

2.1.1、备份准备工作：

配置数据库的"postgresql.conf"开启归档模式

wal_level = replica # 注释也可以，默认值为replica

archive_mode = on
archive_command = 'DIR=/home/pg_archive; test ! -d $DIR && mkdir -p $DIR; test ! -f $DIR/%f && cp %p $DIR/%f'
archive_timeout = 300

“/home/pg_archive” 可自行更改指定目录
归档模式会自动(写满一个自动保存，超时保存)把基础备份后的数据归档到指定目录
重启数据库，当前已经开始自动归档数据了，可以准备开始备份数据库了

2.1.2、备份基准数据

数据库执行SQL，开始备份。
data目录会自动生成一个backup_label文件，里面记录了’basebackup20240514’。

SELECT pg_start_backup('basebackup20240514');

可以cp、scp、pg_basebackup等拷贝基础data数据文件作为基础数据

pg_basebackup -U postgres -Ft -Pv -Xf -z -Z5 -D /home/pg_base/`date +%F`

拷贝完成后执行SQL，结束备份，会生成本次备份信息，会切换WAL段。

SELECT pg_stop_backup(); 

2.1.3、备份归档数据

在基准数据备份完成后，需要保存好 2.1.1 中配置文件中写的目录中的文件"/home/pg_archive",目录中将自动保存一直以来的归档数据。

说明：当前写入数据未归档，如果需要强制归档可以手动执行SQL
Select * FROM pg_switch_wal();

2.2、恢复

2.2.1、恢复基准数据

• 恢复数据需要停机恢复（服务器或数据库损坏时，服务也是停止状态）

解压原压缩好的基准数据到data目录

tar -xzvf /home/pg_base/****/base.tar.gz
cp /home/pg_base/****/* /****/data

说明：如果没有归档数据和未归档数据可以直接启动服务，无需其余操作完成基准数据恢复

2.2.2、恢复归档数据（非必须）

检查或修改 “postgresql.conf” 读取存档数据
“restore_command” 与上面的 “restore_command” 相反，把归档文件拷贝回来

restore_command = 'DIR=/home/pg_archive; cp $DIR/%f %p'

说明：如果没有未归档数据直接跳至 2.2.4

2.2.3、恢复未归档数据（非必须）

删除data/pg_wal目录中的所有wal文件，拷贝旧data/pg_wal中的文件过来，启动后将自动恢复未归档的所有数据到数据库最新数据

cp /****/old_data/pg_wal/* /****/data/pa_wal

说明：如果旧的data目录出错，但pg_wal目录完整存在，那到此步讲恢复数据库所有数据，如果目录不存在将丢失少量数据。

2.2.4、启动数据库并恢复

在data目录复制文件"postgresql.conf"为 “recovery.signal”

cp postgresql.conf recovery.signal

说明：配置文件支持添加参数如：recovery_target_time = “2020-1-1 12:00 GMT”，恢复到指定时间点，而不是归档的最新数据。

启动数据库：docker start postgres、pg_ctl start ***

查看日志，数据库没有报错信息的话将自动恢复完成。"recovery.signal"会被删掉，“backup_label"自动改名为"backup_label.old”。