diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createsubscription.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createsubscription.html | 217 |
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 |