{"Entry":{"collection":"sql","key":"alter-subscription","name":"ALTER SUBSCRIPTION","aliases":["altersubscription"],"metadata":{"aliases":["altersubscription"],"changed_in":["14","15","19"],"changes":[{"from":"9.6","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":[],"removed":[]},"status":"added","synopsis":null,"to":"10"},{"from":"13","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["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 OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }"],"removed":["ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( set_publication_option [= value] [, ... ] ) ]"]},"to":"14"},{"from":"14","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":{"added":["ALTER SUBSCRIPTION name SKIP ( skip_option = value )","ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }"],"removed":[]},"to":"15"},{"from":"15","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"16"},{"from":"16","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"17"},{"from":"17","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["description","parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"18"},{"from":"18","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":{"added":["ALTER SUBSCRIPTION name SERVER server_name","ALTER SUBSCRIPTION name REFRESH SEQUENCES","ALTER SUBSCRIPTION name ENABLE"],"removed":[]},"to":"19"},{"from":"19","purpose_changed":false,"renamed":null,"sections":{"added":[],"changed":["parameters"],"removed":[]},"status":"changed","synopsis":null,"to":"20"}],"content_hash":"f96ff4d61baaaa2cc5f4413e0e88e0f91de26b514178413ea8fb447d377ef846","editorial":{},"first_version":"10","group":"replication","imported_at":"2026-09-30T17:43:38.960514+08:00","last_version":"20","name":"ALTER SUBSCRIPTION","object":"SUBSCRIPTION","position":10004,"present_in":["10","11","12","13","14","15","16","17","18","19","20"],"purpose":"change the definition of a subscription","purpose_zh":"","related":["create-subscription","drop-subscription","create-publication","alter-publication"],"slug":"alter-subscription","source_rev":"a709ab85","synopsis":"ALTER SUBSCRIPTION name SERVER server_name\nALTER SUBSCRIPTION name CONNECTION 'conninfo'\nALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name REFRESH SEQUENCES\nALTER SUBSCRIPTION name ENABLE\nALTER SUBSCRIPTION name DISABLE\nALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )\nALTER SUBSCRIPTION name SKIP ( skip_option = value )\nALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER SUBSCRIPTION name RENAME TO new_name","verb":"ALTER"}},"Definition":{"Collection":"sql","Key":"alter-subscription","SourceDatabase":"center","Version":"18","SourceTable":"sqlcmd","SourceKey":"alter-subscription","SourceRevision":"a709ab85","Facts":{"anchor":"SQL-ALTERSUBSCRIPTION","file":"sql-altersubscription.html","lang":"en","name":"ALTER SUBSCRIPTION","purpose":"change the definition of a subscription","purpose_zh":"","related":["create-subscription","drop-subscription","create-publication","alter-publication"],"sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION\u003c/code\u003e can change most of the subscription properties that can be specified in \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eYou must own the subscription to use \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION\u003c/code\u003e. To rename a subscription or alter the owner, you must have \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e permission on the database. In addition, to alter the owner, you must be able to \u003ccode class=\"literal\"\u003eSET ROLE\u003c/code\u003e to the new owning role. If the subscription has \u003ccode class=\"literal\"\u003epassword_required=false\u003c/code\u003e, only superusers can modify it.\u003c/p\u003e\u003cp\u003eWhen 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, \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 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 \u003ca href=\"/docs/18/sql-dropsubscription.html\" title=\"DROP SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e.\u003c/p\u003e\u003cp\u003eCommands \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... REFRESH PUBLICATION\u003c/code\u003e, \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...\u003c/code\u003e with \u003ccode class=\"literal\"\u003erefresh\u003c/code\u003e option as \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e, \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... SET (failover = true|false)\u003c/code\u003e and \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... SET (two_phase = false)\u003c/code\u003e cannot be executed inside a transaction block.\u003c/p\u003e\u003cp\u003eCommands \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... REFRESH PUBLICATION\u003c/code\u003e and \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...\u003c/code\u003e with \u003ccode class=\"literal\"\u003erefresh\u003c/code\u003e option as \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e also cannot be executed when the subscription has \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e commit enabled, unless \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-COPY-DATA\"\u003e\u003ccode class=\"literal\"\u003ecopy_data\u003c/code\u003e\u003c/a\u003e is \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e. See column \u003ccode class=\"structfield\"\u003esubtwophasestate\u003c/code\u003e of \u003ca href=\"/docs/18/catalog-pg-subscription.html\" title=\"52.54. pg_subscription\"\u003e\u003ccode class=\"structname\"\u003epg_subscription\u003c/code\u003e\u003c/a\u003e to know the actual two-phase state.\u003c/p\u003e","key":"description","title":"Description"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe name of a subscription whose properties are to be altered.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCONNECTION '\u003cem class=\"replaceable\"\u003e\u003ccode\u003econninfo\u003c/code\u003e\u003c/em\u003e'\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause replaces the connection string originally set by \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e. See there for more information.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eADD PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDROP PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThese forms change the list of subscribed publications. \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e replaces the entire list of publications with a new list, \u003ccode class=\"literal\"\u003eADD\u003c/code\u003e adds additional publications to the list of publications, and \u003ccode class=\"literal\"\u003eDROP\u003c/code\u003e removes the publications from the list of publications. We allow non-existent publications to be specified in \u003ccode class=\"literal\"\u003eADD\u003c/code\u003e and \u003ccode class=\"literal\"\u003eSET\u003c/code\u003e variants so that users can add those later. See \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e for more information. By default, this command will also act like \u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e.\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e specifies additional options for this operation. The supported options are:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003erefresh\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eWhen false, the command will not try to refresh table information. \u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e should then be executed separately. The default is \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eAdditionally, the options described under \u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e may be specified, to control the implicit refresh operation.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eFetch missing table information from publisher. This will start replication of tables that were added to the subscribed-to publications since \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003ccode class=\"command\"\u003eCREATE SUBSCRIPTION\u003c/code\u003e\u003c/a\u003e or the last invocation of \u003ccode class=\"command\"\u003eREFRESH PUBLICATION\u003c/code\u003e.\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003erefresh_option\u003c/code\u003e\u003c/em\u003e specifies additional options for the refresh operation. The supported options are:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ecopy_data\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies whether to copy pre-existing data in the publications that are being subscribed to when the replication starts. The default is \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e.\u003c/p\u003e\u003cp\u003ePreviously subscribed tables are not copied, even if a table's row filter \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e clause has since been modified.\u003c/p\u003e\u003cp\u003eSee \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES\" title=\"Notes\"\u003eNotes\u003c/a\u003e for details of how \u003ccode class=\"literal\"\u003ecopy_data = true\u003c/code\u003e can interact with the \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN\"\u003e\u003ccode class=\"literal\"\u003eorigin\u003c/code\u003e\u003c/a\u003e parameter.\u003c/p\u003e\u003cp\u003eSee the \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY\"\u003e\u003ccode class=\"literal\"\u003ebinary\u003c/code\u003e\u003c/a\u003e parameter of \u003ccode class=\"command\"\u003eCREATE SUBSCRIPTION\u003c/code\u003e for details about copying pre-existing data in binary format.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eENABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eEnables a previously disabled subscription, starting the logical replication worker at the end of the transaction.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDISABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eDisables a running subscription, stopping the logical replication worker at the end of the transaction.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscription_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThis clause alters parameters originally set by \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e. See there for more information. The parameters that can be altered are \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME\"\u003e\u003ccode class=\"literal\"\u003eslot_name\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SYNCHRONOUS-COMMIT\"\u003e\u003ccode class=\"literal\"\u003esynchronous_commit\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY\"\u003e\u003ccode class=\"literal\"\u003ebinary\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-STREAMING\"\u003e\u003ccode class=\"literal\"\u003estreaming\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR\"\u003e\u003ccode class=\"literal\"\u003edisable_on_error\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-PASSWORD-REQUIRED\"\u003e\u003ccode class=\"literal\"\u003epassword_required\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-RUN-AS-OWNER\"\u003e\u003ccode class=\"literal\"\u003erun_as_owner\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN\"\u003e\u003ccode class=\"literal\"\u003eorigin\u003c/code\u003e\u003c/a\u003e, \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e, and \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e. Only a superuser can set \u003ccode class=\"literal\"\u003epassword_required = false\u003c/code\u003e.\u003c/p\u003e\u003cp\u003eWhen altering the \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME\"\u003e\u003ccode class=\"literal\"\u003eslot_name\u003c/code\u003e\u003c/a\u003e, the \u003ccode class=\"literal\"\u003efailover\u003c/code\u003e and \u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e property values of the named slot may differ from the counterpart \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e and \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e parameters specified in the subscription. When creating the slot, ensure the slot properties \u003ccode class=\"literal\"\u003efailover\u003c/code\u003e and \u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e match their counterpart parameters of the subscription. Otherwise, the slot on the publisher may behave differently from what these subscription options say: for example, the slot on the publisher could either be synced to the standbys even when the subscription's \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e option is disabled or could be disabled for sync even when the subscription's \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e option is enabled.\u003c/p\u003e\u003cp\u003eThe \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e and \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e parameters can only be altered when the subscription is disabled.\u003c/p\u003e\u003cp\u003eWhen altering \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e from \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e to \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e, the backend process reports an error if any prepared transactions done by the logical replication worker (from when \u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e parameter was still \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e) are found. You can resolve prepared transactions on the publisher node, or manually roll them back on the subscriber, and then try again. The transactions prepared by logical replication worker corresponding to a particular subscription have the following pattern: \u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003epg_gid_%u_%u\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e (parameters: subscription \u003cem class=\"parameter\"\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e, remote transaction id \u003cem class=\"parameter\"\u003e\u003ccode\u003exid\u003c/code\u003e\u003c/em\u003e). To resolve such transactions manually, you need to roll back all the prepared transactions with corresponding subscription IDs in their names. Applications can check \u003ca href=\"/docs/18/view-pg-prepared-xacts.html\" title=\"53.17. pg_prepared_xacts\"\u003e\u003ccode class=\"structname\"\u003epg_prepared_xacts\u003c/code\u003e\u003c/a\u003e to find the required prepared transactions. After the \u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e option is changed from \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e to \u003ccode class=\"literal\"\u003efalse\u003c/code\u003e, the publisher will replicate the transactions again when they are committed.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSKIP ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e = \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSkips applying all changes of the remote transaction. If incoming data violates any constraints, logical replication will stop until it is resolved. By using the \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... SKIP\u003c/code\u003e 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 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e on the subscriber. After the logical replication worker successfully skips the transaction or finishes a transaction, the LSN (stored in \u003ccode class=\"structname\"\u003epg_subscription\u003c/code\u003e.\u003ccode class=\"structfield\"\u003esubskiplsn\u003c/code\u003e) is cleared. See \u003ca href=\"/docs/18/logical-replication-conflicts.html\" title=\"29.7. Conflicts\"\u003eSection 29.7\u003c/a\u003e for the details of logical replication conflicts.\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e specifies options for this operation. The supported option is:\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003elsn\u003c/code\u003e (\u003ccode class=\"type\"\u003epg_lsn\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eSpecifies 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 \u003ccode class=\"literal\"\u003eNONE\u003c/code\u003e resets the LSN.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe user name of the new owner of the subscription.\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003eThe new name for the subscription.\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003eWhen specifying a parameter of type \u003ccode class=\"type\"\u003eboolean\u003c/code\u003e, the \u003ccode class=\"literal\"\u003e=\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e part can be omitted, which is equivalent to specifying \u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e.\u003c/p\u003e","key":"parameters","title":"Parameters"},{"html":"\u003cp\u003eChange the publication subscribed by a subscription to \u003ccode class=\"literal\"\u003einsert_only\u003c/code\u003e:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;\n\u003c/pre\u003e\u003cp\u003eDisable (stop) the subscription:\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER SUBSCRIPTION mysub DISABLE;\n\u003c/pre\u003e","key":"examples","title":"Examples"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION\u003c/code\u003e is a \u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e extension.\u003c/p\u003e","key":"compatibility","title":"Compatibility"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-subscription/?v=18\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-subscription/?v=18\" title=\"DROP SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-publication/?v=18\" title=\"CREATE PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/alter-publication/?v=18\" title=\"ALTER PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PUBLICATION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"See Also"}],"sections_same_as":"","slug":"18","synopsis_html":"ALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e CONNECTION '\u003cem class=\"replaceable\"\u003e\u003ccode\u003econninfo\u003c/code\u003e\u003c/em\u003e'\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e SET PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e [, ...] [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ADD PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e [, ...] [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e DROP PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e [, ...] [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e REFRESH PUBLICATION [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003erefresh_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ENABLE\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e DISABLE\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e SET ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscription_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e SKIP ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e = \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e )\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e OWNER TO { \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e RENAME TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e","synopsis_text":"ALTER SUBSCRIPTION name CONNECTION 'conninfo'\nALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ENABLE\nALTER SUBSCRIPTION name DISABLE\nALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )\nALTER SUBSCRIPTION name SKIP ( skip_option = value )\nALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER SUBSCRIPTION name RENAME TO new_name"},"ManualEvidence":{},"MeasuredEvidence":{}},"Text":{"Collection":"sql","Key":"alter-subscription","SourceDatabase":"pgweb","Version":"18","Locale":"zh-Hans","Title":"ALTER SUBSCRIPTION","Summary":"修改订阅的定义","BodyHTML":"\u003cpre\u003eALTER SUBSCRIPTION name CONNECTION \u0026#39;conninfo\u0026#39;\nALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ENABLE\nALTER SUBSCRIPTION name DISABLE\nALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )\nALTER SUBSCRIPTION name SKIP ( skip_option = value )\nALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER SUBSCRIPTION name RENAME TO new_name\u003c/pre\u003e\u003csection\u003e\u003ch2\u003e描述\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eALTER SUBSCRIPTION\u003c/code\u003e可以更改大多数可在\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e中指定的订阅属性。\u003c/p\u003e\u003cp\u003e要使用\u003ccode\u003eALTER SUBSCRIPTION\u003c/code\u003e，你必须拥有该订阅。要重命名订阅或更改其所有者，你必须在数据库上具有 \u003ccode\u003eCREATE\u003c/code\u003e权限。此外，要更改所有者，你必须能够对新的拥有角色执行 \u003ccode\u003eSET ROLE\u003c/code\u003e。如果该订阅具有 \u003ccode\u003epassword_required=false\u003c/code\u003e，则只有超级用户可以修改它。\u003c/p\u003e\u003cp\u003e刷新发布时，会移除不再属于该发布的关系；如果存在表同步槽，也会一并删除。必须删除这些槽，才能释放远端主机上为该订阅分配的资源。如果由于网络故障或其他错误，\u003cspan\u003ePostgreSQL\u003c/span\u003e无法删除这些槽，则会报告错误。在这种情况下，如需继续，用户要么重试该操作，要么按\u003ca href=\"/docs/18/sql-dropsubscription.html\" title=\"DROP SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e中的说明将该槽与订阅解除关联并删除该订阅。\u003c/p\u003e\u003cp\u003e命令\u003ccode\u003eALTER SUBSCRIPTION ... REFRESH PUBLICATION\u003c/code\u003e、带有\u003ccode\u003erefresh\u003c/code\u003e = \u003ccode\u003etrue\u003c/code\u003e选项的 \u003ccode\u003eALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...\u003c/code\u003e、\u003ccode\u003eALTER SUBSCRIPTION ... SET (failover = true|false)\u003c/code\u003e以及 \u003ccode\u003eALTER SUBSCRIPTION ... SET (two_phase = false)\u003c/code\u003e 不能在事务块内执行。\u003c/p\u003e\u003cp\u003e命令\u003ccode\u003eALTER SUBSCRIPTION ... REFRESH PUBLICATION\u003c/code\u003e以及带有\u003ccode\u003erefresh\u003c/code\u003e = \u003ccode\u003etrue\u003c/code\u003e选项的 \u003ccode\u003eALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...\u003c/code\u003e，在订阅启用了 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\" rel=\"nofollow\"\u003e\u003ccode\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 参数时也不能执行，除非 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-COPY-DATA\" rel=\"nofollow\"\u003e\u003ccode\u003ecopy_data\u003c/code\u003e\u003c/a\u003e 为\u003ccode\u003efalse\u003c/code\u003e。要了解实际的两阶段状态，可参见 \u003ca href=\"/docs/18/catalog-pg-subscription.html\" rel=\"nofollow\"\u003e\u003ccode\u003epg_subscription\u003c/code\u003e\u003c/a\u003e 的\u003ccode\u003esubtwophasestate\u003c/code\u003e列。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e参数\u003c/h2\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要更改其属性的订阅名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eCONNECTION \u0026#39;\u003cem\u003e\u003ccode\u003econninfo\u003c/code\u003e\u003c/em\u003e\u0026#39;\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句替换最初由\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e设置的连接字符串。详情请参见该命令。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSET PUBLICATION \u003cem\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eADD PUBLICATION \u003cem\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan\u003e\u003ccode\u003eDROP PUBLICATION \u003cem\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这些形式会更改已订阅发布的列表。\u003ccode\u003eSET\u003c/code\u003e用新列表替换整个发布列表，\u003ccode\u003eADD\u003c/code\u003e向发布列表中添加额外的发布，而\u003ccode\u003eDROP\u003c/code\u003e 从发布列表中移除发布。在\u003ccode\u003eADD\u003c/code\u003e和\u003ccode\u003eSET\u003c/code\u003e变体中，允许指定不存在的发布，以便用户稍后再创建这些发布。详情请参见\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e。默认情况下，此命令还会执行与 \u003ccode\u003eREFRESH PUBLICATION\u003c/code\u003e类似的操作。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e指定此操作的附加选项。支持的选项有：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003erefresh\u003c/code\u003e (\u003ccode\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e为\u003ccode\u003efalse\u003c/code\u003e时，该命令不会尝试刷新表信息。此时应单独执行 \u003ccode\u003eREFRESH PUBLICATION\u003c/code\u003e。默认值为\u003ccode\u003etrue\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e此外，还可以指定\u003ccode\u003eREFRESH PUBLICATION\u003c/code\u003e下描述的选项，以控制隐式执行的刷新操作。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eREFRESH PUBLICATION\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e从发布者获取缺失的表信息。这将开始复制自 \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003ccode\u003eCREATE SUBSCRIPTION\u003c/code\u003e\u003c/a\u003e 或上次执行\u003ccode\u003eREFRESH PUBLICATION\u003c/code\u003e以来添加到已订阅发布中的表。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003erefresh_option\u003c/code\u003e\u003c/em\u003e指定刷新操作的附加选项。支持的选项有：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003ecopy_data\u003c/code\u003e (\u003ccode\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定在复制开始时，是否复制所订阅发布中预先存在的数据。默认值为\u003ccode\u003etrue\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e以前已订阅的表不会再次复制，即使某个表的行过滤器 \u003ccode\u003eWHERE\u003c/code\u003e子句此后已被修改。\u003c/p\u003e\u003cp\u003e关于\u003ccode\u003ecopy_data = true\u003c/code\u003e如何与 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN\" rel=\"nofollow\"\u003e\u003ccode\u003eorigin\u003c/code\u003e\u003c/a\u003e 参数相互作用的细节，请参见\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES\" title=\"注解\" rel=\"nofollow\"\u003e注意\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e关于以二进制格式复制预先存在的数据的细节，请参见 \u003ccode\u003eCREATE SUBSCRIPTION\u003c/code\u003e的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY\" rel=\"nofollow\"\u003e\u003ccode\u003ebinary\u003c/code\u003e\u003c/a\u003e 参数。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eENABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e启用先前已禁用的订阅，并在事务结束时启动逻辑复制工作进程。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eDISABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e禁用正在运行的订阅，并在事务结束时停止逻辑复制工作进程。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSET ( \u003cem\u003e\u003ccode\u003esubscription_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句修改最初由\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e设置的参数。详情请参见该命令。可更改的参数有 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME\" rel=\"nofollow\"\u003e\u003ccode\u003eslot_name\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SYNCHRONOUS-COMMIT\" rel=\"nofollow\"\u003e\u003ccode\u003esynchronous_commit\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY\" rel=\"nofollow\"\u003e\u003ccode\u003ebinary\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-STREAMING\" rel=\"nofollow\"\u003e\u003ccode\u003estreaming\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR\" rel=\"nofollow\"\u003e\u003ccode\u003edisable_on_error\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-PASSWORD-REQUIRED\" rel=\"nofollow\"\u003e\u003ccode\u003epassword_required\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-RUN-AS-OWNER\" rel=\"nofollow\"\u003e\u003ccode\u003erun_as_owner\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN\" rel=\"nofollow\"\u003e\u003ccode\u003eorigin\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\" rel=\"nofollow\"\u003e\u003ccode\u003efailover\u003c/code\u003e\u003c/a\u003e 以及 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\" rel=\"nofollow\"\u003e\u003ccode\u003etwo_phase\u003c/code\u003e\u003c/a\u003e。只有超级用户才能设置\u003ccode\u003epassword_required = false\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e更改 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME\" rel=\"nofollow\"\u003e\u003ccode\u003eslot_name\u003c/code\u003e\u003c/a\u003e 时，所指定槽的\u003ccode\u003efailover\u003c/code\u003e和\u003ccode\u003etwo_phase\u003c/code\u003e属性值，可能与订阅中相应的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\" rel=\"nofollow\"\u003e\u003ccode\u003efailover\u003c/code\u003e\u003c/a\u003e 和 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\" rel=\"nofollow\"\u003e\u003ccode\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 参数不同。创建该槽时，应确保槽的\u003ccode\u003efailover\u003c/code\u003e和 \u003ccode\u003etwo_phase\u003c/code\u003e属性与订阅中对应的参数一致。否则，发布者上的该槽的行为可能与这些订阅选项所表明的不同：例如，即使订阅的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\" rel=\"nofollow\"\u003e\u003ccode\u003efailover\u003c/code\u003e\u003c/a\u003e 选项已禁用，发布者上的该槽仍可能被同步到备库；或者即使订阅的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\" rel=\"nofollow\"\u003e\u003ccode\u003efailover\u003c/code\u003e\u003c/a\u003e 选项已启用，该槽也可能不会被同步到备库。\u003c/p\u003e\u003cp\u003e只有在订阅被禁用时，才能更改 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\" rel=\"nofollow\"\u003e\u003ccode\u003efailover\u003c/code\u003e\u003c/a\u003e 和 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\" rel=\"nofollow\"\u003e\u003ccode\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 参数。\u003c/p\u003e\u003cp\u003e当将 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\" rel=\"nofollow\"\u003e\u003ccode\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 从\u003ccode\u003etrue\u003c/code\u003e改为\u003ccode\u003efalse\u003c/code\u003e时，如果发现逻辑复制工作进程在\u003ccode\u003etwo_phase\u003c/code\u003e参数仍为\u003ccode\u003etrue\u003c/code\u003e时执行的任何预备事务，后端进程会报告错误。你可以在发布者节点上处理这些预备事务，或者在订阅者上手工回滚它们，然后再次尝试。与特定订阅对应、由逻辑复制工作进程预备的事务具有如下模式：\u003cspan\u003e“\u003cspan\u003e\u003ccode\u003epg_gid_%u_%u\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e（参数：订阅\u003cem\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e，远端事务 ID \u003cem\u003e\u003ccode\u003exid\u003c/code\u003e\u003c/em\u003e）。要手工处理这类事务，需要回滚名称中包含相应订阅 ID 的所有预备事务。应用可以检查 \u003ca href=\"/docs/18/view-pg-prepared-xacts.html\" rel=\"nofollow\"\u003e\u003ccode\u003epg_prepared_xacts\u003c/code\u003e\u003c/a\u003e 来找出所需的预备事务。在\u003ccode\u003etwo_phase\u003c/code\u003e选项从\u003ccode\u003etrue\u003c/code\u003e 改为\u003ccode\u003efalse\u003c/code\u003e之后，这些事务在提交时会由发布者再次复制。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003eSKIP ( \u003cem\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e = \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e跳过应用远端事务的所有更改。如果传入数据违反任何约束，逻辑复制会停止，直到问题解决。通过使用\u003ccode\u003eALTER SUBSCRIPTION ... SKIP\u003c/code\u003e命令，逻辑复制工作进程会跳过该事务中的所有数据修改操作。此选项对因在订阅者上启用 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\" rel=\"nofollow\"\u003e\u003ccode\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 而已经预备的事务没有作用。逻辑复制工作进程成功跳过该事务或完成一个事务后，LSN（存储在\u003ccode\u003epg_subscription\u003c/code\u003e.\u003ccode\u003esubskiplsn\u003c/code\u003e中）会被清除。有关逻辑复制冲突的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-conflicts.html\" rel=\"nofollow\"\u003e第 29.7 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e\u003cem\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e指定此操作的选项。支持的选项有：\u003c/p\u003e\u003cdiv\u003e\u003cdl\u003e\u003cdt\u003e\u003cspan\u003e\u003ccode\u003elsn\u003c/code\u003e (\u003ccode\u003epg_lsn\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定逻辑复制工作进程要跳过其更改的远端事务的完成 LSN。完成 LSN 是该事务提交或预备时所在的 LSN。不支持跳过单个子事务。设置 \u003ccode\u003eNONE\u003c/code\u003e会重置该 LSN。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e订阅新所有者的用户名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan\u003e\u003cem\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e订阅的新名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e当指定\u003ccode\u003eboolean\u003c/code\u003e类型的参数时，可以省略 \u003ccode\u003e=\u003c/code\u003e \u003cem\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e 部分，这等价于指定\u003ccode\u003eTRUE\u003c/code\u003e。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e示例\u003c/h2\u003e\u003cp\u003e将订阅所订阅的发布更改为\u003ccode\u003einsert_only\u003c/code\u003e：\u003c/p\u003e\u003cpre\u003eALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;\n\u003c/pre\u003e\u003cp\u003e禁用（停止）该订阅：\u003c/p\u003e\u003cpre\u003eALTER SUBSCRIPTION mysub DISABLE;\n\u003c/pre\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e兼容性\u003c/h2\u003e\u003cp\u003e\u003ccode\u003eALTER SUBSCRIPTION\u003c/code\u003e是\u003cspan\u003ePostgreSQL\u003c/span\u003e 的一个扩展。\u003c/p\u003e\u003c/section\u003e\u003csection\u003e\u003ch2\u003e另见\u003c/h2\u003e\u003cspan\u003e\u003ca href=\"/wiki/sql/create-subscription/?v=18\" title=\"CREATE SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-subscription/?v=18\" title=\"DROP SUBSCRIPTION\" rel=\"nofollow\"\u003e\u003cspan\u003eDROP SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-publication/?v=18\" title=\"CREATE PUBLICATION\" rel=\"nofollow\"\u003e\u003cspan\u003eCREATE PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/alter-publication/?v=18\" title=\"ALTER PUBLICATION\" rel=\"nofollow\"\u003e\u003cspan\u003eALTER PUBLICATION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e\u003c/section\u003e","SourceRevision":"1b5ca64c","ContentHash":"5028865da3dfe442302df4fb0e7ae1aeb7555df37c80941a3689b5c825b2de0e","Payload":{"purpose_zh":"修改订阅的定义","sections":[{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION\u003c/code\u003e可以更改大多数可在\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e中指定的订阅属性。\u003c/p\u003e\u003cp\u003e要使用\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION\u003c/code\u003e，你必须拥有该订阅。要重命名订阅或更改其所有者，你必须在数据库上具有 \u003ccode class=\"literal\"\u003eCREATE\u003c/code\u003e权限。此外，要更改所有者，你必须能够对新的拥有角色执行 \u003ccode class=\"literal\"\u003eSET ROLE\u003c/code\u003e。如果该订阅具有 \u003ccode class=\"literal\"\u003epassword_required=false\u003c/code\u003e，则只有超级用户可以修改它。\u003c/p\u003e\u003cp\u003e刷新发布时，会移除不再属于该发布的关系；如果存在表同步槽，也会一并删除。必须删除这些槽，才能释放远端主机上为该订阅分配的资源。如果由于网络故障或其他错误，\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e无法删除这些槽，则会报告错误。在这种情况下，如需继续，用户要么重试该操作，要么按\u003ca href=\"/docs/18/sql-dropsubscription.html\" title=\"DROP SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e中的说明将该槽与订阅解除关联并删除该订阅。\u003c/p\u003e\u003cp\u003e命令\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... REFRESH PUBLICATION\u003c/code\u003e、带有\u003ccode class=\"literal\"\u003erefresh\u003c/code\u003e = \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e选项的 \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...\u003c/code\u003e、\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... SET (failover = true|false)\u003c/code\u003e以及 \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... SET (two_phase = false)\u003c/code\u003e 不能在事务块内执行。\u003c/p\u003e\u003cp\u003e命令\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... REFRESH PUBLICATION\u003c/code\u003e以及带有\u003ccode class=\"literal\"\u003erefresh\u003c/code\u003e = \u003ccode class=\"literal\"\u003etrue\u003c/code\u003e选项的 \u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...\u003c/code\u003e，在订阅启用了 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 参数时也不能执行，除非 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-COPY-DATA\"\u003e\u003ccode class=\"literal\"\u003ecopy_data\u003c/code\u003e\u003c/a\u003e 为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e。要了解实际的两阶段状态，可参见 \u003ca href=\"/docs/18/catalog-pg-subscription.html\" title=\"52.54. pg_subscription\"\u003e\u003ccode class=\"structname\"\u003epg_subscription\u003c/code\u003e\u003c/a\u003e 的\u003ccode class=\"structfield\"\u003esubtwophasestate\u003c/code\u003e列。\u003c/p\u003e","key":"description","title":"描述"},{"html":"\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e要更改其属性的订阅名称。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eCONNECTION '\u003cem class=\"replaceable\"\u003e\u003ccode\u003econninfo\u003c/code\u003e\u003c/em\u003e'\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句替换最初由\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e设置的连接字符串。详情请参见该命令。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eADD PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003cbr\u003e\u003c/span\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDROP PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e这些形式会更改已订阅发布的列表。\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e用新列表替换整个发布列表，\u003ccode class=\"literal\"\u003eADD\u003c/code\u003e向发布列表中添加额外的发布，而\u003ccode class=\"literal\"\u003eDROP\u003c/code\u003e 从发布列表中移除发布。在\u003ccode class=\"literal\"\u003eADD\u003c/code\u003e和\u003ccode class=\"literal\"\u003eSET\u003c/code\u003e变体中，允许指定不存在的发布，以便用户稍后再创建这些发布。详情请参见\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e。默认情况下，此命令还会执行与 \u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e类似的操作。\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e指定此操作的附加选项。支持的选项有：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003erefresh\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e时，该命令不会尝试刷新表信息。此时应单独执行 \u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e。默认值为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e此外，还可以指定\u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e下描述的选项，以控制隐式执行的刷新操作。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eREFRESH PUBLICATION\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e从发布者获取缺失的表信息。这将开始复制自 \u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003ccode class=\"command\"\u003eCREATE SUBSCRIPTION\u003c/code\u003e\u003c/a\u003e 或上次执行\u003ccode class=\"command\"\u003eREFRESH PUBLICATION\u003c/code\u003e以来添加到已订阅发布中的表。\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003erefresh_option\u003c/code\u003e\u003c/em\u003e指定刷新操作的附加选项。支持的选项有：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003ecopy_data\u003c/code\u003e (\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定在复制开始时，是否复制所订阅发布中预先存在的数据。默认值为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e以前已订阅的表不会再次复制，即使某个表的行过滤器 \u003ccode class=\"literal\"\u003eWHERE\u003c/code\u003e子句此后已被修改。\u003c/p\u003e\u003cp\u003e关于\u003ccode class=\"literal\"\u003ecopy_data = true\u003c/code\u003e如何与 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN\"\u003e\u003ccode class=\"literal\"\u003eorigin\u003c/code\u003e\u003c/a\u003e 参数相互作用的细节，请参见\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES\" title=\"注解\"\u003e注意\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e关于以二进制格式复制预先存在的数据的细节，请参见 \u003ccode class=\"command\"\u003eCREATE SUBSCRIPTION\u003c/code\u003e的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY\"\u003e\u003ccode class=\"literal\"\u003ebinary\u003c/code\u003e\u003c/a\u003e 参数。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eENABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e启用先前已禁用的订阅，并在事务结束时启动逻辑复制工作进程。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eDISABLE\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e禁用正在运行的订阅，并在事务结束时停止逻辑复制工作进程。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSET ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscription_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e该子句修改最初由\u003ca href=\"/docs/18/sql-createsubscription.html\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e设置的参数。详情请参见该命令。可更改的参数有 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME\"\u003e\u003ccode class=\"literal\"\u003eslot_name\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SYNCHRONOUS-COMMIT\"\u003e\u003ccode class=\"literal\"\u003esynchronous_commit\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-BINARY\"\u003e\u003ccode class=\"literal\"\u003ebinary\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-STREAMING\"\u003e\u003ccode class=\"literal\"\u003estreaming\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-DISABLE-ON-ERROR\"\u003e\u003ccode class=\"literal\"\u003edisable_on_error\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-PASSWORD-REQUIRED\"\u003e\u003ccode class=\"literal\"\u003epassword_required\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-RUN-AS-OWNER\"\u003e\u003ccode class=\"literal\"\u003erun_as_owner\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-ORIGIN\"\u003e\u003ccode class=\"literal\"\u003eorigin\u003c/code\u003e\u003c/a\u003e、\u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e 以及 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e。只有超级用户才能设置\u003ccode class=\"literal\"\u003epassword_required = false\u003c/code\u003e。\u003c/p\u003e\u003cp\u003e更改 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-SLOT-NAME\"\u003e\u003ccode class=\"literal\"\u003eslot_name\u003c/code\u003e\u003c/a\u003e 时，所指定槽的\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e和\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e属性值，可能与订阅中相应的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e 和 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 参数不同。创建该槽时，应确保槽的\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e和 \u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e属性与订阅中对应的参数一致。否则，发布者上的该槽的行为可能与这些订阅选项所表明的不同：例如，即使订阅的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e 选项已禁用，发布者上的该槽仍可能被同步到备库；或者即使订阅的 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e 选项已启用，该槽也可能不会被同步到备库。\u003c/p\u003e\u003cp\u003e只有在订阅被禁用时，才能更改 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-FAILOVER\"\u003e\u003ccode class=\"literal\"\u003efailover\u003c/code\u003e\u003c/a\u003e 和 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 参数。\u003c/p\u003e\u003cp\u003e当将 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 从\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e改为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e时，如果发现逻辑复制工作进程在\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e参数仍为\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e时执行的任何预备事务，后端进程会报告错误。你可以在发布者节点上处理这些预备事务，或者在订阅者上手工回滚它们，然后再次尝试。与特定订阅对应、由逻辑复制工作进程预备的事务具有如下模式：\u003cspan class=\"quote\"\u003e“\u003cspan class=\"quote\"\u003e\u003ccode class=\"literal\"\u003epg_gid_%u_%u\u003c/code\u003e\u003c/span\u003e”\u003c/span\u003e（参数：订阅\u003cem class=\"parameter\"\u003e\u003ccode\u003eoid\u003c/code\u003e\u003c/em\u003e，远端事务 ID \u003cem class=\"parameter\"\u003e\u003ccode\u003exid\u003c/code\u003e\u003c/em\u003e）。要手工处理这类事务，需要回滚名称中包含相应订阅 ID 的所有预备事务。应用可以检查 \u003ca href=\"/docs/18/view-pg-prepared-xacts.html\" title=\"53.17. pg_prepared_xacts\"\u003e\u003ccode class=\"structname\"\u003epg_prepared_xacts\u003c/code\u003e\u003c/a\u003e 来找出所需的预备事务。在\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e选项从\u003ccode class=\"literal\"\u003etrue\u003c/code\u003e 改为\u003ccode class=\"literal\"\u003efalse\u003c/code\u003e之后，这些事务在提交时会由发布者再次复制。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003eSKIP ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e = \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e )\u003c/code\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e跳过应用远端事务的所有更改。如果传入数据违反任何约束，逻辑复制会停止，直到问题解决。通过使用\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION ... SKIP\u003c/code\u003e命令，逻辑复制工作进程会跳过该事务中的所有数据修改操作。此选项对因在订阅者上启用 \u003ca href=\"/docs/18/sql-createsubscription.html#SQL-CREATESUBSCRIPTION-PARAMS-WITH-TWO-PHASE\"\u003e\u003ccode class=\"literal\"\u003etwo_phase\u003c/code\u003e\u003c/a\u003e 而已经预备的事务没有作用。逻辑复制工作进程成功跳过该事务或完成一个事务后，LSN（存储在\u003ccode class=\"structname\"\u003epg_subscription\u003c/code\u003e.\u003ccode class=\"structfield\"\u003esubskiplsn\u003c/code\u003e中）会被清除。有关逻辑复制冲突的详细信息，请参见\u003ca href=\"/docs/18/logical-replication-conflicts.html\" title=\"29.7. 冲突\"\u003e第 29.7 节\u003c/a\u003e。\u003c/p\u003e\u003cp\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e指定此操作的选项。支持的选项有：\u003c/p\u003e\u003cdiv class=\"variablelist\"\u003e\u003cdl class=\"variablelist\"\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003ccode class=\"literal\"\u003elsn\u003c/code\u003e (\u003ccode class=\"type\"\u003epg_lsn\u003c/code\u003e)\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e指定逻辑复制工作进程要跳过其更改的远端事务的完成 LSN。完成 LSN 是该事务提交或预备时所在的 LSN。不支持跳过单个子事务。设置 \u003ccode class=\"literal\"\u003eNONE\u003c/code\u003e会重置该 LSN。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e订阅新所有者的用户名。\u003c/p\u003e\u003c/dd\u003e\u003cdt\u003e\u003cspan class=\"term\"\u003e\u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e\u003c/span\u003e\u003c/dt\u003e\u003cdd\u003e\u003cp\u003e订阅的新名称。\u003c/p\u003e\u003c/dd\u003e\u003c/dl\u003e\u003c/div\u003e\u003cp\u003e当指定\u003ccode class=\"type\"\u003eboolean\u003c/code\u003e类型的参数时，可以省略 \u003ccode class=\"literal\"\u003e=\u003c/code\u003e \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e 部分，这等价于指定\u003ccode class=\"literal\"\u003eTRUE\u003c/code\u003e。\u003c/p\u003e","key":"parameters","title":"参数"},{"html":"\u003cp\u003e将订阅所订阅的发布更改为\u003ccode class=\"literal\"\u003einsert_only\u003c/code\u003e：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;\n\u003c/pre\u003e\u003cp\u003e禁用（停止）该订阅：\u003c/p\u003e\u003cpre class=\"programlisting\"\u003eALTER SUBSCRIPTION mysub DISABLE;\n\u003c/pre\u003e","key":"examples","title":"示例"},{"html":"\u003cp\u003e\u003ccode class=\"command\"\u003eALTER SUBSCRIPTION\u003c/code\u003e是\u003cspan class=\"productname\"\u003ePostgreSQL\u003c/span\u003e 的一个扩展。\u003c/p\u003e","key":"compatibility","title":"兼容性"},{"html":"\u003cspan class=\"simplelist\"\u003e\u003ca href=\"/wiki/sql/create-subscription/?v=18\" title=\"CREATE SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/drop-subscription/?v=18\" title=\"DROP SUBSCRIPTION\"\u003e\u003cspan class=\"refentrytitle\"\u003eDROP SUBSCRIPTION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/create-publication/?v=18\" title=\"CREATE PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eCREATE PUBLICATION\u003c/span\u003e\u003c/a\u003e, \u003ca href=\"/wiki/sql/alter-publication/?v=18\" title=\"ALTER PUBLICATION\"\u003e\u003cspan class=\"refentrytitle\"\u003eALTER PUBLICATION\u003c/span\u003e\u003c/a\u003e\u003c/span\u003e","key":"see_also","title":"另见"}],"sections_same_as":"","synopsis_html":"ALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e CONNECTION '\u003cem class=\"replaceable\"\u003e\u003ccode\u003econninfo\u003c/code\u003e\u003c/em\u003e'\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e SET PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e [, ...] [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ADD PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e [, ...] [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e DROP PUBLICATION \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_name\u003c/code\u003e\u003c/em\u003e [, ...] [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003epublication_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e REFRESH PUBLICATION [ WITH ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003erefresh_option\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] ) ]\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e ENABLE\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e DISABLE\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e SET ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003esubscription_parameter\u003c/code\u003e\u003c/em\u003e [= \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e] [, ... ] )\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e SKIP ( \u003cem class=\"replaceable\"\u003e\u003ccode\u003eskip_option\u003c/code\u003e\u003c/em\u003e = \u003cem class=\"replaceable\"\u003e\u003ccode\u003evalue\u003c/code\u003e\u003c/em\u003e )\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e OWNER TO { \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_owner\u003c/code\u003e\u003c/em\u003e | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER SUBSCRIPTION \u003cem class=\"replaceable\"\u003e\u003ccode\u003ename\u003c/code\u003e\u003c/em\u003e RENAME TO \u003cem class=\"replaceable\"\u003e\u003ccode\u003enew_name\u003c/code\u003e\u003c/em\u003e","synopsis_text":"ALTER SUBSCRIPTION name CONNECTION 'conninfo'\nALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]\nALTER SUBSCRIPTION name ENABLE\nALTER SUBSCRIPTION name DISABLE\nALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )\nALTER SUBSCRIPTION name SKIP ( skip_option = value )\nALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }\nALTER SUBSCRIPTION name RENAME TO new_name"}},"RequestedLocale":"zh-Hans","Fallback":false,"Versions":["10","11","12","13","14","15","16","17","18","19","20"],"Locales":["en","zh-Hans"],"Signatures":null,"Spellings":null,"SQLState":null,"Evidence":null}