1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
|
<?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 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 14.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 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">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 that have
binary send and receive functions will be transferred in binary.
</p><p>
When doing cross-version replication, it could happen that the
publisher has a binary send function for some data type, but the
subscriber lacks a binary receive function for the 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">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><dt><span class="term"><code class="literal">streaming</code> (<code class="type">boolean</code>)</span></dt><dd><p>
Specifies whether streaming of in-progress transactions should
be enabled 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></dl></div></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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE TABLE</td></tr></table></div></body></html>
|
