A subscription is the downstream side of logical replication. The node where a subscription is defined is referred to as the subscriber. A subscription defines the connection to another database and set of publications (one or more) to which it wants to subscribe.

The subscriber database behaves in the same way as any other PostgreSQL instance and can be used as a publisher for other databases by defining its own publications.

A subscriber node may have multiple subscriptions if desired. It is possible to define multiple subscriptions between a single publisher-subscriber pair, in which case care must be taken to ensure that the subscribed publication objects don't overlap.

Each subscription will receive changes via one replication slot (see Section 27.2.6). Additional replication slots may be required for the initial data synchronization of pre-existing table data and those will be dropped at the end of data synchronization.

A logical replication subscription can be a standby for synchronous replication (see Section 27.2.8). The standby name is by default the subscription name. An alternative name can be specified as application_name in the connection information of the subscription.

Subscriptions are dumped by pg_dump if the current user is a superuser. Otherwise a warning is written and subscriptions are skipped, because non-superusers cannot read all subscription information from the pg_subscription catalog.

The subscription is added using CREATE SUBSCRIPTION and can be stopped/resumed at any time using the ALTER SUBSCRIPTION command and removed using DROP SUBSCRIPTION.

When a subscription is dropped and recreated, the synchronization information is lost. This means that the data has to be resynchronized afterwards.

The schema definitions are not replicated, and the published tables must exist on the subscriber. Only regular tables may be the target of replication. For example, you can't replicate to a view.

The tables are matched between the publisher and the subscriber using the fully qualified table name. Replication to differently-named tables on the subscriber is not supported.

Columns of a table are also matched by name. The order of columns in the subscriber table does not need to match that of the publisher. The data types of the columns do not need to match, as long as the text representation of the data can be converted to the target type. For example, you can replicate from a column of type integer to a column of type bigint. The target table can also have additional columns not provided by the published table. Any such columns will be filled with the default value as specified in the definition of the target table. However, logical replication in binary format is more restrictive. See the binary option of CREATE SUBSCRIPTION for details.

31.2.1. Replication Slot Management

As mentioned earlier, each (active) subscription receives changes from a replication slot on the remote (publishing) side.

Additional table synchronization slots are normally transient, created internally to perform initial table synchronization and dropped automatically when they are no longer needed. These table synchronization slots have generated names: “pg_%u_sync_%u_%llu” (parameters: Subscription oid, Table relid, system identifier sysid)

Normally, the remote replication slot is created automatically when the subscription is created using CREATE SUBSCRIPTION and it is dropped automatically when the subscription is dropped using DROP SUBSCRIPTION. In some situations, however, it can be useful or necessary to manipulate the subscription and the underlying replication slot separately. Here are some scenarios:

• When creating a subscription, the replication slot already exists. In that case, the subscription can be created using the create_slot = false option to associate with the existing slot.

• When creating a subscription, the remote host is not reachable or in an unclear state. In that case, the subscription can be created using the connect = false option. The remote host will then not be contacted at all. This is what pg_dump uses. The remote replication slot will then have to be created manually before the subscription can be activated.

• When dropping a subscription, the replication slot should be kept. This could be useful when the subscriber database is being moved to a different host and will be activated from there. In that case, disassociate the slot from the subscription using ALTER SUBSCRIPTION before attempting to drop the subscription.

• When dropping a subscription, the remote host is not reachable. In that case, disassociate the slot from the subscription using ALTER SUBSCRIPTION before attempting to drop the subscription. If the remote database instance no longer exists, no further action is then necessary. If, however, the remote database instance is just unreachable, the replication slot (and any still remaining table synchronization slots) should then be dropped manually; otherwise it/they would continue to reserve WAL and might eventually cause the disk to fill up. Such cases should be carefully investigated.

31.2.2. Examples: Set Up Logical Replication

Create some test tables on the publisher.

test_pub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
CREATE TABLE
test_pub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
CREATE TABLE
test_pub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
CREATE TABLE

Create the same tables on the subscriber.

test_sub=# CREATE TABLE t1(a int, b text, PRIMARY KEY(a));
CREATE TABLE
test_sub=# CREATE TABLE t2(c int, d text, PRIMARY KEY(c));
CREATE TABLE
test_sub=# CREATE TABLE t3(e int, f text, PRIMARY KEY(e));
CREATE TABLE

Insert data to the tables at the publisher side.

test_pub=# INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
INSERT 0 3
test_pub=# INSERT INTO t2 VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT 0 3
test_pub=# INSERT INTO t3 VALUES (1, 'i'), (2, 'ii'), (3, 'iii');
INSERT 0 3

Create publications for the tables. The publications pub2 and pub3a disallow some publish operations. The publication pub3b has a row filter (see Section 31.3).

test_pub=# CREATE PUBLICATION pub1 FOR TABLE t1;
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub2 FOR TABLE t2 WITH (publish = 'truncate');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub3a FOR TABLE t3 WITH (publish = 'truncate');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION pub3b FOR TABLE t3 WHERE (e > 5);
CREATE PUBLICATION

