【技术文章】PostgreSQL日常维护（1）

a. 首先 backend 开启是一个事务,获得一个事务号 XID;

b. 在这个事务中对数据的任意修改，都被 XID 标记。

c. 其他 backend 在扫描数据时，会看到被这个 XID 修改过的数据，根据当前的隔离级别，选择对这些数据是否可见（默认的读已提交隔离级别看不到这些数据）。

d. 只有当此 XID 最后被标记成 commit （写 WAL commit log 和写 clog xact）后，其他的 backend 才能看到这个 XID 修改的数据。

• 使用MVCC，读操作不会阻塞写，写操作也不会阻塞读，提高了并发访问下的性能

• 事务的回滚可立即完成，无论事务进行了多少操作

• 数据可以进行大量更新，不段像MySQL innodb引擎和Oracle那样需要保证回滚段不会被耗尽

• 事务ID个数有限制 2 32

• 事务ID由32位数保存，而事务ID递增，当事务ID用完时，会出现wraparound问题。

• PostgreSQL通过VACUUM机制来解决该问题。对于事务ID，PostgreSQL有三个事务ID有特殊意义：

• 0代表invalid 无效的事务号

• 1代表bootstrap事务号

• 2代表frozon事务。frozon transaction id比任何事务都要老

• 可用的有效最小事务ID为3。VACUUM时将所有已提交的事务ID均设置为2，即frozon。之后所有的事务都比frozon事务新，因此VACUUM之前的所有已提交的数据都对之后的事务可见。PostgreSQL通过这种方式实现了事务ID的循环利用。

6、MVCC的工作机制

PostgreSQL中的MVCC就是通过以上几个隐藏字段协同实现的，下面举几个具体的例子来看下工作机制首先我们开启事务插入一条数据，其中ctid代表数据的物理位置，xmin为当前事务id，xmax为0继续在上个事务中再插入一条数据，因为在同一个事务中，可以看到cmin，cmax按顺序增长修改id为1的数据name为c，此时id为1的ctid变为了(0,3)，同时开启另外一个窗口，可以看到id为1的xmax标识为修改数据时的事务id，即代表此条tuple已删除。

3、手动收集

手动收集统计信息的命令是analyze命令，analyze的语法格式：

analyze [verbose] [table[(column[,..])]] 

• verbose：显示处理的进度，以及表的一些统计信息

• table：要分析的表名，如果不指定，则对整个数据库中的所有表作分析

• column：要分析的特定字段的名字默认是分析所有字段

• analyze 命令 会在表上加读锁

4、谁来更新统计信息？

统计信息是有autovacuum进程更新，PG使用MVCC机制进行数据库的并发控制，因此同样需要一组后台进程进行过期版本的清理。

autovacuum进程不止负责对过期元组进行清理，同时也负责定期更新表的统计信息。

4.1 为什么要把这两个操作放在一起？

PG的MVCC机制数据和数据的旧版本是统一存放在表文件上的，在清理时要进行全表扫描的操作，而统计信息的收集也是需要读取表文件的，这两个操作放在一起做可以在一定程度上节省IO；清理废旧元组和更新统计信息都是通过收集表的元组变更数据来触发的，共享一套机制，因此放在一起处理也比较方便；

4.2 何时触发统计信息（when）

统计信息有两种触发方式：

a、用户使用analyze命令手动触发(analyze/vacuum analyze)

在PG中，事务提交/回滚时会发消息给进程pgstat，pgstat会汇总这份信息并记录到文件中，autovacuum launcher会定期读取文件，获得，当某个表的改动超过阈值时便会触发一次统计信息的更新操作。

需要注意的是autovacuum worker也会给pgstat发消息，但实际上这个消息是通知pgstat统计更新/清理已经完成，可以清理统计信息了。因此stat file只有pgstat更新，launcher只是读取，不涉及并发写冲突。

5、收集进程及目录存放

PG用于收集统计信息的进程

stats collector process 源码路径 ："src/backend/postmaster/pgstat.c"

统计信息的存放地启动时, 读入已存在的统计文件, 或初始化0. 数据库运行过程中存储在内存和temp文件 postgresql.conf ->stats_temp_directory , 推荐配置在高速磁盘或内存文件系统中，正常关闭时会把统计信息从tmp目录拷贝到$PGDATA/pg_stat目录中，确保统计信息不会丢失。

1、逻辑备份恢复

pg_dump   可以选择一个数据库或部分表进行备份pg_dumpall   备份集簇服务所有数据库copy     导入导出表数据COPY 命令是SQL命令，\copy 命令是元命令。

COPY 命令和 \copy 元命令的区别：

• COPY 命令必须具有SUPERUSER超级权限（将数据通过stdin/stdout方式导入导出情况除外），而 \copy 元命令不需要SUPERUSER权限。

• COPY 命令读取或写入数据库服务端主机上的文件，而 \copy 元命令是从psql客户端主机读取或写入文件。

• 从性能方面看， 大数据量导出文件或大文件数据导入数据库，COPY 比 \copy 性能高。

2、物理备份恢复

• 冷备份及恢复（文件系统复制）

• 热备份及恢复（基于时间点的备份恢复）

3、pg_basebackup 简介

3.1  pg_basebackup工作原理

3.2 pg_basebackup 参数说明

• -h 指定连接的数据库的主机名或IP地址，这里就是主库的ip。

• -U 指定连接的用户名，此处是我们刚才创建的专门负责流复制的repl用户。

• -F 指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。

• -x 表示备份开始后，启动另一个流复制连接从主库接收WAL日志。

• -P 表示允许在备份过程中实时的打印备份的进度。

• -R 表示会在备份结束后自动生成recovery.conf文件，这样也就避免了手动创建。

• -D 指定把备份写到哪个目录，这里尤其要注意一点就是做基础备份之前从库的数据目录（$PGDATA）目录需要手动清空。

• -l 表示指定一个备份的标识

• S3兼容：支持备份存储S3和Azure兼容对象存储上，如对象存储Minio。

• 压缩技术：支持gzip, bzip, lz4, zstd的压缩算法，并提供不同压缩等级

• 异步、并行：在异步模式下通过并行推送和获取以加快处理速度，提高备份与还原处理效率。

• 校验和：采用checksum 算法，验证备份期间复制的每个文件的校验和。在完全备份期间，将验证所有页面的校验和，而在差异备份和增量式备份期间，将验证已更改文件的校验和。

• 加密：通过定义加密类型及密码，来保证备份数据的存储安全性

在技术特性基础上，pgbackrest实现了备份、还原、保留策略、云端备份等相关功能，如图所示：

• 保留策略：支持保留的备份数量，可以指定全量、差异备份的具体数量。

• 还原方式：支持全量、增量、差异还原以及时间点还原，还同时支持还原指定数据库、表空间和链接支持等。

• 异步归档：支持异步的推送和获取，利用pgbackrest并发特性，提升备份和还原的性能，同时在处理过程中对重新数据进行校验和处理，保证备份和还原数据正确性。

• 检查：检查命令验证pgBackRest和archive_command设置是否已正确配置以进行归档和备份。

• 监控：支持通过SQL查询方式，在PostgreSQL中创建函数查询检查信息，也可以通过Jq从Json中提取数据来进行查询。

• 加密：基于用户提供的密码及加密类型对存储库进行加密，防止未经授权访问存储库中的数据。

• 压缩：根据用户设定的压缩算法及压缩等级，将压缩后的数据存储到存储库中。

4、备份方式