1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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>54.4. Streaming Replication Protocol</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="sasl-authentication.html" title="54.3. SASL Authentication" /><link rel="next" href="protocol-logical-replication.html" title="54.5. Logical Streaming Replication Protocol" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">54.4. Streaming Replication Protocol</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sasl-authentication.html" title="54.3. SASL Authentication">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="protocol.html" title="Chapter 54. Frontend/Backend Protocol">Up</a></td><th width="60%" align="center">Chapter 54. Frontend/Backend Protocol</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="protocol-logical-replication.html" title="54.5. Logical Streaming Replication Protocol">Next</a></td></tr></table><hr /></div><div class="sect1" id="PROTOCOL-REPLICATION"><div class="titlepage"><div><div><h2 class="title" style="clear: both">54.4. Streaming Replication Protocol <a href="#PROTOCOL-REPLICATION" class="id_link">#</a></h2></div></div></div><p>
3 To initiate streaming replication, the frontend sends the
4 <code class="literal">replication</code> parameter in the startup message. A Boolean
5 value of <code class="literal">true</code> (or <code class="literal">on</code>,
6 <code class="literal">yes</code>, <code class="literal">1</code>) tells the backend to go into
7 physical replication walsender mode, wherein a small set of replication
8 commands, shown below, can be issued instead of SQL statements.
10 Passing <code class="literal">database</code> as the value for the
11 <code class="literal">replication</code> parameter instructs the backend to go into
12 logical replication walsender mode, connecting to the database specified in
13 the <code class="literal">dbname</code> parameter. In logical replication walsender
14 mode, the replication commands shown below as well as normal SQL commands can
17 In either physical replication or logical replication walsender mode, only the
18 simple query protocol can be used.
20 For the purpose of testing replication commands, you can make a replication
21 connection via <span class="application">psql</span> or any other
22 <span class="application">libpq</span>-using tool with a connection string including
23 the <code class="literal">replication</code> option,
25 </p><pre class="programlisting">
26 psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"
28 However, it is often more useful to use
29 <a class="xref" href="app-pgreceivewal.html" title="pg_receivewal"><span class="refentrytitle"><span class="application">pg_receivewal</span></span></a> (for physical replication) or
30 <a class="xref" href="app-pgrecvlogical.html" title="pg_recvlogical"><span class="refentrytitle"><span class="application">pg_recvlogical</span></span></a> (for logical replication).
32 Replication commands are logged in the server log when
33 <a class="xref" href="runtime-config-logging.html#GUC-LOG-REPLICATION-COMMANDS">log_replication_commands</a> is enabled.
35 The commands accepted in replication mode are:
37 </p><div class="variablelist"><dl class="variablelist"><dt id="PROTOCOL-REPLICATION-IDENTIFY-SYSTEM"><span class="term"><code class="literal">IDENTIFY_SYSTEM</code>
38 <a id="id-1.10.6.9.7.1.1.1.2" class="indexterm"></a>
39 </span> <a href="#PROTOCOL-REPLICATION-IDENTIFY-SYSTEM" class="id_link">#</a></dt><dd><p>
40 Requests the server to identify itself. Server replies with a result
41 set of a single row, containing four fields:
42 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">systemid</code> (<code class="type">text</code>)</span></dt><dd><p>
43 The unique system identifier identifying the cluster. This
44 can be used to check that the base backup used to initialize the
45 standby came from the same cluster.
46 </p></dd><dt><span class="term"><code class="literal">timeline</code> (<code class="type">int8</code>)</span></dt><dd><p>
47 Current timeline ID. Also useful to check that the standby is
48 consistent with the primary.
49 </p></dd><dt><span class="term"><code class="literal">xlogpos</code> (<code class="type">text</code>)</span></dt><dd><p>
50 Current WAL flush location. Useful to get a known location in the
51 write-ahead log where streaming can start.
52 </p></dd><dt><span class="term"><code class="literal">dbname</code> (<code class="type">text</code>)</span></dt><dd><p>
53 Database connected to or null.
54 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-SHOW"><span class="term"><code class="literal">SHOW</code> <em class="replaceable"><code>name</code></em>
55 <a id="id-1.10.6.9.7.1.2.1.3" class="indexterm"></a>
56 </span> <a href="#PROTOCOL-REPLICATION-SHOW" class="id_link">#</a></dt><dd><p>
57 Requests the server to send the current setting of a run-time parameter.
58 This is similar to the SQL command <a class="xref" href="sql-show.html" title="SHOW"><span class="refentrytitle">SHOW</span></a>.
59 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>
60 The name of a run-time parameter. Available parameters are documented
61 in <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a>.
62 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-TIMELINE-HISTORY"><span class="term"><code class="literal">TIMELINE_HISTORY</code> <em class="replaceable"><code>tli</code></em>
63 <a id="id-1.10.6.9.7.1.3.1.3" class="indexterm"></a>
64 </span> <a href="#PROTOCOL-REPLICATION-TIMELINE-HISTORY" class="id_link">#</a></dt><dd><p>
65 Requests the server to send over the timeline history file for timeline
66 <em class="replaceable"><code>tli</code></em>. Server replies with a
67 result set of a single row, containing two fields. While the fields
68 are labeled as <code class="type">text</code>, they effectively return raw bytes,
69 with no encoding conversion:
70 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">filename</code> (<code class="type">text</code>)</span></dt><dd><p>
71 File name of the timeline history file, e.g., <code class="filename">00000002.history</code>.
72 </p></dd><dt><span class="term"><code class="literal">content</code> (<code class="type">text</code>)</span></dt><dd><p>
73 Contents of the timeline history file.
74 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-CREATE-REPLICATION-SLOT"><span class="term"><code class="literal">CREATE_REPLICATION_SLOT</code> <em class="replaceable"><code>slot_name</code></em> [ <code class="literal">TEMPORARY</code> ] { <code class="literal">PHYSICAL</code> | <code class="literal">LOGICAL</code> <em class="replaceable"><code>output_plugin</code></em> } [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ]
75 <a id="id-1.10.6.9.7.1.4.1.8" class="indexterm"></a>
76 </span> <a href="#PROTOCOL-REPLICATION-CREATE-REPLICATION-SLOT" class="id_link">#</a></dt><dd><p>
77 Create a physical or logical replication
78 slot. See <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">Section 26.2.6</a> for more about
80 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>slot_name</code></em></span></dt><dd><p>
81 The name of the slot to create. Must be a valid replication slot
82 name (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS-MANIPULATION" title="26.2.6.1. Querying and Manipulating Replication Slots">Section 26.2.6.1</a>).
83 </p></dd><dt><span class="term"><em class="replaceable"><code>output_plugin</code></em></span></dt><dd><p>
84 The name of the output plugin used for logical decoding
85 (see <a class="xref" href="logicaldecoding-output-plugin.html" title="47.6. Logical Decoding Output Plugins">Section 47.6</a>).
86 </p></dd><dt><span class="term"><code class="literal">TEMPORARY</code></span></dt><dd><p>
87 Specify that this replication slot is a temporary one. Temporary
88 slots are not saved to disk and are automatically dropped on error
89 or when the session has finished.
90 </p></dd></dl></div><p>The following options are supported:</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">TWO_PHASE [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
91 If true, this logical replication slot supports decoding of two-phase
92 commit. With this option, commands related to two-phase commit such as
93 <code class="literal">PREPARE TRANSACTION</code>, <code class="literal">COMMIT PREPARED</code>
94 and <code class="literal">ROLLBACK PREPARED</code> are decoded and transmitted.
95 The transaction will be decoded and transmitted at
96 <code class="literal">PREPARE TRANSACTION</code> time.
98 </p></dd><dt><span class="term"><code class="literal">RESERVE_WAL [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
99 If true, this physical replication slot reserves <acronym class="acronym">WAL</acronym>
100 immediately. Otherwise, <acronym class="acronym">WAL</acronym> is only reserved upon
101 connection from a streaming replication client.
102 The default is false.
103 </p></dd><dt><span class="term"><code class="literal">SNAPSHOT { 'export' | 'use' | 'nothing' }</code></span></dt><dd><p>
104 Decides what to do with the snapshot created during logical slot
105 initialization. <code class="literal">'export'</code>, which is the default,
106 will export the snapshot for use in other sessions. This option can't
107 be used inside a transaction. <code class="literal">'use'</code> will use the
108 snapshot for the current transaction executing the command. This
109 option must be used in a transaction, and
110 <code class="literal">CREATE_REPLICATION_SLOT</code> must be the first command
111 run in that transaction. Finally, <code class="literal">'nothing'</code> will
112 just use the snapshot for logical decoding as normal but won't do
113 anything else with it.
114 </p></dd><dt><span class="term"><code class="literal">FAILOVER [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
115 If true, the slot is enabled to be synced to the standbys
116 so that logical replication can be resumed after failover.
117 The default is false.
118 </p></dd></dl></div><p>
119 In response to this command, the server will send a one-row result set
120 containing the following fields:
122 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">slot_name</code> (<code class="type">text</code>)</span></dt><dd><p>
123 The name of the newly-created replication slot.
124 </p></dd><dt><span class="term"><code class="literal">consistent_point</code> (<code class="type">text</code>)</span></dt><dd><p>
125 The WAL location at which the slot became consistent. This is the
126 earliest location from which streaming can start on this replication
128 </p></dd><dt><span class="term"><code class="literal">snapshot_name</code> (<code class="type">text</code>)</span></dt><dd><p>
129 The identifier of the snapshot exported by the command. The
130 snapshot is valid until a new command is executed on this connection
131 or the replication connection is closed. Null if the created slot
133 </p></dd><dt><span class="term"><code class="literal">output_plugin</code> (<code class="type">text</code>)</span></dt><dd><p>
134 The name of the output plugin used by the newly-created replication
135 slot. Null if the created slot is physical.
136 </p></dd></dl></div><p>
137 </p></dd><dt id="PROTOCOL-REPLICATION-CREATE-REPLICATION-SLOT-LEGACY"><span class="term"><code class="literal">CREATE_REPLICATION_SLOT</code> <em class="replaceable"><code>slot_name</code></em> [ <code class="literal">TEMPORARY</code> ] { <code class="literal">PHYSICAL</code> [ <code class="literal">RESERVE_WAL</code> ] | <code class="literal">LOGICAL</code> <em class="replaceable"><code>output_plugin</code></em> [ <code class="literal">EXPORT_SNAPSHOT</code> | <code class="literal">NOEXPORT_SNAPSHOT</code> | <code class="literal">USE_SNAPSHOT</code> | <code class="literal">TWO_PHASE</code> ] }
138 </span> <a href="#PROTOCOL-REPLICATION-CREATE-REPLICATION-SLOT-LEGACY" class="id_link">#</a></dt><dd><p>
139 For compatibility with older releases, this alternative syntax for
140 the <code class="literal">CREATE_REPLICATION_SLOT</code> command is still supported.
141 </p></dd><dt id="PROTOCOL-REPLICATION-ALTER-REPLICATION-SLOT"><span class="term"><code class="literal">ALTER_REPLICATION_SLOT</code> <em class="replaceable"><code>slot_name</code></em> ( <em class="replaceable"><code>option</code></em> [, ...] )
142 <a id="id-1.10.6.9.7.1.6.1.4" class="indexterm"></a>
143 </span> <a href="#PROTOCOL-REPLICATION-ALTER-REPLICATION-SLOT" class="id_link">#</a></dt><dd><p>
144 Change the definition of a replication slot.
145 See <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS" title="26.2.6. Replication Slots">Section 26.2.6</a> for more about
146 replication slots. This command is currently only supported for logical
148 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>slot_name</code></em></span></dt><dd><p>
149 The name of the slot to alter. Must be a valid replication slot
150 name (see <a class="xref" href="warm-standby.html#STREAMING-REPLICATION-SLOTS-MANIPULATION" title="26.2.6.1. Querying and Manipulating Replication Slots">Section 26.2.6.1</a>).
151 </p></dd></dl></div><p>The following options are supported:</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">TWO_PHASE [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
152 If true, this logical replication slot supports decoding of two-phase
153 commit. With this option, commands related to two-phase commit such as
154 <code class="literal">PREPARE TRANSACTION</code>, <code class="literal">COMMIT PREPARED</code>
155 and <code class="literal">ROLLBACK PREPARED</code> are decoded and transmitted.
156 The transaction will be decoded and transmitted at
157 <code class="literal">PREPARE TRANSACTION</code> time.
158 </p></dd></dl></div><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">FAILOVER [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
159 If true, the slot is enabled to be synced to the standbys
160 so that logical replication can be resumed after failover.
161 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-READ-REPLICATION-SLOT"><span class="term"><code class="literal">READ_REPLICATION_SLOT</code> <em class="replaceable"><code>slot_name</code></em>
162 <a id="id-1.10.6.9.7.1.7.1.3" class="indexterm"></a>
163 </span> <a href="#PROTOCOL-REPLICATION-READ-REPLICATION-SLOT" class="id_link">#</a></dt><dd><p>
164 Read some information associated with a replication slot. Returns a tuple
165 with <code class="literal">NULL</code> values if the replication slot does not
166 exist. This command is currently only supported for physical replication
169 In response to this command, the server will return a one-row result set,
170 containing the following fields:
171 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">slot_type</code> (<code class="type">text</code>)</span></dt><dd><p>
172 The replication slot's type, either <code class="literal">physical</code> or
173 <code class="literal">NULL</code>.
174 </p></dd><dt><span class="term"><code class="literal">restart_lsn</code> (<code class="type">text</code>)</span></dt><dd><p>
175 The replication slot's <code class="literal">restart_lsn</code>.
176 </p></dd><dt><span class="term"><code class="literal">restart_tli</code> (<code class="type">int8</code>)</span></dt><dd><p>
177 The timeline ID associated with <code class="literal">restart_lsn</code>,
178 following the current timeline history.
179 </p></dd></dl></div><p>
180 </p></dd><dt id="PROTOCOL-REPLICATION-START-REPLICATION"><span class="term"><code class="literal">START_REPLICATION</code> [ <code class="literal">SLOT</code> <em class="replaceable"><code>slot_name</code></em> ] [ <code class="literal">PHYSICAL</code> ] <em class="replaceable"><code>XXX/XXX</code></em> [ <code class="literal">TIMELINE</code> <em class="replaceable"><code>tli</code></em> ]
181 <a id="id-1.10.6.9.7.1.8.1.8" class="indexterm"></a>
182 </span> <a href="#PROTOCOL-REPLICATION-START-REPLICATION" class="id_link">#</a></dt><dd><p>
183 Instructs server to start streaming WAL, starting at
184 WAL location <em class="replaceable"><code>XXX/XXX</code></em>.
185 If <code class="literal">TIMELINE</code> option is specified,
186 streaming starts on timeline <em class="replaceable"><code>tli</code></em>;
187 otherwise, the server's current timeline is selected. The server can
188 reply with an error, for example if the requested section of WAL has already
189 been recycled. On success, the server responds with a CopyBothResponse
190 message, and then starts to stream WAL to the frontend.
192 If a slot's name is provided
193 via <em class="replaceable"><code>slot_name</code></em>, it will be updated
194 as replication progresses so that the server knows which WAL segments,
195 and if <code class="varname">hot_standby_feedback</code> is on which transactions,
196 are still needed by the standby.
198 If the client requests a timeline that's not the latest but is part of
199 the history of the server, the server will stream all the WAL on that
200 timeline starting from the requested start point up to the point where
201 the server switched to another timeline. If the client requests
202 streaming at exactly the end of an old timeline, the server skips COPY
205 After streaming all the WAL on a timeline that is not the latest one,
206 the server will end streaming by exiting the COPY mode. When the client
207 acknowledges this by also exiting COPY mode, the server sends a result
208 set with one row and two columns, indicating the next timeline in this
209 server's history. The first column is the next timeline's ID (type <code class="type">int8</code>), and the
210 second column is the WAL location where the switch happened (type <code class="type">text</code>). Usually,
211 the switch position is the end of the WAL that was streamed, but there
212 are corner cases where the server can send some WAL from the old
213 timeline that it has not itself replayed before promoting. Finally, the
214 server sends two CommandComplete messages (one that ends the CopyData
215 and the other ends the <code class="literal">START_REPLICATION</code> itself), and
216 is ready to accept a new command.
218 WAL data is sent as a series of CopyData messages;
219 see <a class="xref" href="protocol-message-types.html" title="54.6. Message Data Types">Section 54.6</a> and <a class="xref" href="protocol-message-formats.html" title="54.7. Message Formats">Section 54.7</a> for details.
220 (This allows other information to be intermixed; in particular the server can send
221 an ErrorResponse message if it encounters a failure after beginning
222 to stream.) The payload of each CopyData message from server to the
223 client contains a message of one of the following formats:
224 </p><div class="variablelist"><dl class="variablelist"><dt id="PROTOCOL-REPLICATION-XLOGDATA"><span class="term">XLogData (B)</span> <a href="#PROTOCOL-REPLICATION-XLOGDATA" class="id_link">#</a></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('w')</span></dt><dd><p>
225 Identifies the message as WAL data.
226 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
227 The starting point of the WAL data in this message.
228 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
229 The current end of WAL on the server.
230 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
231 The server's system clock at the time of transmission, as
232 microseconds since midnight on 2000-01-01.
233 </p></dd><dt><span class="term">Byte<em class="replaceable"><code>n</code></em></span></dt><dd><p>
234 A section of the WAL data stream.
236 A single WAL record is never split across two XLogData messages.
237 When a WAL record crosses a WAL page boundary, and is therefore
238 already split using continuation records, it can be split at the page
239 boundary. In other words, the first main WAL record and its
240 continuation records can be sent in different XLogData messages.
241 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-PRIMARY-KEEPALIVE-MESSAGE"><span class="term">Primary keepalive message (B)</span> <a href="#PROTOCOL-REPLICATION-PRIMARY-KEEPALIVE-MESSAGE" class="id_link">#</a></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('k')</span></dt><dd><p>
242 Identifies the message as a sender keepalive.
243 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
244 The current end of WAL on the server.
245 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
246 The server's system clock at the time of transmission, as
247 microseconds since midnight on 2000-01-01.
248 </p></dd><dt><span class="term">Byte1</span></dt><dd><p>
249 1 means that the client should reply to this message as soon as
250 possible, to avoid a timeout disconnect. 0 otherwise.
251 </p></dd></dl></div></dd></dl></div><p>
252 The receiving process can send replies back to the sender at any time,
253 using one of the following message formats (also in the payload of a
255 </p><div class="variablelist"><dl class="variablelist"><dt id="PROTOCOL-REPLICATION-STANDBY-STATUS-UPDATE"><span class="term">Standby status update (F)</span> <a href="#PROTOCOL-REPLICATION-STANDBY-STATUS-UPDATE" class="id_link">#</a></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('r')</span></dt><dd><p>
256 Identifies the message as a receiver status update.
257 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
258 The location of the last WAL byte + 1 received and written to disk
260 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
261 The location of the last WAL byte + 1 flushed to disk in
263 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
264 The location of the last WAL byte + 1 applied in the standby.
265 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
266 The client's system clock at the time of transmission, as
267 microseconds since midnight on 2000-01-01.
268 </p></dd><dt><span class="term">Byte1</span></dt><dd><p>
269 If 1, the client requests the server to reply to this message
270 immediately. This can be used to ping the server, to test if
271 the connection is still healthy.
272 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-HOT-STANDBY-FEEDBACK-MESSAGE"><span class="term">Hot standby feedback message (F)</span> <a href="#PROTOCOL-REPLICATION-HOT-STANDBY-FEEDBACK-MESSAGE" class="id_link">#</a></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('h')</span></dt><dd><p>
273 Identifies the message as a hot standby feedback message.
274 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
275 The client's system clock at the time of transmission, as
276 microseconds since midnight on 2000-01-01.
277 </p></dd><dt><span class="term">Int32</span></dt><dd><p>
278 The standby's current global <code class="literal">xmin</code>, excluding the
279 <code class="literal">catalog_xmin</code> from any replication slots. If both
280 this value and the following <code class="literal">catalog_xmin</code>
281 are 0, this is treated as a notification that hot standby feedback
282 will no longer be sent on this connection. Later non-zero messages
283 may reinitiate the feedback mechanism.
284 </p></dd><dt><span class="term">Int32</span></dt><dd><p>
285 The epoch of the global <code class="literal">xmin</code> xid on the standby.
286 </p></dd><dt><span class="term">Int32</span></dt><dd><p>
287 The lowest <code class="literal">catalog_xmin</code> of any replication
288 slots on the standby. Set to 0 if no <code class="literal">catalog_xmin</code>
289 exists on the standby or if hot standby feedback is being
291 </p></dd><dt><span class="term">Int32</span></dt><dd><p>
292 The epoch of the <code class="literal">catalog_xmin</code> xid on the standby.
293 </p></dd></dl></div></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL"><span class="term"><code class="literal">START_REPLICATION</code> <code class="literal">SLOT</code> <em class="replaceable"><code>slot_name</code></em> <code class="literal">LOGICAL</code> <em class="replaceable"><code>XXX/XXX</code></em> [ ( <em class="replaceable"><code>option_name</code></em> [ <em class="replaceable"><code>option_value</code></em> ] [, ...] ) ]</span> <a href="#PROTOCOL-REPLICATION-START-REPLICATION-SLOT-LOGICAL" class="id_link">#</a></dt><dd><p>
294 Instructs server to start streaming WAL for logical replication,
295 starting at either WAL location <em class="replaceable"><code>XXX/XXX</code></em> or the slot's
296 <code class="literal">confirmed_flush_lsn</code> (see <a class="xref" href="view-pg-replication-slots.html" title="53.20. pg_replication_slots">Section 53.20</a>), whichever is greater. This
297 behavior makes it easier for clients to avoid updating their local LSN
298 status when there is no data to process. However, starting at a
299 different LSN than requested might not catch certain kinds of client
300 errors; so the client may wish to check that
301 <code class="literal">confirmed_flush_lsn</code> matches its expectations before
302 issuing <code class="literal">START_REPLICATION</code>.
304 The server can reply with an error, for example if the
305 slot does not exist. On success, the server responds with a CopyBothResponse
306 message, and then starts to stream WAL to the frontend.
308 The messages inside the CopyBothResponse messages are of the same format
309 documented for <code class="literal">START_REPLICATION ... PHYSICAL</code>, including
310 two CommandComplete messages.
312 The output plugin associated with the selected slot is used
313 to process the output for streaming.
314 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">SLOT</code> <em class="replaceable"><code>slot_name</code></em></span></dt><dd><p>
315 The name of the slot to stream changes from. This parameter is required,
316 and must correspond to an existing logical replication slot created
317 with <code class="literal">CREATE_REPLICATION_SLOT</code> in
318 <code class="literal">LOGICAL</code> mode.
319 </p></dd><dt><span class="term"><em class="replaceable"><code>XXX/XXX</code></em></span></dt><dd><p>
320 The WAL location to begin streaming at.
321 </p></dd><dt><span class="term"><em class="replaceable"><code>option_name</code></em></span></dt><dd><p>
322 The name of an option passed to the slot's logical decoding output
323 plugin. See <a class="xref" href="protocol-logical-replication.html" title="54.5. Logical Streaming Replication Protocol">Section 54.5</a> for
324 options that are accepted by the standard (<code class="literal">pgoutput</code>)
326 </p></dd><dt><span class="term"><em class="replaceable"><code>option_value</code></em></span></dt><dd><p>
327 Optional value, in the form of a string constant, associated with the
329 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-DROP-REPLICATION-SLOT"><span class="term">
330 <code class="literal">DROP_REPLICATION_SLOT</code> <em class="replaceable"><code>slot_name</code></em> [<span class="optional"> <code class="literal">WAIT</code> </span>]
331 <a id="id-1.10.6.9.7.1.10.1.4" class="indexterm"></a>
332 </span> <a href="#PROTOCOL-REPLICATION-DROP-REPLICATION-SLOT" class="id_link">#</a></dt><dd><p>
333 Drops a replication slot, freeing any reserved server-side resources.
334 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>slot_name</code></em></span></dt><dd><p>
335 The name of the slot to drop.
336 </p></dd><dt><span class="term"><code class="literal">WAIT</code></span></dt><dd><p>
337 This option causes the command to wait if the slot is active until
338 it becomes inactive, instead of the default behavior of raising an
340 </p></dd></dl></div></dd><dt id="PROTOCOL-REPLICATION-UPLOAD-MANIFEST"><span class="term">
341 <code class="literal">UPLOAD_MANIFEST</code>
342 <a id="id-1.10.6.9.7.1.11.1.2" class="indexterm"></a>
343 </span> <a href="#PROTOCOL-REPLICATION-UPLOAD-MANIFEST" class="id_link">#</a></dt><dd><p>
344 Uploads a backup manifest in preparation for taking an incremental
346 </p></dd><dt id="PROTOCOL-REPLICATION-BASE-BACKUP"><span class="term"><code class="literal">BASE_BACKUP</code> [ ( <em class="replaceable"><code>option</code></em> [, ...] ) ]
347 <a id="id-1.10.6.9.7.1.12.1.3" class="indexterm"></a>
348 </span> <a href="#PROTOCOL-REPLICATION-BASE-BACKUP" class="id_link">#</a></dt><dd><p>
349 Instructs the server to start streaming a base backup.
350 The system will automatically be put in backup mode before the backup
351 is started, and taken out of it when the backup is complete. The
352 following options are accepted:
354 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">LABEL</code> <em class="replaceable"><code>'label'</code></em></span></dt><dd><p>
355 Sets the label of the backup. If none is specified, a backup label
356 of <code class="literal">base backup</code> will be used. The quoting rules
357 for the label are the same as a standard SQL string with
358 <a class="xref" href="runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS">standard_conforming_strings</a> turned on.
359 </p></dd><dt><span class="term"><code class="literal">TARGET</code> <em class="replaceable"><code>'target'</code></em></span></dt><dd><p>
360 Tells the server where to send the backup. If the target is
361 <code class="literal">client</code>, which is the default, the backup data is
362 sent to the client. If it is <code class="literal">server</code>, the backup
363 data is written to the server at the pathname specified by the
364 <code class="literal">TARGET_DETAIL</code> option. If it is
365 <code class="literal">blackhole</code>, the backup data is not sent
366 anywhere; it is simply discarded.
368 The <code class="literal">server</code> target requires superuser privilege or
369 being granted the <code class="literal">pg_write_server_files</code> role.
370 </p></dd><dt><span class="term"><code class="literal">TARGET_DETAIL</code> <em class="replaceable"><code>'detail'</code></em></span></dt><dd><p>
371 Provides additional information about the backup target.
373 Currently, this option can only be used when the backup target is
374 <code class="literal">server</code>. It specifies the server directory
375 to which the backup should be written.
376 </p></dd><dt><span class="term"><code class="literal">PROGRESS [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
377 If set to true, request information required to generate a progress
378 report. This will send back an approximate size in the header of each
379 tablespace, which can be used to calculate how far along the stream
380 is done. This is calculated by enumerating all the file sizes once
381 before the transfer is even started, and might as such have a
382 negative impact on the performance. In particular, it might take
383 longer before the first data
384 is streamed. Since the database files can change during the backup,
385 the size is only approximate and might both grow and shrink between
386 the time of approximation and the sending of the actual files.
387 The default is false.
388 </p></dd><dt><span class="term"><code class="literal">CHECKPOINT { 'fast' | 'spread' }</code></span></dt><dd><p>
389 Sets the type of checkpoint to be performed at the beginning of the
390 base backup. The default is <code class="literal">spread</code>.
391 </p></dd><dt><span class="term"><code class="literal">WAL [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
392 If set to true, include the necessary WAL segments in the backup.
393 This will include all the files between start and stop backup in the
394 <code class="filename">pg_wal</code> directory of the base directory tar
395 file. The default is false.
396 </p></dd><dt><span class="term"><code class="literal">WAIT [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
397 If set to true, the backup will wait until the last required WAL
398 segment has been archived, or emit a warning if WAL archiving is
399 not enabled. If false, the backup will neither wait nor warn,
400 leaving the client responsible for ensuring the required log is
401 available. The default is true.
402 </p></dd><dt><span class="term"><code class="literal">COMPRESSION</code> <em class="replaceable"><code>'method'</code></em></span></dt><dd><p>
403 Instructs the server to compress the backup using the specified
404 method. Currently, the supported methods are <code class="literal">gzip</code>,
405 <code class="literal">lz4</code>, and <code class="literal">zstd</code>.
406 </p></dd><dt><span class="term"><code class="literal">COMPRESSION_DETAIL</code> <em class="replaceable"><code>detail</code></em></span></dt><dd><p>
407 Specifies details for the chosen compression method. This should only
408 be used in conjunction with the <code class="literal">COMPRESSION</code>
409 option. If the value is an integer, it specifies the compression
410 level. Otherwise, it should be a comma-separated list of items,
411 each of the form <em class="replaceable"><code>keyword</code></em> or
412 <em class="replaceable"><code>keyword=value</code></em>. Currently, the supported
413 keywords are <code class="literal">level</code>, <code class="literal">long</code> and
414 <code class="literal">workers</code>.
416 The <code class="literal">level</code> keyword sets the compression level.
417 For <code class="literal">gzip</code> the compression level should be an
418 integer between <code class="literal">1</code> and <code class="literal">9</code>
419 (default <code class="literal">Z_DEFAULT_COMPRESSION</code> or
420 <code class="literal">-1</code>), for <code class="literal">lz4</code> an integer
421 between 1 and 12 (default <code class="literal">0</code> for fast compression
422 mode), and for <code class="literal">zstd</code> an integer between
423 <code class="literal">ZSTD_minCLevel()</code> (usually <code class="literal">-131072</code>)
424 and <code class="literal">ZSTD_maxCLevel()</code> (usually <code class="literal">22</code>),
425 (default <code class="literal">ZSTD_CLEVEL_DEFAULT</code> or
426 <code class="literal">3</code>).
428 The <code class="literal">long</code> keyword enables long-distance matching
429 mode, for improved compression ratio, at the expense of higher memory
430 use. Long-distance mode is supported only for
431 <code class="literal">zstd</code>.
433 The <code class="literal">workers</code> keyword sets the number of threads
434 that should be used for parallel compression. Parallel compression
435 is supported only for <code class="literal">zstd</code>.
436 </p></dd><dt><span class="term"><code class="literal">MAX_RATE</code> <em class="replaceable"><code>rate</code></em></span></dt><dd><p>
437 Limit (throttle) the maximum amount of data transferred from server
438 to client per unit of time. The expected unit is kilobytes per second.
439 If this option is specified, the value must either be equal to zero
440 or it must fall within the range from 32 kB through 1 GB (inclusive).
441 If zero is passed or the option is not specified, no restriction is
442 imposed on the transfer.
443 </p></dd><dt><span class="term"><code class="literal">TABLESPACE_MAP [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
444 If true, include information about symbolic links present in the
445 directory <code class="filename">pg_tblspc</code> in a file named
446 <code class="filename">tablespace_map</code>. The tablespace map file includes
447 each symbolic link name as it exists in the directory
448 <code class="filename">pg_tblspc/</code> and the full path of that symbolic link.
449 The default is false.
450 </p></dd><dt><span class="term"><code class="literal">VERIFY_CHECKSUMS [ <em class="replaceable"><code>boolean</code></em> ]</code></span></dt><dd><p>
451 If true, checksums are verified during a base backup if they are
452 enabled. If false, this is skipped. The default is true.
453 </p></dd><dt><span class="term"><code class="literal">MANIFEST</code> <em class="replaceable"><code>manifest_option</code></em></span></dt><dd><p>
454 When this option is specified with a value of <code class="literal">yes</code>
455 or <code class="literal">force-encode</code>, a backup manifest is created
456 and sent along with the backup. The manifest is a list of every
457 file present in the backup with the exception of any WAL files that
458 may be included. It also stores the size, last modification time, and
459 optionally a checksum for each file.
460 A value of <code class="literal">force-encode</code> forces all filenames
461 to be hex-encoded; otherwise, this type of encoding is performed only
462 for files whose names are non-UTF8 octet sequences.
463 <code class="literal">force-encode</code> is intended primarily for testing
464 purposes, to be sure that clients which read the backup manifest
465 can handle this case. For compatibility with previous releases,
466 the default is <code class="literal">MANIFEST 'no'</code>.
467 </p></dd><dt><span class="term"><code class="literal">MANIFEST_CHECKSUMS</code> <em class="replaceable"><code>checksum_algorithm</code></em></span></dt><dd><p>
468 Specifies the checksum algorithm that should be applied to each file included
469 in the backup manifest. Currently, the available
470 algorithms are <code class="literal">NONE</code>, <code class="literal">CRC32C</code>,
471 <code class="literal">SHA224</code>, <code class="literal">SHA256</code>,
472 <code class="literal">SHA384</code>, and <code class="literal">SHA512</code>.
473 The default is <code class="literal">CRC32C</code>.
474 </p></dd><dt><span class="term"><code class="literal">INCREMENTAL</code></span></dt><dd><p>
475 Requests an incremental backup. The
476 <code class="literal">UPLOAD_MANIFEST</code> command must be executed
477 before running a base backup with this option.
478 </p></dd></dl></div><p>
480 When the backup is started, the server will first send two
481 ordinary result sets, followed by one or more CopyOutResponse
484 The first ordinary result set contains the starting position of the
485 backup, in a single row with two columns. The first column contains
486 the start position given in XLogRecPtr format, and the second column
487 contains the corresponding timeline ID.
489 The second ordinary result set has one row for each tablespace.
490 The fields in this row are:
492 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">spcoid</code> (<code class="type">oid</code>)</span></dt><dd><p>
493 The OID of the tablespace, or null if it's the base
495 </p></dd><dt><span class="term"><code class="literal">spclocation</code> (<code class="type">text</code>)</span></dt><dd><p>
496 The full path of the tablespace directory, or null
497 if it's the base directory.
498 </p></dd><dt><span class="term"><code class="literal">size</code> (<code class="type">int8</code>)</span></dt><dd><p>
499 The approximate size of the tablespace, in kilobytes (1024 bytes),
500 if progress report has been requested; otherwise it's null.
501 </p></dd></dl></div><p>
503 After the second regular result set, a CopyOutResponse will be sent.
504 The payload of each CopyData message will contain a message in one of
505 the following formats:
506 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term">new archive (B)</span></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('n')</span></dt><dd><p>
507 Identifies the message as indicating the start of a new archive.
508 There will be one archive for the main data directory and one
509 for each additional tablespace; each will use tar format
510 (following the <span class="quote">“<span class="quote">ustar interchange format</span>”</span> specified
511 in the POSIX 1003.1-2008 standard).
512 </p></dd><dt><span class="term">String</span></dt><dd><p>
513 The file name for this archive.
514 </p></dd><dt><span class="term">String</span></dt><dd><p>
515 For the main data directory, an empty string. For other
516 tablespaces, the full path to the directory from which this
518 </p></dd></dl></div></dd><dt><span class="term">manifest (B)</span></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('m')</span></dt><dd><p>
519 Identifies the message as indicating the start of the backup
521 </p></dd></dl></div></dd><dt><span class="term">archive or manifest data (B)</span></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('d')</span></dt><dd><p>
522 Identifies the message as containing archive or manifest data.
523 </p></dd><dt><span class="term">Byte<em class="replaceable"><code>n</code></em></span></dt><dd><p>
525 </p></dd></dl></div></dd><dt><span class="term">progress report (B)</span></dt><dd><div class="variablelist"><dl class="variablelist"><dt><span class="term">Byte1('p')</span></dt><dd><p>
526 Identifies the message as a progress report.
527 </p></dd><dt><span class="term">Int64</span></dt><dd><p>
528 The number of bytes from the current tablespace for which
529 processing has been completed.
530 </p></dd></dl></div></dd></dl></div><p>
531 After the CopyOutResponse, or all such responses, have been sent, a
532 final ordinary result set will be sent, containing the WAL end position
533 of the backup, in the same format as the start position.
535 The tar archive for the data directory and each tablespace will contain
536 all files in the directories, regardless of whether they are
537 <span class="productname">PostgreSQL</span> files or other files added to the same
538 directory. The only excluded files are:
540 </p><div class="itemizedlist"><ul class="itemizedlist compact" style="list-style-type: bullet; "><li class="listitem" style="list-style-type: disc"><p>
541 <code class="filename">postmaster.pid</code>
542 </p></li><li class="listitem" style="list-style-type: disc"><p>
543 <code class="filename">postmaster.opts</code>
544 </p></li><li class="listitem" style="list-style-type: disc"><p>
545 <code class="filename">pg_internal.init</code> (found in multiple directories)
546 </p></li><li class="listitem" style="list-style-type: disc"><p>
547 Various temporary files and directories created during the operation
548 of the PostgreSQL server, such as any file or directory beginning
549 with <code class="filename">pgsql_tmp</code> and temporary relations.
550 </p></li><li class="listitem" style="list-style-type: disc"><p>
551 Unlogged relations, except for the init fork which is required to
552 recreate the (empty) unlogged relation on recovery.
553 </p></li><li class="listitem" style="list-style-type: disc"><p>
554 <code class="filename">pg_wal</code>, including subdirectories. If the backup is run
555 with WAL files included, a synthesized version of <code class="filename">pg_wal</code> will be
556 included, but it will only contain the files necessary for the
557 backup to work, not the rest of the contents.
558 </p></li><li class="listitem" style="list-style-type: disc"><p>
559 <code class="filename">pg_dynshmem</code>, <code class="filename">pg_notify</code>,
560 <code class="filename">pg_replslot</code>, <code class="filename">pg_serial</code>,
561 <code class="filename">pg_snapshots</code>, <code class="filename">pg_stat_tmp</code>, and
562 <code class="filename">pg_subtrans</code> are copied as empty directories (even if
563 they are symbolic links).
564 </p></li><li class="listitem" style="list-style-type: disc"><p>
565 Files other than regular files and directories, such as symbolic
566 links (other than for the directories listed above) and special
567 device and operating system files, are skipped. (Symbolic links
568 in <code class="filename">pg_tblspc</code> are maintained.)
569 </p></li></ul></div><p>
570 Owner, group, and file mode are set if the underlying file system on
571 the server supports it.
572 </p></dd></dl></div><p>
574 In all the above commands,
575 when specifying a parameter of type <code class="type">boolean</code> the
576 <em class="replaceable"><code>value</code></em> part can be omitted,
577 which is equivalent to specifying <code class="literal">TRUE</code>.
578 </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sasl-authentication.html" title="54.3. SASL Authentication">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="protocol.html" title="Chapter 54. Frontend/Backend Protocol">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="protocol-logical-replication.html" title="54.5. Logical Streaming Replication Protocol">Next</a></td></tr><tr><td width="40%" align="left" valign="top">54.3. SASL Authentication </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 54.5. Logical Streaming Replication Protocol</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob