ALTER_SUBSCRIPTION(7)

1ALTER SUBSCRIPTION(7)    PostgreSQL 15.4 Documentation   ALTER SUBSCRIPTION(7)
2
3
4

NAME

6       ALTER_SUBSCRIPTION - change the definition of a subscription
7

SYNOPSIS

9       ALTER SUBSCRIPTION name CONNECTION 'conninfo'
10       ALTER SUBSCRIPTION name SET PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
11       ALTER SUBSCRIPTION name ADD PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
12       ALTER SUBSCRIPTION name DROP PUBLICATION publication_name [, ...] [ WITH ( publication_option [= value] [, ... ] ) ]
13       ALTER SUBSCRIPTION name REFRESH PUBLICATION [ WITH ( refresh_option [= value] [, ... ] ) ]
14       ALTER SUBSCRIPTION name ENABLE
15       ALTER SUBSCRIPTION name DISABLE
16       ALTER SUBSCRIPTION name SET ( subscription_parameter [= value] [, ... ] )
17       ALTER SUBSCRIPTION name SKIP ( skip_option = value )
18       ALTER SUBSCRIPTION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
19       ALTER SUBSCRIPTION name RENAME TO new_name
20

DESCRIPTION

22       ALTER SUBSCRIPTION can change most of the subscription properties that
23       can be specified in CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)).
24
25       You must own the subscription to use ALTER SUBSCRIPTION. To alter the
26       owner, you must also be a direct or indirect member of the new owning
27       role. The new owner has to be a superuser. (Currently, all subscription
28       owners must be superusers, so the owner checks will be bypassed in
29       practice. But this might change in the future.)
30
31       When refreshing a publication we remove the relations that are no
32       longer part of the publication and we also remove the table
33       synchronization slots if there are any. It is necessary to remove these
34       slots so that the resources allocated for the subscription on the
35       remote host are released. If due to network breakdown or some other
36       error, PostgreSQL is unable to remove the slots, an error will be
37       reported. To proceed in this situation, the user either needs to retry
38       the operation or disassociate the slot from the subscription and drop
39       the subscription as explained in DROP SUBSCRIPTION
40       (DROP_SUBSCRIPTION(7)).
41
42       Commands ALTER SUBSCRIPTION ... REFRESH PUBLICATION and ALTER
43       SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ...  with refresh option as
44       true cannot be executed inside a transaction block. These commands also
45       cannot be executed when the subscription has two_phase commit enabled,
46       unless copy_data is false. See column subtwophasestate of
47       pg_subscription to know the actual two-phase state.
48

PARAMETERS

50       name
51           The name of a subscription whose properties are to be altered.
52
53       CONNECTION 'conninfo'
54           This clause replaces the connection string originally set by CREATE
55           SUBSCRIPTION (CREATE_SUBSCRIPTION(7)). See there for more
56           information.
57
58       SET PUBLICATION publication_name
59       ADD PUBLICATION publication_name
60       DROP PUBLICATION publication_name
61           These forms change the list of subscribed publications.  SET
62           replaces the entire list of publications with a new list, ADD adds
63           additional publications to the list of publications, and DROP
64           removes the publications from the list of publications. We allow
65           non-existent publications to be specified in ADD and SET variants
66           so that users can add those later. See CREATE SUBSCRIPTION
67           (CREATE_SUBSCRIPTION(7)) for more information. By default, this
68           command will also act like REFRESH PUBLICATION.
69
70           publication_option specifies additional options for this operation.
71           The supported options are:
72
73           refresh (boolean)
74               When false, the command will not try to refresh table
75               information.  REFRESH PUBLICATION should then be executed
76               separately. The default is true.
77
78           Additionally, the options described under REFRESH PUBLICATION may
79           be specified, to control the implicit refresh operation.
80
81       REFRESH PUBLICATION
82           Fetch missing table information from publisher. This will start
83           replication of tables that were added to the subscribed-to
84           publications since CREATE SUBSCRIPTION or the last invocation of
85           REFRESH PUBLICATION.
86
87           refresh_option specifies additional options for the refresh
88           operation. The supported options are:
89
90           copy_data (boolean)
91               Specifies whether to copy pre-existing data in the publications
92               that are being subscribed to when the replication starts. The
93               default is true.
94
95               Previously subscribed tables are not copied, even if a table's
96               row filter WHERE clause has since been modified.
97
98       ENABLE
99           Enables a previously disabled subscription, starting the logical
100           replication worker at the end of the transaction.
101
102       DISABLE
103           Disables a running subscription, stopping the logical replication
104           worker at the end of the transaction.
105
106       SET ( subscription_parameter [= value] [, ... ] )
107           This clause alters parameters originally set by CREATE SUBSCRIPTION
108           (CREATE_SUBSCRIPTION(7)). See there for more information. The
109           parameters that can be altered are slot_name, synchronous_commit,
110           binary, streaming, and disable_on_error.
111
112       SKIP ( skip_option = value )
113           Skips applying all changes of the remote transaction. If incoming
114           data violates any constraints, logical replication will stop until
115           it is resolved. By using the ALTER SUBSCRIPTION ... SKIP command,
116           the logical replication worker skips all data modification changes
117           within the transaction. This option has no effect on the
118           transactions that are already prepared by enabling two_phase on
119           subscriber. After the logical replication worker successfully skips
120           the transaction or finishes a transaction, the LSN (stored in
121           pg_subscription.subskiplsn) is cleared. See Section 31.5 for the
122           details of logical replication conflicts. Using this command
123           requires superuser privilege.
124
125           skip_option specifies options for this operation. The supported
126           option is:
127
128           lsn (pg_lsn)
129               Specifies the finish LSN of the remote transaction whose
130               changes are to be skipped by the logical replication worker.
131               The finish LSN is the LSN at which the transaction is either
132               committed or prepared. Skipping individual subtransactions is
133               not supported. Setting NONE resets the LSN.
134
135       new_owner
136           The user name of the new owner of the subscription.
137
138       new_name
139           The new name for the subscription.
140

EXAMPLES

142       Change the publication subscribed by a subscription to insert_only:
143
144           ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;
145
146       Disable (stop) the subscription:
147
148           ALTER SUBSCRIPTION mysub DISABLE;
149

COMPATIBILITY

151       ALTER SUBSCRIPTION is a PostgreSQL extension.
152

NAME

SYNOPSIS

DESCRIPTION

PARAMETERS

EXAMPLES

COMPATIBILITY

SEE ALSO