PostgreSQL逻辑复制使用记录

物理复制的好处
1. 物理层面完全一致，这是许多商业数据库的惯用手段。例如Oracle的DG。2. 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据。不论事务多大，都一样。3. 物理复制的一致性、可靠性达到了金融级的需求，不必担心数据逻辑层面不一致。但是物理复制要求主备块级完全一致，所以有一些无法覆盖的应用场景，例如备库不仅要只读，还要可写。又比如备库不需要完全和主库一致，只需要复制部分数据，或者备库要从多个数据源复制数据，等等。物理复制无法覆盖的场景
1. 数据库实例的部分，例如单个数据库或者某些表的复制需求。例如某个游戏业务，账号体系是一套数据库，如果全国各地有多个接入点，全部都连到中心数据库进行认证可能不太科学。那么就希望将登陆需要用到的一些数据表同步到多个数据中心，而不是整个数据库实例。2. 数据到达subcriber后，针对不同数据，设置触发器。3. 将多个数据库实例的数据，同步到一个目标数据库。例如多个数据库同步到一个大的数据仓库。4. 在不同的数据库版本之间，复制数据5. 将一个数据库实例的不同数据，复制到不同的目标库。例如省级数据库的数据，按地区划分，分别复制到不同的地区。6. 在多个数据库实例之间，共享部分数据。例如某个业务按用户ID哈希，拆分成了8个数据库，但是有些小的维度表，需要在多个数据库之间共享。以上场景是物理复制无法覆盖的。

