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