在排查连接问题时需要检查的事项
数据库服务器是否可达？
使用 telnet <host> <port> 或
使用 nc -zv <host> <port>
使用 traceroute （Unix/Linux）或 tracert （Windows），指定主机和中间路由器允许的协议。
主机名和端口是否正确？
服务器是否在运行？
使用 lt_ctl status
服务器端防火墙是否允许通过该端口进行通信？
客户端防火墙是否允许与服务器端口通信？
lt_hba.conf 是否有任何条目允许组合使用 SSL/非SSL、客户端主机、数据库和用户？
listen_addresses 参数是否配置为允许通过所需的 IP 地址（包括 IPv4 和/或 IPv6）连接？
数据库名称、用户名和密码是否正确？
用户是否有权限连接到数据库？
使用 ltsql 的 \l 或 pg_database.datacl 检查权限。
数据库服务器是否有足够的 CPU 和内存资源？
最大连接数限制是否已达到？
max_connections 和 superuser_reserved_connections 参数（实例级别）
CREATE/ALTER DATABASE CONNECTION LIMIT（数据库级别）
CREATE/ALTER ROLE CONNECTION LIMIT（用户级别）
连接终止和查询取消
当连接关闭时，任何未完成的事务都将回滚。
终止连接（pg_terminate_backend()）和取消查询（pg_cancel_backend()）并不总是有效。例如，在后端进程处于不可中断部分时，它们不起作用，如等待获取轻量级锁、针对网络存储设备的读/写系统调用以及没有取消点的循环。
设置适当的 statement_timeout（语句、用户、数据库、实例级别）。广泛设置较短的超时时间并不推荐，因为它会取消有意运行的长时间查询。
适当设置客户端超时时间。
适当设置服务器端超时时间。
tcp_keepalives_idle, tcp_keepalives_interval, tcp_keepalives_count
TCP 保持活动仅在网络连接空闲时起作用。当套接字连接正在建立或已经发送了一些数据并在等待其 ACK 时，它不起作用。
实际的超时时间为 tcp_keepalives_idle + tcp_keepalives_interval * tcp_keepalives_count。
tcp_user_timeout
设置 TCP 重传超时。相对新近在 Linux 上可用。
在 TCP 保持活动无法帮助的情况下介入，即当套接字连接正在建立或已经发送了一些数据并在等待其 ACK 时。
与 TCP 保持活动一起使用时会令人困惑，因为这改变了 TCP 保持活动超时的时间。安全的做法是将 tcp_user_timeout 设置为 tcp_keepalives_idle + tcp_keepalives_interval * tcp_keepalives_count。
authentication_timeout
idle_in_transaction_session_timeout
idle_session_timeout
client_connection_check_interval
连接故障转移
客户端驱动程序允许在连接字符串中指定多个主机。
连接超时应用于连接字符串中的每个主机。因此，如果在列表中有许多失败的主机，连接可能会花费意想不到的长时间。
故障转移后恢复会话状态：会话变量、预编译语句、临时表、保持游标（使用 DECLARE CURSOR WITH HOLD 创建）、咨询锁、会话用户（使用 SET SESSION AUTHORIZATION 设置）、当前用户（使用 SET ROLE 设置）。
WAL 的用途
用于崩溃恢复、归档恢复（时间点恢复：PITR）及复制
无论事务是否提交，更新都会重新执行。
不像其他流行的 DBMS，没有撤销日志（前镜像）或操作。
因此，事务回滚和崩溃恢复速度快。
被中止事务所做的更改保留在内存和磁盘上，但由于 MVCC 的存在，这些更改对其他事务是不可见的。
WAL 结构
一系列 8 KB 的块。
每个块可以包含多个 WAL 记录。同时，每个 WAL 记录可以跨越多个块。
在内存和存储上的内容相同。
WAL 缓冲区是在内存中的一段连续的块数组。它是以循环的方式使用的。
存储上的 WAL 被划分为 WAL 段文件。默认情况下，每个 WAL 段文件大小为 16 MB，可以通过 lt_initdb 的 --wal-segsize=size 参数来配置。
写入 WAL
在内存中修改共享缓冲区中的数据页，然后将更改写入 WAL 缓冲区。
WAL 缓冲区始终顺序写入 WAL 文件（不是随机写入）。
在将共享缓冲区中的脏数据页写入磁盘之前，首先记录所有影响该数据页的最新 WAL 记录。这一规则被称为 WAL（Write Ahead Log）。
每个数据页在其页头中都有一个位置（LSN），表示最新的更新记录在 WAL 中的位置。这是页 LSN。
LSN（日志序列号）：一个无符号 8 字节整数。它代表 WAL 段、块及其在块中的偏移。
如果写入 WAL 文件失败，实例将以 PANIC 消息崩溃。
WAL 卷可能由于以下原因而超出 max_wal_size：
大量写入，如使用 COPY 加载数据。
归档 WAL 文件失败。
wal_keep_size 的值过大。
未使用的复制槽位。
SELECT 可以在以下情况下修改数据页并写入 WAL：
获取行锁，例如 SELECT FOR UPDATE。它们会在元组头中设置 xmax，并可能更新 MultiXact 数据结构。
修剪行指针和碎片整理页面。
设置元组头的提示位。当启用页面校验和时，会发出 WAL。
ACID 属性解释
原子性：事务回滚和数据库恢复
一致性：完整性约束和触发器，如非空、检查、主键/唯一/外键约束
隔离性：多版本并发控制（MVCC）和锁
持久性：WAL（写前日志）
事务 ID (XID)
当事务首次修改数据时，例如在 INSERT、UPDATE、DELETE 和 SELECT FOR SHARE/UPDATE 中，会被分配一个 XID。
XID 分配通过 XidGen 轻量级锁（LWLock）进行序列化。
XID 分配通常非常快，但在某些情况下可能会遇到延迟。每 32K 个事务，它会通过 SLRU 缓存分配并清零一个新的提交日志（clog）页面。这个 clog 页面的分配可能会为了页面替换而刷新一个脏页。
这可能会导致响应时间的不可预测的峰值。
只读事务不会分配 XID。它们不会受到分配新 XID 时的 LWLock 争用的影响。
XID 存储在元组头部，并且作为 xmin 和 xmax 系统列可见（SELECT xmin, xmax, * FROM mytable）。
xmin 是创建元组的事务的 XID（INSERT、UPDATE、COPY）。
xmax 是删除或锁定元组的事务的 XID（例如，DELETE、UPDATE 或 SELECT FOR SHARE/UPDATE）。
删除元组（DELETE、UPDATE）。
锁定元组（例如，SELECT FOR SHARE/UPDATE）。
特殊的 XID 值
0：无效的 XID
1：引导 XID。在 lt_initdb 期间由引导处理使用。
2：冻结 XID：Lightdb 的较新版本只在序列元组中使用这个值，而不是在表中使用。
MVCC：多版本并发控制
MVCC 的主要优点是“读取永远不会阻塞写入，写入也永远不会阻塞读取”，即对同一行的 UPDATE/DELETE 和 SELECT 不会相互阻塞。
对同一行的写入会互相阻塞。
在传统的基于锁的并发控制中，对同一行的读取和写入会产生冲突。
其总体工作原理如下：
插入或更新一行会创建该行的新版本。更新保留旧行版本供其他运行中的事务使用。（多版本）
创建事务的 XID 被设置为新行版本中的 xmin 字段。
新行版本仅在创建事务提交后才对创建事务可见。
一旦创建事务提交，所有后续的新事务都能看到新的行版本。其他现有事务继续看到旧行版本。旧行版本现在是一个“死元组”。
删除一行并不会移除行版本。而是将删除事务的 XID 设置为行版本中的 xmax 字段。
被删除的行版本仅在删除事务提交之前对其删除事务不可见。
一旦删除事务提交，所有后续的新事务都无法看到该行版本。其他现有事务继续看到行版本。行版本现在是一个“死元组”。
最终，当没有剩余事务能看到该死元组时，VACUUM 将移除它。
元组可见性的原理：
每个事务使用自己的快照、提交日志（clog）以及目标元组头部的 xmin 和/或 xmax 来确定是否能够看到特定的行版本。
快照是什么：
在某个时间点正在运行的事务的快照。
可以运行 "SELECT pg_current_snapshot();" 查看当前事务的快照。
快照的文本表示形式为 xmin:xmax:xip_list。例如，10:20:10,14,15。
xmin：仍在活动中的最低事务 ID。所有小于 xmin 的事务 ID 要么已提交且可见，要么已回滚且无效。
xmax：最高完成事务 ID 的下一个 ID。所有大于等于 xmax 的事务 ID 在快照时刻尚未完成，因此不可见。
xip_list： 快照时正在进行的事务。xmin <= X < xmax 且不在此列表中的事务 ID 在快照时已经完成，因此根据其提交状态，要么可见，要么已死亡。
在 READ COMMITTED 事务中，每次 SQL 语句开始时都会获取一个快照。
在 REPEATABLE READ 或 SERIALIZABLE 事务中，在第一条 SQL 语句开始时获取快照并在整个事务中使用。
提交日志（clog）是什么：
表示事务状态的位数组。
使用两个位来指示事务的结果：进行中、已提交、已中止、部分提交。
存储在 $LTDATA/lt_xact/ 目录下的文件集合中。
缓存在内存缓冲区中，共 128 个 8 KB 页面。
当快照显示目标事务已完成时，会查询 clog。
根据快照和 clog，已提交事务的更改可见，而被中止或正在运行的事务的更改不可见。
实际的元组可见性要复杂得多...
提示位
提示位是元组头部的 infomask 字段中的位，用于帮助确定元组的可见性。
它们是为了性能优化而设计的，并不是数据正确性的必需条件。
它们表示由 xmin 或 xmax 所指示的事务是否已提交或中止。共有四个标志位：
HEAP_XMIN_COMMITTED: xmin 事务已提交
HEAP_XMIN_INVALID: xmin 事务已中止
HEAP_XMAX_COMMITTED: xmax 事务已提交
HEAP_XMAX_INVALID: xmax 事务已中止
提示位是如何使用的：
一个事务检查提示位以查看 xmin 和/或 xmax 事务是否已提交或中止。
如果提示位已设置，则完成。
否则，检查提交日志（$LTDATA/lt_xact/），并且可能还需要检查子事务层次结构（$LTDATA/lt_subtrans/）来确定事务的结果。这是一个耗时的操作。
设置提示位。它们将在稍后被持久化到磁盘。
设置提示位会写入一个数据页，并且如果启用了页面校验和的话，也会写入 WAL。
即使请求的模式与已持有的锁兼容，锁请求也可能等待。
问：你认为事务3是否会继续执行查询？
事务1：一个长时间运行的 SELECT 正在针对 mytable 进行。
事务2：运行 “ALTER TABLE mytable ADD COLUMN new_col int;”。由于 ALTER TABLE 请求的 Access Exclusive 锁与事务1持有的 Access Share 锁冲突，所以被阻塞。
事务3：运行一个针对 mytable 的短 SELECT 查询。
答：事务3会等待直到事务2完成，因为事务2先到并且正在等待。
后来的请求者尊重等待队列中更早的等待者，并不会超越他们。否则，更早的请求者可能会等待过长的时间。
因此，即使是预期可以快速运行的DDL也要：
在非高峰时段执行，和/或
设置锁超时。例如，在DDL之前运行 “SET lock_timeout = '5s';”。如果DDL超时，则重试。
这对轻量级锁并不适用。在极端情况下，一个 LWLock 上的 Exclusive 模式请求可能会由于后来连续不断的 Share 模式请求而等待数十秒。
准备好的事务持有锁
一个准备好的事务将继续持有锁，但由于没有关联的会话，因此不会出现在 pg_stat_activity 中。
pg_locks 显示准备好的事务为具有 NULL pid 的条目。检查 pg_prepared_xacts。
数据校验和
目的和用途
每个关系的数据页，包括所有的分支，都有一个在其页头的 16 位校验和。
设计目的是为了检测 I/O 系统（例如，卷管理器、文件系统、磁盘驱动程序、存储固件、存储设备等）引起的损坏。早期检测可以防止损坏的传播。
不是为了检测内存错误。
在整个集群级别启用，可以通过 lt_initdb 的 -k/--data-checksums 参数或者在数据库服务器关闭时通过 lt_checksums 工具启用。默认情况下是禁用的，因为它会影响性能。
运行 "SHOW data_checksums" 可以查看数据校验和是否启用。返回值为 on 或 off。
如何工作
当数据页即将写入磁盘时，计算校验和并设置。
在从磁盘读取页之后，通过比较页头中的值和新计算出的值来验证校验和。
如果验证失败，会发出 WARNING 和 ERROR 消息，导致查询失败。
如果在执行校验和验证之前页头未能通过基本的合理性检查，查询将以相同的 ERROR 消息失败，但不会有指示校验和失败的 WARNING。
WAL CRC
WAL 在每个 WAL 记录头中使用一个 32 位的 CRC。
当 WAL 记录放入 WAL 缓冲区时设置 CRC，并在读取 WAL 记录时验证。
用于检测、绕过或修复数据损坏的工具（有些可能是危险的！）
额外模块
amcheck： 检测堆表（表、序列）和 B 树索引的逻辑损坏。
pg_visibility_map：
pg_check_frozen() 和 pg_check_visible() 检测可见性映射损坏。
配置参数
ignore_checksum_failure
zero_damaged_pages
ignore_system_indexes
备份与恢复方法
文件系统级别的备份（二进制格式）
使用 lt_dump/lt_dumpall 的 SQL 导出（文本格式）
持续归档（二进制格式）
备份与恢复方法的特点
SQL 导出和持续归档可以在数据库在线的情况下执行。文件系统级别的备份需要数据库服务器关闭。
SQL 导出可以选择性地备份和恢复单个表。其他方法不能只备份或恢复某些特定的表或表空间。
SQL 导出通常较小，因为 SQL 脚本只需包含索引创建命令，而不是索引数据。
SQL 导出可以加载到更新的主要版本的数据库中。
SQL 导出可以将数据库转移到不同的机器架构，例如从 32 位服务器迁移到 64 位服务器。
持续归档可以执行 PITR（基于时间点的恢复）。数据库集群可以恢复到最新状态或特定的时间点。
lt_dump 创建的导出是一致的；每个数据库的导出是 lt_dump 开始时数据库的一个快照。lt_dumpall 依次调用 lt_dump 对每个数据库进行操作，因此无法保证整个数据库集群的一致性。
lt_dump 在一次事务中转储数据库中的所有数据，发出许多 SELECT 命令。这个长时间运行的事务可能会：
阻塞其他需要强锁模式的操作，如 ALTER TABLE、TRUNCATE、CLUSTER、REINDEX。
导致表和索引膨胀，因为 VACUUM 无法移除死元组。
持续归档的 pg_start_backup() 和 pg_basebackup 在开始时执行一个检查点。用户可以选择检查点速度为“快”或“分散”。
归档恢复和崩溃恢复会清空未记录关系的内容。SQL 导出输出未记录表的内容。
lt_dumpall 的 --no-role-passwords 选项使用 pg_roles 而不是 pg_authid 来导出数据库角色。这允许在受限环境中（如 DBaaS）使用 lt_dumpall，因为在这些环境中用户不允许读取 pg_authid 以保护密码。恢复后的角色将具有 NULL 密码。
架构
拓扑结构
只能复制整个数据库集群。部分复制是不可能的。
一台主服务器可以向一台或多台备用服务器复制。
每个备用服务器只能从一个主服务器复制。
备用服务器可以将更改级联到其他备用服务器。
主服务器不知道备用服务器的位置。备用服务器通过 primary_conninfo 参数指定的主服务器连接。
例如：primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
主服务器和备用服务器的版本
不同的主要版本不能一起工作。
不同的小版本可以一起工作，因为磁盘格式相同，但是没有正式的支持。建议保持主服务器和备用服务器相同的小版本。
在小版本升级期间，最安全的做法是先更新备用服务器。
进程和数据流
在服务器启动时，备用服务器首先从归档中读取并应用 WAL，接着从 $LTDATA/lt_wal/ 中读取，然后启动一个 walreceiver，它连接到主服务器并从中流式传输 WAL。如果复制连接中断，它会在 5 秒间隔重复此周期，该间隔可以通过 wal_retrieve_retry_interval 配置。
主服务器在接受来自 walreceiver 的连接请求时启动 walsender。
walsender 读取并向 walreceiver 发送 WAL。
walreceiver 将流式传输的 WAL 写入并刷新到 $LTDATA/lt_wal/ 中，并通知启动进程。
单一的启动进程读取并应用 WAL。
walreceiver 定期通知 walsender 复制进度——它已经写入、刷新和应用了多少 WAL。
级联备用服务器也运行 walsenders 和 walreceiver。
复制用户
复制用户需要 REPLICATION 角色属性。
REPLICATION 允许用户读取所有用于复制的数据，但不能用于 SELECT 查询消费。
一般管理
备用服务器是只读的。任何对象，包括角色，都不能仅在备用服务器上创建。
max_wal_senders 应略高于备用服务器的数量，以便备用服务器在临时意外断开连接后仍能接受连接，而断开连接的 walsender 仍然存在。
可以在备用服务器上进行备份。
不需要设置 archive_timeout 来减少数据丢失窗口。
级联复制减少了主服务器上的负载。
主服务器上的 WAL
如果没有任何措施，主服务器不会关心备用服务器，并删除/回收备用服务器仍需要的旧 WAL 文件。
如果备用服务器请求已经被删除的 WAL，主服务器会发出类似 "ERROR: requested WAL segment 000000020000000300000041 has already been removed" 的消息。
为了让主服务器保留 WAL 文件，可以使用复制槽（首选方法）或设置 keep_wal_size（旧方法）。
max_slot_wal_keep_size 限制了由复制槽保留的 WAL 量。
同步复制
如果没有可用的同步备用服务器，事务将在提交期间挂起。
要恢复挂起的事务，移除 synchronous_standby_names 设置并重新加载配置。这会使复制变为异步。
复制延迟的原因
硬件配置：服务器、存储和网络
主服务器上的重负载：主服务器生成的WAL数量如此之大，以至于单一的启动进程无法跟上。
设置 wal_compression = on 以减少WAL的数量。
从慢速归档中检索WAL：备用服务器无法从主服务器获取WAL，因此必须从WAL归档中获取。
回放冲突：某些操作的回放可能被备用服务器上运行的查询所阻止。
这与使用热备用有关。
减少 max_standby_archive_delay 和 max_standby_streaming_delay 以取消冲突查询并尽早恢复WAL回放。
热备用
在服务器处于归档恢复或备用模式时运行只读查询的能力。
回放冲突
WAl回放与备用服务器上的查询之间的冲突。
要么延迟WAL回放，要么取消查询。
导致回放冲突的主服务器上的操作包括：
需要Access Exclusive锁的操作：DDL、LOCK、VACUUM（包括自动VACUUM）导致的文件截断。
Access Exclusive锁请求被WAL记录，并由备用服务器回放。
删除备用服务器上查询放置临时文件的表空间。
删除备用服务器上有客户端连接的数据库。
VACUUM清理备用事务根据其快照仍可见的死亡元组。
VACUUM清理备用事务有缓冲区锁定的页面（例如，游标位于页面上）。
回放冲突发生时的情况
WAL应用等待最多由max_standby_archive_delay和max_standby_streaming_delay指定的时间（除了DROP DATABASE和ALTER DATABASE SET TABLESPACE的回放）。
然后，在回放DROP DATABASE的情况下终止冲突会话，或者在其他情况下取消冲突查询。
如果一个空闲会话持有锁，则该会话也会被终止。
监控回放冲突
在备用服务器上的 pg_stat_database_conflicts 显示由于每种类型的回放冲突而取消的查询数量。
“log_recovery_conflict_waits = on” 记录消息，表明WAL应用已等待超过deadlock_timeout的时间并且等待结束。
LOG: recovery still waiting after 1.023 ms: recovery conflict on snapshot
DETAIL: Conflicting processes: 1234, 1235
LOG: recovery finished waiting after 3.234 ms: recovery conflict on snapshot
最小化因回放冲突而取消的查询数量
避免需要Access Exclusive锁的操作。例如，ALTER TABLE、VACUUM FULL、CLUSTER、REINDEX、TRUNCATE。
通过设置主服务器上的vacuum_truncate存储参数来禁用VACUUM的文件截断。
例如：ALTER TABLE some_table SET (vacuum_truncate = off);
在备用服务器上设置hot_standby_feedback = on。
发送最老的XID给主服务器，反映在pg_stat_replication.backend_xmin中，这是VACUUM决定移除一个死亡元组时考虑的因素。
可能会导致表膨胀，因为死亡元组的移除被延迟。
不能防止所有冲突。
调整备用服务器上的max_standby_streaming_delay/max_standby_archive_delay。
调整主服务器上的vacuum_defer_cleanup_age。
最理想的是拥有独立的备用服务器，一些用于高可用性，另一些则用于容忍陈旧数据的读取工作负载。
监控复制延迟
不仅在主服务器上可用，也在级联备用服务器上可用。
pg_current_wal_lsn 和视图的 sent_lsn 字段之间存在较大差异可能表明主服务器处于高负载下。
备用服务器上的 sent_lsn 和 pg_last_wal_receive_lsn 之间的差异可能表明网络延迟，或者备用服务器处于高负载下。
pg_last_wal_replay_lsn() 和视图的 flushed_lsn 之间存在较大差异表明WAL接收速度比能够回放的速度快。
例如，
SELECT pg_wal_lsn_diff(pg_last_wal_replay_lsn(), flushed_lsn) FROM pg_stat_wal_receiver;
存储写入延迟、IOPs（每秒输入输出操作数）和吞吐量以检查繁重的写活动。
架构
使用发布和订阅模型
发布是一组其更改将被复制的表。
订阅表示与发布者及其发布的连接。
一个发布者可以发布一个或多个发布。
一个订阅者可以有一个或多个订阅。
一个发布可以有多个订阅者。
一个订阅可以订阅多个发布。
发布可以选择限制它们产生的更改到任何组合的INSERT、UPDATE、DELETE和TRUNCATE。
发布可以限制要复制的行和列。
进程和数据流
参与的进程：发布者上的walsender，订阅者上的订阅工作者（应用工作者，表同步工作者）。
即使使用了一些与walreceiver相关的参数，walreceiver也不会出现。
在订阅者启动时，除非max_logical_replication_workers为0，否则会启动逻辑复制启动器。
逻辑复制启动器为每个启用的订阅启动一个应用工作者。
应用工作者连接到发布者。
应用工作者为尚未完成初始同步的表启动表同步工作者。这些表同步工作者各自连接到发布者。
发布者为来自订阅工作者的每个连接请求启动一个walsender。
表同步工作者的walsender向表同步工作者发送表的初始副本。（初始数据同步/复制）
walsender读取WAL，将更改解码成逻辑复制协议格式，并存储在逻辑解码工作内存中，可能还会存入文件。当事务提交时，walsender将其解码的更改发送给订阅工作者。
订阅工作者应用接收到的更改。
常规管理
主要限制
发布只能包含表。
数据定义语言(DDL)不会被复制。
首先在订阅者上添加表列，然后在发布者上添加。删除表列时则相反。
序列数据不会被复制。
复制身份
已发布的表必须具有副本身份才能复制UPDATE和DELETE操作。
作为键来识别订阅者上要更新或删除的行。
如果已发布的表没有副本身份，则UPDATE和DELETE在发布者上失败。INSERT成功。
可以是主键（默认）、唯一索引或完整行。
可以通过 ALTER TABLE REPLICA IDENTITY 来配置。
副本身份列的旧值会被WAL记录。
调整性能
max_sync_workers_per_subscription
根据max_sync_workers_per_subscription配置，多个表同步工作者（每个表一个）将并行运行。
当订阅中有许多表时，这可能有效地加快初始表同步速度。
复制冲突
由于约束违反或缺少权限，传入更改在订阅者上的应用可能会失败。这就是冲突。
解决冲突：
如果还没有禁用订阅，可以通过运行 "ALTER SUBSCRIPTION name DISABLE;" 来禁用它。订阅可以配置为在应用工作者检测到任何错误时自动禁用。运行 "ALTER SUBSCRIPTION ... WITH (disable_on_error = on);"
查看服务器日志中的冲突事务的复制源名称和结束LSN。
执行以下任一操作：
通过运行 "ALTER SUBSCRIPTION ... SKIP (冲突事务的结束LSN)" 或 "SELECT pg_replication_origin_advance(复 制源名称, 冲突事务结束后的下一个LSN)" 来跳过发布者的事务。
在订阅者上修复表数据。
通过运行 "ALTER SUBSCRIPTION name ENABLE;" 启用订阅。