Create subscriptions for the publications. The subscription sub3 subscribes to both pub3a and pub3b. All subscriptions will copy initial data by default.

test_sub=# CREATE SUBSCRIPTION sub1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub1'
test_sub-# PUBLICATION pub1;
CREATE SUBSCRIPTION
test_sub=# CREATE SUBSCRIPTION sub2
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub2'
test_sub-# PUBLICATION pub2;
CREATE SUBSCRIPTION
test_sub=# CREATE SUBSCRIPTION sub3
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=sub3'
test_sub-# PUBLICATION pub3a, pub3b;
CREATE SUBSCRIPTION

Observe that initial table data is copied, regardless of the publish operation of the publication.

test_sub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
(3 rows)

test_sub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
(3 rows)

Furthermore, because the initial data copy ignores the publish operation, and because publication pub3a has no row filter, it means the copied table t3 contains all rows even when they do not match the row filter of publication pub3b.

test_sub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
(3 rows)

Insert more data to the tables at the publisher side.

test_pub=# INSERT INTO t1 VALUES (4, 'four'), (5, 'five'), (6, 'six');
INSERT 0 3
test_pub=# INSERT INTO t2 VALUES (4, 'D'), (5, 'E'), (6, 'F');
INSERT 0 3
test_pub=# INSERT INTO t3 VALUES (4, 'iv'), (5, 'v'), (6, 'vi');
INSERT 0 3

Now the publisher side data looks like:

test_pub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
 4 | four
 5 | five
 6 | six
(6 rows)

test_pub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
 4 | D
 5 | E
 6 | F
(6 rows)

test_pub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
 4 | iv
 5 | v
 6 | vi
(6 rows)

Observe that during normal replication the appropriate publish operations are used. This means publications pub2 and pub3a will not replicate the INSERT. Also, publication pub3b will only replicate data that matches the row filter of pub3b. Now the subscriber side data looks like:

test_sub=# SELECT * FROM t1;
 a |   b
---+-------
 1 | one
 2 | two
 3 | three
 4 | four
 5 | five
 6 | six
(6 rows)

test_sub=# SELECT * FROM t2;
 c | d
---+---
 1 | A
 2 | B
 3 | C
(3 rows)

test_sub=# SELECT * FROM t3;
 e |  f
---+-----
 1 | i
 2 | ii
 3 | iii
 6 | vi
(4 rows)

31.2.3. Examples: Deferred Replication Slot Creation

There are some cases (e.g. Section 31.2.1) where, if the remote replication slot was not created automatically, the user must create it manually before the subscription can be activated. The steps to create the slot and activate the subscription are shown in the following examples. These examples specify the standard logical decoding plugin (pgoutput), which is what the built-in logical replication uses.

First, create a publication for the examples to use.

test_pub=# CREATE PUBLICATION pub1 FOR ALL TABLES;
CREATE PUBLICATION

Example 1: Where the subscription says connect = false

• Create the subscription.

  Copy

  test_sub=# CREATE SUBSCRIPTION sub1
  test_sub-# CONNECTION 'host=localhost dbname=test_pub'
  test_sub-# PUBLICATION pub1
  test_sub-# WITH (connect=false);
  WARNING:  subscription was created, but is not connected
  HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
  CREATE SUBSCRIPTION

• On the publisher, manually create a slot. Because the name was not specified during CREATE SUBSCRIPTION, the name of the slot to create is same as the subscription name, e.g. "sub1".

  Copy

  test_pub=# SELECT * FROM pg_create_logical_replication_slot('sub1', 'pgoutput');
   slot_name |    lsn
  -----------+-----------
   sub1      | 0/19404D0
  (1 row)

• On the subscriber, complete the activation of the subscription. After this the tables of pub1 will start replicating.

  Copy

  test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
  ALTER SUBSCRIPTION
  test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
  ALTER SUBSCRIPTION

Example 2: Where the subscription says connect = false, but also specifies the slot_name option.

• Create the subscription.

  Copy

  test_sub=# CREATE SUBSCRIPTION sub1
  test_sub-# CONNECTION 'host=localhost dbname=test_pub'
  test_sub-# PUBLICATION pub1
  test_sub-# WITH (connect=false, slot_name='myslot');
  WARNING:  subscription was created, but is not connected
  HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and refresh the subscription.
  CREATE SUBSCRIPTION

• On the publisher, manually create a slot using the same name that was specified during CREATE SUBSCRIPTION, e.g. "myslot".

  Copy

  test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
   slot_name |    lsn
  -----------+-----------
   myslot    | 0/19059A0
  (1 row)

• On the subscriber, the remaining subscription activation steps are the same as before.

  Copy

  test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
  ALTER SUBSCRIPTION
  test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
  ALTER SUBSCRIPTION

Example 3: Where the subscription specifies slot_name = NONE

