1CREATE SUBSCRIPTION(7) PostgreSQL 16.1 Documentation CREATE SUBSCRIPTION(7)
2
6 CREATE_SUBSCRIPTION - define a new subscription
7
9 CREATE SUBSCRIPTION subscription_name
10 CONNECTION 'conninfo'
11 PUBLICATION publication_name [, ...]
12 [ WITH ( subscription_parameter [= value] [, ... ] ) ]
13
15 CREATE SUBSCRIPTION adds a new logical-replication subscription. The
16 user that creates a subscription becomes the owner of the subscription.
17 The subscription name must be distinct from the name of any existing
18 subscription in the current database.
19 A subscription represents a replication connection to the publisher.
21 Hence, in addition to adding definitions in the local catalogs, this
22 command normally creates a replication slot on the publisher.
23 A logical replication worker will be started to replicate data for the
25 new subscription at the commit of the transaction where this command is
26 run, unless the subscription is initially disabled.
27 To be able to create a subscription, you must have the privileges of
29 the the pg_create_subscription role, as well as CREATE privileges on
30 the current database.
31 Additional information about subscriptions and logical replication as a
33 whole is available at Section 31.2 and Chapter 31.
34
36 subscription_name
37 The name of the new subscription.
38 CONNECTION 'conninfo'
40 The libpq connection string defining how to connect to the
41 publisher database. For details see Section 34.1.1.
42 PUBLICATION publication_name [, ...]
44 Names of the publications on the publisher to subscribe to.
45 WITH ( subscription_parameter [= value] [, ... ] )
47 This clause specifies optional parameters for a subscription.
48 The following parameters control what happens during subscription
50 creation:
51 connect (boolean)
53 Specifies whether the CREATE SUBSCRIPTION command should
54 connect to the publisher at all. The default is true. Setting
55 this to false will force the values of create_slot, enabled and
56 copy_data to false. (You cannot combine setting connect to
57 false with setting create_slot, enabled, or copy_data to true.)
58 Since no connection is made when this option is false, no
60 tables are subscribed. To initiate replication, you must
61 manually create the replication slot, enable the subscription,
62 and refresh the subscription. See Section 31.2.3 for examples.
63 create_slot (boolean)
65 Specifies whether the command should create the replication
66 slot on the publisher. The default is true.
67 If set to false, you are responsible for creating the
69 publisher's slot in some other way. See Section 31.2.3 for
70 examples.
71 enabled (boolean)
73 Specifies whether the subscription should be actively
74 replicating or whether it should just be set up but not started
75 yet. The default is true.
76 slot_name (string)
78 Name of the publisher's replication slot to use. The default is
79 to use the name of the subscription for the slot name.
80 Setting slot_name to NONE means there will be no replication
82 slot associated with the subscription. Such subscriptions must
83 also have both enabled and create_slot set to false. Use this
84 when you will be creating the replication slot later manually.
85 See Section 31.2.3 for examples.
86 The following parameters control the subscription's replication
88 behavior after it has been created:
89 binary (boolean)
91 Specifies whether the subscription will request the publisher
92 to send the data in binary format (as opposed to text). The
93 default is false. Any initial table synchronization copy (see
94 copy_data) also uses the same format. Binary format can be
95 faster than the text format, but it is less portable across
96 machine architectures and PostgreSQL versions. Binary format is
97 very data type specific; for example, it will not allow copying
98 from a smallint column to an integer column, even though that
99 would work fine in text format. Even when this option is
100 enabled, only data types having binary send and receive
101 functions will be transferred in binary. Note that the initial
102 synchronization requires all data types to have binary send and
103 receive functions, otherwise the synchronization will fail (see
104 CREATE TYPE (CREATE_TYPE(7)) for more about send/receive
105 functions).
106 When doing cross-version replication, it could be that the
108 publisher has a binary send function for some data type, but
109 the subscriber lacks a binary receive function for that type.
110 In such a case, data transfer will fail, and the binary option
111 cannot be used.
112 If the publisher is a PostgreSQL version before 16, then any
114 initial table synchronization will use text format even if
115 binary = true.
116 copy_data (boolean)
118 Specifies whether to copy pre-existing data in the publications
119 that are being subscribed to when the replication starts. The
120 default is true.
121 If the publications contain WHERE clauses, it will affect what
123 data is copied. Refer to the Notes for details.
124 See Notes for details of how copy_data = true can interact with
126 the origin parameter.
127 streaming (enum)
129 Specifies whether to enable streaming of in-progress
130 transactions for this subscription. The default value is off,
131 meaning all transactions are fully decoded on the publisher and
132 only then sent to the subscriber as a whole.
133 If set to on, the incoming changes are written to temporary
135 files and then applied only after the transaction is committed
136 on the publisher and received by the subscriber.
137 If set to parallel, incoming changes are directly applied via
139 one of the parallel apply workers, if available. If no parallel
140 apply worker is free to handle streaming transactions then the
141 changes are written to temporary files and applied after the
142 transaction is committed. Note that if an error happens in a
143 parallel apply worker, the finish LSN of the remote transaction
144 might not be reported in the server log.
145 synchronous_commit (enum)
147 The value of this parameter overrides the synchronous_commit
148 setting within this subscription's apply worker processes. The
149 default value is off.
150 It is safe to use off for logical replication: If the
152 subscriber loses transactions because of missing
153 synchronization, the data will be sent again from the
154 publisher.
155 A different setting might be appropriate when doing synchronous
157 logical replication. The logical replication workers report the
158 positions of writes and flushes to the publisher, and when
159 using synchronous replication, the publisher will wait for the
160 actual flush. This means that setting synchronous_commit for
161 the subscriber to off when the subscription is used for
162 synchronous replication might increase the latency for COMMIT
163 on the publisher. In this scenario, it can be advantageous to
164 set synchronous_commit to local or higher.
165 two_phase (boolean)
167 Specifies whether two-phase commit is enabled for this
168 subscription. The default is false.
169 When two-phase commit is enabled, prepared transactions are
171 sent to the subscriber at the time of PREPARE TRANSACTION, and
172 are processed as two-phase transactions on the subscriber too.
173 Otherwise, prepared transactions are sent to the subscriber
174 only when committed, and are then processed immediately by the
175 subscriber.
176 The implementation of two-phase commit requires that
178 replication has successfully finished the initial table
179 synchronization phase. So even when two_phase is enabled for a
180 subscription, the internal two-phase state remains temporarily
181 “pending” until the initialization phase completes. See column
182 subtwophasestate of pg_subscription to know the actual
183 two-phase state.
184 disable_on_error (boolean)
186 Specifies whether the subscription should be automatically
187 disabled if any errors are detected by subscription workers
188 during data replication from the publisher. The default is
189 false.
190 password_required (boolean)
192 Specifies whether connections to the publisher made as a result
193 of this subscription must use password authentication. This
194 setting is ignored when the subscription is owned by a
195 superuser. The default is true. Only superusers can set this
196 value to false.
197 run_as_owner (boolean)
199 If true, all replication actions are performed as the
200 subscription owner. If false, replication workers will perform
201 actions on each table as the owner of that table. The latter
202 configuration is generally much more secure; for details, see
203 Section 31.9. The default is false.
204 origin (string)
206 Specifies whether the subscription will request the publisher
207 to only send changes that don't have an origin or send changes
208 regardless of origin. Setting origin to none means that the
209 subscription will request the publisher to only send changes
210 that don't have an origin. Setting origin to any means that the
211 publisher sends changes regardless of their origin. The default
212 is any.
213 See Notes for details of how copy_data = true can interact with
215 the origin parameter.
216 When specifying a parameter of type boolean, the = value part can be
218 omitted, which is equivalent to specifying TRUE.
219
221 See Section 31.9 for details on how to configure access control between
222 the subscription and the publication instance.
223 When creating a replication slot (the default behavior), CREATE
225 SUBSCRIPTION cannot be executed inside a transaction block.
226 Creating a subscription that connects to the same database cluster (for
228 example, to replicate between databases in the same cluster or to
229 replicate within the same database) will only succeed if the
230 replication slot is not created as part of the same command. Otherwise,
231 the CREATE SUBSCRIPTION call will hang. To make this work, create the
232 replication slot separately (using the function
233 pg_create_logical_replication_slot with the plugin name pgoutput) and
234 create the subscription using the parameter create_slot = false. See
235 Section 31.2.3 for examples. This is an implementation restriction that
236 might be lifted in a future release.
237 If any table in the publication has a WHERE clause, rows for which the
239 expression evaluates to false or null will not be published. If the
240 subscription has several publications in which the same table has been
241 published with different WHERE clauses, a row will be published if any
242 of the expressions (referring to that publish operation) are satisfied.
243 In the case of different WHERE clauses, if one of the publications has
244 no WHERE clause (referring to that publish operation) or the
245 publication is declared as FOR ALL TABLES or FOR TABLES IN SCHEMA, rows
246 are always published regardless of the definition of the other
247 expressions. If the subscriber is a PostgreSQL version before 15, then
248 any row filtering is ignored during the initial data synchronization
249 phase. For this case, the user might want to consider deleting any
250 initially copied data that would be incompatible with subsequent
251 filtering. Because initial data synchronization does not take into
252 account the publication publish parameter when copying existing table
253 data, some rows may be copied that would not be replicated using DML.
254 See Section 31.2.2 for examples.
255 Subscriptions having several publications in which the same table has
257 been published with different column lists are not supported.
258 We allow non-existent publications to be specified so that users can
260 add those later. This means pg_subscription can have non-existent
261 publications.
262 When using a subscription parameter combination of copy_data = true and
264 origin = NONE, the initial sync table data is copied directly from the
265 publisher, meaning that knowledge of the true origin of that data is
266 not possible. If the publisher also has subscriptions then the copied
267 table data might have originated from further upstream. This scenario
268 is detected and a WARNING is logged to the user, but the warning is
269 only an indication of a potential problem; it is the user's
270 responsibility to make the necessary checks to ensure the copied data
271 origins are really as wanted or not.
272 To find which tables might potentially include non-local origins (due
274 to other subscriptions created on the publisher) try this SQL query:
275 # substitute <pub-names> below with your publication name(s) to be queried
277 SELECT DISTINCT PT.schemaname, PT.tablename
278 FROM pg_publication_tables PT,
279 pg_subscription_rel PS
280 JOIN pg_class C ON (C.oid = PS.srrelid)
281 JOIN pg_namespace N ON (N.oid = C.relnamespace)
282 WHERE N.nspname = PT.schemaname AND
283 C.relname = PT.tablename AND
284 PT.pubname IN (<pub-names>);
285
287 Create a subscription to a remote server that replicates tables in the
288 publications mypublication and insert_only and starts replicating
289 immediately on commit:
290 CREATE SUBSCRIPTION mysub
292 CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
293 PUBLICATION mypublication, insert_only;
294 Create a subscription to a remote server that replicates tables in the
296 insert_only publication and does not start replicating until enabled at
297 a later time.
298 CREATE SUBSCRIPTION mysub
300 CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
301 PUBLICATION insert_only
302 WITH (enabled = false);
303
305 CREATE SUBSCRIPTION is a PostgreSQL extension.
306
308 ALTER SUBSCRIPTION (ALTER_SUBSCRIPTION(7)), DROP SUBSCRIPTION
309 (DROP_SUBSCRIPTION(7)), CREATE PUBLICATION (CREATE_PUBLICATION(7)),
310 ALTER PUBLICATION (ALTER_PUBLICATION(7))
311PostgreSQL 16.1 2023 CREATE SUBSCRIPTION(7)