PostgreSQL逻辑复制搭建
复制作为一种高可用/数据同步方案，在每一种数据库中都有实现，可以借助复制功能实现数据库的高可用或者数据同步/备份方案。
复制的分类
整体上看，复制可以分为物理复制和逻辑复制，对于物理复制或者逻辑复制，没有所谓的优劣，只有各自的适应场景。
所谓的物理复制，也即复制数据库的redo物理日志，通过redo日志在从节点（或者目标端）回放该日志来实现数据的同步，比如SQLServer的镜像/AG可用性组，postgresql的流复制等都属于物理复制，MySQL没有物理复制
所谓的逻辑复制，通过把主节点上的sql语句传到从节点，同样通过回放来实现数据的一致性。
不同的是，物理复制相比逻辑复制效率较高，但是粒度较粗，往往是库级别（MSSQL）或者是实例级别（postgresql）的，逻辑复制相对来说效率较低，但是粒度可大可小，从库级别到行或者列级别都可以实现。
postgresql逻辑复制的原理
熟悉SQLServer复制的话，应该能快速理解postgresql逻辑复制的原理，甚至SQLServer和postgresql逻辑复制的“术语”都一样，发布（publication）&订阅（subscribtion），主节点上将一个或者多个表绑定到一个发布（publication），目标端通过订阅创建一个订阅subscription，来“对接”发布（publication），相当于源头创建一个出水管（publication），目的地通过一个接水管（subscription）来对接，实现数据的同步。
在熟悉SQLServer的复制（订阅发布）的情况下，再来看postgresql的逻辑复制几乎可以无缝衔接，两者的原理一致，发布端解析redo日志生成sql语句，然后将发布的sql传递到订阅端并执行来实现数据的同步。但是SQLServer中多了一个distrubution库，发布待传递的中间数据通过distrubution中转一次，而postgresql的发布直接由后台进程完成，说实话postgresq的逻辑复制要比SQLServer的复制搭建起来简单多了。
参考下图分别是SQLServer的发布订阅和postgresql的发布订阅原理图。
postgresql逻辑复制搭建步骤
发布端（主节点）创建发布publication
###主节点### ###复制用户 ###创建复制用户 --drop user app_replication; create user app_replication with password 'A-Strong-Password'; alter role app_replication with replication; ###给复制用户授权 grant usage on schema public to app_replication; --revoke usage on schema public from app_replication; ###这一点比较坑爹，grant usage on schema是未来在这个schema建表，有usage的权限，而对于已存在的表，仍没有权限，需要通过grant单独授权 ###更坑爹的是，如果没有单独grant授权，届时复制搭建起来之后是从节点没有权限的错误，而不是主节点报错 grant select on t1 to app_replication; grant select on t2 to app_replication; --revoke select on t1 from app_replication; --revoke select on t2 from app_replication; ###wal日志级别设置 ###确认wal_level为logical级别 select * from pg_settings where name = 'wal_level'; ###复制槽 ###创建逻辑复制槽，逻辑复制槽的作用就是记录标记当前发布与订阅日志发送的位置信息 select * from pg_create_logical_replication_slot('db01_logic_replication_slot01','pgoutput'); ###查看复制槽信息，active: t正在使用,f未使用 SELECT * FROM pg_replication_slots; ###删除逻辑复制槽 select pg_drop_replication_slot('db01_logic_replication_slot01'); ###创建发布（publication） ###创建发布master_db01_pulication并添加表 create publication master_db01_pulication for table t1 with (publish = 'insert,update'); ###添加表到发布对象master_db01_pulication alter publication master_db01_pulication add table t2; ###删除发布 drop publication master_db01_pulication; ###查看发布 select * from pg_catalog.pg_publication; ###查看发布包含的对象 select current_database(),pgtab.tablename,pgpub.pubname,pgpub.puballtables,pgpub.pubinsert,pgpub.pubupdate,pgpub.pubdelete,pgpub.pubtruncate from pg_publication as pgpub inner join pg_publication_tables as pgtab on pgpub.pubname = pgtab.pubname; ###发布对象的测试数据 insert into t1(c2,c3,c4,c5,c6) values ('aa','aa','aa','aa',now()); insert into t1(c2,c3,c4,c5,c6) values ('bb','bb','bb','bb',now()); insert into t1(c2,c3,c4,c5,c6) values ('cc','cc','cc','cc',now()); select * from t1 limit 100; select * from t2 limit 100;
订阅端（从节点）创建订阅subscription
### 订阅端创建表 postgresql的逻辑复制（发布订阅）不会传递DDL，所以表结构需要再订阅端创建好 CREATE TABLE public.t1 ( c1 serial4 NOT NULL, c2 varchar(100) NULL, c3 varchar(100) NULL, c4 varchar(100) NULL, c5 varchar(100) NULL, c6 timestamp(3) NULL, CONSTRAINT t1_pkey PRIMARY KEY (c1) ); CREATE TABLE public.t2 ( c1 serial4 NOT NULL, c2 varchar(100) NULL, c3 varchar(100) NULL, c4 varchar(100) NULL, c5 varchar(100) NULL, c6 timestamp(3) NULL, CONSTRAINT t2_pkey PRIMARY KEY (c1) ); ###订阅 ###创建订阅 create subscription slave_db01_subscription connection 'host=192.168.90.230 port=10000 dbname=db01 user=app_replication password=A-Strong-Password' publication master_db01_pulication with (create_slot = false,slot_name = db01_logic_replication_slot01,copy_data = true); ###查看订阅 select * from pg_subscription; -- 通过 pg_subscription 来监控当前数据库中创建的 subscription ###启动订阅 alter subscription slave_db01_subscription enable; ###删除订阅的步骤 --停止订阅 alter subscription slave_db01_subscription disable; alter subscription slave_db01_subscription SET (slot_name =NONE); drop subscription slave_db01_subscription; ###查看订阅数据同步 select * from t2 limit 10;
复制状态监控
发布端状态监控
###发布状态监控 ###查看发布包含的对象 select current_database(),pgtab.tablename,pgpub.pubname,pgpub.puballtables,pgpub.pubinsert,pgpub.pubupdate,pgpub.pubdelete,pgpub.pubtruncate from pg_publication as pgpub inner join pg_publication_tables as pgtab on pgpub.pubname = pgtab.pubname; ###发布槽信息 select * from pg_replication_slots; slot_name ： 一个唯一的、集簇范围内的复制槽标识符 plugin ： 包含这个逻辑槽正在使用的输出插件的共享对象基础名称，对于物理槽为空值。 slot_type： 槽类型 - physical或者logical datoid： 与这个槽相关的数据库的OID，或者为空值。只有逻辑槽具有相关的数据库。 database： 与这个槽相关的数据库的名称，或者为空值。只有逻辑槽具有相关的数据库。 temporary： 如果这是一个临时复制槽则为真。临时槽不会被保存在磁盘上并且会在出错或会话结束时自动被删除掉。 active 如果这个槽当前正在被使用则为真 active_pid 如果槽当前正在被使用，则记录使用这个槽的会话的进程 ID。如果不活动则为NULL。 xmin 这个槽要需要数据库保留的最旧事务。VACUUM不能移除被其后续事务删除的元组。 catalog_xmin 这个槽要需要数据库保留的影响系统目录的最旧事务。VACUUM不能移除被其后续事务删除的目录元组。 restart_lsn 可能仍被这个槽的消费者要求的最旧WAL地址（LSN），并且因此不会在检查点期间自动被移除。 如果这个槽的LSN从未被保留过，则为NULL。 confirmed_flush_lsn 代表逻辑槽的消费者已经确认接收数据到什么位置的地址（LSN）。 比这个地址更旧的数据已经不再可用。对于物理槽这里是NULL。 wal_status 此插槽定义WAL文件的可用性。 可能的值为: reserved 意味着声称的文件包含max_wal_size。 extended 意味着max_wal_size已超出，但文件仍保留，通过复制插槽或wal_keep_size。 unreserved 意味着该插槽不再保留所需的 WAL 文件，并且将在下一个检查点删除其中一些文件。 此状态可以返回到reserved或extended。 lost 意味着某些需要的 WAL 文件已被删除，并且此插槽不再可用。 最后两种状态仅在max_slot_wal_keep_size为非负值时才看到。 如果restart_lsn为 NULL，则此字段为空。 safe_wal_size 可写入 WAL 的字节数，以便此插槽不会处于"丢失"状态的危险中。 对丢失插槽它是NULL，以及如果max_slot_wal_keep_size是-1。 ###发布状态信息 select * from pg_stat_replication ; id 一个 WAL 发送进程的进程 ID usesysid 登录到这个 WAL 发送进程的用户的 OID usename 登录到这个 WAL 发送进程的用户的名称 application_name 连接到这个 WAL 发送进程的应用的名称 client_addr 连接到这个 WAL 发送进程的客户端的 IP 地址。 如果这个域为空，它表示该客户端通过服务器机器上的一个Unix 套接字连接。 client_hostname 连接上的客户端的主机名，由一次对client_addr的逆向 DNS 查找报告。 这个域将只对 IP 连接非空，并且只有在 log_hostname被启用时非空。 client_port 客户端用来与这个 WAL 发送进程通讯的 TCP 端口号，如果使用 Unix 套接字则为-1 backend_start 这个进程开始的时间，即客户端是何时连接到这个WAL 发送进程的。 backend_xmin 由hot_standby_feedback报告的这个后备机的xmin水平线。 state 当前的 WAL 发送进程状态。 可能的值是： startup: 这个WAL发送器正在启动。 catchup: 这个WAL发送者连接的备用服务器正在赶上主服务器。 streaming: 在其连接的备用服务器赶上主服务器之后，这个WAL发送方正在流化变化。 backup: 这个WAL发送器正在发送一个备份。 stopping: 这个WAL发送器正在停止。 sent_lsn 在这个连接上发送的最后一个预写式日志的位置 write_lsn 被这个后备服务器写入到磁盘的最后一个预写式日志的位置 flush_lsn 被这个后备服务器刷入到磁盘的最后一个预写式日志的位置 replay_lsn 被重放到这个后备服务器上的数据库中的最后一个预写式日志的位置 write_lag 从本地刷新近期的WAL与接收到此备用服务器已写入WAL的通知(但尚未刷新或应用它)之间的时间经过。 如果将此服务器配置为同步备用服务器，则可以使用此参数来衡量在提交时synchronous_commit级别remote_write所导致的延迟。 flush_lag 在本地刷写近期的WAL与接收到后备服务器已经写入并且刷写它（但还没有应用）的通知之间流逝的时间。 如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别on所导致的延迟。 replay_lag 在本地刷写近期的WAL与接收到后备服务器已经写入它、刷写它并且应用它的通知之间流逝的时间。 如果这台服务器被配置为一个同步后备，这可以用来计量在提交时synchronous_commit的级别remote_apply所导致的延迟。 sync_priority 在基于优先的同步复制中，这台后备服务器被选为同步后备的优先级。在基于规定数量的同步复制中，这个值没有效果。 sync_state 这一台后备服务器的同步状态。 可能的值是： async: 这台后备服务器是异步的。 potential: 这台后备服务器现在是异步的，但可能在当前的同步后备失效时变成同步的。 sync: 这台后备服务器是同步的。 quorum: 这台后备服务器被当做规定数量后备服务器的候选。 reply_time 带时区的时间戳从备用服务器收到的最后一条回复信息的发送时间
查看发布包含的对象以及属性
逻辑复制对应的复制槽信息
复制正常的话，state字段是streaming
订阅端复制状态监控
###订阅基础信息查看 SELECT * FROM pg_subscription; oid 行标识符 subdbid 订阅所在的数据库的OID subname 订阅的名称 subowner 订阅的拥有者 subenabled 如果为真，订阅被启用并且应该被复制。 subsynccommit 包含订阅工作者的synchronous_commit设置的值。 subconninfo 到上游数据库的连接字符串 subslotname 上游数据库中的复制槽的名称。也被用于本地复制源名称。 subpublications 被订阅的publication名称的数组。这些引用的是发布者服务器上的publication。 ###订阅以及对应的表 select s.*,c.relname from pg_subscription_rel s inner join pg_class c on s.srrelid = c.oid ; ###字段说明： srsubid (references pg_subscription.oid) 对订阅的引用 srrelid (references pg_class.oid)对关系的引用 srsubstate 状态代码： i = 初始化， d = 数据正在被拷贝， s = 已同步， r = 准备好（普通复制） srsublsn 在s或r状态中，用于同步协调的状态更改的远程 LSN，否则为空 ###订阅日志同步状态 select * from pg_stat_subscription; ###字段说明： subid 订阅的OID subname 订阅的名称 pid 订阅工作者进程的进程ID relid 工作器正在同步的关系的OID;Null用于主应用工作器 received_lsn 接收到的最后一个预写式日志位置，该字段的初始值为0 last_msg_send_time 从WAL发送器收到的最后一条信息的发送时间 last_msg_receipt_time 从WAL发送器收到的最后一条信息的接收时间 latest_end_lsn 向WAL发送器报告的最后预写式日志位置 latest_end_time 向WAL发送器报告的最后一次预写式日志位置的时间
查看订阅状态信息
查看订阅的表以及复制状态信息
查看复制状态
postgresql逻辑复制要吐槽的
逻辑复制本身的粒度很细，已经到表级别了，这一点是所有逻辑复制的共同点，包SQLServer和MySQL，但是postgresql逻辑复制中已有一个要吐槽的，就是一个“发布”publication的数据传递属性是不能基于表的。
怎么理解呢？包含了多张表的时候create publication master_db01_pulication for table t1,t2 with (publish = 'insert,update');其发布类型只能是一样的，比如master_db01_pulication 包含了2张表，如果想让t1表的增删改（insert/update/delete）都发布，t2表只发布增改(insert/update)，postgresql的逻辑发布做不到，但是在其他数据库是可以的。
曲线救国的办法就是做多个发布，将不同类型的日志传递动作的表，放在不同的发布中。缺点就是搞得主节点发布端的publication太多了。