• Create the subscription. When slot_name = NONE then enabled = false, and create_slot = false are also needed.

  Copy

  test_sub=# CREATE SUBSCRIPTION sub1
  test_sub-# CONNECTION 'host=localhost dbname=test_pub'
  test_sub-# PUBLICATION pub1
  test_sub-# WITH (slot_name=NONE, enabled=false, create_slot=false);
  CREATE SUBSCRIPTION

• On the publisher, manually create a slot using any name, e.g. "myslot".

  Copy

  test_pub=# SELECT * FROM pg_create_logical_replication_slot('myslot', 'pgoutput');
   slot_name |    lsn
  -----------+-----------
   myslot    | 0/1905930
  (1 row)

• On the subscriber, associate the subscription with the slot name just created.

  Copy

  test_sub=# ALTER SUBSCRIPTION sub1 SET (slot_name='myslot');
  ALTER SUBSCRIPTION

• The remaining subscription activation steps are same as before.

  Copy

  test_sub=# ALTER SUBSCRIPTION sub1 ENABLE;
  ALTER SUBSCRIPTION
  test_sub=# ALTER SUBSCRIPTION sub1 REFRESH PUBLICATION;
  ALTER SUBSCRIPTION

可以在任何實體複寫主機上定義 PUBLICATION (發佈)。 定義 PUBLICATION 的節點稱為 Publisher (發佈者)。PUBLICATION 是從一個資料表或一組資料表中所産生的一組變更，也可能被描述為變更集合或複寫集合。每個發佈僅能存在於一個資料庫中。

發佈與綱要(Schema)不同，它並不會影響資料表的存取方式。如果需要，每個資料表可以加到多個發佈之中。 發佈目前可能只包含資料表。物件必須明確加入，除非指定選項為 ALL TABLES 建立發佈。

發佈可以選擇將其產生的變更限制為 INSERT、UPDATE 、 DELETE 及 TRUNCATE 或其任意組合，類似於特定事件類型觸發器的方式。預設情況下，所有操作類型都會被複寫。這些 PUBLICATION 規範僅適用於 DML 操作；它們不會影響已同步的資料副本。 （Row filter 不會對 TRUNCATE 產生影響。請參閱第 31.3 節）。

發佈的資料表必須配置「REPLICA IDENTITY （副本識別）」，以便能夠複寫 UPDATE 和 DELETE 操作，使其在訂閱端可以識別更新或刪除適當的資料列。預設情況下，如果有主鍵的話，就以主鍵識別。另一種情況是使用唯一索引（具有某些附加要求），也可以設定為副本識別。如果該資料表沒有任何合適的方式，則可以將副本識別設定為「full」，這意味著整個資料列都作為識別。但是，這樣做的效能是非常低的，只有在沒有其他解決方案可行的情況下才可以這樣使用。如果在發佈方設定了除「full」之外的副本識別，則還必須在訂閱戶設定包含相同或更少欄位的副本標別。有關如何設定副本識別的詳細訊息，請參閱 REPLICA IDENTITY。如果沒有副本識別的資料表被加到複寫 UPDATE 或 DELETE 操作的發佈中，則隨後的 UPDATE 或 DELETE 操作將導致發佈者出錯。不論副本識別如何設定，INSERT 操作都可以繼續進行。

每個發佈可以有多個訂閱者。

使用 CREATE PUBLICATION 指令建立發佈，稍後可以使用相應的命令變更或移除發佈。

可以使用 ALTER PUBLICATION 動態加入和移除單個資料表。ADD TABLE 和 DROP TABLE 操作都是交易安全的；所以一旦交易事務提交後，資料表就會啟動或停止複寫，並且維持正確的交易快照。

邏輯複寫的行為與正常的 DML 操作類似，因為即使在訂閱節點上變更了資料，資料也會更新。 如果傳入的資料違反任何限制條件，複寫將會停止。這被稱為衝突。當複寫 UPDATE 或 DELETE 操作時，失去的資料不會產生衝突，而這些操作將會簡單地跳過。

衝突會產生錯誤並停止複寫；它必須由使用者手動解決。有關衝突的詳細訊息可以在使用者的伺服器日誌中找到。

解決方案可以透過變更訂閱戶上的資料來完成，以避免與傳入變更衝突或跳過與現有資料衝突的交易事務。透過呼叫 pg_replication_origin_advance() 函數以及與訂閱名稱和位置相對應的 node_name，可以跳過該交易事務。可以在 pg_replication_origin_status 系統檢視表中看到開始的目前位置。

用於複寫連線的角色必須具有 REPLICATION 屬性（或者是超級使用者）。該角色的存取必須在 pg_hba.conf 中設定，並且必須具有 LOGIN 屬性。

為了能夠複製初始資料表資料，用於複寫連線的角色必須在發佈的資料表上具有 SELECT 權限（或者是超級使用者）。

要建立發佈，使用者必須在資料庫中具有 CREATE 權限。

要將資料表加到發佈中，使用者必須擁有該資料表的所有權。要建立自動發佈所有資料表的發佈，使用者必須是超級使用者。

要建立訂閱，使用者必須是超級使用者。

訂閱 apply 程序將以超級使用者的權限在本地資料庫中運行。

