Debian Codegraph @

doc/src/sgml/high-availability.sgml

raw
1 <!-- doc/src/sgml/high-availability.sgml -->
2
3 <chapter id="high-availability">
4 <title>High Availability, Load Balancing, and Replication</title>
5
6 <indexterm><primary>high availability</primary></indexterm>
7 <indexterm><primary>failover</primary></indexterm>
8 <indexterm><primary>replication</primary></indexterm>
9 <indexterm><primary>load balancing</primary></indexterm>
10 <indexterm><primary>clustering</primary></indexterm>
11 <indexterm><primary>data partitioning</primary></indexterm>
12
13 <para>
14 Database servers can work together to allow a second server to
15 take over quickly if the primary server fails (high
16 availability), or to allow several computers to serve the same
17 data (load balancing). Ideally, database servers could work
18 together seamlessly. Web servers serving static web pages can
19 be combined quite easily by merely load-balancing web requests
20 to multiple machines. In fact, read-only database servers can
21 be combined relatively easily too. Unfortunately, most database
22 servers have a read/write mix of requests, and read/write servers
23 are much harder to combine. This is because though read-only
24 data needs to be placed on each server only once, a write to any
25 server has to be propagated to all servers so that future read
26 requests to those servers return consistent results.
27 </para>
28
29 <para>
30 This synchronization problem is the fundamental difficulty for
31 servers working together. Because there is no single solution
32 that eliminates the impact of the sync problem for all use cases,
33 there are multiple solutions. Each solution addresses this
34 problem in a different way, and minimizes its impact for a specific
35 workload.
36 </para>
37
38 <para>
39 Some solutions deal with synchronization by allowing only one
40 server to modify the data. Servers that can modify data are
41 called read/write, <firstterm>master</firstterm> or <firstterm>primary</firstterm> servers.
42 Servers that track changes in the primary are called <firstterm>standby</firstterm>
43 or <firstterm>secondary</firstterm> servers. A standby server that cannot be connected
44 to until it is promoted to a primary server is called a <firstterm>warm
45 standby</firstterm> server, and one that can accept connections and serves read-only
46 queries is called a <firstterm>hot standby</firstterm> server.
47 </para>
48
49 <para>
50 Some solutions are synchronous,
51 meaning that a data-modifying transaction is not considered
52 committed until all servers have committed the transaction. This
53 guarantees that a failover will not lose any data and that all
54 load-balanced servers will return consistent results no matter
55 which server is queried. In contrast, asynchronous solutions allow some
56 delay between the time of a commit and its propagation to the other servers,
57 opening the possibility that some transactions might be lost in
58 the switch to a backup server, and that load balanced servers
59 might return slightly stale results. Asynchronous communication
60 is used when synchronous would be too slow.
61 </para>
62
63 <para>
64 Solutions can also be categorized by their granularity. Some solutions
65 can deal only with an entire database server, while others allow control
66 at the per-table or per-database level.
67 </para>
68
69 <para>
70 Performance must be considered in any choice. There is usually a
71 trade-off between functionality and
72 performance. For example, a fully synchronous solution over a slow
73 network might cut performance by more than half, while an asynchronous
74 one might have a minimal performance impact.
75 </para>
76
77 <para>
78 The remainder of this section outlines various failover, replication,
79 and load balancing solutions.
80 </para>
81
82 <sect1 id="different-replication-solutions">
83 <title>Comparison of Different Solutions</title>
84
85 <variablelist>
86
87 <varlistentry>
88 <term>Shared Disk Failover</term>
89 <listitem>
90
91 <para>
92 Shared disk failover avoids synchronization overhead by having only one
93 copy of the database. It uses a single disk array that is shared by
94 multiple servers. If the main database server fails, the standby server
95 is able to mount and start the database as though it were recovering from
96 a database crash. This allows rapid failover with no data loss.
97 </para>
98
99 <para>
100 Shared hardware functionality is common in network storage devices.
101 Using a network file system is also possible, though care must be
102 taken that the file system has full <acronym>POSIX</acronym> behavior (see <xref
103 linkend="creating-cluster-nfs"/>). One significant limitation of this
104 method is that if the shared disk array fails or becomes corrupt, the
105 primary and standby servers are both nonfunctional. Another issue is
106 that the standby server should never access the shared storage while
107 the primary server is running.
108 </para>
109
110 </listitem>
111 </varlistentry>
112
113 <varlistentry>
114 <term>File System (Block Device) Replication</term>
115 <listitem>
116
117 <para>
118 A modified version of shared hardware functionality is file system
119 replication, where all changes to a file system are mirrored to a file
120 system residing on another computer. The only restriction is that
121 the mirroring must be done in a way that ensures the standby server
122 has a consistent copy of the file system &mdash; specifically, writes
123 to the standby must be done in the same order as those on the primary.
124 <productname>DRBD</productname> is a popular file system replication solution
125 for Linux.
126 </para>
127
128 <!--
129 https://forge.continuent.org/pipermail/sequoia/2006-November/004070.html
130
131 Oracle RAC is a shared disk approach and just send cache invalidations
132 to other nodes but not actual data. As the disk is shared, data is
133 only committed once to disk and there is a distributed locking
134 protocol to make nodes agree on a serializable transactional order.
135 -->
136
137 </listitem>
138 </varlistentry>
139
140 <varlistentry>
141 <term>Write-Ahead Log Shipping</term>
142 <listitem>
143
144 <para>
145 Warm and hot standby servers can be kept current by reading a
146 stream of write-ahead log (<acronym>WAL</acronym>)
147 records. If the main server fails, the standby contains
148 almost all of the data of the main server, and can be quickly
149 made the new primary database server. This can be synchronous or
150 asynchronous and can only be done for the entire database server.
151 </para>
152 <para>
153 A standby server can be implemented using file-based log shipping
154 (<xref linkend="warm-standby"/>) or streaming replication (see
155 <xref linkend="streaming-replication"/>), or a combination of both. For
156 information on hot standby, see <xref linkend="hot-standby"/>.
157 </para>
158 </listitem>
159 </varlistentry>
160
161 <varlistentry>
162 <term>Logical Replication</term>
163 <listitem>
164 <para>
165 Logical replication allows a database server to send a stream of data
166 modifications to another server. <productname>PostgreSQL</productname>
167 logical replication constructs a stream of logical data modifications
168 from the WAL. Logical replication allows replication of data changes on
169 a per-table basis. In addition, a server that is publishing its own
170 changes can also subscribe to changes from another server, allowing data
171 to flow in multiple directions. For more information on logical
172 replication, see <xref linkend="logical-replication"/>. Through the
173 logical decoding interface (<xref linkend="logicaldecoding"/>),
174 third-party extensions can also provide similar functionality.
175 </para>
176 </listitem>
177 </varlistentry>
178
179 <varlistentry>
180 <term>Trigger-Based Primary-Standby Replication</term>
181 <listitem>
182
183 <para>
184 A trigger-based replication setup typically funnels data modification
185 queries to a designated primary server. Operating on a per-table basis,
186 the primary server sends data changes (typically) asynchronously to the
187 standby servers. Standby servers can answer queries while the primary is
188 running, and may allow some local data changes or write activity. This
189 form of replication is often used for offloading large analytical or data
190 warehouse queries.
191 </para>
192
193 <para>
194 <productname>Slony-I</productname> is an example of this type of
195 replication, with per-table granularity, and support for multiple standby
196 servers. Because it updates the standby server asynchronously (in
197 batches), there is possible data loss during fail over.
198 </para>
199 </listitem>
200 </varlistentry>
201
202 <varlistentry>
203 <term>SQL-Based Replication Middleware</term>
204 <listitem>
205
206 <para>
207 With SQL-based replication middleware, a program intercepts
208 every SQL query and sends it to one or all servers. Each server
209 operates independently. Read-write queries must be sent to all servers,
210 so that every server receives any changes. But read-only queries can be
211 sent to just one server, allowing the read workload to be distributed
212 among them.
213 </para>
214
215 <para>
216 If queries are simply broadcast unmodified, functions like
217 <function>random()</function>, <function>CURRENT_TIMESTAMP</function>, and
218 sequences can have different values on different servers.
219 This is because each server operates independently, and because
220 SQL queries are broadcast rather than actual data changes. If
221 this is unacceptable, either the middleware or the application
222 must determine such values from a single source and then use those
223 values in write queries. Care must also be taken that all
224 transactions either commit or abort on all servers, perhaps
225 using two-phase commit (<xref linkend="sql-prepare-transaction"/>
226 and <xref linkend="sql-commit-prepared"/>).
227 <productname>Pgpool-II</productname> and <productname>Continuent Tungsten</productname>
228 are examples of this type of replication.
229 </para>
230 </listitem>
231 </varlistentry>
232
233 <varlistentry>
234 <term>Asynchronous Multimaster Replication</term>
235 <listitem>
236
237 <para>
238 For servers that are not regularly connected or have slow
239 communication links, like laptops or
240 remote servers, keeping data consistent among servers is a
241 challenge. Using asynchronous multimaster replication, each
242 server works independently, and periodically communicates with
243 the other servers to identify conflicting transactions. The
244 conflicts can be resolved by users or conflict resolution rules.
245 Bucardo is an example of this type of replication.
246 </para>
247 </listitem>
248 </varlistentry>
249
250 <varlistentry>
251 <term>Synchronous Multimaster Replication</term>
252 <listitem>
253
254 <para>
255 In synchronous multimaster replication, each server can accept
256 write requests, and modified data is transmitted from the
257 original server to every other server before each transaction
258 commits. Heavy write activity can cause excessive locking and
259 commit delays, leading to poor performance. Read requests can
260 be sent to any server. Some implementations use shared disk
261 to reduce the communication overhead. Synchronous multimaster
262 replication is best for mostly read workloads, though its big
263 advantage is that any server can accept write requests &mdash;
264 there is no need to partition workloads between primary and
265 standby servers, and because the data changes are sent from one
266 server to another, there is no problem with non-deterministic
267 functions like <function>random()</function>.
268 </para>
269
270 <para>
271 <productname>PostgreSQL</productname> does not offer this type of replication,
272 though <productname>PostgreSQL</productname> two-phase commit (<xref
273 linkend="sql-prepare-transaction"/> and <xref
274 linkend="sql-commit-prepared"/>)
275 can be used to implement this in application code or middleware.
276 </para>
277 </listitem>
278 </varlistentry>
279
280 </variablelist>
281
282 <para>
283 <xref linkend="high-availability-matrix"/> summarizes
284 the capabilities of the various solutions listed above.
285 </para>
286
287 <table id="high-availability-matrix">
288 <title>High Availability, Load Balancing, and Replication Feature Matrix</title>
289 <tgroup cols="9">
290 <colspec colname="col1" colwidth="1.1*"/>
291 <colspec colname="col2" colwidth="1*"/>
292 <colspec colname="col3" colwidth="1*"/>
293 <colspec colname="col4" colwidth="1*"/>
294 <colspec colname="col5" colwidth="1*"/>
295 <colspec colname="col6" colwidth="1*"/>
296 <colspec colname="col7" colwidth="1*"/>
297 <colspec colname="col8" colwidth="1*"/>
298 <colspec colname="col9" colwidth="1*"/>
299 <thead>
300 <row>
301 <entry>Feature</entry>
302 <entry>Shared Disk</entry>
303 <entry>File System Repl.</entry>
304 <entry>Write-Ahead Log Shipping</entry>
305 <entry>Logical Repl.</entry>
306 <entry>Trigger-&zwsp;Based Repl.</entry>
307 <entry>SQL Repl. Middle-ware</entry>
308 <entry>Async. MM Repl.</entry>
309 <entry>Sync. MM Repl.</entry>
310 </row>
311 </thead>
312
313 <tbody>
314
315 <row>
316 <entry>Popular examples</entry>
317 <entry align="center">NAS</entry>
318 <entry align="center">DRBD</entry>
319 <entry align="center">built-in streaming repl.</entry>
320 <entry align="center">built-in logical repl., pglogical</entry>
321 <entry align="center">Londiste, Slony</entry>
322 <entry align="center">pgpool-II</entry>
323 <entry align="center">Bucardo</entry>
324 <entry align="center"></entry>
325 </row>
326
327 <row>
328 <entry>Comm. method</entry>
329 <entry align="center">shared disk</entry>
330 <entry align="center">disk blocks</entry>
331 <entry align="center">WAL</entry>
332 <entry align="center">logical decoding</entry>
333 <entry align="center">table rows</entry>
334 <entry align="center">SQL</entry>
335 <entry align="center">table rows</entry>
336 <entry align="center">table rows and row locks</entry>
337 </row>
338
339 <row>
340 <entry>No special hardware required</entry>
341 <entry align="center"></entry>
342 <entry align="center">&bull;</entry>
343 <entry align="center">&bull;</entry>
344 <entry align="center">&bull;</entry>
345 <entry align="center">&bull;</entry>
346 <entry align="center">&bull;</entry>
347 <entry align="center">&bull;</entry>
348 <entry align="center">&bull;</entry>
349 </row>
350
351 <row>
352 <entry>Allows multiple primary servers</entry>
353 <entry align="center"></entry>
354 <entry align="center"></entry>
355 <entry align="center"></entry>
356 <entry align="center">&bull;</entry>
357 <entry align="center"></entry>
358 <entry align="center">&bull;</entry>
359 <entry align="center">&bull;</entry>
360 <entry align="center">&bull;</entry>
361 </row>
362
363 <row>
364 <entry>No overhead on primary</entry>
365 <entry align="center">&bull;</entry>
366 <entry align="center"></entry>
367 <entry align="center">&bull;</entry>
368 <entry align="center">&bull;</entry>
369 <entry align="center"></entry>
370 <entry align="center">&bull;</entry>
371 <entry align="center"></entry>
372 <entry align="center"></entry>
373 </row>
374
375 <row>
376 <entry>No waiting for multiple servers</entry>
377 <entry align="center">&bull;</entry>
378 <entry align="center"></entry>
379 <entry align="center">with sync off</entry>
380 <entry align="center">with sync off</entry>
381 <entry align="center">&bull;</entry>
382 <entry align="center"></entry>
383 <entry align="center">&bull;</entry>
384 <entry align="center"></entry>
385 </row>
386
387 <row>
388 <entry>Primary failure will never lose data</entry>
389 <entry align="center">&bull;</entry>
390 <entry align="center">&bull;</entry>
391 <entry align="center">with sync on</entry>
392 <entry align="center">with sync on</entry>
393 <entry align="center"></entry>
394 <entry align="center">&bull;</entry>
395 <entry align="center"></entry>
396 <entry align="center">&bull;</entry>
397 </row>
398
399 <row>
400 <entry>Replicas accept read-only queries</entry>
401 <entry align="center"></entry>
402 <entry align="center"></entry>
403 <entry align="center">with hot standby</entry>
404 <entry align="center">&bull;</entry>
405 <entry align="center">&bull;</entry>
406 <entry align="center">&bull;</entry>
407 <entry align="center">&bull;</entry>
408 <entry align="center">&bull;</entry>
409 </row>
410
411 <row>
412 <entry>Per-table granularity</entry>
413 <entry align="center"></entry>
414 <entry align="center"></entry>
415 <entry align="center"></entry>
416 <entry align="center">&bull;</entry>
417 <entry align="center">&bull;</entry>
418 <entry align="center"></entry>
419 <entry align="center">&bull;</entry>
420 <entry align="center">&bull;</entry>
421 </row>
422
423 <row>
424 <entry>No conflict resolution necessary</entry>
425 <entry align="center">&bull;</entry>
426 <entry align="center">&bull;</entry>
427 <entry align="center">&bull;</entry>
428 <entry align="center"></entry>
429 <entry align="center">&bull;</entry>
430 <entry align="center">&bull;</entry>
431 <entry align="center"></entry>
432 <entry align="center">&bull;</entry>
433 </row>
434
435 </tbody>
436 </tgroup>
437 </table>
438
439 <para>
440 There are a few solutions that do not fit into the above categories:
441 </para>
442
443 <variablelist>
444
445 <varlistentry>
446 <term>Data Partitioning</term>
447 <listitem>
448
449 <para>
450 Data partitioning splits tables into data sets. Each set can
451 be modified by only one server. For example, data can be
452 partitioned by offices, e.g., London and Paris, with a server
453 in each office. If queries combining London and Paris data
454 are necessary, an application can query both servers, or
455 primary/standby replication can be used to keep a read-only copy
456 of the other office's data on each server.
457 </para>
458 </listitem>
459 </varlistentry>
460
461 <varlistentry>
462 <term>Multiple-Server Parallel Query Execution</term>
463 <listitem>
464
465 <para>
466 Many of the above solutions allow multiple servers to handle multiple
467 queries, but none allow a single query to use multiple servers to
468 complete faster. This solution allows multiple servers to work
469 concurrently on a single query. It is usually accomplished by
470 splitting the data among servers and having each server execute its
471 part of the query and return results to a central server where they
472 are combined and returned to the user. This can be implemented using the
473 <productname>PL/Proxy</productname> tool set.
474 </para>
475
476 </listitem>
477 </varlistentry>
478
479 </variablelist>
480
481 <para>
482 It should also be noted that because <productname>PostgreSQL</productname>
483 is open source and easily extended, a number of companies have
484 taken <productname>PostgreSQL</productname> and created commercial
485 closed-source solutions with unique failover, replication, and load
486 balancing capabilities. These are not discussed here.
487 </para>
488
489 </sect1>
490
491
492 <sect1 id="warm-standby">
493 <title>Log-Shipping Standby Servers</title>
494
495
496 <para>
497 Continuous archiving can be used to create a <firstterm>high
498 availability</firstterm> (HA) cluster configuration with one or more
499 <firstterm>standby servers</firstterm> ready to take over operations if the
500 primary server fails. This capability is widely referred to as
501 <firstterm>warm standby</firstterm> or <firstterm>log shipping</firstterm>.
502 </para>
503
504 <para>
505 The primary and standby server work together to provide this capability,
506 though the servers are only loosely coupled. The primary server operates
507 in continuous archiving mode, while each standby server operates in
508 continuous recovery mode, reading the WAL files from the primary. No
509 changes to the database tables are required to enable this capability,
510 so it offers low administration overhead compared to some other
511 replication solutions. This configuration also has relatively low
512 performance impact on the primary server.
513 </para>
514
515 <para>
516 Directly moving WAL records from one database server to another
517 is typically described as log shipping. <productname>PostgreSQL</productname>
518 implements file-based log shipping by transferring WAL records
519 one file (WAL segment) at a time. WAL files (16MB) can be
520 shipped easily and cheaply over any distance, whether it be to an
521 adjacent system, another system at the same site, or another system on
522 the far side of the globe. The bandwidth required for this technique
523 varies according to the transaction rate of the primary server.
524 Record-based log shipping is more granular and streams WAL changes
525 incrementally over a network connection (see <xref
526 linkend="streaming-replication"/>).
527 </para>
528
529 <para>
530 It should be noted that log shipping is asynchronous, i.e., the WAL
531 records are shipped after transaction commit. As a result, there is a
532 window for data loss should the primary server suffer a catastrophic
533 failure; transactions not yet shipped will be lost. The size of the
534 data loss window in file-based log shipping can be limited by use of the
535 <varname>archive_timeout</varname> parameter, which can be set as low
536 as a few seconds. However such a low setting will
537 substantially increase the bandwidth required for file shipping.
538 Streaming replication (see <xref linkend="streaming-replication"/>)
539 allows a much smaller window of data loss.
540 </para>
541
542 <para>
543 Recovery performance is sufficiently good that the standby will
544 typically be only moments away from full
545 availability once it has been activated. As a result, this is called
546 a warm standby configuration which offers high
547 availability. Restoring a server from an archived base backup and
548 rollforward will take considerably longer, so that technique only
549 offers a solution for disaster recovery, not high availability.
550 A standby server can also be used for read-only queries, in which case
551 it is called a <firstterm>hot standby</firstterm> server. See
552 <xref linkend="hot-standby"/> for more information.
553 </para>
554
555 <indexterm zone="high-availability">
556 <primary>warm standby</primary>
557 </indexterm>
558
559 <indexterm zone="high-availability">
560 <primary>PITR standby</primary>
561 </indexterm>
562
563 <indexterm zone="high-availability">
564 <primary>standby server</primary>
565 </indexterm>
566
567 <indexterm zone="high-availability">
568 <primary>log shipping</primary>
569 </indexterm>
570
571 <indexterm zone="high-availability">
572 <primary>witness server</primary>
573 </indexterm>
574
575 <indexterm zone="high-availability">
576 <primary>STONITH</primary>
577 </indexterm>
578
579 <sect2 id="standby-planning">
580 <title>Planning</title>
581
582 <para>
583 It is usually wise to create the primary and standby servers
584 so that they are as similar as possible, at least from the
585 perspective of the database server. In particular, the path names
586 associated with tablespaces will be passed across unmodified, so both
587 primary and standby servers must have the same mount paths for
588 tablespaces if that feature is used. Keep in mind that if
589 <xref linkend="sql-createtablespace"/>
590 is executed on the primary, any new mount point needed for it must
591 be created on the primary and all standby servers before the command
592 is executed. Hardware need not be exactly the same, but experience shows
593 that maintaining two identical systems is easier than maintaining two
594 dissimilar ones over the lifetime of the application and system.
595 In any case the hardware architecture must be the same &mdash; shipping
596 from, say, a 32-bit to a 64-bit system will not work.
597 </para>
598
599 <para>
600 In general, log shipping between servers running different major
601 <productname>PostgreSQL</productname> release
602 levels is not possible. It is the policy of the PostgreSQL Global
603 Development Group not to make changes to disk formats during minor release
604 upgrades, so it is likely that running different minor release levels
605 on primary and standby servers will work successfully. However, no
606 formal support for that is offered and you are advised to keep primary
607 and standby servers at the same release level as much as possible.
608 When updating to a new minor release, the safest policy is to update
609 the standby servers first &mdash; a new minor release is more likely
610 to be able to read WAL files from a previous minor release than vice
611 versa.
612 </para>
613
614 </sect2>
615
616 <sect2 id="standby-server-operation" xreflabel="Standby Server Operation">
617 <title>Standby Server Operation</title>
618
619 <para>
620 A server enters standby mode if a
621 <anchor id="file-standby-signal" xreflabel="standby.signal"/>
622 <filename>standby.signal</filename>
623 <indexterm><primary><filename>standby.signal</filename></primary></indexterm>
624 file exists in the data directory when the server is started.
625 </para>
626
627 <para>
628 In standby mode, the server continuously applies WAL received from the
629 primary server. The standby server can read WAL from a WAL archive
630 (see <xref linkend="guc-restore-command"/>) or directly from the primary
631 over a TCP connection (streaming replication). The standby server will
632 also attempt to restore any WAL found in the standby cluster's
633 <filename>pg_wal</filename> directory. That typically happens after a server
634 restart, when the standby replays again WAL that was streamed from the
635 primary before the restart, but you can also manually copy files to
636 <filename>pg_wal</filename> at any time to have them replayed.
637 </para>
638
639 <para>
640 At startup, the standby begins by restoring all WAL available in the
641 archive location, calling <varname>restore_command</varname>. Once it
642 reaches the end of WAL available there and <varname>restore_command</varname>
643 fails, it tries to restore any WAL available in the <filename>pg_wal</filename> directory.
644 If that fails, and streaming replication has been configured, the
645 standby tries to connect to the primary server and start streaming WAL
646 from the last valid record found in archive or <filename>pg_wal</filename>. If that fails
647 or streaming replication is not configured, or if the connection is
648 later disconnected, the standby goes back to step 1 and tries to
649 restore the file from the archive again. This loop of retries from the
650 archive, <filename>pg_wal</filename>, and via streaming replication goes on until the server
651 is stopped or is promoted.
652 </para>
653
654 <para>
655 Standby mode is exited and the server switches to normal operation
656 when <command>pg_ctl promote</command> is run, or
657 <function>pg_promote()</function> is called. Before failover,
658 any WAL immediately available in the archive or in <filename>pg_wal</filename>
659 will be restored, but no attempt is made to connect to the primary.
660 </para>
661 </sect2>
662
663 <sect2 id="preparing-primary-for-standby">
664 <title>Preparing the Primary for Standby Servers</title>
665
666 <para>
667 Set up continuous archiving on the primary to an archive directory
668 accessible from the standby, as described
669 in <xref linkend="continuous-archiving"/>. The archive location should be
670 accessible from the standby even when the primary is down, i.e., it should
671 reside on the standby server itself or another trusted server, not on
672 the primary server.
673 </para>
674
675 <para>
676 If you want to use streaming replication, set up authentication on the
677 primary server to allow replication connections from the standby
678 server(s); that is, create a role and provide a suitable entry or
679 entries in <filename>pg_hba.conf</filename> with the database field set to
680 <literal>replication</literal>. Also ensure <varname>max_wal_senders</varname> is set
681 to a sufficiently large value in the configuration file of the primary
682 server. If replication slots will be used,
683 ensure that <varname>max_replication_slots</varname> is set sufficiently
684 high as well.
685 </para>
686
687 <para>
688 Take a base backup as described in <xref linkend="backup-base-backup"/>
689 to bootstrap the standby server.
690 </para>
691 </sect2>
692
693 <sect2 id="standby-server-setup">
694 <title>Setting Up a Standby Server</title>
695
696 <para>
697 To set up the standby server, restore the base backup taken from primary
698 server (see <xref linkend="backup-pitr-recovery"/>). Create a file
699 <link linkend="file-standby-signal"><filename>standby.signal</filename></link><indexterm><primary>standby.signal</primary></indexterm>
700 in the standby's cluster data
701 directory. Set <xref linkend="guc-restore-command"/> to a simple command to copy files from
702 the WAL archive. If you plan to have multiple standby servers for high
703 availability purposes, make sure that <varname>recovery_target_timeline</varname> is set to
704 <literal>latest</literal> (the default), to make the standby server follow the timeline change
705 that occurs at failover to another standby.
706 </para>
707
708 <note>
709 <para>
710 <xref linkend="guc-restore-command"/> should return immediately
711 if the file does not exist; the server will retry the command again if
712 necessary.
713 </para>
714 </note>
715
716 <para>
717 If you want to use streaming replication, fill in
718 <xref linkend="guc-primary-conninfo"/> with a libpq connection string, including
719 the host name (or IP address) and any additional details needed to
720 connect to the primary server. If the primary needs a password for
721 authentication, the password needs to be specified in
722 <xref linkend="guc-primary-conninfo"/> as well.
723 </para>
724
725 <para>
726 If you're setting up the standby server for high availability purposes,
727 set up WAL archiving, connections and authentication like the primary
728 server, because the standby server will work as a primary server after
729 failover.
730 </para>
731
732 <para>
733 If you're using a WAL archive, its size can be minimized using the <xref
734 linkend="guc-archive-cleanup-command"/> parameter to remove files that are no
735 longer required by the standby server.
736 The <application>pg_archivecleanup</application> utility is designed specifically to
737 be used with <varname>archive_cleanup_command</varname> in typical single-standby
738 configurations, see <xref linkend="pgarchivecleanup"/>.
739 Note however, that if you're using the archive for backup purposes, you
740 need to retain files needed to recover from at least the latest base
741 backup, even if they're no longer needed by the standby.
742 </para>
743
744 <para>
745 A simple example of configuration is:
746 <programlisting>
747 primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass options=''-c wal_sender_timeout=5000'''
748 restore_command = 'cp /path/to/archive/%f %p'
749 archive_cleanup_command = 'pg_archivecleanup /path/to/archive %r'
750 </programlisting>
751 </para>
752
753 <para>
754 You can have any number of standby servers, but if you use streaming
755 replication, make sure you set <varname>max_wal_senders</varname> high enough in
756 the primary to allow them to be connected simultaneously.
757 </para>
758
759 </sect2>
760
761 <sect2 id="streaming-replication">
762 <title>Streaming Replication</title>
763
764 <indexterm zone="high-availability">
765 <primary>Streaming Replication</primary>
766 </indexterm>
767
768 <para>
769 Streaming replication allows a standby server to stay more up-to-date
770 than is possible with file-based log shipping. The standby connects
771 to the primary, which streams WAL records to the standby as they're
772 generated, without waiting for the WAL file to be filled.
773 </para>
774
775 <para>
776 Streaming replication is asynchronous by default
777 (see <xref linkend="synchronous-replication"/>), in which case there is
778 a small delay between committing a transaction in the primary and the
779 changes becoming visible in the standby. This delay is however much
780 smaller than with file-based log shipping, typically under one second
781 assuming the standby is powerful enough to keep up with the load. With
782 streaming replication, <varname>archive_timeout</varname> is not required to
783 reduce the data loss window.
784 </para>
785
786 <para>
787 If you use streaming replication without file-based continuous
788 archiving, the server might recycle old WAL segments before the standby
789 has received them. If this occurs, the standby will need to be
790 reinitialized from a new base backup. You can avoid this by setting
791 <varname>wal_keep_size</varname> to a value large enough to ensure that
792 WAL segments are not recycled too early, or by configuring a replication
793 slot for the standby. If you set up a WAL archive that's accessible from
794 the standby, these solutions are not required, since the standby can
795 always use the archive to catch up provided it retains enough segments.
796 </para>
797
798 <para>
799 To use streaming replication, set up a file-based log-shipping standby
800 server as described in <xref linkend="warm-standby"/>. The step that
801 turns a file-based log-shipping standby into streaming replication
802 standby is setting the <varname>primary_conninfo</varname> setting
803 to point to the primary server. Set
804 <xref linkend="guc-listen-addresses"/> and authentication options
805 (see <filename>pg_hba.conf</filename>) on the primary so that the standby server
806 can connect to the <literal>replication</literal> pseudo-database on the primary
807 server (see <xref linkend="streaming-replication-authentication"/>).
808 </para>
809
810 <para>
811 On systems that support the keepalive socket option, setting
812 <xref linkend="guc-tcp-keepalives-idle"/>,
813 <xref linkend="guc-tcp-keepalives-interval"/> and
814 <xref linkend="guc-tcp-keepalives-count"/> helps the primary promptly
815 notice a broken connection.
816 </para>
817
818 <para>
819 Set the maximum number of concurrent connections from the standby servers
820 (see <xref linkend="guc-max-wal-senders"/> for details).
821 </para>
822
823 <para>
824 When the standby is started and <varname>primary_conninfo</varname> is set
825 correctly, the standby will connect to the primary after replaying all
826 WAL files available in the archive. If the connection is established
827 successfully, you will see a <literal>walreceiver</literal> in the standby, and
828 a corresponding <literal>walsender</literal> process in the primary.
829 </para>
830
831 <sect3 id="streaming-replication-authentication">
832 <title>Authentication</title>
833 <para>
834 It is very important that the access privileges for replication be set up
835 so that only trusted users can read the WAL stream, because it is
836 easy to extract privileged information from it. Standby servers must
837 authenticate to the primary as an account that has the
838 <literal>REPLICATION</literal> privilege or a superuser. It is
839 recommended to create a dedicated user account with
840 <literal>REPLICATION</literal> and <literal>LOGIN</literal>
841 privileges for replication. While <literal>REPLICATION</literal>
842 privilege gives very high permissions, it does not allow the user to
843 modify any data on the primary system, which the
844 <literal>SUPERUSER</literal> privilege does.
845 </para>
846
847 <para>
848 Client authentication for replication is controlled by a
849 <filename>pg_hba.conf</filename> record specifying <literal>replication</literal> in the
850 <replaceable>database</replaceable> field. For example, if the standby is running on
851 host IP <literal>192.168.1.100</literal> and the account name for replication
852 is <literal>foo</literal>, the administrator can add the following line to the
853 <filename>pg_hba.conf</filename> file on the primary:
854
855 <programlisting>
856 # Allow the user "foo" from host 192.168.1.100 to connect to the primary
857 # as a replication standby if the user's password is correctly supplied.
858 #
859 # TYPE DATABASE USER ADDRESS METHOD
860 host replication foo 192.168.1.100/32 scram-sha-256
861 </programlisting>
862 </para>
863 <para>
864 The host name and port number of the primary, connection user name,
865 and password are specified in the <xref linkend="guc-primary-conninfo"/>.
866 The password can also be set in the <filename>~/.pgpass</filename> file on the
867 standby (specify <literal>replication</literal> in the <replaceable>database</replaceable>
868 field).
869 For example, if the primary is running on host IP <literal>192.168.1.50</literal>,
870 port <literal>5432</literal>, the account name for replication is
871 <literal>foo</literal>, and the password is <literal>foopass</literal>, the administrator
872 can add the following line to the <filename>postgresql.conf</filename> file on the
873 standby:
874
875 <programlisting>
876 # The standby connects to the primary that is running on host 192.168.1.50
877 # and port 5432 as the user "foo" whose password is "foopass".
878 primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
879 </programlisting>
880 </para>
881 </sect3>
882
883 <sect3 id="streaming-replication-monitoring">
884 <title>Monitoring</title>
885 <para>
886 An important health indicator of streaming replication is the amount
887 of WAL records generated in the primary, but not yet applied in the
888 standby. You can calculate this lag by comparing the current WAL write
889 location on the primary with the last WAL location received by the
890 standby. These locations can be retrieved using
891 <function>pg_current_wal_lsn</function> on the primary and
892 <function>pg_last_wal_receive_lsn</function> on the standby,
893 respectively (see <xref linkend="functions-admin-backup-table"/> and
894 <xref linkend="functions-recovery-info-table"/> for details).
895 The last WAL receive location in the standby is also displayed in the
896 process status of the WAL receiver process, displayed using the
897 <command>ps</command> command (see <xref linkend="monitoring-ps"/> for details).
898 </para>
899 <para>
900 You can retrieve a list of WAL sender processes via the
901 <link linkend="monitoring-pg-stat-replication-view"><structname>
902 pg_stat_replication</structname></link> view. Large differences between
903 <function>pg_current_wal_lsn</function> and the view's <literal>sent_lsn</literal> field
904 might indicate that the primary server is under heavy load, while
905 differences between <literal>sent_lsn</literal> and
906 <function>pg_last_wal_receive_lsn</function> on the standby might indicate
907 network delay, or that the standby is under heavy load.
908 </para>
909 <para>
910 On a hot standby, the status of the WAL receiver process can be retrieved
911 via the <link linkend="monitoring-pg-stat-wal-receiver-view">
912 <structname>pg_stat_wal_receiver</structname></link> view. A large
913 difference between <function>pg_last_wal_replay_lsn</function> and the
914 view's <literal>flushed_lsn</literal> indicates that WAL is being
915 received faster than it can be replayed.
916 </para>
917 </sect3>
918 </sect2>
919
920 <sect2 id="streaming-replication-slots">
921 <title>Replication Slots</title>
922 <indexterm>
923 <primary>replication slot</primary>
924 <secondary>streaming replication</secondary>
925 </indexterm>
926 <para>
927 Replication slots provide an automated way to ensure that the
928 primary server does
929 not remove WAL segments until they have been received by all standbys,
930 and that the primary does not remove rows which could cause a
931 <link linkend="hot-standby-conflict">recovery conflict</link> even when the
932 standby is disconnected.
933 </para>
934 <para>
935 In lieu of using replication slots, it is possible to prevent the removal
936 of old WAL segments using <xref linkend="guc-wal-keep-size"/>, or by
937 storing the segments in an archive using
938 <xref linkend="guc-archive-command"/> or <xref linkend="guc-archive-library"/>.
939 A disadvantage of these methods is that they
940 often result in retaining more WAL segments than
941 required, whereas replication slots retain only the number of segments
942 known to be needed.
943 </para>
944 <para>
945 Similarly, <xref linkend="guc-hot-standby-feedback"/> on its own, without
946 also using a replication slot, provides protection against relevant rows
947 being removed by vacuum, but provides no protection during any time period
948 when the standby is not connected.
949 </para>
950
951 <caution>
952 <para>
953 Beware that replication slots can cause the server to retain so
954 many WAL segments that they fill up the space allocated for
955 <literal>pg_wal</literal>.
956 <xref linkend="guc-max-slot-wal-keep-size"/> can be used to limit the size
957 of WAL files retained by replication slots.
958 </para>
959 </caution>
960
961 <sect3 id="streaming-replication-slots-manipulation">
962 <title>Querying and Manipulating Replication Slots</title>
963 <para>
964 Each replication slot has a name, which can contain lower-case letters,
965 numbers, and the underscore character.
966 </para>
967 <para>
968 Existing replication slots and their state can be seen in the
969 <link linkend="view-pg-replication-slots"><structname>pg_replication_slots</structname></link>
970 view.
971 </para>
972 <para>
973 Slots can be created and dropped either via the streaming replication
974 protocol (see <xref linkend="protocol-replication"/>) or via SQL
975 functions (see <xref linkend="functions-replication"/>).
976 </para>
977 </sect3>
978 <sect3 id="streaming-replication-slots-config">
979 <title>Configuration Example</title>
980 <para>
981 You can create a replication slot like this:
982 <programlisting>
983 postgres=# SELECT * FROM pg_create_physical_replication_slot('node_a_slot');
984 slot_name | lsn
985 -------------+-----
986 node_a_slot |
987
988 postgres=# SELECT slot_name, slot_type, active FROM pg_replication_slots;
989 slot_name | slot_type | active
990 -------------+-----------+--------
991 node_a_slot | physical | f
992 (1 row)
993 </programlisting>
994 To configure the standby to use this slot, <varname>primary_slot_name</varname>
995 should be configured on the standby. Here is a simple example:
996 <programlisting>
997 primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass'
998 primary_slot_name = 'node_a_slot'
999 </programlisting>
1000 </para>
1001 </sect3>
1002 </sect2>
1003
1004 <sect2 id="cascading-replication">
1005 <title>Cascading Replication</title>
1006
1007 <indexterm zone="high-availability">
1008 <primary>Cascading Replication</primary>
1009 </indexterm>
1010
1011 <para>
1012 The cascading replication feature allows a standby server to accept replication
1013 connections and stream WAL records to other standbys, acting as a relay.
1014 This can be used to reduce the number of direct connections to the primary
1015 and also to minimize inter-site bandwidth overheads.
1016 </para>
1017
1018 <para>
1019 A standby acting as both a receiver and a sender is known as a cascading
1020 standby. Standbys that are more directly connected to the primary are known
1021 as upstream servers, while those standby servers further away are downstream
1022 servers. Cascading replication does not place limits on the number or
1023 arrangement of downstream servers, though each standby connects to only
1024 one upstream server which eventually links to a single primary server.
1025 </para>
1026
1027 <para>
1028 A cascading standby sends not only WAL records received from the
1029 primary but also those restored from the archive. So even if the replication
1030 connection in some upstream connection is terminated, streaming replication
1031 continues downstream for as long as new WAL records are available.
1032 </para>
1033
1034 <para>
1035 Cascading replication is currently asynchronous. Synchronous replication
1036 (see <xref linkend="synchronous-replication"/>) settings have no effect on
1037 cascading replication at present.
1038 </para>
1039
1040 <para>
1041 Hot standby feedback propagates upstream, whatever the cascaded arrangement.
1042 </para>
1043
1044 <para>
1045 If an upstream standby server is promoted to become the new primary, downstream
1046 servers will continue to stream from the new primary if
1047 <varname>recovery_target_timeline</varname> is set to <literal>'latest'</literal> (the default).
1048 </para>
1049
1050 <para>
1051 To use cascading replication, set up the cascading standby so that it can
1052 accept replication connections (that is, set
1053 <xref linkend="guc-max-wal-senders"/> and <xref linkend="guc-hot-standby"/>,
1054 and configure
1055 <link linkend="auth-pg-hba-conf">host-based authentication</link>).
1056 You will also need to set <varname>primary_conninfo</varname> in the downstream
1057 standby to point to the cascading standby.
1058 </para>
1059 </sect2>
1060
1061 <sect2 id="synchronous-replication">
1062 <title>Synchronous Replication</title>
1063
1064 <indexterm zone="high-availability">
1065 <primary>Synchronous Replication</primary>
1066 </indexterm>
1067
1068 <para>
1069 <productname>PostgreSQL</productname> streaming replication is asynchronous by
1070 default. If the primary server
1071 crashes then some transactions that were committed may not have been
1072 replicated to the standby server, causing data loss. The amount
1073 of data loss is proportional to the replication delay at the time of
1074 failover.
1075 </para>
1076
1077 <para>
1078 Synchronous replication offers the ability to confirm that all changes
1079 made by a transaction have been transferred to one or more synchronous
1080 standby servers. This extends that standard level of durability
1081 offered by a transaction commit. This level of protection is referred
1082 to as 2-safe replication in computer science theory, and group-1-safe
1083 (group-safe and 1-safe) when <varname>synchronous_commit</varname> is set to
1084 <literal>remote_write</literal>.
1085 </para>
1086
1087 <para>
1088 When requesting synchronous replication, each commit of a
1089 write transaction will wait until confirmation is
1090 received that the commit has been written to the write-ahead log on disk
1091 of both the primary and standby server. The only possibility that data
1092 can be lost is if both the primary and the standby suffer crashes at the
1093 same time. This can provide a much higher level of durability, though only
1094 if the sysadmin is cautious about the placement and management of the two
1095 servers. Waiting for confirmation increases the user's confidence that the
1096 changes will not be lost in the event of server crashes but it also
1097 necessarily increases the response time for the requesting transaction.
1098 The minimum wait time is the round-trip time between primary and standby.
1099 </para>
1100
1101 <para>
1102 Read-only transactions and transaction rollbacks need not wait for
1103 replies from standby servers. Subtransaction commits do not wait for
1104 responses from standby servers, only top-level commits. Long
1105 running actions such as data loading or index building do not wait
1106 until the very final commit message. All two-phase commit actions
1107 require commit waits, including both prepare and commit.
1108 </para>
1109
1110 <para>
1111 A synchronous standby can be a physical replication standby or a logical
1112 replication subscriber. It can also be any other physical or logical WAL
1113 replication stream consumer that knows how to send the appropriate
1114 feedback messages. Besides the built-in physical and logical replication
1115 systems, this includes special programs such
1116 as <command>pg_receivewal</command> and <command>pg_recvlogical</command>
1117 as well as some third-party replication systems and custom programs.
1118 Check the respective documentation for details on synchronous replication
1119 support.
1120 </para>
1121
1122 <sect3 id="synchronous-replication-config">
1123 <title>Basic Configuration</title>
1124
1125 <para>
1126 Once streaming replication has been configured, configuring synchronous
1127 replication requires only one additional configuration step:
1128 <xref linkend="guc-synchronous-standby-names"/> must be set to
1129 a non-empty value. <varname>synchronous_commit</varname> must also be set to
1130 <literal>on</literal>, but since this is the default value, typically no change is
1131 required. (See <xref linkend="runtime-config-wal-settings"/> and
1132 <xref linkend="runtime-config-replication-primary"/>.)
1133 This configuration will cause each commit to wait for
1134 confirmation that the standby has written the commit record to durable
1135 storage.
1136 <varname>synchronous_commit</varname> can be set by individual
1137 users, so it can be configured in the configuration file, for particular
1138 users or databases, or dynamically by applications, in order to control
1139 the durability guarantee on a per-transaction basis.
1140 </para>
1141
1142 <para>
1143 After a commit record has been written to disk on the primary, the
1144 WAL record is then sent to the standby. The standby sends reply
1145 messages each time a new batch of WAL data is written to disk, unless
1146 <varname>wal_receiver_status_interval</varname> is set to zero on the standby.
1147 In the case that <varname>synchronous_commit</varname> is set to
1148 <literal>remote_apply</literal>, the standby sends reply messages when the commit
1149 record is replayed, making the transaction visible.
1150 If the standby is chosen as a synchronous standby, according to the setting
1151 of <varname>synchronous_standby_names</varname> on the primary, the reply
1152 messages from that standby will be considered along with those from other
1153 synchronous standbys to decide when to release transactions waiting for
1154 confirmation that the commit record has been received. These parameters
1155 allow the administrator to specify which standby servers should be
1156 synchronous standbys. Note that the configuration of synchronous
1157 replication is mainly on the primary. Named standbys must be directly
1158 connected to the primary; the primary knows nothing about downstream
1159 standby servers using cascaded replication.
1160 </para>
1161
1162 <para>
1163 Setting <varname>synchronous_commit</varname> to <literal>remote_write</literal> will
1164 cause each commit to wait for confirmation that the standby has received
1165 the commit record and written it out to its own operating system, but not
1166 for the data to be flushed to disk on the standby. This
1167 setting provides a weaker guarantee of durability than <literal>on</literal>
1168 does: the standby could lose the data in the event of an operating system
1169 crash, though not a <productname>PostgreSQL</productname> crash.
1170 However, it's a useful setting in practice
1171 because it can decrease the response time for the transaction.
1172 Data loss could only occur if both the primary and the standby crash and
1173 the database of the primary gets corrupted at the same time.
1174 </para>
1175
1176 <para>
1177 Setting <varname>synchronous_commit</varname> to <literal>remote_apply</literal> will
1178 cause each commit to wait until the current synchronous standbys report
1179 that they have replayed the transaction, making it visible to user
1180 queries. In simple cases, this allows for load balancing with causal
1181 consistency.
1182 </para>
1183
1184 <para>
1185 Users will stop waiting if a fast shutdown is requested. However, as
1186 when using asynchronous replication, the server will not fully
1187 shutdown until all outstanding WAL records are transferred to the currently
1188 connected standby servers.
1189 </para>
1190
1191 </sect3>
1192
1193 <sect3 id="synchronous-replication-multiple-standbys">
1194 <title>Multiple Synchronous Standbys</title>
1195
1196 <para>
1197 Synchronous replication supports one or more synchronous standby servers;
1198 transactions will wait until all the standby servers which are considered
1199 as synchronous confirm receipt of their data. The number of synchronous
1200 standbys that transactions must wait for replies from is specified in
1201 <varname>synchronous_standby_names</varname>. This parameter also specifies
1202 a list of standby names and the method (<literal>FIRST</literal> and
1203 <literal>ANY</literal>) to choose synchronous standbys from the listed ones.
1204 </para>
1205 <para>
1206 The method <literal>FIRST</literal> specifies a priority-based synchronous
1207 replication and makes transaction commits wait until their WAL records are
1208 replicated to the requested number of synchronous standbys chosen based on
1209 their priorities. The standbys whose names appear earlier in the list are
1210 given higher priority and will be considered as synchronous. Other standby
1211 servers appearing later in this list represent potential synchronous
1212 standbys. If any of the current synchronous standbys disconnects for
1213 whatever reason, it will be replaced immediately with the
1214 next-highest-priority standby.
1215 </para>
1216 <para>
1217 An example of <varname>synchronous_standby_names</varname> for
1218 a priority-based multiple synchronous standbys is:
1219 <programlisting>
1220 synchronous_standby_names = 'FIRST 2 (s1, s2, s3)'
1221 </programlisting>
1222 In this example, if four standby servers <literal>s1</literal>, <literal>s2</literal>,
1223 <literal>s3</literal> and <literal>s4</literal> are running, the two standbys
1224 <literal>s1</literal> and <literal>s2</literal> will be chosen as synchronous standbys
1225 because their names appear early in the list of standby names.
1226 <literal>s3</literal> is a potential synchronous standby and will take over
1227 the role of synchronous standby when either of <literal>s1</literal> or
1228 <literal>s2</literal> fails. <literal>s4</literal> is an asynchronous standby since
1229 its name is not in the list.
1230 </para>
1231 <para>
1232 The method <literal>ANY</literal> specifies a quorum-based synchronous
1233 replication and makes transaction commits wait until their WAL records
1234 are replicated to <emphasis>at least</emphasis> the requested number of
1235 synchronous standbys in the list.
1236 </para>
1237 <para>
1238 An example of <varname>synchronous_standby_names</varname> for
1239 a quorum-based multiple synchronous standbys is:
1240 <programlisting>
1241 synchronous_standby_names = 'ANY 2 (s1, s2, s3)'
1242 </programlisting>
1243 In this example, if four standby servers <literal>s1</literal>, <literal>s2</literal>,
1244 <literal>s3</literal> and <literal>s4</literal> are running, transaction commits will
1245 wait for replies from at least any two standbys of <literal>s1</literal>,
1246 <literal>s2</literal> and <literal>s3</literal>. <literal>s4</literal> is an asynchronous
1247 standby since its name is not in the list.
1248 </para>
1249 <para>
1250 The synchronous states of standby servers can be viewed using
1251 the <structname>pg_stat_replication</structname> view.
1252 </para>
1253 </sect3>
1254
1255 <sect3 id="synchronous-replication-performance">
1256 <title>Planning for Performance</title>
1257
1258 <para>
1259 Synchronous replication usually requires carefully planned and placed
1260 standby servers to ensure applications perform acceptably. Waiting
1261 doesn't utilize system resources, but transaction locks continue to be
1262 held until the transfer is confirmed. As a result, incautious use of
1263 synchronous replication will reduce performance for database
1264 applications because of increased response times and higher contention.
1265 </para>
1266
1267 <para>
1268 <productname>PostgreSQL</productname> allows the application developer
1269 to specify the durability level required via replication. This can be
1270 specified for the system overall, though it can also be specified for
1271 specific users or connections, or even individual transactions.
1272 </para>
1273
1274 <para>
1275 For example, an application workload might consist of:
1276 10% of changes are important customer details, while
1277 90% of changes are less important data that the business can more
1278 easily survive if it is lost, such as chat messages between users.
1279 </para>
1280
1281 <para>
1282 With synchronous replication options specified at the application level
1283 (on the primary) we can offer synchronous replication for the most
1284 important changes, without slowing down the bulk of the total workload.
1285 Application level options are an important and practical tool for allowing
1286 the benefits of synchronous replication for high performance applications.
1287 </para>
1288
1289 <para>
1290 You should consider that the network bandwidth must be higher than
1291 the rate of generation of WAL data.
1292 </para>
1293
1294 </sect3>
1295
1296 <sect3 id="synchronous-replication-ha">
1297 <title>Planning for High Availability</title>
1298
1299 <para>
1300 <varname>synchronous_standby_names</varname> specifies the number and
1301 names of synchronous standbys that transaction commits made when
1302 <varname>synchronous_commit</varname> is set to <literal>on</literal>,
1303 <literal>remote_apply</literal> or <literal>remote_write</literal> will wait for
1304 responses from. Such transaction commits may never be completed
1305 if any one of the synchronous standbys should crash.
1306 </para>
1307
1308 <para>
1309 The best solution for high availability is to ensure you keep as many
1310 synchronous standbys as requested. This can be achieved by naming multiple
1311 potential synchronous standbys using <varname>synchronous_standby_names</varname>.
1312 </para>
1313
1314 <para>
1315 In a priority-based synchronous replication, the standbys whose names
1316 appear earlier in the list will be used as synchronous standbys.
1317 Standbys listed after these will take over the role of synchronous standby
1318 if one of current ones should fail.
1319 </para>
1320
1321 <para>
1322 In a quorum-based synchronous replication, all the standbys appearing
1323 in the list will be used as candidates for synchronous standbys.
1324 Even if one of them should fail, the other standbys will keep performing
1325 the role of candidates of synchronous standby.
1326 </para>
1327
1328 <para>
1329 When a standby first attaches to the primary, it will not yet be properly
1330 synchronized. This is described as <literal>catchup</literal> mode. Once
1331 the lag between standby and primary reaches zero for the first time
1332 we move to real-time <literal>streaming</literal> state.
1333 The catch-up duration may be long immediately after the standby has
1334 been created. If the standby is shut down, then the catch-up period
1335 will increase according to the length of time the standby has been down.
1336 The standby is only able to become a synchronous standby
1337 once it has reached <literal>streaming</literal> state.
1338 This state can be viewed using
1339 the <structname>pg_stat_replication</structname> view.
1340 </para>
1341
1342 <para>
1343 If primary restarts while commits are waiting for acknowledgment, those
1344 waiting transactions will be marked fully committed once the primary
1345 database recovers.
1346 There is no way to be certain that all standbys have received all
1347 outstanding WAL data at time of the crash of the primary. Some
1348 transactions may not show as committed on the standby, even though
1349 they show as committed on the primary. The guarantee we offer is that
1350 the application will not receive explicit acknowledgment of the
1351 successful commit of a transaction until the WAL data is known to be
1352 safely received by all the synchronous standbys.
1353 </para>
1354
1355 <para>
1356 If you really cannot keep as many synchronous standbys as requested
1357 then you should decrease the number of synchronous standbys that
1358 transaction commits must wait for responses from
1359 in <varname>synchronous_standby_names</varname> (or disable it) and
1360 reload the configuration file on the primary server.
1361 </para>
1362
1363 <para>
1364 If the primary is isolated from remaining standby servers you should
1365 fail over to the best candidate of those other remaining standby servers.
1366 </para>
1367
1368 <para>
1369 If you need to re-create a standby server while transactions are
1370 waiting, make sure that the functions <function>pg_backup_start()</function>
1371 and <function>pg_backup_stop()</function> are run in a session with
1372 <varname>synchronous_commit</varname> = <literal>off</literal>, otherwise those
1373 requests will wait forever for the standby to appear.
1374 </para>
1375
1376 </sect3>
1377 </sect2>
1378
1379 <sect2 id="continuous-archiving-in-standby">
1380 <title>Continuous Archiving in Standby</title>
1381
1382 <indexterm>
1383 <primary>continuous archiving</primary>
1384 <secondary>in standby</secondary>
1385 </indexterm>
1386
1387 <para>
1388 When continuous WAL archiving is used in a standby, there are two
1389 different scenarios: the WAL archive can be shared between the primary
1390 and the standby, or the standby can have its own WAL archive. When
1391 the standby has its own WAL archive, set <varname>archive_mode</varname>
1392 to <literal>always</literal>, and the standby will call the archive
1393 command for every WAL segment it receives, whether it's by restoring
1394 from the archive or by streaming replication. The shared archive can
1395 be handled similarly, but the <varname>archive_command</varname> or <varname>archive_library</varname> must
1396 test if the file being archived exists already, and if the existing file
1397 has identical contents. This requires more care in the
1398 <varname>archive_command</varname> or <varname>archive_library</varname>, as it must
1399 be careful to not overwrite an existing file with different contents,
1400 but return success if the exactly same file is archived twice. And
1401 all that must be done free of race conditions, if two servers attempt
1402 to archive the same file at the same time.
1403 </para>
1404
1405 <para>
1406 If <varname>archive_mode</varname> is set to <literal>on</literal>, the
1407 archiver is not enabled during recovery or standby mode. If the standby
1408 server is promoted, it will start archiving after the promotion, but
1409 will not archive any WAL or timeline history files that
1410 it did not generate itself. To get a complete
1411 series of WAL files in the archive, you must ensure that all WAL is
1412 archived, before it reaches the standby. This is inherently true with
1413 file-based log shipping, as the standby can only restore files that
1414 are found in the archive, but not if streaming replication is enabled.
1415 When a server is not in recovery mode, there is no difference between
1416 <literal>on</literal> and <literal>always</literal> modes.
1417 </para>
1418 </sect2>
1419 </sect1>
1420
1421 <sect1 id="warm-standby-failover">
1422 <title>Failover</title>
1423
1424 <para>
1425 If the primary server fails then the standby server should begin
1426 failover procedures.
1427 </para>
1428
1429 <para>
1430 If the standby server fails then no failover need take place. If the
1431 standby server can be restarted, even some time later, then the recovery
1432 process can also be restarted immediately, taking advantage of
1433 restartable recovery. If the standby server cannot be restarted, then a
1434 full new standby server instance should be created.
1435 </para>
1436
1437 <para>
1438 If the primary server fails and the standby server becomes the
1439 new primary, and then the old primary restarts, you must have
1440 a mechanism for informing the old primary that it is no longer the primary. This is
1441 sometimes known as <acronym>STONITH</acronym> (Shoot The Other Node In The Head), which is
1442 necessary to avoid situations where both systems think they are the
1443 primary, which will lead to confusion and ultimately data loss.
1444 </para>
1445
1446 <para>
1447 Many failover systems use just two systems, the primary and the standby,
1448 connected by some kind of heartbeat mechanism to continually verify the
1449 connectivity between the two and the viability of the primary. It is
1450 also possible to use a third system (called a witness server) to prevent
1451 some cases of inappropriate failover, but the additional complexity
1452 might not be worthwhile unless it is set up with sufficient care and
1453 rigorous testing.
1454 </para>
1455
1456 <para>
1457 <productname>PostgreSQL</productname> does not provide the system
1458 software required to identify a failure on the primary and notify
1459 the standby database server. Many such tools exist and are well
1460 integrated with the operating system facilities required for
1461 successful failover, such as IP address migration.
1462 </para>
1463
1464 <para>
1465 Once failover to the standby occurs, there is only a
1466 single server in operation. This is known as a degenerate state.
1467 The former standby is now the primary, but the former primary is down
1468 and might stay down. To return to normal operation, a standby server
1469 must be recreated,
1470 either on the former primary system when it comes up, or on a third,
1471 possibly new, system. The <xref linkend="app-pgrewind"/> utility can be
1472 used to speed up this process on large clusters.
1473 Once complete, the primary and standby can be
1474 considered to have switched roles. Some people choose to use a third
1475 server to provide backup for the new primary until the new standby
1476 server is recreated,
1477 though clearly this complicates the system configuration and
1478 operational processes.
1479 </para>
1480
1481 <para>
1482 So, switching from primary to standby server can be fast but requires
1483 some time to re-prepare the failover cluster. Regular switching from
1484 primary to standby is useful, since it allows regular downtime on
1485 each system for maintenance. This also serves as a test of the
1486 failover mechanism to ensure that it will really work when you need it.
1487 Written administration procedures are advised.
1488 </para>
1489
1490 <para>
1491 If you have opted for logical replication slot synchronization (see
1492 <xref linkend="logicaldecoding-replication-slots-synchronization"/>),
1493 then before switching to the standby server, it is recommended to check
1494 if the logical slots synchronized on the standby server are ready
1495 for failover. This can be done by following the steps described in
1496 <xref linkend="logical-replication-failover"/>.
1497 </para>
1498
1499 <para>
1500 To trigger failover of a log-shipping standby server, run
1501 <command>pg_ctl promote</command> or call <function>pg_promote()</function>.
1502 If you're setting up reporting servers that are only used to offload
1503 read-only queries from the primary, not for high availability purposes,
1504 you don't need to promote.
1505 </para>
1506 </sect1>
1507
1508 <sect1 id="hot-standby">
1509 <title>Hot Standby</title>
1510
1511 <indexterm zone="high-availability">
1512 <primary>hot standby</primary>
1513 </indexterm>
1514
1515 <para>
1516 Hot standby is the term used to describe the ability to connect to
1517 the server and run read-only queries while the server is in archive
1518 recovery or standby mode. This
1519 is useful both for replication purposes and for restoring a backup
1520 to a desired state with great precision.
1521 The term hot standby also refers to the ability of the server to move
1522 from recovery through to normal operation while users continue running
1523 queries and/or keep their connections open.
1524 </para>
1525
1526 <para>
1527 Running queries in hot standby mode is similar to normal query operation,
1528 though there are several usage and administrative differences
1529 explained below.
1530 </para>
1531
1532 <sect2 id="hot-standby-users">
1533 <title>User's Overview</title>
1534
1535 <para>
1536 When the <xref linkend="guc-hot-standby"/> parameter is set to true on a
1537 standby server, it will begin accepting connections once the recovery has
1538 brought the system to a consistent state and be ready for hot standby.
1539 All such connections are
1540 strictly read-only; not even temporary tables may be written.
1541 </para>
1542
1543 <para>
1544 The data on the standby takes some time to arrive from the primary server
1545 so there will be a measurable delay between primary and standby. Running the
1546 same query nearly simultaneously on both primary and standby might therefore
1547 return differing results. We say that data on the standby is
1548 <firstterm>eventually consistent</firstterm> with the primary. Once the
1549 commit record for a transaction is replayed on the standby, the changes
1550 made by that transaction will be visible to any new snapshots taken on
1551 the standby. Snapshots may be taken at the start of each query or at the
1552 start of each transaction, depending on the current transaction isolation
1553 level. For more details, see <xref linkend="transaction-iso"/>.
1554 </para>
1555
1556 <para>
1557 Transactions started during hot standby may issue the following commands:
1558
1559 <itemizedlist>
1560 <listitem>
1561 <para>
1562 Query access: <command>SELECT</command>, <command>COPY TO</command>
1563 </para>
1564 </listitem>
1565 <listitem>
1566 <para>
1567 Cursor commands: <command>DECLARE</command>, <command>FETCH</command>, <command>CLOSE</command>
1568 </para>
1569 </listitem>
1570 <listitem>
1571 <para>
1572 Settings: <command>SHOW</command>, <command>SET</command>, <command>RESET</command>
1573 </para>
1574 </listitem>
1575 <listitem>
1576 <para>
1577 Transaction management commands:
1578 <itemizedlist>
1579 <listitem>
1580 <para>
1581 <command>BEGIN</command>, <command>END</command>, <command>ABORT</command>, <command>START TRANSACTION</command>
1582 </para>
1583 </listitem>
1584 <listitem>
1585 <para>
1586 <command>SAVEPOINT</command>, <command>RELEASE</command>, <command>ROLLBACK TO SAVEPOINT</command>
1587 </para>
1588 </listitem>
1589 <listitem>
1590 <para>
1591 <command>EXCEPTION</command> blocks and other internal subtransactions
1592 </para>
1593 </listitem>
1594 </itemizedlist>
1595 </para>
1596 </listitem>
1597 <listitem>
1598 <para>
1599 <command>LOCK TABLE</command>, though only when explicitly in one of these modes:
1600 <literal>ACCESS SHARE</literal>, <literal>ROW SHARE</literal> or <literal>ROW EXCLUSIVE</literal>.
1601 </para>
1602 </listitem>
1603 <listitem>
1604 <para>
1605 Plans and resources: <command>PREPARE</command>, <command>EXECUTE</command>,
1606 <command>DEALLOCATE</command>, <command>DISCARD</command>
1607 </para>
1608 </listitem>
1609 <listitem>
1610 <para>
1611 Plugins and extensions: <command>LOAD</command>
1612 </para>
1613 </listitem>
1614 <listitem>
1615 <para>
1616 <command>UNLISTEN</command>
1617 </para>
1618 </listitem>
1619 </itemizedlist>
1620 </para>
1621
1622 <para>
1623 Transactions started during hot standby will never be assigned a
1624 transaction ID and cannot write to the system write-ahead log.
1625 Therefore, the following actions will produce error messages:
1626
1627 <itemizedlist>
1628 <listitem>
1629 <para>
1630 Data Manipulation Language (DML): <command>INSERT</command>,
1631 <command>UPDATE</command>, <command>DELETE</command>,
1632 <command>MERGE</command>, <command>COPY FROM</command>,
1633 <command>TRUNCATE</command>.
1634 Note that there are no allowed actions that result in a trigger
1635 being executed during recovery. This restriction applies even to
1636 temporary tables, because table rows cannot be read or written without
1637 assigning a transaction ID, which is currently not possible in a
1638 hot standby environment.
1639 </para>
1640 </listitem>
1641 <listitem>
1642 <para>
1643 Data Definition Language (DDL): <command>CREATE</command>,
1644 <command>DROP</command>, <command>ALTER</command>, <command>COMMENT</command>.
1645 This restriction applies even to temporary tables, because carrying
1646 out these operations would require updating the system catalog tables.
1647 </para>
1648 </listitem>
1649 <listitem>
1650 <para>
1651 <command>SELECT ... FOR SHARE | UPDATE</command>, because row locks cannot be
1652 taken without updating the underlying data files.
1653 </para>
1654 </listitem>
1655 <listitem>
1656 <para>
1657 Rules on <command>SELECT</command> statements that generate DML commands.
1658 </para>
1659 </listitem>
1660 <listitem>
1661 <para>
1662 <command>LOCK</command> that explicitly requests a mode higher than <literal>ROW EXCLUSIVE MODE</literal>.
1663 </para>
1664 </listitem>
1665 <listitem>
1666 <para>
1667 <command>LOCK</command> in short default form, since it requests <literal>ACCESS EXCLUSIVE MODE</literal>.
1668 </para>
1669 </listitem>
1670 <listitem>
1671 <para>
1672 Transaction management commands that explicitly set non-read-only state:
1673 <itemizedlist>
1674 <listitem>
1675 <para>
1676 <command>BEGIN READ WRITE</command>,
1677 <command>START TRANSACTION READ WRITE</command>
1678 </para>
1679 </listitem>
1680 <listitem>
1681 <para>
1682 <command>SET TRANSACTION READ WRITE</command>,
1683 <command>SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE</command>
1684 </para>
1685 </listitem>
1686 <listitem>
1687 <para>
1688 <command>SET transaction_read_only = off</command>
1689 </para>
1690 </listitem>
1691 </itemizedlist>
1692 </para>
1693 </listitem>
1694 <listitem>
1695 <para>
1696 Two-phase commit commands: <command>PREPARE TRANSACTION</command>,
1697 <command>COMMIT PREPARED</command>, <command>ROLLBACK PREPARED</command>
1698 because even read-only transactions need to write WAL in the
1699 prepare phase (the first phase of two phase commit).
1700 </para>
1701 </listitem>
1702 <listitem>
1703 <para>
1704 Sequence updates: <function>nextval()</function>, <function>setval()</function>
1705 </para>
1706 </listitem>
1707 <listitem>
1708 <para>
1709 <command>LISTEN</command>, <command>NOTIFY</command>
1710 </para>
1711 </listitem>
1712 </itemizedlist>
1713 </para>
1714
1715 <para>
1716 In normal operation, <quote>read-only</quote> transactions are allowed to
1717 use <command>LISTEN</command> and <command>NOTIFY</command>,
1718 so hot standby sessions operate under slightly tighter
1719 restrictions than ordinary read-only sessions. It is possible that some
1720 of these restrictions might be loosened in a future release.
1721 </para>
1722
1723 <para>
1724 During hot standby, the parameter <varname>transaction_read_only</varname> is always
1725 true and may not be changed. But as long as no attempt is made to modify
1726 the database, connections during hot standby will act much like any other
1727 database connection. If failover or switchover occurs, the database will
1728 switch to normal processing mode. Sessions will remain connected while the
1729 server changes mode. Once hot standby finishes, it will be possible to
1730 initiate read-write transactions (even from a session begun during
1731 hot standby).
1732 </para>
1733
1734 <para>
1735 Users can determine whether hot standby is currently active for their
1736 session by issuing <command>SHOW in_hot_standby</command>.
1737 (In server versions before 14, the <varname>in_hot_standby</varname>
1738 parameter did not exist; a workable substitute method for older servers
1739 is <command>SHOW transaction_read_only</command>.) In addition, a set of
1740 functions (<xref linkend="functions-recovery-info-table"/>) allow users to
1741 access information about the standby server. These allow you to write
1742 programs that are aware of the current state of the database. These
1743 can be used to monitor the progress of recovery, or to allow you to
1744 write complex programs that restore the database to particular states.
1745 </para>
1746 </sect2>
1747
1748 <sect2 id="hot-standby-conflict">
1749 <title>Handling Query Conflicts</title>
1750
1751 <para>
1752 The primary and standby servers are in many ways loosely connected. Actions
1753 on the primary will have an effect on the standby. As a result, there is
1754 potential for negative interactions or conflicts between them. The easiest
1755 conflict to understand is performance: if a huge data load is taking place
1756 on the primary then this will generate a similar stream of WAL records on the
1757 standby, so standby queries may contend for system resources, such as I/O.
1758 </para>
1759
1760 <para>
1761 There are also additional types of conflict that can occur with hot standby.
1762 These conflicts are <emphasis>hard conflicts</emphasis> in the sense that queries
1763 might need to be canceled and, in some cases, sessions disconnected to resolve them.
1764 The user is provided with several ways to handle these
1765 conflicts. Conflict cases include:
1766
1767 <itemizedlist>
1768 <listitem>
1769 <para>
1770 Access Exclusive locks taken on the primary server, including both
1771 explicit <command>LOCK</command> commands and various <acronym>DDL</acronym>
1772 actions, conflict with table accesses in standby queries.
1773 </para>
1774 </listitem>
1775 <listitem>
1776 <para>
1777 Dropping a tablespace on the primary conflicts with standby queries
1778 using that tablespace for temporary work files.
1779 </para>
1780 </listitem>
1781 <listitem>
1782 <para>
1783 Dropping a database on the primary conflicts with sessions connected
1784 to that database on the standby.
1785 </para>
1786 </listitem>
1787 <listitem>
1788 <para>
1789 Application of a vacuum cleanup record from WAL conflicts with
1790 standby transactions whose snapshots can still <quote>see</quote> any of
1791 the rows to be removed.
1792 </para>
1793 </listitem>
1794 <listitem>
1795 <para>
1796 Application of a vacuum cleanup record from WAL conflicts with
1797 queries accessing the target page on the standby, whether or not
1798 the data to be removed is visible.
1799 </para>
1800 </listitem>
1801 </itemizedlist>
1802 </para>
1803
1804 <para>
1805 On the primary server, these cases simply result in waiting; and the
1806 user might choose to cancel either of the conflicting actions. However,
1807 on the standby there is no choice: the WAL-logged action already occurred
1808 on the primary so the standby must not fail to apply it. Furthermore,
1809 allowing WAL application to wait indefinitely may be very undesirable,
1810 because the standby's state will become increasingly far behind the
1811 primary's. Therefore, a mechanism is provided to forcibly cancel standby
1812 queries that conflict with to-be-applied WAL records.
1813 </para>
1814
1815 <para>
1816 An example of the problem situation is an administrator on the primary
1817 server running <command>DROP TABLE</command> on a table that is currently being
1818 queried on the standby server. Clearly the standby query cannot continue
1819 if the <command>DROP TABLE</command> is applied on the standby. If this situation
1820 occurred on the primary, the <command>DROP TABLE</command> would wait until the
1821 other query had finished. But when <command>DROP TABLE</command> is run on the
1822 primary, the primary doesn't have information about what queries are
1823 running on the standby, so it will not wait for any such standby
1824 queries. The WAL change records come through to the standby while the
1825 standby query is still running, causing a conflict. The standby server
1826 must either delay application of the WAL records (and everything after
1827 them, too) or else cancel the conflicting query so that the <command>DROP
1828 TABLE</command> can be applied.
1829 </para>
1830
1831 <para>
1832 When a conflicting query is short, it's typically desirable to allow it to
1833 complete by delaying WAL application for a little bit; but a long delay in
1834 WAL application is usually not desirable. So the cancel mechanism has
1835 parameters, <xref linkend="guc-max-standby-archive-delay"/> and <xref
1836 linkend="guc-max-standby-streaming-delay"/>, that define the maximum
1837 allowed delay in WAL application. Conflicting queries will be canceled
1838 once it has taken longer than the relevant delay setting to apply any
1839 newly-received WAL data. There are two parameters so that different delay
1840 values can be specified for the case of reading WAL data from an archive
1841 (i.e., initial recovery from a base backup or <quote>catching up</quote> a
1842 standby server that has fallen far behind) versus reading WAL data via
1843 streaming replication.
1844 </para>
1845
1846 <para>
1847 In a standby server that exists primarily for high availability, it's
1848 best to set the delay parameters relatively short, so that the server
1849 cannot fall far behind the primary due to delays caused by standby
1850 queries. However, if the standby server is meant for executing
1851 long-running queries, then a high or even infinite delay value may be
1852 preferable. Keep in mind however that a long-running query could
1853 cause other sessions on the standby server to not see recent changes
1854 on the primary, if it delays application of WAL records.
1855 </para>
1856
1857 <para>
1858 Once the delay specified by <varname>max_standby_archive_delay</varname> or
1859 <varname>max_standby_streaming_delay</varname> has been exceeded, conflicting
1860 queries will be canceled. This usually results just in a cancellation
1861 error, although in the case of replaying a <command>DROP DATABASE</command>
1862 the entire conflicting session will be terminated. Also, if the conflict
1863 is over a lock held by an idle transaction, the conflicting session is
1864 terminated (this behavior might change in the future).
1865 </para>
1866
1867 <para>
1868 Canceled queries may be retried immediately (after beginning a new
1869 transaction, of course). Since query cancellation depends on
1870 the nature of the WAL records being replayed, a query that was
1871 canceled may well succeed if it is executed again.
1872 </para>
1873
1874 <para>
1875 Keep in mind that the delay parameters are compared to the elapsed time
1876 since the WAL data was received by the standby server. Thus, the grace
1877 period allowed to any one query on the standby is never more than the
1878 delay parameter, and could be considerably less if the standby has already
1879 fallen behind as a result of waiting for previous queries to complete, or
1880 as a result of being unable to keep up with a heavy update load.
1881 </para>
1882
1883 <para>
1884 The most common reason for conflict between standby queries and WAL replay
1885 is <quote>early cleanup</quote>. Normally, <productname>PostgreSQL</productname> allows
1886 cleanup of old row versions when there are no transactions that need to
1887 see them to ensure correct visibility of data according to MVCC rules.
1888 However, this rule can only be applied for transactions executing on the
1889 primary. So it is possible that cleanup on the primary will remove row
1890 versions that are still visible to a transaction on the standby.
1891 </para>
1892
1893 <para>
1894 Row version cleanup isn't the only potential cause of conflicts with
1895 standby queries. All index-only scans (including those that run on
1896 standbys) must use an <acronym>MVCC</acronym> snapshot that
1897 <quote>agrees</quote> with the visibility map. Conflicts are therefore
1898 required whenever <command>VACUUM</command> <link
1899 linkend="vacuum-for-visibility-map">sets a page as all-visible in the
1900 visibility map</link> containing one or more rows
1901 <emphasis>not</emphasis> visible to all standby queries. So even running
1902 <command>VACUUM</command> against a table with no updated or deleted rows
1903 requiring cleanup might lead to conflicts.
1904 </para>
1905
1906 <para>
1907 Users should be clear that tables that are regularly and heavily updated
1908 on the primary server will quickly cause cancellation of longer running
1909 queries on the standby. In such cases the setting of a finite value for
1910 <varname>max_standby_archive_delay</varname> or
1911 <varname>max_standby_streaming_delay</varname> can be considered similar to
1912 setting <varname>statement_timeout</varname>.
1913 </para>
1914
1915 <para>
1916 Remedial possibilities exist if the number of standby-query cancellations
1917 is found to be unacceptable. The first option is to set the parameter
1918 <varname>hot_standby_feedback</varname>, which prevents <command>VACUUM</command> from
1919 removing recently-dead rows and so cleanup conflicts do not occur.
1920 If you do this, you
1921 should note that this will delay cleanup of dead rows on the primary,
1922 which may result in undesirable table bloat. However, the cleanup
1923 situation will be no worse than if the standby queries were running
1924 directly on the primary server, and you are still getting the benefit of
1925 off-loading execution onto the standby.
1926 If standby servers connect and disconnect frequently, you
1927 might want to make adjustments to handle the period when
1928 <varname>hot_standby_feedback</varname> feedback is not being provided.
1929 For example, consider increasing <varname>max_standby_archive_delay</varname>
1930 so that queries are not rapidly canceled by conflicts in WAL archive
1931 files during disconnected periods. You should also consider increasing
1932 <varname>max_standby_streaming_delay</varname> to avoid rapid cancellations
1933 by newly-arrived streaming WAL entries after reconnection.
1934 </para>
1935
1936 <para>
1937 The number of query cancels and the reason for them can be viewed using
1938 the <structname>pg_stat_database_conflicts</structname> system view on the standby
1939 server. The <structname>pg_stat_database</structname> system view also contains
1940 summary information.
1941 </para>
1942
1943 <para>
1944 Users can control whether a log message is produced when WAL replay is waiting
1945 longer than <varname>deadlock_timeout</varname> for conflicts. This
1946 is controlled by the <xref linkend="guc-log-recovery-conflict-waits"/> parameter.
1947 </para>
1948 </sect2>
1949
1950 <sect2 id="hot-standby-admin">
1951 <title>Administrator's Overview</title>
1952
1953 <para>
1954 If <varname>hot_standby</varname> is <literal>on</literal> in <filename>postgresql.conf</filename>
1955 (the default value) and there is a
1956 <link linkend="file-standby-signal"><filename>standby.signal</filename></link><indexterm><primary>standby.signal</primary><secondary>for hot standby</secondary></indexterm>
1957 file present, the server will run in hot standby mode.
1958 However, it may take some time for hot standby connections to be allowed,
1959 because the server will not accept connections until it has completed
1960 sufficient recovery to provide a consistent state against which queries
1961 can run. During this period,
1962 clients that attempt to connect will be refused with an error message.
1963 To confirm the server has come up, either loop trying to connect from
1964 the application, or look for these messages in the server logs:
1965
1966 <programlisting>
1967 LOG: entering standby mode
1968
1969 ... then some time later ...
1970
1971 LOG: consistent recovery state reached
1972 LOG: database system is ready to accept read-only connections
1973 </programlisting>
1974
1975 Consistency information is recorded once per checkpoint on the primary.
1976 It is not possible to enable hot standby when reading WAL
1977 written during a period when <varname>wal_level</varname> was not set to
1978 <literal>replica</literal> or <literal>logical</literal> on the primary.
1979 Even after reaching a consistent state, the recovery snapshot may not
1980 be ready for hot standby if both of the following conditions are met,
1981 delaying accepting read-only connections. To enable hot standby,
1982 long-lived write transactions with more than 64 subtransactions
1983 need to be closed on the primary.
1984
1985 <itemizedlist>
1986 <listitem>
1987 <para>
1988 A write transaction has more than 64 subtransactions
1989 </para>
1990 </listitem>
1991 <listitem>
1992 <para>
1993 Very long-lived write transactions
1994 </para>
1995 </listitem>
1996 </itemizedlist>
1997
1998 If you are running file-based log shipping ("warm standby"), you might need
1999 to wait until the next WAL file arrives, which could be as long as the
2000 <varname>archive_timeout</varname> setting on the primary.
2001 </para>
2002
2003 <para>
2004 The settings of some parameters determine the size of shared memory for
2005 tracking transaction IDs, locks, and prepared transactions. These shared
2006 memory structures must be no smaller on a standby than on the primary in
2007 order to ensure that the standby does not run out of shared memory during
2008 recovery. For example, if the primary had used a prepared transaction but
2009 the standby had not allocated any shared memory for tracking prepared
2010 transactions, then recovery could not continue until the standby's
2011 configuration is changed. The parameters affected are:
2012
2013 <itemizedlist>
2014 <listitem>
2015 <para>
2016 <varname>max_connections</varname>
2017 </para>
2018 </listitem>
2019 <listitem>
2020 <para>
2021 <varname>max_prepared_transactions</varname>
2022 </para>
2023 </listitem>
2024 <listitem>
2025 <para>
2026 <varname>max_locks_per_transaction</varname>
2027 </para>
2028 </listitem>
2029 <listitem>
2030 <para>
2031 <varname>max_wal_senders</varname>
2032 </para>
2033 </listitem>
2034 <listitem>
2035 <para>
2036 <varname>max_worker_processes</varname>
2037 </para>
2038 </listitem>
2039 </itemizedlist>
2040
2041 The easiest way to ensure this does not become a problem is to have these
2042 parameters set on the standbys to values equal to or greater than on the
2043 primary. Therefore, if you want to increase these values, you should do
2044 so on all standby servers first, before applying the changes to the
2045 primary server. Conversely, if you want to decrease these values, you
2046 should do so on the primary server first, before applying the changes to
2047 all standby servers. Keep in mind that when a standby is promoted, it
2048 becomes the new reference for the required parameter settings for the
2049 standbys that follow it. Therefore, to avoid this becoming a problem
2050 during a switchover or failover, it is recommended to keep these settings
2051 the same on all standby servers.
2052 </para>
2053
2054 <para>
2055 The WAL tracks changes to these parameters on the
2056 primary. If a hot standby processes WAL that indicates that the current
2057 value on the primary is higher than its own value, it will log a warning
2058 and pause recovery, for example:
2059 <screen>
2060 WARNING: hot standby is not possible because of insufficient parameter settings
2061 DETAIL: max_connections = 80 is a lower setting than on the primary server, where its value was 100.
2062 LOG: recovery has paused
2063 DETAIL: If recovery is unpaused, the server will shut down.
2064 HINT: You can then restart the server after making the necessary configuration changes.
2065 </screen>
2066 At that point, the settings on the standby need to be updated and the
2067 instance restarted before recovery can continue. If the standby is not a
2068 hot standby, then when it encounters the incompatible parameter change, it
2069 will shut down immediately without pausing, since there is then no value
2070 in keeping it up.
2071 </para>
2072
2073 <para>
2074 It is important that the administrator select appropriate settings for
2075 <xref linkend="guc-max-standby-archive-delay"/> and <xref
2076 linkend="guc-max-standby-streaming-delay"/>. The best choices vary
2077 depending on business priorities. For example if the server is primarily
2078 tasked as a High Availability server, then you will want low delay
2079 settings, perhaps even zero, though that is a very aggressive setting. If
2080 the standby server is tasked as an additional server for decision support
2081 queries then it might be acceptable to set the maximum delay values to
2082 many hours, or even -1 which means wait forever for queries to complete.
2083 </para>
2084
2085 <para>
2086 Transaction status "hint bits" written on the primary are not WAL-logged,
2087 so data on the standby will likely re-write the hints again on the standby.
2088 Thus, the standby server will still perform disk writes even though
2089 all users are read-only; no changes occur to the data values
2090 themselves. Users will still write large sort temporary files and
2091 re-generate relcache info files, so no part of the database
2092 is truly read-only during hot standby mode.
2093 Note also that writes to remote databases using
2094 <application>dblink</application> module, and other operations outside the
2095 database using PL functions will still be possible, even though the
2096 transaction is read-only locally.
2097 </para>
2098
2099 <para>
2100 The following types of administration commands are not accepted
2101 during recovery mode:
2102
2103 <itemizedlist>
2104 <listitem>
2105 <para>
2106 Data Definition Language (DDL): e.g., <command>CREATE INDEX</command>
2107 </para>
2108 </listitem>
2109 <listitem>
2110 <para>
2111 Privilege and Ownership: <command>GRANT</command>, <command>REVOKE</command>,
2112 <command>REASSIGN</command>
2113 </para>
2114 </listitem>
2115 <listitem>
2116 <para>
2117 Maintenance commands: <command>ANALYZE</command>, <command>VACUUM</command>,
2118 <command>CLUSTER</command>, <command>REINDEX</command>
2119 </para>
2120 </listitem>
2121 </itemizedlist>
2122 </para>
2123
2124 <para>
2125 Again, note that some of these commands are actually allowed during
2126 "read only" mode transactions on the primary.
2127 </para>
2128
2129 <para>
2130 As a result, you cannot create additional indexes that exist solely
2131 on the standby, nor statistics that exist solely on the standby.
2132 If these administration commands are needed, they should be executed
2133 on the primary, and eventually those changes will propagate to the
2134 standby.
2135 </para>
2136
2137 <para>
2138 <function>pg_cancel_backend()</function>
2139 and <function>pg_terminate_backend()</function> will work on user backends,
2140 but not the startup process, which performs
2141 recovery. <structname>pg_stat_activity</structname> does not show
2142 recovering transactions as active. As a result,
2143 <structname>pg_prepared_xacts</structname> is always empty during
2144 recovery. If you wish to resolve in-doubt prepared transactions, view
2145 <literal>pg_prepared_xacts</literal> on the primary and issue commands to
2146 resolve transactions there or resolve them after the end of recovery.
2147 </para>
2148
2149 <para>
2150 <structname>pg_locks</structname> will show locks held by backends,
2151 as normal. <structname>pg_locks</structname> also shows
2152 a virtual transaction managed by the startup process that owns all
2153 <literal>AccessExclusiveLocks</literal> held by transactions being replayed by recovery.
2154 Note that the startup process does not acquire locks to
2155 make database changes, and thus locks other than <literal>AccessExclusiveLocks</literal>
2156 do not show in <structname>pg_locks</structname> for the Startup
2157 process; they are just presumed to exist.
2158 </para>
2159
2160 <para>
2161 The <productname>Nagios</productname> plugin <productname>check_pgsql</productname> will
2162 work, because the simple information it checks for exists.
2163 The <productname>check_postgres</productname> monitoring script will also work,
2164 though some reported values could give different or confusing results.
2165 For example, last vacuum time will not be maintained, since no
2166 vacuum occurs on the standby. Vacuums running on the primary
2167 do still send their changes to the standby.
2168 </para>
2169
2170 <para>
2171 WAL file control commands will not work during recovery,
2172 e.g., <function>pg_backup_start</function>, <function>pg_switch_wal</function> etc.
2173 </para>
2174
2175 <para>
2176 Dynamically loadable modules work, including <structname>pg_stat_statements</structname>.
2177 </para>
2178
2179 <para>
2180 Advisory locks work normally in recovery, including deadlock detection.
2181 Note that advisory locks are never WAL logged, so it is impossible for
2182 an advisory lock on either the primary or the standby to conflict with WAL
2183 replay. Nor is it possible to acquire an advisory lock on the primary
2184 and have it initiate a similar advisory lock on the standby. Advisory
2185 locks relate only to the server on which they are acquired.
2186 </para>
2187
2188 <para>
2189 Trigger-based replication systems such as <productname>Slony</productname>,
2190 <productname>Londiste</productname> and <productname>Bucardo</productname> won't run on the
2191 standby at all, though they will run happily on the primary server as
2192 long as the changes are not sent to standby servers to be applied.
2193 WAL replay is not trigger-based so you cannot relay from the
2194 standby to any system that requires additional database writes or
2195 relies on the use of triggers.
2196 </para>
2197
2198 <para>
2199 New OIDs cannot be assigned, though some <acronym>UUID</acronym> generators may still
2200 work as long as they do not rely on writing new status to the database.
2201 </para>
2202
2203 <para>
2204 Currently, temporary table creation is not allowed during read-only
2205 transactions, so in some cases existing scripts will not run correctly.
2206 This restriction might be relaxed in a later release. This is
2207 both an SQL standard compliance issue and a technical issue.
2208 </para>
2209
2210 <para>
2211 <command>DROP TABLESPACE</command> can only succeed if the tablespace is empty.
2212 Some standby users may be actively using the tablespace via their
2213 <varname>temp_tablespaces</varname> parameter. If there are temporary files in the
2214 tablespace, all active queries are canceled to ensure that temporary
2215 files are removed, so the tablespace can be removed and WAL replay
2216 can continue.
2217 </para>
2218
2219 <para>
2220 Running <command>DROP DATABASE</command> or <command>ALTER DATABASE ... SET
2221 TABLESPACE</command> on the primary
2222 will generate a WAL entry that will cause all users connected to that
2223 database on the standby to be forcibly disconnected. This action occurs
2224 immediately, whatever the setting of
2225 <varname>max_standby_streaming_delay</varname>. Note that
2226 <command>ALTER DATABASE ... RENAME</command> does not disconnect users, which
2227 in most cases will go unnoticed, though might in some cases cause a
2228 program confusion if it depends in some way upon database name.
2229 </para>
2230
2231 <para>
2232 In normal (non-recovery) mode, if you issue <command>DROP USER</command> or <command>DROP ROLE</command>
2233 for a role with login capability while that user is still connected then
2234 nothing happens to the connected user &mdash; they remain connected. The user cannot
2235 reconnect however. This behavior applies in recovery also, so a
2236 <command>DROP USER</command> on the primary does not disconnect that user on the standby.
2237 </para>
2238
2239 <para>
2240 The cumulative statistics system is active during recovery. All scans,
2241 reads, blocks, index usage, etc., will be recorded normally on the
2242 standby. However, WAL replay will not increment relation and database
2243 specific counters. I.e. replay will not increment
2244 <structname>pg_stat_all_tables</structname> columns (like <structfield>n_tup_ins</structfield>),
2245 nor will reads or writes performed by the startup process be tracked in the
2246 <structname>pg_statio_</structname> views, nor will associated
2247 <structname>pg_stat_database</structname> columns be incremented.
2248 </para>
2249
2250 <para>
2251 Autovacuum is not active during recovery. It will start normally at the
2252 end of recovery.
2253 </para>
2254
2255 <para>
2256 The checkpointer process and the background writer process are active during
2257 recovery. The checkpointer process will perform restartpoints (similar to
2258 checkpoints on the primary) and the background writer process will perform
2259 normal block cleaning activities. This can include updates of the hint bit
2260 information stored on the standby server.
2261 The <command>CHECKPOINT</command> command is accepted during recovery,
2262 though it performs a restartpoint rather than a new checkpoint.
2263 </para>
2264 </sect2>
2265
2266 <sect2 id="hot-standby-parameters">
2267 <title>Hot Standby Parameter Reference</title>
2268
2269 <para>
2270 Various parameters have been mentioned above in
2271 <xref linkend="hot-standby-conflict"/> and
2272 <xref linkend="hot-standby-admin"/>.
2273 </para>
2274
2275 <para>
2276 On the primary, the <xref linkend="guc-wal-level"/> parameter can be used.
2277 <xref linkend="guc-max-standby-archive-delay"/> and
2278 <xref linkend="guc-max-standby-streaming-delay"/> have no effect if set on
2279 the primary.
2280 </para>
2281
2282 <para>
2283 On the standby, parameters <xref linkend="guc-hot-standby"/>,
2284 <xref linkend="guc-max-standby-archive-delay"/> and
2285 <xref linkend="guc-max-standby-streaming-delay"/> can be used.
2286 </para>
2287 </sect2>
2288
2289 <sect2 id="hot-standby-caveats">
2290 <title>Caveats</title>
2291
2292 <para>
2293 There are several limitations of hot standby.
2294 These can and probably will be fixed in future releases:
2295
2296 <itemizedlist>
2297 <listitem>
2298 <para>
2299 Full knowledge of running transactions is required before snapshots
2300 can be taken. Transactions that use large numbers of subtransactions
2301 (currently greater than 64) will delay the start of read-only
2302 connections until the completion of the longest running write transaction.
2303 If this situation occurs, explanatory messages will be sent to the server log.
2304 </para>
2305 </listitem>
2306 <listitem>
2307 <para>
2308 Valid starting points for standby queries are generated at each
2309 checkpoint on the primary. If the standby is shut down while the primary
2310 is in a shutdown state, it might not be possible to re-enter hot standby
2311 until the primary is started up, so that it generates further starting
2312 points in the WAL logs. This situation isn't a problem in the most
2313 common situations where it might happen. Generally, if the primary is
2314 shut down and not available anymore, that's likely due to a serious
2315 failure that requires the standby being converted to operate as
2316 the new primary anyway. And in situations where the primary is
2317 being intentionally taken down, coordinating to make sure the standby
2318 becomes the new primary smoothly is also standard procedure.
2319 </para>
2320 </listitem>
2321 <listitem>
2322 <para>
2323 At the end of recovery, <literal>AccessExclusiveLocks</literal> held by prepared transactions
2324 will require twice the normal number of lock table entries. If you plan
2325 on running either a large number of concurrent prepared transactions
2326 that normally take <literal>AccessExclusiveLocks</literal>, or you plan on having one
2327 large transaction that takes many <literal>AccessExclusiveLocks</literal>, you are
2328 advised to select a larger value of <varname>max_locks_per_transaction</varname>,
2329 perhaps as much as twice the value of the parameter on
2330 the primary server. You need not consider this at all if
2331 your setting of <varname>max_prepared_transactions</varname> is 0.
2332 </para>
2333 </listitem>
2334 <listitem>
2335 <para>
2336 The Serializable transaction isolation level is not yet available in hot
2337 standby. (See <xref linkend="xact-serializable"/> and
2338 <xref linkend="serializable-consistency"/> for details.)
2339 An attempt to set a transaction to the serializable isolation level in
2340 hot standby mode will generate an error.
2341 </para>
2342 </listitem>
2343 </itemizedlist>
2344
2345 </para>
2346 </sect2>
2347
2348 </sect1>
2349
2350 </chapter>