PostgreSQL 15 preview - 逻辑复制支持row filters

刚看到PostgreSQL15 新增加功能－Allow specifying row filters for logical replication of tables，就第一时间体验一把。之前版本的逻辑复制针对的都是整个表的复制，某些情况下，对于一个极大的表，可能只关心部分数据，这时候row filters功能就显得特别有用。

发布端准备环境

psql (15devel)
Type 'help' for help.

mydb=# select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 15devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)

mydb=# show wal_level ;　　　/* 确保wal_level为logical*/
 wal_level
-----------
 logical
(1 row)

mydb=# show port;
 port
------
 5557
(1 row)

mydb=# create table test_row_filters(id serial primary key, name text, address text);    /*测试表创建*/
CREATE TABLE

mydb=# create publication row_filters_pub for table test_row_filters WHERE (address = 'beijing') WITH ( PUBLISH  = 'insert, update, delete, truncate');         /*创建发布，并且只发布address = 'beijing'的数据*/
CREATE PUBLICATION

mydb=# \d+ test_row_filters
                                                        Table "public.test_row_filters"
 Column  |  Type   | Collation | Nullable |                   Default                    | Storage  | Compression | Stats target | Description
---------+---------+-----------+----------+----------------------------------------------+----------+-------------+--------------+-------------
 id      | integer |           | not null | nextval('test_row_filters_id_seq'::regclass) | plain    |             |              |
 name    | text    |           |          |                                              | extended |             |              |
 address | text    |           |          |                                              | extended |             |              |
Indexes:
    "test_row_filters_pkey" PRIMARY KEY, btree (id)
Publications:
    "row_filters_pub" WHERE (address = 'beijing'::text)
Access method: heap

订阅端准备环境

postgres=# select version();
                                            version
------------------------------------------------------------------------------------------------
 PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-10ubuntu2) 9.3.0, 64-bit
(1 row)

mydb=# create table test_row_filters(id serial primary key, name text, address text);    /*订阅端测试表创建*/
CREATE TABLE

postgres=# create subscription row_filter_sub CONNECTION 'host=localhost port=5557 dbname=mydb' PUBLICATION row_filters_pub;/*创建订阅*/
NOTICE:  created replication slot 'row_filter_sub' on publisher
CREATE SUBSCRIPTION

发布端插入数据

mydb=# select * from test_row_filters;
 id | name | address
----+------+---------
(0 rows)

mydb=# insert into test_row_filters(name, address) values('tking', 'bijie');
INSERT 0 1
mydb=# insert into test_row_filters(name, address) values('tking', 'beijing');
INSERT 0 1
mydb=#
mydb=# insert into test_row_filters(name, address) values('tking', 'guiyang');
INSERT 0 1
mydb=#
mydb=# select * from test_row_filters;
 id | name  | address
----+-------+---------
  1 | tking | bijie
  2 | tking | beijing
  3 | tking | guiyang
(3 rows)

订阅端查看数据

postgres=# select * from test_row_filters;
 id | name  | address
----+-------+---------
  2 | tking | beijing
(1 row)

总结

从测试中可以看出，PostgreSQL订阅端的数据满足 address = ‘beijing’条件，逻辑复制功能越来越强大。

参考

提交日志如下：

commit 52e4f0cd472d39d07732b99559989ea3b615be78 (HEAD -> master, origin/master, origin/HEAD)
Author: Amit Kapila <akapila@postgresql.org>
Date:   Tue Feb 22 07:54:12 2022 +0530

    Allow specifying row filters for logical replication of tables.

    This feature adds row filtering for publication tables. When a publication
    is defined or modified, an optional WHERE clause can be specified. Rows
    that don't satisfy this WHERE clause will be filtered out. This allows a
    set of tables to be partially replicated. The row filter is per table. A
    new row filter can be added simply by specifying a WHERE clause after the
    table name. The WHERE clause must be enclosed by parentheses.

    The row filter WHERE clause for a table added to a publication that
    publishes UPDATE and/or DELETE operations must contain only columns that
    are covered by REPLICA IDENTITY. The row filter WHERE clause for a table
    added to a publication that publishes INSERT can use any column. If the
    row filter evaluates to NULL, it is regarded as 'false'. The WHERE clause
    only allows simple expressions that don't have user-defined functions,
    user-defined operators, user-defined types, user-defined collations,
    non-immutable built-in functions, or references to system columns. These
    restrictions could be addressed in the future.

    If you choose to do the initial table synchronization, only data that
    satisfies the row filters is copied to the subscriber. If the subscription
    has several publications in which a table has been published with
    different WHERE clauses, rows that satisfy ANY of the expressions will be
    copied. If a subscriber is a pre-15 version, the initial table
    synchronization won't use row filters even if they are defined in the
    publisher.

    The row filters are applied before publishing the changes. If the
    subscription has several publications in which the same table has been
    published with different filters (for the same publish operation), those
    expressions get OR'ed together so that rows satisfying any of the
    expressions will be replicated.

    This means all the other filters become redundant if (a) one of the
    publications have no filter at all, (b) one of the publications was
    created using FOR ALL TABLES, (c) one of the publications was created
    using FOR ALL TABLES IN SCHEMA and the table belongs to that same schema.

    If your publication contains a partitioned table, the publication
    parameter publish_via_partition_root determines if it uses the partition's
    row filter (if the parameter is false, the default) or the root
    partitioned table's row filter.

    Psql commands \dRp+ and \d <table-name> will display any row filters.

    Author: Hou Zhijie, Euler Taveira, Peter Smith, Ajin Cherian
    Reviewed-by: Greg Nancarrow, Haiying Tang, Amit Kapila, Tomas Vondra, Dilip Kumar, Vignesh C, Alvaro Herrera, Andres Freund, Wei Wang
    Discussion: https://www.postgresql.org/message-id/flat/CAHE3wggb715X%2BmK_DitLXF25B%3DjE6xyNCH4YOwM860JR7HarGQ%40mail.gmail.com