權限僅在複寫連線開始時檢查一次。由於每個變更記錄都是從發佈者處讀取的，因此不會重新檢查，也不會在套用每個變更時重新檢查。

由於邏輯複寫基於與相似的體系結構，因此發佈節點上的監控與監控物理式串流複寫主節點類似（請參閱）。

有關訂閱的監控訊息可以在 中找到。該檢視表對每個訂閱程序都有一個資料列。根據訂閱的狀態，訂閱可以有零個或多個活動訂閱程序。

一般而言，每一個啟用的訂閱會有單個 apply 程序運行。此檢視表中禁用的訂閱或當掉的訂閱將不會看到資料。如果任何資料表的初始資料同步正在進行，則會有額外的程序表示正在同步資料表。

邏輯複寫目前有以下限制或功能不足的地方。這些可能會在將來的版本中解決。

• 資料庫結構和 DDL 指令不會被複寫。初始模式可以使用 pg_dump --schema-only 手動複寫。後續的結構變更需要手動保持同步。（但是，請注意，兩側的結構沒有必要完全相同。）當主要資料庫中的結構定義變更時，邏輯複寫是沒問題的：當發佈者上的結構産生變更並且複寫的資料開始到達訂閱戶但不符合資料表結構，複寫將産生錯誤，直到結構更新。在許多情況下，可以透過先將預定的架構變更套用於訂閱戶來避免間歇性的錯誤。

• 序列資料不會被複寫。序列支援的序列或識別欄位中的資料當然會作為資料表的一部分進行複寫，但序列本身仍然會顯示訂閱戶的初始值。如果使用者用作唯讀資料庫，那麼這通常不成問題。但是，如果打算對訂閱戶資料庫進行某種切換或故障轉移，則需要將序列更新為最新值，方法是複寫發佈者的目前資料（可能使用 pg_dump）或設定對該資料表而言足夠高的值。

• 支援複寫 TRUNCATE 指令，但是在 TRUNCATE 由外部鍵連結的資料表群組時必須格外小心。當複寫 TRUNCATE 操作時，訂閱者將 TRUNCATE 在發佈者上被 TRUNCATE 的同一組資料表，這些資料表群組是明確指定的或透過 CASCADE 所收集的，再減去了不屬於發佈的資料表。如果所有受影響的資料表都屬於同一個發佈，則此方法將會正常運作。但是，如果某些要在訂閱者上被 TRUNCATE 的資料表具有指向不屬於同一（或任何）訂閱的資料表的外部鍵連結，則在訂閱伺服器上套用 TRUNCATE 操作將會失敗。

• Large objects（請參閱第 34 章）不會被複寫。除了將資料儲存在普通資料表中之外，沒有其他解決方法。

• 僅 TABLE（包括 PARTITION TABLE）支援複寫。 嘗試製寫其他類型的關連物件，例如檢視表 VIEW，具體化檢視圖 MATERIALIZED VIEW 或外部資料表 FOREIGN TABLE，將會導致錯誤。

• 在分割資料表之間複寫時，預設情況下，會實際複寫來自發佈者上的子資料表，因此發佈者上的分割區也必須作為有效目標資料表存在於訂閱者上。 （它們本身可以是子資料表，也可以進一步再分割，甚至可以是獨立的資料表。）PUBLICATION 還可以指定要使用分割父資料表的 identity 和 schema 來複寫變更，取代實際要複寫的各個子資料表（請參閱 CREATE PUBLICATION）。

首先在 postgresql.conf 中進行組態設定：

wal_level = logical

其他所需的設定的預設值已經足夠。

需要調整 pg_hba.conf 以允許複寫（這裡的內容取決於您想要用於連線的實際網路配置和使用者）：

host     all     repuser     0.0.0.0/0     md5

然後在發佈者的資料庫上：

CREATE PUBLICATION mypub FOR TABLE users, departments;

在訂閱者的資料庫上：

CREATE SUBSCRIPTION mysub CONNECTION 'dbname=foo host=bar user=repuser' PUBLICATION mypub;

以上將啟動複寫程序，此程序同步資料表 users 和 departments 的初始資料表內容，然後開始以增量變更複寫到這些資料表。

邏輯複寫透過複寫發佈者資料庫上的資料快照開始。一旦完成，發佈者的變化就會即時發送給訂閱者。訂閱者按照對發佈者進行提交的順序套用資料，以確保任何單個訂閱中的發佈的交易事務一致性。

邏輯複寫採用類似於實體的串流複寫的體系結構構建立（請參閱第 26.2.5 節）。 它由「walsender」和「apply」過程實施。walsender 程序啟動 WAL 的邏輯解碼（在第 48 章中介紹）並載入標準的邏輯解碼套件（pgoutput）。該套件將從 WAL 讀取的變更轉換為邏輯複寫協定（請參閱第 52.5 節），並根據發佈規範過濾資料。然後使用串流複寫協定將資料連續傳輸到 apply 程序，後者將資料映射到本地資料表並按照正確的事務順序套用收到的各個變更。

