summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-createsubscription.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/sql-createsubscription.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/sql-createsubscription.html')
-rw-r--r--doc/src/sgml/html/sql-createsubscription.html217
1 files changed, 217 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createsubscription.html b/doc/src/sgml/html/sql-createsubscription.html
new file mode 100644
index 0000000..66ff222
--- /dev/null
+++ b/doc/src/sgml/html/sql-createsubscription.html
@@ -0,0 +1,217 @@
+<?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 id="docContent" class="container-fluid col-10"><div 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 15.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 /></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 logical-replication
+ subscription. The subscription name must be distinct from the name of
+ any existing subscription in the current database.
+ </p><p>
+ A subscription represents a replication connection to the publisher.
+ Hence, in addition to adding definitions in the local catalogs, this
+ command normally 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,
+ unless the subscription is initially disabled.
+ </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 <span class="application">libpq</span> connection string defining how
+ to connect to the publisher database. 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.
+ </p><p>
+ The following parameters control what happens during subscription creation:
+
+ </p><div class="variablelist"><dl class="variablelist"><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>
+ command should connect to the publisher at all. The default
+ is <code class="literal">true</code>. Setting this to
+ <code class="literal">false</code> will force the values of
+ <code class="literal">create_slot</code>, <code class="literal">enabled</code> and
+ <code class="literal">copy_data</code> to <code class="literal">false</code>.
+ (You cannot combine setting <code class="literal">connect</code>
+ to <code class="literal">false</code> with
+ setting <code class="literal">create_slot</code>, <code class="literal">enabled</code>,
+ or <code class="literal">copy_data</code> to <code class="literal">true</code>.)
+ </p><p>
+ Since no connection is made when this option is
+ <code class="literal">false</code>, no tables are subscribed, and so
+ after you enable the subscription nothing will be replicated.
+ You will need to then run
+ <code class="literal">ALTER SUBSCRIPTION ... REFRESH PUBLICATION</code>
+ for tables to be subscribed.
+ </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>.
+ If set to <code class="literal">false</code>, you are responsible for
+ creating the publisher's slot in some other way.
+ </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 just be set up 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 publisher's replication slot to use. The default is
+ to use the name of the subscription for the slot name.
+ </p><p>
+ Setting <code class="literal">slot_name</code> to <code class="literal">NONE</code>
+ means there will be no replication slot
+ associated with the subscription. Use this when you will be
+ creating the replication slot 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></dl></div><p>
+ </p><p>
+ The following parameters control the subscription's replication
+ behavior after it has been created:
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">binary</code> (<code class="type">boolean</code>)</span></dt><dd><p>
+ Specifies whether the subscription will request the publisher to
+ send the data in binary format (as opposed to text).
+ The default is <code class="literal">false</code>.
+ Even when this option is enabled, only data types having
+ binary send and receive functions will be transferred in binary.
+ </p><p>
+ When doing cross-version replication, it could be that the
+ publisher has a binary send function for some data type, but the
+ subscriber lacks a binary receive function for that type. In
+ such a case, data transfer will fail, and
+ the <code class="literal">binary</code> option cannot be used.
+ </p></dd><dt><span class="term"><code class="literal">copy_data</code> (<code class="type">boolean</code>)</span></dt><dd><p>
+ Specifies whether to copy pre-existing data in the publications
+ that are being subscribed to when the replication starts.
+ The default is <code class="literal">true</code>.
+ </p><p>
+ If the publications contain <code class="literal">WHERE</code> clauses, it
+ will affect what data is copied. Refer to the
+ <a class="xref" href="sql-createsubscription.html#SQL-CREATESUBSCRIPTION-NOTES" title="Notes">Notes</a> for details.
+ </p></dd><dt><span class="term"><code class="literal">streaming</code> (<code class="type">boolean</code>)</span></dt><dd><p>
+ Specifies whether to enable streaming of in-progress transactions
+ for this subscription. By default, all transactions
+ are fully decoded on the publisher and only then sent to the
+ subscriber as a whole.
+ </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 within this
+ subscription's apply worker processes. 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 sent again 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">two_phase</code> (<code class="type">boolean</code>)</span></dt><dd><p>
+ Specifies whether two-phase commit is enabled for this subscription.
+ The default is <code class="literal">false</code>.
+ </p><p>
+ When two-phase commit is enabled, prepared transactions are sent
+ to the subscriber at the time of <code class="command">PREPARE
+ TRANSACTION</code>, and are processed as two-phase
+ transactions on the subscriber too. Otherwise, prepared
+ transactions are sent to the subscriber only when committed, and
+ are then processed immediately by the subscriber.
+ </p><p>
+ The implementation of two-phase commit requires that replication
+ has successfully finished the initial table synchronization
+ phase. So even when <code class="literal">two_phase</code> is enabled for a
+ subscription, the internal two-phase state remains
+ temporarily <span class="quote">“<span class="quote">pending</span>”</span> until the initialization phase
+ completes. See column <code class="structfield">subtwophasestate</code>
+ of <a class="link" href="catalog-pg-subscription.html" title="53.54. pg_subscription"><code class="structname">pg_subscription</code></a>
+ to know the actual two-phase state.
+ </p></dd><dt><span class="term"><code class="literal">disable_on_error</code> (<code class="type">boolean</code>)</span></dt><dd><p>
+ Specifies whether the subscription should be automatically disabled
+ if any errors are detected by subscription workers during data
+ replication from the publisher. The default is
+ <code class="literal">false</code>.
+ </p></dd></dl></div></dd></dl></div></div><div class="refsect1" id="SQL-CREATESUBSCRIPTION-NOTES"><h2>Notes</h2><p>
+ See <a class="xref" href="logical-replication-security.html" title="31.9. Security">Section 31.9</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><p>
+ If any table in the publication has a <code class="literal">WHERE</code> clause, rows
+ for which the <em class="replaceable"><code>expression</code></em>
+ evaluates to false or null will not be published. If the subscription has
+ several publications in which the same table has been published with
+ different <code class="literal">WHERE</code> clauses, a row will be published if any
+ of the expressions (referring to that publish operation) are satisfied. In
+ the case of different <code class="literal">WHERE</code> clauses, if one of the
+ publications has no <code class="literal">WHERE</code> clause (referring to that
+ publish operation) or the publication is declared as
+ <code class="literal">FOR ALL TABLES</code> or
+ <code class="literal">FOR TABLES IN SCHEMA</code>, rows are always published
+ regardless of the definition of the other expressions.
+ If the subscriber is a <span class="productname">PostgreSQL</span> version before
+ 15, then any row filtering is ignored during the initial data synchronization
+ phase. For this case, the user might want to consider deleting any initially
+ copied data that would be incompatible with subsequent filtering.
+ Because initial data synchronization does not take into account the publication
+ <code class="literal">publish</code> parameter when copying existing table data, some rows
+ may be copied that would not be replicated using DML. See
+ <a class="xref" href="logical-replication-subscription.html#LOGICAL-REPLICATION-SUBSCRIPTION-EXAMPLES" title="31.2.2. Examples">Section 31.2.2</a> for examples.
+ </p><p>
+ Subscriptions having several publications in which the same table has been
+ published with different column lists are not supported.
+ </p><p>
+ We allow non-existent publications to be specified so that users can add
+ those later. This means
+ <a class="link" href="catalog-pg-subscription.html" title="53.54. pg_subscription"><code class="structname">pg_subscription</code></a>
+ can have non-existent publications.
+ </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" title="CREATE STATISTICS">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createtable.html" title="CREATE TABLE">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" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TABLE</td></tr></table></div></body></html> \ No newline at end of file