1ALTER SUBSCRIPTION(7) PostgreSQL 16.1 Documentation ALTER SUBSCRIPTION(7)
2
6 ALTER_SUBSCRIPTION - change the definition of a subscription
7
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
22 ALTER SUBSCRIPTION can change most of the subscription properties that
23 can be specified in CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)).
24 You must own the subscription to use ALTER SUBSCRIPTION. To rename a
26 subscription or alter the owner, you must have CREATE permission on the
27 database. In addition, to alter the owner, you must be able to SET ROLE
28 to the new owning role. If the subscription has
29 password_required=false, only superusers can modify it.
30 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 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
50 name
51 The name of a subscription whose properties are to be altered.
52 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 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 publication_option specifies additional options for this operation.
71 The supported options are:
72 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 Additionally, the options described under REFRESH PUBLICATION may
79 be specified, to control the implicit refresh operation.
80 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 refresh_option specifies additional options for the refresh
88 operation. The supported options are:
89 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 Previously subscribed tables are not copied, even if a table's
96 row filter WHERE clause has since been modified.
97 See Notes for details of how copy_data = true can interact with
99 the origin parameter.
100 See the binary parameter of CREATE SUBSCRIPTION for details
102 about copying pre-existing data in binary format.
103 ENABLE
105 Enables a previously disabled subscription, starting the logical
106 replication worker at the end of the transaction.
107 DISABLE
109 Disables a running subscription, stopping the logical replication
110 worker at the end of the transaction.
111 SET ( subscription_parameter [= value] [, ... ] )
113 This clause alters parameters originally set by CREATE SUBSCRIPTION
114 (CREATE_SUBSCRIPTION(7)). See there for more information. The
115 parameters that can be altered are slot_name, synchronous_commit,
116 binary, streaming, disable_on_error, password_required,
117 run_as_owner, and origin. Only a superuser can set
118 password_required = false.
119 SKIP ( skip_option = value )
121 Skips applying all changes of the remote transaction. If incoming
122 data violates any constraints, logical replication will stop until
123 it is resolved. By using the ALTER SUBSCRIPTION ... SKIP command,
124 the logical replication worker skips all data modification changes
125 within the transaction. This option has no effect on the
126 transactions that are already prepared by enabling two_phase on the
127 subscriber. After the logical replication worker successfully skips
128 the transaction or finishes a transaction, the LSN (stored in
129 pg_subscription.subskiplsn) is cleared. See Section 31.5 for the
130 details of logical replication conflicts.
131 skip_option specifies options for this operation. The supported
133 option is:
134 lsn (pg_lsn)
136 Specifies the finish LSN of the remote transaction whose
137 changes are to be skipped by the logical replication worker.
138 The finish LSN is the LSN at which the transaction is either
139 committed or prepared. Skipping individual subtransactions is
140 not supported. Setting NONE resets the LSN.
141 new_owner
143 The user name of the new owner of the subscription.
144 new_name
146 The new name for the subscription.
147 When specifying a parameter of type boolean, the = value part can be
149 omitted, which is equivalent to specifying TRUE.
150
152 Change the publication subscribed by a subscription to insert_only:
153 ALTER SUBSCRIPTION mysub SET PUBLICATION insert_only;
155 Disable (stop) the subscription:
157 ALTER SUBSCRIPTION mysub DISABLE;
159
161 ALTER SUBSCRIPTION is a PostgreSQL extension.
162
164 CREATE SUBSCRIPTION (CREATE_SUBSCRIPTION(7)), DROP SUBSCRIPTION
165 (DROP_SUBSCRIPTION(7)), CREATE PUBLICATION (CREATE_PUBLICATION(7)),
166 ALTER PUBLICATION (ALTER_PUBLICATION(7))
167PostgreSQL 16.1 2023 ALTER SUBSCRIPTION(7)