使用者資料庫上的應用程序始終在將 session_replication_role 設定為 replica 的情況下運行，這會對事件觸發器和限制條件會產生影響。

邏輯複寫 apply 程序當下只觸發資料列觸發器，而不觸發查詢語句觸發器。但是，初始資料表同步會像 COPY 指令一樣執行，因此會觸發 INSERT 的資料列和查詢語句觸發器。

31.5.1. 初始快照

現有訂閱資料表中的初始資料被快照並複寫到特殊型別的 apply 程序的平行程序中。此過程將建立自己的臨時複寫插槽並複寫現有資料。一旦複寫了現有資料，程該序便進入同步模式，透過使用標準邏輯複寫對初始資料複寫過程中發生的任何變更進行串流傳輸，確保該資料表與主應用程序處於同步狀態。同步完成後，資料表的複寫控制將回到正常複寫繼續進行的主應用程序。

邏輯複寫需要設定幾個系統組態選項。

在發佈端，必須將 wal_level 設定為 logical，並且必須將 max_replication_slots 設定為至少預計要連線的訂閱數量，並為資料表同步保留一些預留數量。然後 max_wal_senders 應至少設定為與 max_replication_slots 相同的數量再加上同時連線的實體副本數量。

用戶還需要設定 max_replication_slots。在這種情況下，它應至少設定為將加到訂閱伺服器的訂閱數。max_logical_replication_workers 必須至少設定為訂閱數量，再加上資料表同步的一些保留數。此外，可能需要調整 max_worker_processes 以滿足複寫程序，至少為（max_logical_replication_workers + 1）。請注意，某些延伸功能和平行查詢也需要 max_worker_processes 中的程序插槽。

By default, all data from all published tables will be replicated to the appropriate subscribers. The replicated data can be reduced by using a row filter. A user might choose to use row filters for behavioral, security or performance reasons. If a published table sets a row filter, a row is replicated only if its data satisfies the row filter expression. This allows a set of tables to be partially replicated. The row filter is defined per table. Use a WHERE clause after the table name for each published table that requires data to be filtered out. The WHERE clause must be enclosed by parentheses. See CREATE PUBLICATION for details.

31.3.1. Row Filter Rules

Row filters are applied before publishing the changes. If the row filter evaluates to false or NULL then the row is not replicated. The WHERE clause expression is evaluated with the same role used for the replication connection (i.e. the role specified in the CONNECTION clause of the CREATE SUBSCRIPTION). Row filters have no effect for TRUNCATE command.

31.3.2. Expression Restrictions

The WHERE clause allows only simple expressions. It cannot contain user-defined functions, operators, types, and collations, system column references or non-immutable built-in functions.

If a publication publishes UPDATE or DELETE operations, the row filter WHERE clause must contain only columns that are covered by the replica identity (see REPLICA IDENTITY). If a publication publishes only INSERT operations, the row filter WHERE clause can use any column.

31.3.3. UPDATE Transformations

Whenever an UPDATE is processed, the row filter expression is evaluated for both the old and new row (i.e. using the data before and after the update). If both evaluations are true, it replicates the UPDATE change. If both evaluations are false, it doesn't replicate the change. If only one of the old/new rows matches the row filter expression, the UPDATE is transformed to INSERT or DELETE, to avoid any data inconsistency. The row on the subscriber should reflect what is defined by the row filter expression on the publisher.

If the old row satisfies the row filter expression (it was sent to the subscriber) but the new row doesn't, then, from a data consistency perspective the old row should be removed from the subscriber. So the UPDATE is transformed into a DELETE.

If the old row doesn't satisfy the row filter expression (it wasn't sent to the subscriber) but the new row does, then, from a data consistency perspective the new row should be added to the subscriber. So the UPDATE is transformed into an INSERT.

Table 31.1 summarizes the applied transformations.

Table 31.1. UPDATE Transformation Summary

31.3.4. Partitioned Tables

If the publication contains a partitioned table, the publication parameter publish_via_partition_root determines which row filter is used. If publish_via_partition_root is true, the root partitioned table's row filter is used. Otherwise, if publish_via_partition_root is false (default), each partition's row filter is used.

31.3.5. Initial Data Synchronization

If the subscription requires copying pre-existing table data and a publication contains WHERE clauses, only data that satisfies the row filter expressions 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. See Section 31.3.6 for details.

Warning

Because initial data synchronization does not take into account the publish parameter when copying existing table data, some rows may be copied that would not be replicated using DML. Refer to Section 31.7.1, and see Section 31.2.2 for examples.

Note

If the subscriber is in a release prior to 15, copy pre-existing data doesn't use row filters even if they are defined in the publication. This is because old releases can only copy the entire table data.

31.3.6. Combining Multiple Row Filters

If the subscription has several publications in which the same table has been published with different row filters (for the same publish operation), those expressions get ORed together, so that rows satisfying any of the expressions will be replicated. This means all the other row filters for the same table become redundant if:

• One of the publications has no row filter.

• One of the publications was created using FOR ALL TABLES. This clause does not allow row filters.

