PostgreSQL 9.4 add logical replication command and parameters

PostgreSQL 9.4逻辑复制详解

最新推荐文章于 2025-09-09 08:37:13 发布

weixin_34175509

最新推荐文章于 2025-09-09 08:37:13 发布

阅读量149

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 git

原文链接：https://yq.aliyun.com/articles/14640

PostgreSQL 9.4引入了逻辑复制的基础，包括新的wal_level选项logical，用于支持逻辑解码。更新包括对ALTER TABLE命令的增强，支持REPLICA IDENTITY设置，以确定更新或删除操作中涉及的行标识。这些更改对于实现逻辑复制至关重要。

逻辑复制已经讲了蛮久, 以前拿未提交的补丁测试过一下.

http://blog.163.com/digoal@126/blog/static/163877040201362693010810/

现在终于看到9.4提交的一些信息了, 从提交的信息来看, 基础基本上打好了. 看样子逻辑复制很快就要实现了.

Add new wal_level, logical, sufficient for logical decoding.

When wal_level=logical, we'll log columns from the old tuple as
configured by the REPLICA IDENTITY facility added in commit
07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65.  This makes it possible
a properly-configured logical replication solution to correctly
follow table updates even if they change the chosen key columns,
or, with REPLICA IDENTITY FULL, even if the table has no key at
all.  Note that updates which do not modify the replica identity
column won't log anything extra, making the choice of a good key
(i.e. one that will rarely be changed) important to performance
when wal_level=logical is configured.

Each insert, update, or delete to a catalog table will also log
the CMIN and/or CMAX values of stamped by the current transaction.
This is necessary because logical decoding will require access to
historical snapshots of the catalog in order to decode some data
types, and the CMIN/CMAX values that we may need in order to judge
row visibility may have been overwritten by the time we need them.

Andres Freund, reviewed in various versions by myself, Heikki
Linnakangas, KONDO Mitsumasa, and many others.

其中包含一些变更 :

alter table 语法中和逻辑复制相关的部分, REPLICA IDENTITY, 设置如何定位到变更或删除的数据行. 可以设置为主键, FULL(表示所有字段), 或者非空的唯一索引. 例如设置为主键的话, 那么XLOG中记录OLD值就只记录主键的OLD值. FULL的话是记录所有的字段的OLD值. 值影响logical级别的wal.

http://www.postgresql.org/docs/devel/static/sql-altertable.html

    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. DEFAULT records the old values of the columns of the primary key, if any. 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. FULL records the old values of all columns in the row. NOTHING records no information about the old row. 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.

参数和逻辑复制相关的部分, wal_level新增了logical级别, 是目前的最高级别, 包含逻辑复制需要的额外xlog信息.

http://www.postgresql.org/docs/devel/static/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS

wal_level (enum)

wal_level determines how much information is written to the WAL. The default value is minimal, which writes only the information needed to recover from a crash or immediate shutdown. archive adds logging required for WAL archiving; hot_standby further adds information required to run read-only queries on a standby server; and, finally logical adds information necessary to support logical decoding. Each level includes the information logged at all lower levels. This parameter can only be set at server start.

In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include:

CREATE TABLE AS

CREATE INDEX

CLUSTER

COPY into tables that were created or truncated in the same transaction

But minimal WAL does not contain enough information to reconstruct the data from a base backup and the WAL logs, so archive or higher must be used to enable WAL archiving (archive_mode) and streaming replication.

In hot_standby level, the same information is logged as with archive, plus information needed to reconstruct the status of running transactions from the WAL. To enable read-only queries on a standby server, wal_level must be set to hot_standby or higher on the primary, and hot_standby must be enabled in the standby. It is thought that there is little measurable difference in performance between using hot_standby and archive levels, so feedback is welcome if any production impacts are noticeable.

In logical level, the same information is logged as with hot_standby, plus information needed to allow extracting logical changesets from the WAL. Using a level of logical will increase the WAL volume, particularly if many tables are configured for REPLICA IDENTITY FULL and many UPDATE and DELETE statements are executed.

pg_class.relreplident

relreplident

char

Columns used to form "replica identity" for rows: d = default (primary key, if any), n = nothing, f = all columns i = index with indisreplident set, or default

测试部分 :

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/test/regress/expected/replica_identity.out;h=60b866ad7ed35ec3b34c167c2634b2aa243eba6f;hb=07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65

[参考]

1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e55704d8b2fe522fbc9435acbb5bc59033478bd5

2. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=60dd40bbda92cb1818d5a2651b9d2ba49afd804c

3. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65