1<!--
2doc/src/sgml/ref/create_subscription.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="SQL-CREATESUBSCRIPTION">
7 <indexterm zone="sql-createsubscription">
8  <primary>CREATE SUBSCRIPTION</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>CREATE SUBSCRIPTION</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>CREATE SUBSCRIPTION</refname>
19  <refpurpose>define a new subscription</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24CREATE SUBSCRIPTION <replaceable class="PARAMETER">subscription_name</replaceable>
25    CONNECTION '<replaceable class="PARAMETER">conninfo</replaceable>'
26    PUBLICATION <replaceable class="PARAMETER">publication_name</replaceable> [, ...]
27    [ WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28</synopsis>
29 </refsynopsisdiv>
30
31 <refsect1>
32  <title>Description</title>
33
34  <para>
35   <command>CREATE SUBSCRIPTION</command> adds a new subscription for the
36   current database.  The subscription name must be distinct from the name of
37   any existing subscription in the database.
38  </para>
39
40  <para>
41   The subscription represents a replication connection to the publisher.  As
42   such this command does not only add definitions in the local catalogs but
43   also creates a replication slot on the publisher.
44  </para>
45
46  <para>
47   A logical replication worker will be started to replicate data for the new
48   subscription at the commit of the transaction where this command is run.
49  </para>
50
51  <para>
52   Additional information about subscriptions and logical replication as a
53   whole is available at <xref linkend="logical-replication-subscription"> and
54   <xref linkend="logical-replication">.
55  </para>
56
57 </refsect1>
58
59 <refsect1>
60  <title>Parameters</title>
61
62  <variablelist>
63   <varlistentry>
64    <term><replaceable class="parameter">subscription_name</replaceable></term>
65    <listitem>
66     <para>
67      The name of the new subscription.
68     </para>
69    </listitem>
70   </varlistentry>
71
72   <varlistentry>
73    <term><literal>CONNECTION '<replaceable class="parameter">conninfo</replaceable>'</literal></term>
74    <listitem>
75     <para>
76      The connection string to the publisher.  For details
77      see <xref linkend="libpq-connstring">.
78     </para>
79    </listitem>
80   </varlistentry>
81
82   <varlistentry>
83    <term><literal>PUBLICATION <replaceable class="parameter">publication_name</replaceable></literal></term>
84    <listitem>
85     <para>
86      Names of the publications on the publisher to subscribe to.
87     </para>
88    </listitem>
89   </varlistentry>
90
91   <varlistentry>
92    <term><literal>WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
93    <listitem>
94     <para>
95      This clause specifies optional parameters for a subscription.  The
96      following parameters are supported:
97
98      <variablelist>
99       <varlistentry>
100        <term><literal>copy_data</literal> (<type>boolean</type>)</term>
101        <listitem>
102         <para>
103          Specifies whether the existing data in the publications that are
104          being subscribed to should be copied once the replication starts.
105          The default is <literal>true</literal>.
106         </para>
107        </listitem>
108       </varlistentry>
109
110       <varlistentry>
111        <term><literal>create_slot</literal> (<type>boolean</type>)</term>
112        <listitem>
113         <para>
114          Specifies whether the command should create the replication slot on
115          the publisher.  The default is <literal>true</literal>.
116         </para>
117        </listitem>
118       </varlistentry>
119
120       <varlistentry>
121        <term><literal>enabled</literal> (<type>boolean</type>)</term>
122        <listitem>
123         <para>
124          Specifies whether the subscription should be actively replicating,
125          or whether it should be just setup but not started yet.  The default
126          is <literal>true</literal>.
127         </para>
128        </listitem>
129       </varlistentry>
130
131       <varlistentry>
132        <term><literal>slot_name</literal> (<type>string</type>)</term>
133        <listitem>
134         <para>
135          Name of the replication slot to use.  The default behavior is to
136          use the name of the subscription for the slot name.
137         </para>
138
139         <para>
140          When <literal>slot_name</literal> is set to
141          <literal>NONE</literal>, there will be no replication slot
142          associated with the subscription.  This can be used if the
143          replication slot will be created later manually.  Such
144          subscriptions must also have both <literal>enabled</literal> and
145          <literal>create_slot</literal> set to <literal>false</literal>.
146         </para>
147        </listitem>
148       </varlistentry>
149
150       <varlistentry>
151        <term><literal>synchronous_commit</literal> (<type>enum</type>)</term>
152        <listitem>
153         <para>
154          The value of this parameter overrides the
155          <xref linkend="guc-synchronous-commit"> setting.  The default
156          value is <literal>off</literal>.
157         </para>
158
159         <para>
160          It is safe to use <literal>off</literal> for logical replication:
161          If the subscriber loses transactions because of missing
162          synchronization, the data will be sent again from the publisher.
163         </para>
164
165         <para>
166          A different setting might be appropriate when doing synchronous
167          logical replication.  The logical replication workers report the
168          positions of writes and flushes to the publisher, and when using
169          synchronous replication, the publisher will wait for the actual
170          flush.  This means that setting
171          <literal>synchronous_commit</literal> for the subscriber to
172          <literal>off</literal> when the subscription is used for
173          synchronous replication might increase the latency for
174          <command>COMMIT</command> on the publisher.  In this scenario, it
175          can be advantageous to set <literal>synchronous_commit</literal>
176          to <literal>local</literal> or higher.
177         </para>
178        </listitem>
179       </varlistentry>
180
181       <varlistentry>
182        <term><literal>connect</literal> (<type>boolean</type>)</term>
183        <listitem>
184         <para>
185          Specifies whether the <command>CREATE SUBSCRIPTION</command>
186          should connect to the publisher at all.  Setting this to
187          <literal>false</literal> will change default values of
188          <literal>enabled</literal>, <literal>create_slot</literal> and
189          <literal>copy_data</literal> to <literal>false</literal>.
190         </para>
191
192         <para>
193          It is not allowed to combine <literal>connect</literal> set to
194          <literal>false</literal> and <literal>enabled</literal>,
195          <literal>create_slot</literal>, or <literal>copy_data</literal>
196          set to <literal>true</literal>.
197         </para>
198
199         <para>
200          Since no connection is made when this option is set
201          to <literal>false</literal>, the tables are not subscribed, and so
202          after you enable the subscription nothing will be replicated.
203          It is required to run
204          <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</> in order
205          for tables to be subscribed.
206         </para>
207        </listitem>
208       </varlistentry>
209      </variablelist>
210     </para>
211    </listitem>
212   </varlistentry>
213  </variablelist>
214 </refsect1>
215
216 <refsect1>
217  <title>Notes</title>
218
219  <para>
220   See <xref linkend="logical-replication-security"> for details on
221   how to configure access control between the subscription and the
222   publication instance.
223  </para>
224
225  <para>
226   When creating a replication slot (the default behavior), <command>CREATE
227   SUBSCRIPTION</command> cannot be executed inside a transaction block.
228  </para>
229
230  <para>
231   Creating a subscription that connects to the same database cluster (for
232   example, to replicate between databases in the same cluster or to replicate
233   within the same database) will only succeed if the replication slot is not
234   created as part of the same command.  Otherwise, the <command>CREATE
235   SUBSCRIPTION</command> call will hang.  To make this work, create the
236   replication slot separately (using the
237   function <function>pg_create_logical_replication_slot</function> with the
238   plugin name <literal>pgoutput</literal>) and create the subscription using
239   the parameter <literal>create_slot = false</literal>.  This is an
240   implementation restriction that might be lifted in a future release.
241  </para>
242 </refsect1>
243
244 <refsect1>
245  <title>Examples</title>
246
247  <para>
248   Create a subscription to a remote server that replicates tables in
249   the publications <literal>mypublication</literal> and
250   <literal>insert_only</literal> and starts replicating immediately on
251   commit:
252<programlisting>
253CREATE SUBSCRIPTION mysub
254         CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
255        PUBLICATION mypublication, insert_only;
256</programlisting>
257  </para>
258
259  <para>
260   Create a subscription to a remote server that replicates tables in
261   the <literal>insert_only</literal> publication and does not start replicating
262   until enabled at a later time.
263<programlisting>
264CREATE SUBSCRIPTION mysub
265         CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb'
266        PUBLICATION insert_only
267               WITH (enabled = false);
268</programlisting></para>
269 </refsect1>
270
271 <refsect1>
272  <title>Compatibility</title>
273
274  <para>
275   <command>CREATE SUBSCRIPTION</command> is a <productname>PostgreSQL</>
276   extension.
277  </para>
278 </refsect1>
279
280 <refsect1>
281  <title>See Also</title>
282
283  <simplelist type="inline">
284   <member><xref linkend="sql-altersubscription"></member>
285   <member><xref linkend="sql-dropsubscription"></member>
286   <member><xref linkend="sql-createpublication"></member>
287   <member><xref linkend="sql-alterpublication"></member>
288  </simplelist>
289 </refsect1>
290</refentry>
291