Postgresql 中文操作指南

ALTER SUBSCRIPTION

ALTER SUBSCRIPTION ——更改订阅的定义

ALTER SUBSCRIPTION — change the definition of a subscription

Synopsis

ALTER SUBSCRIPTION name CONNECTION 'conninfo'
ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
ALTER SUBSCRIPTION name ENABLE
ALTER SUBSCRIPTION name DISABLE
ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
ALTER SUBSCRIPTION name SKIP ( skip_option = value )
ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SUBSCRIPTION name RENAME TO new_name

Description

ALTER SUBSCRIPTION 可以更改大多数可于 CREATE SUBSCRIPTION 中指定的订阅属性。

ALTER SUBSCRIPTION can change most of the subscription properties that can be specified in CREATE SUBSCRIPTION.

您必须拥有 ALTER SUBSCRIPTION 的所有权，才能使用它。要重命名订阅或更改所有者，您必须具有数据库的 CREATE 权限。另外，要更改所有者，您必须能够 SET ROLE 至新的所有者角色。如果订阅具有 password_required=false ，则只有超级用户才能修改它。

You must own the subscription to use ALTER SUBSCRIPTION. To rename a subscription or alter the owner, you must have CREATE permission on the database. In addition, to alter the owner, you must be able to SET ROLE to the new owning role. If the subscription has password_required=false, only superusers can modify it.

在刷新发布时，我们将移除不再是发布一部分的关系，并且如果存在任何表同步插槽，我们也将移除它们。移除这些插槽是必要的，以便释放为远程主机上的订阅分配的资源。如果由于网络中断或某些其他错误，PostgreSQL 无法移除插槽，它将报告一个错误。出现这种情况时，用户需要重试该操作或从订阅中取消关联插槽，并按照 DROP SUBSCRIPTION 中的说明删除订阅。

When refreshing a publication we remove the relations that are no longer part of the publication and we also remove the table synchronization slots if there are any. It is necessary to remove these slots so that the resources allocated for the subscription on the remote host are released. If due to network breakdown or some other error, PostgreSQL is unable to remove the slots, an error will be reported. To proceed in this situation, the user either needs to retry the operation or disassociate the slot from the subscription and drop the subscription as explained in DROP SUBSCRIPTION.

具有 refresh 选项的命令 ALTER SUBSCRIPTION …​ REFRESH PUBLICATION 和 ALTER SUBSCRIPTION …​ {SET|ADD|DROP} PUBLICATION …​ 不可以执行于事务块内。在订阅启用了 two_phase 提交时，这些命令也不能执行，除非 copy_data 为 false 。请参阅 pg_subscription 的列 subtwophasestate 以了解实际的两阶段状态。

Commands ALTER SUBSCRIPTION …​ REFRESH PUBLICATION and ALTER SUBSCRIPTION …​ {SET|ADD|DROP} PUBLICATION …​ with refresh option as true cannot be executed inside a transaction block. These commands also cannot be executed when the subscription has two_phase commit enabled, unless copy_data is false. See column subtwophasestate of pg_subscription to know the actual two-phase state.

Parameters

  • name

    • The name of a subscription whose properties are to be altered.

  • CONNECTION '_conninfo'_

    • This clause replaces the connection string originally set by CREATE SUBSCRIPTION. See there for more information.

  • SET PUBLICATION _publication_nameADD PUBLICATION _publication_name_DROP PUBLICATION _publication_name__

    • These forms change the list of subscribed publications. SET replaces the entire list of publications with a new list, ADD adds additional publications to the list of publications, and DROP removes the publications from the list of publications. We allow non-existent publications to be specified in ADD and SET variants so that users can add those later. See CREATE SUBSCRIPTION for more information. By default, this command will also act like REFRESH PUBLICATION.

    • publication_option specifies additional options for this operation. The supported options are:

    • Additionally, the options described under REFRESH PUBLICATION may be specified, to control the implicit refresh operation.

  • REFRESH PUBLICATION

    • Fetch missing table information from publisher. This will start replication of tables that were added to the subscribed-to publications since CREATE SUBSCRIPTION or the last invocation of REFRESH PUBLICATION.

    • refresh_option specifies additional options for the refresh operation. The supported options are:

  • ENABLE

    • Enables a previously disabled subscription, starting the logical replication worker at the end of the transaction.

  • DISABLE

    • Disables a running subscription, stopping the logical replication worker at the end of the transaction.

  • SET ( _subscription_parameter [= value] [, …​ ] )_

  • SKIP ( _skip_option = value )_

    • Skips applying all changes of the remote transaction. If incoming data violates any constraints, logical replication will stop until it is resolved. By using the ALTER SUBSCRIPTION …​ SKIP command, the logical replication worker skips all data modification changes within the transaction. This option has no effect on the transactions that are already prepared by enabling two_phase on the subscriber. After the logical replication worker successfully skips the transaction or finishes a transaction, the LSN (stored in pg_subscription.subskiplsn) is cleared. See Section 31.5 for the details of logical replication conflicts.

    • skip_option specifies options for this operation. The supported option is:

  • new_owner

    • The user name of the new owner of the subscription.

  • new_name

    • The new name for the subscription.

  • refresh (boolean)

    • When false, the command will not try to refresh table information. REFRESH PUBLICATION should then be executed separately. The default is true.

  • copy_data (boolean)

    • Specifies whether to copy pre-existing data in the publications that are being subscribed to when the replication starts. The default is true.

    • Previously subscribed tables are not copied, even if a table’s row filter WHERE clause has since been modified.

    • See Notes for details of how copy_data = true can interact with the origin parameter.

    • See the binary parameter of CREATE SUBSCRIPTION for details about copying pre-existing data in binary format.

  • lsn (pg_lsn)

    • Specifies the finish LSN of the remote transaction whose changes are to be skipped by the logical replication worker. The finish LSN is the LSN at which the transaction is either committed or prepared. Skipping individual subtransactions is not supported. Setting NONE resets the LSN.

在指定类型为 boolean 的参数时，可以省略 = value 部分，这等同于指定 TRUE 。

When specifying a parameter of type boolean, the = value part can be omitted, which is equivalent to specifying TRUE.

Examples

将订阅订阅的出版物更改为 insert_only :

Change the publication subscribed by a subscription to insert_only:

ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;

禁用（停止）订阅:

Disable (stop) the subscription:

ALTER SUBSCRIPTION mysub DISABLE;

Compatibility

ALTER SUBSCRIPTION 是一个 PostgreSQL 扩展。

ALTER SUBSCRIPTION is a PostgreSQL extension.

See Also

CREATE SUBSCRIPTION , DROP SUBSCRIPTION , CREATE PUBLICATION , ALTER PUBLICATION

CREATE SUBSCRIPTION, DROP SUBSCRIPTION, CREATE PUBLICATION, ALTER PUBLICATION