12-BackAndReBack
BackupAndReply
PostgreSQL有三种备份方式：
SQL备份
（逻辑备份）：即类似dump方式
- 可靠，简单
- 慢
备份
-- 备份
-- pgdump备份不会导致用户阻塞
-- pg_dump pg_dumpall
pg_dump [connection-option] [option...] [dbname]
连接信息，库/用户
[postgres@desktop-f5s26of ~]$ pg_dump postgres > postgres.sql
[postgres@desktop-f5s26of ~]$ ls
data postgres.sql
恢复
-- 恢复
-- 先删除一些表进行测试
drop table student;
\q 推出
psql -d postgres < postgres.sql
-- 这时进入，发现表已恢复
物理备份
找到文件的实际存储位置，进行备份。
- 快
- 写入时备份可能会丢失数据，不太 稳定。
备份
-- -D 指定备份文件的位置 -Ft 备份文件打包 -Pv 输出详细信息及进度 -U用户 -h ip -p 端口 -R 自动生成配置文件
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.1.13 -p 123456 -R
-- 首先创建一个备份目录
mkdir /pg_basebackup
-- 设置拥有着
sudo chown -R postgres. /pg_basebackup
-- 执行备份
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.1.13 -p 123456 -R
❯ sudo pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.1.13 -p 5432 -R -W
口令:
pg_basebackup: 开始基础备份，等待检查点完成
pg_basebackup: 已完成检查点
pg_basebackup: 预写日志起始于时间点: 0/CC000028, 基于时间轴1
pg_basebackup: 启动后台 WAL 接收进程
pg_basebackup: 已创建临时复制槽"pg_basebackup_982381"
7754/31628 kB (24%), 0/2 多个表空间 (/pg_basebackup/24585.tar 7754/31628 kB (24%), 1/2 多个表空间 (/pg_basebackup/24585.tar 31640/31640 kB (100%), 1/2 多个表空间 (/pg_basebackup/base.tar 31640/31640 kB (100%), 2/2 多个表空间
pg_basebackup: 预写日志结束点: 0/CC000120
pg_basebackup: 等待后台进程结束流操作...
pg_basebackup: 同步数据到磁盘...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: 基础备份已完成
❯ ls /pg_basebackup
24585.tar base.tar
backup_manifest pg_wal.tar
恢复
首先删除所有文件，方便下面测试
[postgres@desktop-f5s26of ~]$ cd data
[postgres@desktop-f5s26of data]$ ls
base pg_multixact pg_subtrans postgresql.conf
global pg_notify pg_tblspc postmaster.opts
pg_commit_ts pg_replslot pg_twophase postmaster.pid
pg_dynshmem pg_serial PG_VERSION tp_data
pg_hba.conf pg_snapshots pg_wal
pg_ident.conf pg_stat pg_xact
pg_logical pg_stat_tmp postgresql.auto.conf
[postgres@desktop-f5s26of data]$ rm -r ./*
[postgres@desktop-f5s26of data]$ ls
之后进行恢复
-- 将上面的base.tar和pg_wal.tar准备好
-- 将base.tar的内容解压到/data下
tar -xf base.tar -C ~/data/
-- 将pg_wal.tar解压到/archivedir目录下
tar -xf pg_wal.tar -C /archivedir
[postgres@desktop-f5s26of ~]$ cd /pg_basebackup/
[postgres@desktop-f5s26of pg_basebackup]$ ls
24585.tar backup_manifest base.tar pg_wal.tar
[postgres@desktop-f5s26of pg_basebackup]$ tar -xf base.tar -C ~/data/
[postgres@desktop-f5s26of pg_basebackup]$ tar -xf pg_wal.tar -C /archivedir/
此时，我们发现/archiverdir目录下有一些文件，同时/data下面文件也恢复了
[postgres@desktop-f5s26of /archivedir]$ ls
0000000100000000000000CC
[postgres@desktop-f5s26of data]$ ls
backup_label pg_logical pg_stat_tmp postgresql.auto.conf
base pg_multixact pg_subtrans postgresql.conf
global pg_notify pg_tblspc standby.signal
pg_commit_ts pg_replslot pg_twophase tablespace_map
pg_dynshmem pg_serial PG_VERSION tp_data
pg_hba.conf pg_snapshots pg_wal
pg_ident.conf pg_stat pg_xact
这时候物理配置文件已经恢复，但是需要进行配置一些文件
-- 打开postgresql.auto.conf文件，有以下内容
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres password=38324 channel_binding=prefer host=192.168.1.13 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
我们暂时不需要现有的内容直接注释掉已有内容
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
# primary_conninfo = 'user=postgres password=38324 channel_binding=prefer host=192.168.1.13 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
添加新的内容，保证可以恢复指定位置的物理数据文件
# 指定归档位置
restore_command = 'cp /archivedir/%f %p'
# 最大程度恢复
recovery_target = 'immediate'
这时候重启服务，进入psql,可以正常看到数据。
[postgres@desktop-f5s26of data]$ psql
psql (18.1)
Type "help" for help.
postgres=# \dt
List of tables
Schema | Name | Type | Owner
--------+---------+-------+----------
public | student | table | postgres
(1 row)
postgres=# select * from student
postgres-# ;
student_id | name
------------+------
2 | bbb
3 | ccc
5 |
6 |
9999 |
999967867 |
(6 rows)
但是当我们进行写入操作或者建表等操作，就会发现不允许我们做。
postgres=# create table tt(id int);
ERROR: cannot execute CREATE TABLE in a read-only transaction
postgres=# insert into student values(22);
ERROR: cannot execute INSERT in a read-only transaction
这是因为物理恢复之后，数据库仍认为自己处于恢复模式，是只读的。因此我们需要执行以下函数pg_wal_replay_resume，恢复状态，即可正常执行写入操作。
postgres=# select pg_wal_replay_resume();
pg_wal_replay_resume
----------------------
(1 row)
postgres=# insert into student values(22);
INSERT 0 1
归档备份
先了解几个概念，在PostgreSQL有多个子进程来辅助一些操作
-
BgWriter进程:BgWriter是将内存中的数据写到磁盘中的一个辅助进程。当向数据库中执行写操作后，数据不会马上持久化到磁盘里。这个主要是为了提升性能。BgWriter会周期性的将内存中的数据写入到磁盘。但是这个周期时间，长了不行，短了也不行。
-
如果快了，I0操作频繁，效率慢。
-
如果慢了，有查询操作需要内存中的数据时，需要BgWriter现把数据从内存写到磁盘中，再提供给查询操作作为返回结果。会导致查询操作效率变低。。考虑一个问题: 事务提交了，数据没落到磁盘，这时，服务器宕机了怎么办?
-
-
WalWriter进程:WAL就是write ahead log的缩写，说人话就是预写日志(redo log)。其实数据还在内存中时，其实已经写入到WAL日志中一份，这样一来，即便BgWriter进程没写入到磁盘中时，数据也不会存在丢失的问题。
- WAL能单独做备份么?单独不行!
- 但是WAL日志有个问题，这个日志会循环使用，WAL日志有大小的线程，只能保存指定时间的日志信息，如果超过了，会覆盖之前的日志。
-
PgArch进程:WAL日志会循环使用，数据会丢失。没关系，还有一个归档的进程，会在数据覆盖前，将WAL日志备份出来，PostgreSOL也提供了一个全量备份的操作，可以根据WAL日志，
备份
在postgres配饰文件目录目录下，可以看到pg_wal目录：
[postgres@desktop-f5s26of data]$ ls
base pg_multixact pg_subtrans postgresql.conf
global pg_notify pg_tblspc postmaster.opts
pg_commit_ts pg_replslot pg_twophase postmaster.pid
pg_dynshmem pg_serial PG_VERSION tp_data
pg_hba.conf pg_snapshots pg_wal
pg_ident.conf pg_stat pg_xact
pg_logical pg_stat_tmp postgresql.auto.conf
[postgres@desktop-f5s26of pg_wal]$ ls
0000000100000000000000C4 0000000100000000000000E5
0000000100000000000000C5 0000000100000000000000E6
0000000100000000000000C6 0000000100000000000000E7
0000000100000000000000C7 0000000100000000000000E8
0000000100000000000000C8 0000000100000000000000E9
0000000100000000000000C9 0000000100000000000000EA
0000000100000000000000CA 0000000100000000000000EB
0000000100000000000000CB 0000000100000000000000EC
0000000100000000000000CC 0000000100000000000000ED
0000000100000000000000CD 0000000100000000000000EE
0000000100000000000000CE 0000000100000000000000EF
0000000100000000000000CF 0000000100000000000000F0
0000000100000000000000D0 0000000100000000000000F1
0000000100000000000000D1 0000000100000000000000F2
0000000100000000000000D2 0000000100000000000000F3
0000000100000000000000D3 0000000100000000000000F4
0000000100000000000000D4 0000000100000000000000F5
0000000100000000000000D5 0000000100000000000000F6
0000000100000000000000D6 0000000100000000000000F7
0000000100000000000000D7 0000000100000000000000F8
0000000100000000000000D8 0000000100000000000000F9
0000000100000000000000D9 0000000100000000000000FA
0000000100000000000000DA 0000000100000000000000FB
0000000100000000000000DB 0000000100000000000000FC
0000000100000000000000DC 0000000100000000000000FD
0000000100000000000000DD 0000000100000000000000FE
0000000100000000000000DE 0000000100000000000000FF
0000000100000000000000DF 000000010000000100000000
0000000100000000000000E0 000000010000000100000001
0000000100000000000000E1 000000010000000100000002
0000000100000000000000E2 000000010000000100000003
0000000100000000000000E3 archive_status
0000000100000000000000E4 summaries
这些文件如何区分呢？每8位一组，16进制标识。
00000001 00000000 000000C4
时间线 逻辑id 物理id
查询当前使用的wal
postgres=# select pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/C4715C58
(1 row)
postgres=# select pg_walfile_name('0/C4715C58');
pg_walfile_name
--------------------------
0000000100000000000000C4
(1 row)
默认归档备份不开启，我们可以找data/postgre.conf文件设置，设置完成记得重启服务。
wal_level = replica
fsync = on
archive_mode = on
archive_command = 'test ! -f /archivedir/%f && cp %p /archivedir/%f'
命令操作：
test ! -f /archivedir/%f # 检查条件
&& # 逻辑与（条件满足才执行）
cp %p /archivedir/%f # 执行拷贝
%p: 源文件路径（完整路径）
$PGDATA/pg_wal/000000010000000100000001
%f: 文件名
000000010000000100000001
正常归档流程
1. PostgreSQL 生成 WAL 文件
2. WAL 文件写满（16MB）或被切换
3. 触发归档进程
4. 执行 archive_command:
a. test ! -f /archivedir/000000010000000100000001
b. 如果文件不存在 → 执行 cp 命令
c. 如果文件已存在 → 跳过（避免重复）
5. 如果命令返回 0（成功）：
- PostgreSQL 标记该 WAL 文件可被回收
6. 如果命令返回非 0（失败）：
- PostgreSQL 会定期重试
- WAL 文件会一直保留
实际执行归档过程
# 假设当前 WAL 文件是：0000000100000001000000AB
# PostgreSQL 内部执行：
test ! -f /archivedir/0000000100000001000000AB
# 如果返回 true（文件不存在），执行：
cp $PGDATA/pg_wal/0000000100000001000000AB /archivedir/0000000100000001000000AB
# 完整命令在 shell 中执行：
sh -c 'test ! -f /archivedir/0000000100000001000000AB && cp /var/lib/postgresql/12/main/pg_wal/0000000100000001000000AB /archivedir/0000000100000001000000AB'
如何测试，真的开启了归档了呢？我们提过还有一个归档的进程，会在数据覆盖前，将WAL日志备份出来.因此我们需要让pg可以切换日志，那么就要有大量的写操作，导致切换日志。
create table test_archive(
id int
);
-- 查询当前的归档位置
select pg_walfile_name(pg_current_wal_lsn());
-- 插入40万条数据
insert into test_archive values(generate_series(1,400000));
-- 再次查询当前的归档位置
select pg_walfile_name(pg_current_wal_lsn());
这时候我们发现，文件从C5切换到了C7，日志切换了。
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
0000000100000000000000C5
(1 row)
postgres=# insert into test_archive values(generate_series(1,400000));
INSERT 0 400000
postgres=# select pg_walfile_name(pg_current_wal_lsn());
pg_walfile_name
--------------------------
0000000100000000000000C7
(1 row)
这时候我们去指定的归档地点查看实际文件
❯ cd /archivedir
❯ ls
0000000100000000000000C5 0000000100000000000000C6
我们发现确实存储成功了。
恢复（PITR）
时间点恢复，比如每天晚上24点做全备份，第二天14数据被删除。我们需要恢复到昨晚24点的未删除状态。
如果使用物理备份，也就是全量备份，会丢失很多数据。
而如果使用PITR，有昨晚24点->第二天14点的所有数据信息，我们指定数据恢复到制定的事务id或指定的时间点，从事实现数据完整恢复。
先进行全备操作（24:00）
pg_basebackup -D /pg_basebackup -Ft -Pv -Upostgres -h 192.168.1.13 -p 5432 -R
之后进行写入操作
insert into student values(24);
误删操作
delete from student;
恢复数据
将当前服务的全部干掉，按照前面的套路全备恢复，停掉服务
su - postgres
cd ~/data/
rm -rf *
sudo systemctl stop postgresql
之后进行恢复
[postgres@desktop-f5s26of pg_basebackup]$ tar -xf base.tar -C ~/data/
[postgres@desktop-f5s26of pg_basebackup]$ tar -xf pg_wal.tar -C /archivedir/
来到归档目录，查看归档日志，使用pg_waldump查询
cd /archivedir
pg_waldump 0000000100000000000000CC
❯ sudo pg_waldump 0000000100000000000000CC
rmgr: XLOG len (rec/tot): 30/ 30, tx: 0, lsn: 0/CC000028, prev 0/CB0000A0, desc: CHECKPOINT_REDO wal_level replica
rmgr: Standby len (rec/tot): 50/ 50, tx: 0, lsn: 0/CC000048, prev 0/CC000028, desc: RUNNING_XACTS nextXid 826 latestCompletedXid 825 oldestRunningXid 826
rmgr: XLOG len (rec/tot): 114/ 114, tx: 0, lsn: 0/CC000080, prev 0/CC000048, desc: CHECKPOINT_ONLINE redo 0/CC000028; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:826; oid 32791; multi 1; offset 0; oldest xid 744 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 826; online
rmgr: XLOG len (rec/tot): 34/ 34, tx: 0, lsn: 0/CC0000F8, prev 0/CC000080, desc: BACKUP_END 0/CC000028
rmgr: XLOG len (rec/tot): 24/ 24, tx: 0, lsn: 0/CC000120, prev 0/CC0000F8, desc: SWITCH
找到你要恢复的节点的id
这时候修改data/postgresql.auto.conf，下面是之前填写的内容，我们修改最大程度恢复为，指定事务id恢复。注释immediate
# 指定归档位置
restore_command = 'cp /archivedir/%f %p'
# 最大程度恢复
recovery_target = 'immediate'
# 指定归档位置
restore_command = 'cp /archivedir/%f %p'
# 最大程度恢复
# recovery_target = 'immediate'
同时我们打开postgresql.conf搜索recovery，定位#recovery_target_xid = ''选项,告诉我们是用来指定事务id的，我们这时候复制这个选项到文件postgresql.auto.conf中
# 指定归档位置
restore_command = 'cp /archivedir/%f %p'
# 最大程度恢复
# recovery_target = 'immediate'
recovery_target_xid = '718'
保存之后，正常启动postgre服务即可
systemctl start postgresql
psql
注意
我们这里备份常常使用postgresql.conf和postgresql.auto.conf文件，加载顺序如下
# 实际加载顺序：
1. postgresql.conf # 主配置
2. 包含的文件 (include_dir/*.conf) # 按文件名排序
3. postgresql.auto.conf # 自动配置（最后，优先级最高）
也就是每次服务启动都会执行一次 postgresql.auto.conf ，为了防止每次启动都会导致一次恢复操作，我们可以在每次恢复完之后，立马清除 auto 文件.
seq -i 's/^/# /' data/postgresql.auto.conf
浙公网安备 33010602011771号