• One of the publications was created using FOR TABLES IN SCHEMA and the table belongs to the referred schema. This clause does not allow row filters.

31.3.7. Examples

Create some tables to be used in the following examples.

test_pub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
CREATE TABLE
test_pub=# CREATE TABLE t2(d int, e int, f int, PRIMARY KEY(d));
CREATE TABLE
test_pub=# CREATE TABLE t3(g int, h int, i int, PRIMARY KEY(g));
CREATE TABLE

Create some publications. Publication p1 has one table (t1) and that table has a row filter. Publication p2 has two tables. Table t1 has no row filter, and table t2 has a row filter. Publication p3 has two tables, and both of them have a row filter.

test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 WHERE (a > 5 AND c = 'NSW');
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION p2 FOR TABLE t1, t2 WHERE (e = 99);
CREATE PUBLICATION
test_pub=# CREATE PUBLICATION p3 FOR TABLE t2 WHERE (d = 10), t3 WHERE (g = 10);
CREATE PUBLICATION

psql can be used to show the row filter expressions (if defined) for each publication.

test_pub=# \dRp+
                               Publication p1
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1" WHERE ((a > 5) AND (c = 'NSW'::text))

                               Publication p2
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1"
    "public.t2" WHERE (e = 99)

                               Publication p3
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t2" WHERE (d = 10)
    "public.t3" WHERE (g = 10)

psql can be used to show the row filter expressions (if defined) for each table. See that table t1 is a member of two publications, but has a row filter only in p1. See that table t2 is a member of two publications, and has a different row filter in each of them.

test_pub=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | text    |           | not null |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a, c)
Publications:
    "p1" WHERE ((a > 5) AND (c = 'NSW'::text))
    "p2"

test_pub=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 d      | integer |           | not null |
 e      | integer |           |          |
 f      | integer |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (d)
Publications:
    "p2" WHERE (e = 99)
    "p3" WHERE (d = 10)

test_pub=# \d t3
                 Table "public.t3"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 g      | integer |           | not null |
 h      | integer |           |          |
 i      | integer |           |          |
Indexes:
    "t3_pkey" PRIMARY KEY, btree (g)
Publications:
    "p3" WHERE (g = 10)

On the subscriber node, create a table t1 with the same definition as the one on the publisher, and also create the subscription s1 that subscribes to the publication p1.

test_sub=# CREATE TABLE t1(a int, b int, c text, PRIMARY KEY(a,c));
CREATE TABLE
test_sub=# CREATE SUBSCRIPTION s1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
test_sub-# PUBLICATION p1;
CREATE SUBSCRIPTION

Insert some rows. Only the rows satisfying the t1 WHERE clause of publication p1 are replicated.

test_pub=# INSERT INTO t1 VALUES (2, 102, 'NSW');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (3, 103, 'QLD');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (4, 104, 'VIC');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (5, 105, 'ACT');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (6, 106, 'NSW');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (7, 107, 'NT');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (8, 108, 'QLD');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES (9, 109, 'NSW');
INSERT 0 1

test_pub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 2 | 102 | NSW
 3 | 103 | QLD
 4 | 104 | VIC
 5 | 105 | ACT
 6 | 106 | NSW
 7 | 107 | NT
 8 | 108 | QLD
 9 | 109 | NSW
(8 rows)

test_sub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 6 | 106 | NSW
 9 | 109 | NSW
(2 rows)

Update some data, where the old and new row values both satisfy the t1 WHERE clause of publication p1. The UPDATE replicates the change as normal.

test_pub=# UPDATE t1 SET b = 999 WHERE a = 6;
UPDATE 1

test_pub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 2 | 102 | NSW
 3 | 103 | QLD
 4 | 104 | VIC
 5 | 105 | ACT
 7 | 107 | NT
 8 | 108 | QLD
 9 | 109 | NSW
 6 | 999 | NSW
(8 rows)

test_sub=# SELECT * FROM t1;
 a |  b  |  c
---+-----+-----
 9 | 109 | NSW
 6 | 999 | NSW
(2 rows)

Update some data, where the old row values did not satisfy the t1 WHERE clause of publication p1, but the new row values do satisfy it. The UPDATE is transformed into an INSERT and the change is replicated. See the new row on the subscriber.

test_pub=# UPDATE t1 SET a = 555 WHERE a = 2;
UPDATE 1

test_pub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   3 | 103 | QLD
   4 | 104 | VIC
   5 | 105 | ACT
   7 | 107 | NT
   8 | 108 | QLD
   9 | 109 | NSW
   6 | 999 | NSW
 555 | 102 | NSW
(8 rows)

test_sub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   9 | 109 | NSW
   6 | 999 | NSW
 555 | 102 | NSW
(3 rows)

Update some data, where the old row values satisfied the t1 WHERE clause of publication p1, but the new row values do not satisfy it. The UPDATE is transformed into a DELETE and the change is replicated. See that the row is removed from the subscriber.