逻辑复制概念
PostgreSQL 逻辑复制是事务级别的复制，引入了几个概念publication - 发布者
发布者指数据上游节点，你需要将哪些表发布出去？上游节点需要配置这些东西1. 需要将数据库的REDO的wal_level配置为logical。2. 需要发布逻辑复制的表，必须配置表的REPLICA IDENTITY，即如何标示老的记录。被复制的表，建议有PK约束。alter table table_name
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
解释REPLICA IDENTITY This form changes the information which is written to the write-ahead log to identify rows which are updated or deleted. This option has no effect except when logical replication is in use. 记录PK列的
1. DEFAULT (the default for non-system tables) records the old values of the columns of the primary key, if any. 记录指定索引列（索引的所有列须是not null列，其实和PK一样，但是某些情况下，你可以选一个比PK更小的UK）
2. USING INDEX records the old values of the columns covered by the named index, which must be unique, not partial, not deferrable, and include only columns marked NOT NULL. 记录完整记录
3. FULL records the old values of all columns in the row. 啥也不记录，这样做是否不支持update, delete?
user_catalog_table=true或者系统表，默认为replica identity nothing啥也不记录。如果这种表发布出去了，允许insert，但是执行delete或者update时，会报错。
4. NOTHING records no information about the old row (This is the default for system tables.) 仅仅当数据有变更时才会记录old value，比如delete。 或者update前后old.*<>new.*。
In all cases, no old values are logged unless at least one of the columns that would be logged differs between the old and new versions of the row.
什么是system table?发布语法
创建发布Command: CREATE PUBLICATION
Description: define a new publication
Syntax:
CREATE PUBLICATION name
[ FOR TABLE table_name [, ...]
| FOR ALL TABLES ]
[ WITH ( option [, ... ] ) ] where option can be: PUBLISH INSERT | NOPUBLISH INSERT
| PUBLISH UPDATE | NOPUBLISH UPDATE
| PUBLISH DELETE | NOPUBLISH DELETE 默认发布insert,update,delete。
修改发布Command: ALTER PUBLICATION
Description: change the definition of a publication
Syntax:
ALTER PUBLICATION name WITH ( option [, ... ] ) where option can be: PUBLISH INSERT | NOPUBLISH INSERT
| PUBLISH UPDATE | NOPUBLISH UPDATE
| PUBLISH DELETE | NOPUBLISH DELETE ALTER PUBLICATION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION name ADD TABLE table_name [, ...]
ALTER PUBLICATION name SET TABLE table_name [, ...]
ALTER PUBLICATION name DROP TABLE table_name [, ...]
发布者小结
1. 目前仅仅支持发布表，不允许发布其他对象。2. 同一张表，可以发布多次。3. 在同一个数据库中，可以创建多个publication，但是不能重名，通过系统表查看已创建的publicationpostgres=# \d pg_publication
Table "pg_catalog.pg_publication"
Column | Type | Collation | Nullable | Default
--------------+---------+-----------+----------+---------
pubname | name | | not null |
pubowner | oid | | not null |
puballtables | boolean | | not null |
pubinsert | boolean | | not null |
pubupdate | boolean | | not null |
pubdelete | boolean | | not null |
Indexes:
"pg_publication_oid_index" UNIQUE, btree (oid)
"pg_publication_pubname_index" UNIQUE, btree (pubname)
4. 允许使用all tables发布所有表。5. 一个publication允许有多个订阅者。6. 目前publication仅支持insert, update, delete。7. 允许发布时，选择发布insert、update、delete，比如只发布insert，而不发布update, delete。8. 当发布了表的update, delete时，表必须设置replica identity，即如何标示OLD TUPLE，通过pk或者uk或者full。如果设置了nothing，则执行update,delete时会报错alter table table_name
REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }
报错例子postgres=# delete from c;
ERROR: cannot delete from table "c" because it does not have replica identity and publishes deletes
HINT: To enable deleting from the table, set REPLICA IDENTITY using ALTER TABLE.
9. create publication或者alter publication，发布或者修改发布内容中添加或者删除表时，都是事务级别，不会出现复制了部分事务的情况。 so the table will start or stop replicating at the correct snapshot once the transaction has committed。10. 发布者需要设置wal_level=logical，同时开启足够的worker，设置足够大的replication slot，设置足够多的sender。因为每一个订阅，都要消耗掉一个replication slot，需要消耗一个wal sender，一个worker进程。发布者的pg_hba.conf需要设置replication条目，允许订阅者连接。发布者的数据库中，必须有replication角色的用户，或者超级用户，并且订阅者要使用它通过流复制协议连接到发布者。subscription - 订阅者
订阅者，需要指定发布者的连接信息，以及 publication name，同时指定需要在publication数据库中创建的slot name。在同一个数据库中，可以创建多个订阅。订阅者和发布者的角色可以同时出现在同一个实例的同一个数据库中。订阅语法
创建订阅Command: CREATE SUBSCRIPTION
Description: define a new subscription
Syntax:
CREATE SUBSCRIPTION subscription_name CONNECTION 'conninfo' PUBLICATION { publication_name [, ...] } [ WITH ( option [, ... ] ) ] where option can be: | ENABLED | DISABLED
| CREATE SLOT | NOCREATE SLOT
| SLOT NAME = slot_name
修改订阅Command: ALTER SUBSCRIPTION
Description: change the definition of a subscription
Syntax:
ALTER SUBSCRIPTION name WITH ( option [, ... ] ) ] where option can be: SLOT NAME = slot_name ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
订阅者小结
1. 订阅者需要通过流复制协议连接到发布者，同时需要在发布者创建replication slot。因此发布者的pg_hba.conf中需要配置相应的replication条目，允许订阅者通过流复制协议连接。同时连接发布者的用户，必须具备replication权限，或者具备超级用户权限。2. 同一个数据库中，可以创建多个subscription，这些subscription可以连自一个或多个发布者。3. 当同一个数据库中有多个subscription时，如果这些subscriptions是来自同一个发布者，那么他们之间发布的表不能重叠。也就是说，订阅者的同一张表，不能接受来自同一个源的多个发布。例如发布者create table public.a (id int primary key, info text); create publication pub1 for table a; create publication pub2 for table a;
订阅者A表接受了同一个源的多次发布，会报错。create table public.a (id int primary key, info text); create subscription sub1 connection 'hostaddr=127.0.0.1 port=1922 user=postgres dbname=postgres' publication pub1; create subscription sub2 connection 'hostaddr=127.0.0.1 port=1922 user=postgres dbname=postgres' publication pub2;
4. 每一个订阅，都需要在发布端创建一个slot，可以使用slot name = ?指定，或者默认为subscription name。即使是同一个发布端，只要订阅了多次，就需要创建多个SLOT，因为slot中记录了同步的LSN信息。例如create table public.a (id int primary key, info text);
create table public.b (id int primary key, info text); create publication pub1 for table a; create publication pub2 for table b;
订阅者create table public.a (id int primary key, info text);
create table public.b (id int primary key, info text); create subscription sub1 connection 'hostaddr=127.0.0.1 port=1922 user=postgres dbname=postgres' publication pub1; create subscription sub2 connection 'hostaddr=127.0.0.1 port=1922 user=postgres dbname=postgres' publication pub2;
这种情况，对于这个订阅者，建议合并成一个，例如create subscription sub1 connection 'hostaddr=127.0.0.1 port=1922 user=postgres dbname=postgres' publication pub1, pub2;
5. pg_dump导出数据库逻辑数据时，默认不会导出subscription的定义，除非使用选项 --include-subscriptions6. 在创建subscription或者alter subscription时，可以使用enable来启用该订阅，或者使用disable暂停该订阅。7. 如果要完全删除订阅，使用drop subscription，注意，删除订阅后，本地的表不会被删除，数据也不会清除，仅仅是不在接收该订阅的上游信息。这个也很好理解，因为同一个表可能接收多个订阅。删订阅和删表是两码事。8. 删除订阅后，如果要重新使用该订阅，数据需要resync，比如订阅的上游节点有100万数据，resync会将这100万数据同步过来。随后进入增量同步。将来10.0正式发布时，也许会提供一个选项，选择要不要resync。(目前来说，一次订阅，意味着这些被订阅的表会和发布端一模一样（只要发布端发布了insert,update,delete语句）。如果发布端只发布了insert，那么源表的update和delete不会被订阅)9. 订阅时，不会自动创建发布端的表，所以表需要在订阅端先创建好。将来10.0正式发布时，也许会填补这个功能。目前发布端和订阅端的表定义必须完全一致，包括schema，表名必须一致。字段名和字段类型必须一致。字段顺序可以不一致。除了表，其他对象都不能被订阅，例如你不能将表订阅到一张视图中。10. 必须使用超级用户创建订阅逻辑复制的冲突
逻辑复制，本质上是事务层级的复制，需要在订阅端执行SQL。如果订阅端执行SQL失败（或者说引发了任何错误，包括约束等），都会导致该订阅暂停。注意，update, delete没有匹配的记录时，不会报错，也不会导致订阅暂停。用户可以在订阅端数据库日志中查看错误原因。冲突修复方法
1. 通过修改订阅端的数据，解决冲突。例如insert违反了唯一约束时，可以删除订阅端造成唯一约束冲突的记录先DELETE掉。然后使用ALTER SUBSCRIPTION name ENABLE让订阅继续。2. 在订阅端调用pg_replication_origin_advance(node_name text, pos pg_lsn)函数，node_name就是subscription name，pos指重新开始的LSN，从而跳过有冲突的事务。pg_replication_origin_advance(node_name text, pos pg_lsn) Set replication progress for the given node to the given position. This primarily is useful for setting up the initial position or a new position after configuration changes and similar. Be aware that careless use of this function can lead to inconsistently replicated data.
当前的lsn通过pg_replication_origin_status.remote_lsn查看。

