<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE SUBSCRIPTION</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createstatistics.html" title="CREATE STATISTICS" /><link rel="next" href="sql-createtable.html" title="CREATE TABLE" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE SUBSCRIPTION</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createstatistics.html" title="CREATE STATISTICS">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createtable.html" title="CREATE TABLE">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-CREATESUBSCRIPTION"><div class="titlepage"></div><a id="id-1.9.3.84.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE SUBSCRIPTION</span></h2><p>CREATE SUBSCRIPTION — define a new subscription</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> CREATE SUBSCRIPTION <em class="replaceable"><code>subscription_name</code></em> CONNECTION '<em class="replaceable"><code>conninfo</code></em>' PUBLICATION <em class="replaceable"><code>publication_name</code></em> [, ...] [ WITH ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] ) ] </pre></div><div class="refsect1" id="id-1.9.3.84.5"><h2>Description</h2><p> <code class="command">CREATE SUBSCRIPTION</code> adds a new subscription for the current database. The subscription name must be distinct from the name of any existing subscription in the database. </p><p> The subscription represents a replication connection to the publisher. As such this command does not only add definitions in the local catalogs but also creates a replication slot on the publisher. </p><p> A logical replication worker will be started to replicate data for the new subscription at the commit of the transaction where this command is run. </p><p> Additional information about subscriptions and logical replication as a whole is available at <a class="xref" href="logical-replication-subscription.html" title="31.2. Subscription">Section 31.2</a> and <a class="xref" href="logical-replication.html" title="Chapter 31. Logical Replication">Chapter 31</a>. </p></div><div class="refsect1" id="id-1.9.3.84.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>subscription_name</code></em></span></dt><dd><p> The name of the new subscription. </p></dd><dt><span class="term"><code class="literal">CONNECTION '<em class="replaceable"><code>conninfo</code></em>'</code></span></dt><dd><p> The connection string to the publisher. For details see <a class="xref" href="libpq-connect.html#LIBPQ-CONNSTRING" title="34.1.1. Connection Strings">Section 34.1.1</a>. </p></dd><dt><span class="term"><code class="literal">PUBLICATION <em class="replaceable"><code>publication_name</code></em></code></span></dt><dd><p> Names of the publications on the publisher to subscribe to. </p></dd><dt><span class="term"><code class="literal">WITH ( <em class="replaceable"><code>subscription_parameter</code></em> [= <em class="replaceable"><code>value</code></em>] [, ... ] )</code></span></dt><dd><p> This clause specifies optional parameters for a subscription. The following parameters are supported: </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">copy_data</code> (<code class="type">boolean</code>)</span></dt><dd><p> Specifies whether the existing data in the publications that are being subscribed to should be copied once the replication starts. The default is <code class="literal">true</code>. </p></dd><dt><span class="term"><code class="literal">create_slot</code> (<code class="type">boolean</code>)</span></dt><dd><p> Specifies whether the command should create the replication slot on the publisher. The default is <code class="literal">true</code>. </p></dd><dt><span class="term"><code class="literal">enabled</code> (<code class="type">boolean</code>)</span></dt><dd><p> Specifies whether the subscription should be actively replicating, or whether it should be just setup but not started yet. The default is <code class="literal">true</code>. </p></dd><dt><span class="term"><code class="literal">slot_name</code> (<code class="type">string</code>)</span></dt><dd><p> Name of the replication slot to use. The default behavior is to use the name of the subscription for the slot name. </p><p> When <code class="literal">slot_name</code> is set to <code class="literal">NONE</code>, there will be no replication slot associated with the subscription. This can be used if the replication slot will be created later manually. Such subscriptions must also have both <code class="literal">enabled</code> and <code class="literal">create_slot</code> set to <code class="literal">false</code>. </p></dd><dt><span class="term"><code class="literal">synchronous_commit</code> (<code class="type">enum</code>)</span></dt><dd><p> The value of this parameter overrides the <a class="xref" href="runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT">synchronous_commit</a> setting. The default value is <code class="literal">off</code>. </p><p> It is safe to use <code class="literal">off</code> for logical replication: If the subscriber loses transactions because of missing synchronization, the data will be resent from the publisher. </p><p> A different setting might be appropriate when doing synchronous logical replication. The logical replication workers report the positions of writes and flushes to the publisher, and when using synchronous replication, the publisher will wait for the actual flush. This means that setting <code class="literal">synchronous_commit</code> for the subscriber to <code class="literal">off</code> when the subscription is used for synchronous replication might increase the latency for <code class="command">COMMIT</code> on the publisher. In this scenario, it can be advantageous to set <code class="literal">synchronous_commit</code> to <code class="literal">local</code> or higher. </p></dd><dt><span class="term"><code class="literal">connect</code> (<code class="type">boolean</code>)</span></dt><dd><p> Specifies whether the <code class="command">CREATE SUBSCRIPTION</code> should connect to the publisher at all. Setting this to <code class="literal">false</code> will change default values of <code class="literal">enabled</code>, <code class="literal">create_slot</code> and <code class="literal">copy_data</code> to <code class="literal">false</code>. </p><p> It is not allowed to combine <code class="literal">connect</code> set to <code class="literal">false</code> and <code class="literal">enabled</code>, <code class="literal">create_slot</code>, or <code class="literal">copy_data</code> set to <code class="literal">true</code>. </p><p> Since no connection is made when this option is set to <code class="literal">false</code>, the tables are not subscribed, and so after you enable the subscription nothing will be replicated. It is required to run <code class="literal">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code> in order for tables to be subscribed. </p></dd></dl></div><p> </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.84.7"><h2>Notes</h2><p> See <a class="xref" href="logical-replication-security.html" title="31.7. Security">Section 31.7</a> for details on how to configure access control between the subscription and the publication instance. </p><p> When creating a replication slot (the default behavior), <code class="command">CREATE SUBSCRIPTION</code> cannot be executed inside a transaction block. </p><p> Creating a subscription that connects to the same database cluster (for example, to replicate between databases in the same cluster or to replicate within the same database) will only succeed if the replication slot is not created as part of the same command. Otherwise, the <code class="command">CREATE SUBSCRIPTION</code> call will hang. To make this work, create the replication slot separately (using the function <code class="function">pg_create_logical_replication_slot</code> with the plugin name <code class="literal">pgoutput</code>) and create the subscription using the parameter <code class="literal">create_slot = false</code>. This is an implementation restriction that might be lifted in a future release. </p></div><div class="refsect1" id="id-1.9.3.84.8"><h2>Examples</h2><p> Create a subscription to a remote server that replicates tables in the publications <code class="literal">mypublication</code> and <code class="literal">insert_only</code> and starts replicating immediately on commit: </p><pre class="programlisting"> CREATE SUBSCRIPTION mysub CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb' PUBLICATION mypublication, insert_only; </pre><p> </p><p> Create a subscription to a remote server that replicates tables in the <code class="literal">insert_only</code> publication and does not start replicating until enabled at a later time. </p><pre class="programlisting"> CREATE SUBSCRIPTION mysub CONNECTION 'host=192.168.1.50 port=5432 user=foo dbname=foodb' PUBLICATION insert_only WITH (enabled = false); </pre></div><div class="refsect1" id="id-1.9.3.84.9"><h2>Compatibility</h2><p> <code class="command">CREATE SUBSCRIPTION</code> is a <span class="productname">PostgreSQL</span> extension. </p></div><div class="refsect1" id="id-1.9.3.84.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altersubscription.html" title="ALTER SUBSCRIPTION"><span class="refentrytitle">ALTER SUBSCRIPTION</span></a>, <a class="xref" href="sql-dropsubscription.html" title="DROP SUBSCRIPTION"><span class="refentrytitle">DROP SUBSCRIPTION</span></a>, <a class="xref" href="sql-createpublication.html" title="CREATE PUBLICATION"><span class="refentrytitle">CREATE PUBLICATION</span></a>, <a class="xref" href="sql-alterpublication.html" title="ALTER PUBLICATION"><span class="refentrytitle">ALTER PUBLICATION</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createstatistics.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtable.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE STATISTICS </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> CREATE TABLE</td></tr></table></div></body></html>