PostgreSQL 10.0 逻辑复制原理与最佳实践本文章来自于阿里云云栖社区摘要：标签 PostgreSQL , logical replication , 逻辑复制 , 最佳实践 背景 PostgreSQL 从2010年发布的9.0开始支持流式物理复制，备库可以作为只读库打开，提供给用户使用。标签PostgreSQL , logical replication , 逻辑复制 , 最佳实践背景PostgreSQL 从2010年发布的9.0开始支持流式物理复制，备库可以作为只读库打开，提供给用户使用。物理复制的好处1. 物理层面完全一致，这是许多商业数据库的惯用手段。例如Oracle的DG。2. 延迟低，事务执行过程中产生REDO record，实时的在备库apply，事务结束时，备库立马能见到数据。不论事务多大，都一样。3. 物理复制的一致性、可靠性达到了金融级的需求，不必担心数据逻辑层面不一致。但是物理复制要求主备块级完全一致，所以有一些无法覆盖的应用场景，例如备库不仅要只读，还要可写。又比如备库不需要完全和主库一致，只需要复制部分数据，或者备库要从多个数据源复制数据，等等。物理复制无法覆盖的场景1. 数据库实例的部分，例如单个数据库或者某些表的复制需求。例如某个游戏业务，账号体系是一套数据库，如果全国各地有多个接入点，全部都连到中心数据库进行认证可能不太科学。那么就希望将登陆需要用到的一些数据表同步到多个数据中心，而不是整个数据库实例。2. 数据到达subcriber后，针对不同数据，设置触发器。3. 将多个数据库实例的数据，同步到一个目标数据库。例如多个数据库同步到一个大的数据仓库。4. 在不同的数据库版本之间，复制数据5. 将一个数据库实例的不同数据，复制到不同的目标库。例如省级数据库的数据，按地区划分，分别复制到不同的地区。6. 在多个数据库实例之间，共享部分数据。例如某个业务按用户ID哈希，拆分成了8个数据库，但是有些小的维度表，需要在多个数据库之间共享。以上场景是物理复制无法覆盖的。逻辑复制应运而生，实际上，从2014年发布的9.4版本开始，PostgreSQL就支持逻辑复制了，只是一直没有将其引入内核。2017年即将发布的10.0，将会在内核层面支持基于REDO流的逻辑复制。另一个好消息是，你可以针对同一个数据库实例，同时使用逻辑复制和物理复制，因为他们都是基于REDO的。下面我们来看一下逻辑复制的概念、架构、监控、安全、最佳实践。逻辑复制概念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?指catalog或者user_catalog_table = true的表，不允许修改列的数据类型。postgres=# d+ c Table public.c Column | Type | Modifiers | Storage | Stats target | Description -+-+-+-+-+- id | integer | | plain | | info | text | | extended | | c | text | | extended | | Replica Identity: FULL Options: user_catalog_table=true postgres=# alter table c alter column c type int8; ERROR: column c cannot be cast automatically to type bigint HINT: You might need to specify USING c:bigint. 解释create table table_name () with (user_catalog_table =true); user_catalog_table (boolean) Declare the table as an additional catalog table for purposes of logical replication. See Section 48.6.2, “Capabilities” for details. This parameter cannot be set for TOAST tables. To decode, format and output changes, output plugins can use most of the backends normal infrastructure, including calling output functions. Read only access to relations is permitted as long as only relations are accessed that either have been created by initdb in the pg_catalog schema, or have been marked as user provided catalog tables using ALTER TABLE user_catalog_table SET (user_catalog_table = true); CREATE TABLE another_catalog_table(data text) WITH (user_catalog_table = true); Any actions leading to transaction ID assignment are prohibited. That, among others, includes writing to tables, performing DDL changes, and calling txid_current(). 3. output plugin发布者还需要一个output plugin，将redo按发布的定义，解析成需要的格式，等待订阅者的订阅。https:/www.postgresql.org/docs/devel/static/logicaldecoding-output-plugin.html是不是有点像这个呢？PostgreSQL 闪回 - flash back query emulate by trigger（原文链接：https:/github.com/digoal/blog/blob/master/201408/20140828_01.md?spm=5176.100239.blogcont71128.21.HW4nWO&file=20140828_01.md）发布语法创建发布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。 修改发布C