postgres=# select * from pg_publication;
pubname | pubowner | puballtables | pubinsert | pubupdate | pubdelete
---------+----------+--------------+-----------+-----------+-----------
pub2 | 10 | f | t | t | t
pub3 | 10 | f | t | t | t
pub_all | 10 | t | t | t | t
(3 rows)postgres=# select * from pg_stat_replication;
pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state
------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+------------+------------+------------+------------+-----------+-----------+------------+---------------+------------
3737 | 10 | postgres | sub3_second | 10.9.5.35 | | 58979 | 2018-11-19 15:23:32.930924+08 | | streaming | 0/D60433C0 | 0/D60433C0 | 0/D60433C0 | 0/D60433C0 | | | | 0 | async
3733 | 10 | postgres | sub3 | 10.9.5.35 | | 58976 | 2018-11-19 15:23:00.189641+08 | | streaming | 0/D60433C0 | 0/D60433C0 | 0/D60433C0 | 0/D60433C0 | | | | 0 | async
3611 | 10 | postgres | sub2 | 10.9.5.35 | | 58969 | 2018-11-19 14:22:52.20122+08 | | streaming | 0/D60433C0 | 0/D60433C0 | 0/D60433C0 | 0/D60433C0 | | | | 0 | async
(3 rows)

postgres=# select * from pg_stat_subscription;
subid | subname | pid | relid | received_lsn | last_msg_send_time | last_msg_receipt_time | latest_end_lsn | latest_end_time
-------+-------------+-------+-------+--------------+-------------------------------+-------------------------------+----------------+-------------------------------
16396 | sub1 | 12442 | | 0/2701C1D8 | 2018-11-19 14:15:08.351034+08 | 2018-11-19 14:15:08.498873+08 | 0/2701C1D8 | 2018-11-19 14:15:08.351034+08
16408 | sub2 | 12469 | | 0/D60434A0 | 2018-11-19 15:28:29.458445+08 | 2018-11-19 15:28:29.463565+08 | 0/D60434A0 | 2018-11-19 15:28:29.458445+08
16415 | sub3 | 12588 | | 0/D60434A0 | 2018-11-19 15:28:29.434858+08 | 2018-11-19 15:28:29.440074+08 | 0/D60434A0 | 2018-11-19 15:28:29.434858+08
16416 | sub3_second | 12590 | | 0/D60434A0 | 2018-11-19 15:28:29.485905+08 | 2018-11-19 15:28:29.491036+08 | 0/D60434A0 | 2018-11-19 15:28:29.485905+08
(4 rows)

alter subscription sub1 refresh publication;

发生冲突人为手动解决，删除或者修改冲突数据，忽略该冲突通过 node_name=订阅者名称 和一个 position 调用pg_replication_origin_advance()函数忽略冲突
pg_replication_origin_advance(node_name text, lsn pg_lsn)
1
查看当前数据的位置
postgres=# select * from pg_replication_origin_status;
local_id | external_id | remote_lsn | local_lsn
----------+-------------+------------+-----------
1 | pg_24606 | 0/0 | 0/0
2 | pg_24608 | 0/0 | 0/0
3 | pg_24622 | 1/8D00B470 | 0/0
4 | pg_24629 | 1/F80070D0 | 2/C01EED8
(4 rows)