test_pub=# UPDATE t1 SET c = 'VIC' WHERE a = 9;
UPDATE 1

test_pub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   3 | 103 | QLD
   4 | 104 | VIC
   5 | 105 | ACT
   7 | 107 | NT
   8 | 108 | QLD
   6 | 999 | NSW
 555 | 102 | NSW
   9 | 109 | VIC
(8 rows)

test_sub=# SELECT * FROM t1;
  a  |  b  |  c
-----+-----+-----
   6 | 999 | NSW
 555 | 102 | NSW
(2 rows)

The following examples show how the publication parameter publish_via_partition_root determines whether the row filter of the parent or child table will be used in the case of partitioned tables.

Create a partitioned table on the publisher.

test_pub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
CREATE TABLE
test_pub=# CREATE TABLE child PARTITION OF parent DEFAULT;
CREATE TABLE

Create the same tables on the subscriber.

test_sub=# CREATE TABLE parent(a int PRIMARY KEY) PARTITION BY RANGE(a);
CREATE TABLE
test_sub=# CREATE TABLE child PARTITION OF parent DEFAULT;
CREATE TABLE

Create a publication p4, and then subscribe to it. The publication parameter publish_via_partition_root is set as true. There are row filters defined on both the partitioned table (parent), and on the partition (child).

test_pub=# CREATE PUBLICATION p4 FOR TABLE parent WHERE (a < 5), child WHERE (a >= 5)
test_pub-# WITH (publish_via_partition_root=true);
CREATE PUBLICATION

test_sub=# CREATE SUBSCRIPTION s4
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s4'
test_sub-# PUBLICATION p4;
CREATE SUBSCRIPTION

Insert some values directly into the parent and child tables. They replicate using the row filter of parent (because publish_via_partition_root is true).

test_pub=# INSERT INTO parent VALUES (2), (4), (6);
INSERT 0 3
test_pub=# INSERT INTO child VALUES (3), (5), (7);
INSERT 0 3

test_pub=# SELECT * FROM parent ORDER BY a;
 a
---
 2
 3
 4
 5
 6
 7
(6 rows)

test_sub=# SELECT * FROM parent ORDER BY a;
 a
---
 2
 3
 4
(3 rows)

Repeat the same test, but with a different value for publish_via_partition_root. The publication parameter publish_via_partition_root is set as false. A row filter is defined on the partition (child).

test_pub=# DROP PUBLICATION p4;
DROP PUBLICATION
test_pub=# CREATE PUBLICATION p4 FOR TABLE parent, child WHERE (a >= 5)
test_pub-# WITH (publish_via_partition_root=false);
CREATE PUBLICATION

test_sub=# ALTER SUBSCRIPTION s4 REFRESH PUBLICATION;
ALTER SUBSCRIPTION

Do the inserts on the publisher same as before. They replicate using the row filter of child (because publish_via_partition_root is false).

test_pub=# TRUNCATE parent;
TRUNCATE TABLE
test_pub=# INSERT INTO parent VALUES (2), (4), (6);
INSERT 0 3
test_pub=# INSERT INTO child VALUES (3), (5), (7);
INSERT 0 3

test_pub=# SELECT * FROM parent ORDER BY a;
 a
---
 2
 3
 4
 5
 6
 7
(6 rows)

test_sub=# SELECT * FROM child ORDER BY a;
 a
---
 5
 6
 7
(3 rows)

Each publication can optionally specify which columns of each table are replicated to subscribers. The table on the subscriber side must have at least all the columns that are published. If no column list is specified, then all columns on the publisher are replicated. See CREATE PUBLICATION for details on the syntax.

The choice of columns can be based on behavioral or performance reasons. However, do not rely on this feature for security: a malicious subscriber is able to obtain data from columns that are not specifically published. If security is a consideration, protections can be applied at the publisher side.

If no column list is specified, any columns added later are automatically replicated. This means that having a column list which names all columns is not the same as having no column list at all.

A column list can contain only simple column references. The order of columns in the list is not preserved.

Specifying a column list when the publication also publishes FOR TABLES IN SCHEMA is not supported.

For partitioned tables, the publication parameter publish_via_partition_root determines which column list is used. If publish_via_partition_root is true, the root partitioned table's column list is used. Otherwise, if publish_via_partition_root is false (the default), each partition's column list is used.

If a publication publishes UPDATE or DELETE operations, any column list must include the table's replica identity columns (see REPLICA IDENTITY). If a publication publishes only INSERT operations, then the column list may omit replica identity columns.

Column lists have no effect for the TRUNCATE command.

During initial data synchronization, only the published columns are copied. However, if the subscriber is from a release prior to 15, then all the columns in the table are copied during initial data synchronization, ignoring any column lists.

Warning: Combining Column Lists from Multiple Publications

There's currently no support for subscriptions comprising several publications where the same table has been published with different column lists. CREATE SUBSCRIPTION disallows creating such subscriptions, but it is still possible to get into that situation by adding or altering column lists on the publication side after a subscription has been created.

This means changing the column lists of tables on publications that are already subscribed could lead to errors being thrown on the subscriber side.

If a subscription is affected by this problem, the only way to resume replication is to adjust one of the column lists on the publication side so that they all match; and then either recreate the subscription, or use ALTER SUBSCRIPTION ... DROP PUBLICATION to remove one of the offending publications and add it again.

31.4.1. Examples

Create a table t1 to be used in the following example.

test_pub=# CREATE TABLE t1(id int, a text, b text, c text, d text, e text, PRIMARY KEY(id));
CREATE TABLE

Create a publication p1. A column list is defined for table t1 to reduce the number of columns that will be replicated. Notice that the order of column names in the column list does not matter.

test_pub=# CREATE PUBLICATION p1 FOR TABLE t1 (id, b, a, d);
CREATE PUBLICATION

psql can be used to show the column lists (if defined) for each publication.

test_pub=# \dRp+
                               Publication p1
  Owner   | All tables | Inserts | Updates | Deletes | Truncates | Via root
----------+------------+---------+---------+---------+-----------+----------
 postgres | f          | t       | t       | t       | t         | f
Tables:
    "public.t1" (id, a, b, d)

psql can be used to show the column lists (if defined) for each table.

test_pub=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 a      | text    |           |          |
 b      | text    |           |          |
 c      | text    |           |          |
 d      | text    |           |          |
 e      | text    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (id)
Publications:
    "p1" (id, a, b, d)

On the subscriber node, create a table t1 which now only needs a subset of the columns that were on the publisher table t1, and also create the subscription s1 that subscribes to the publication p1.

test_sub=# CREATE TABLE t1(id int, b text, a text, d text, PRIMARY KEY(id));
CREATE TABLE
test_sub=# CREATE SUBSCRIPTION s1
test_sub-# CONNECTION 'host=localhost dbname=test_pub application_name=s1'
test_sub-# PUBLICATION p1;
CREATE SUBSCRIPTION

On the publisher node, insert some rows to table t1.

test_pub=# INSERT INTO t1 VALUES(1, 'a-1', 'b-1', 'c-1', 'd-1', 'e-1');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES(2, 'a-2', 'b-2', 'c-2', 'd-2', 'e-2');
INSERT 0 1
test_pub=# INSERT INTO t1 VALUES(3, 'a-3', 'b-3', 'c-3', 'd-3', 'e-3');
INSERT 0 1
test_pub=# SELECT * FROM t1 ORDER BY id;
 id |  a  |  b  |  c  |  d  |  e
----+-----+-----+-----+-----+-----
  1 | a-1 | b-1 | c-1 | d-1 | e-1
  2 | a-2 | b-2 | c-2 | d-2 | e-2
  3 | a-3 | b-3 | c-3 | d-3 | e-3
(3 rows)

Only data from the column list of publication p1 is replicated.

test_sub=# SELECT * FROM t1 ORDER BY id;
 id |  b  |  a  |  d
----+-----+-----+-----
  1 | b-1 | a-1 | d-1
  2 | b-2 | a-2 | d-2
  3 | b-3 | a-3 | d-3
(3 rows)

邏輯複寫（Logical Replication）是一種依據複寫指標（通常是主鍵）複製資料物件及其更新的方法。我們使用術語邏輯與物理複寫相對比，物理複寫使用確切的區塊位址進行每一個字元組的複寫。PostgreSQL 同時支持這兩種機制，請參閱第 27 章。邏輯複寫允許對資料複寫和安全性進行更精細的控制。

邏輯複寫使用 publish（發佈）和 subscribe（訂閱）模式，其中一個或多個訂閱者 subscribe 發布者節點上的一個或多個 publish。 訂閱戶從他們訂閱的 publish 中提取資料，並可能隨後重新發布資料以允許串聯複寫或更複雜的複寫架構。

資料表的邏輯複寫通常始於對發佈者資料庫上的資料進行快照並將其複寫到訂閱伺服器。一旦完成，發佈者的變化就會即時發送給訂閱者。訂閱者按照與發佈者相同的順序變動資料，以確保單個訂閱內的發佈的交易事務一致性。這種資料複寫方法有時被稱為交易事務複寫。

典型的邏輯複寫情況有：

• 在訂閱者時常向單個資料庫或資料庫的子集發送增量變更時。

• 當訂閱者收到個別的變更時能觸發事件。

• 將多個資料庫合併成一個資料庫（例如為了分析的需求）。

• 在不同的 PostgreSQL 版本之間複寫。

• 在不同平台（例如 Linux 到 Windows）上的 PostgreSQL 伺服器之間的複寫

• 將複寫的資料給予不同的使用者群組存取權限。

• 在多個資料庫之間共享資料庫的一部份。

訂閱戶資料庫的行為與任何其他 PostgreSQL 的行為相同，並且可以透過定義其自己的發佈來用作其他資料庫的發佈者。當訂閱者被應用程序視為唯讀時，單個訂閱就不會發生衝突。另一方面，如果應用程序或其他使用者對同一組資料表執行了其他的寫入操作，則可能會出現衝突。