summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/hot-standby.html
blob: eb2d543e510e5b4c15940d0743272013462fefe5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>27.4. Hot Standby</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="warm-standby-failover.html" title="27.3. Failover" /><link rel="next" href="monitoring.html" title="Chapter 28. Monitoring Database Activity" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">27.4. Hot Standby</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="warm-standby-failover.html" title="27.3. Failover">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="high-availability.html" title="Chapter 27. High Availability, Load Balancing, and Replication">Up</a></td><th width="60%" align="center">Chapter 27. High Availability, Load Balancing, and Replication</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="monitoring.html" title="Chapter 28. Monitoring Database Activity">Next</a></td></tr></table><hr /></div><div class="sect1" id="HOT-STANDBY"><div class="titlepage"><div><div><h2 class="title" style="clear: both">27.4. Hot Standby <a href="#HOT-STANDBY" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-USERS">27.4.1. User's Overview</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-CONFLICT">27.4.2. Handling Query Conflicts</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-ADMIN">27.4.3. Administrator's Overview</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-PARAMETERS">27.4.4. Hot Standby Parameter Reference</a></span></dt><dt><span class="sect2"><a href="hot-standby.html#HOT-STANDBY-CAVEATS">27.4.5. Caveats</a></span></dt></dl></div><a id="id-1.6.14.18.2" class="indexterm"></a><p>
    Hot standby is the term used to describe the ability to connect to
    the server and run read-only queries while the server is in archive
    recovery or standby mode. This
    is useful both for replication purposes and for restoring a backup
    to a desired state with great precision.
    The term hot standby also refers to the ability of the server to move
    from recovery through to normal operation while users continue running
    queries and/or keep their connections open.
   </p><p>
    Running queries in hot standby mode is similar to normal query operation,
    though there are several usage and administrative differences
    explained below.
   </p><div class="sect2" id="HOT-STANDBY-USERS"><div class="titlepage"><div><div><h3 class="title">27.4.1. User's Overview <a href="#HOT-STANDBY-USERS" class="id_link">#</a></h3></div></div></div><p>
    When the <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a> parameter is set to true on a
    standby server, it will begin accepting connections once the recovery has
    brought the system to a consistent state.  All such connections are
    strictly read-only; not even temporary tables may be written.
   </p><p>
    The data on the standby takes some time to arrive from the primary server
    so there will be a measurable delay between primary and standby. Running the
    same query nearly simultaneously on both primary and standby might therefore
    return differing results. We say that data on the standby is
    <em class="firstterm">eventually consistent</em> with the primary.  Once the
    commit record for a transaction is replayed on the standby, the changes
    made by that transaction will be visible to any new snapshots taken on
    the standby.  Snapshots may be taken at the start of each query or at the
    start of each transaction, depending on the current transaction isolation
    level.  For more details, see <a class="xref" href="transaction-iso.html" title="13.2. Transaction Isolation">Section 13.2</a>.
   </p><p>
    Transactions started during hot standby may issue the following commands:

    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
       Query access: <code class="command">SELECT</code>, <code class="command">COPY TO</code>
      </p></li><li class="listitem"><p>
       Cursor commands: <code class="command">DECLARE</code>, <code class="command">FETCH</code>, <code class="command">CLOSE</code>
      </p></li><li class="listitem"><p>
       Settings: <code class="command">SHOW</code>, <code class="command">SET</code>, <code class="command">RESET</code>
      </p></li><li class="listitem"><p>
       Transaction management commands:
        </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
           <code class="command">BEGIN</code>, <code class="command">END</code>, <code class="command">ABORT</code>, <code class="command">START TRANSACTION</code>
          </p></li><li class="listitem"><p>
           <code class="command">SAVEPOINT</code>, <code class="command">RELEASE</code>, <code class="command">ROLLBACK TO SAVEPOINT</code>
          </p></li><li class="listitem"><p>
           <code class="command">EXCEPTION</code> blocks and other internal subtransactions
          </p></li></ul></div><p>
      </p></li><li class="listitem"><p>
       <code class="command">LOCK TABLE</code>, though only when explicitly in one of these modes:
       <code class="literal">ACCESS SHARE</code>, <code class="literal">ROW SHARE</code> or <code class="literal">ROW EXCLUSIVE</code>.
      </p></li><li class="listitem"><p>
       Plans and resources: <code class="command">PREPARE</code>, <code class="command">EXECUTE</code>,
       <code class="command">DEALLOCATE</code>, <code class="command">DISCARD</code>
      </p></li><li class="listitem"><p>
       Plugins and extensions: <code class="command">LOAD</code>
      </p></li><li class="listitem"><p>
       <code class="command">UNLISTEN</code>
      </p></li></ul></div><p>
   </p><p>
    Transactions started during hot standby will never be assigned a
    transaction ID and cannot write to the system write-ahead log.
    Therefore, the following actions will produce error messages:

    </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
       Data Manipulation Language (DML): <code class="command">INSERT</code>,
       <code class="command">UPDATE</code>, <code class="command">DELETE</code>,
       <code class="command">MERGE</code>, <code class="command">COPY FROM</code>,
       <code class="command">TRUNCATE</code>.
       Note that there are no allowed actions that result in a trigger
       being executed during recovery.  This restriction applies even to
       temporary tables, because table rows cannot be read or written without
       assigning a transaction ID, which is currently not possible in a
       hot standby environment.
      </p></li><li class="listitem"><p>
       Data Definition Language (DDL): <code class="command">CREATE</code>,
       <code class="command">DROP</code>, <code class="command">ALTER</code>, <code class="command">COMMENT</code>.
       This restriction applies even to temporary tables, because carrying
       out these operations would require updating the system catalog tables.
      </p></li><li class="listitem"><p>
       <code class="command">SELECT ... FOR SHARE | UPDATE</code>, because row locks cannot be
       taken without updating the underlying data files.
      </p></li><li class="listitem"><p>
       Rules on <code class="command">SELECT</code> statements that generate DML commands.
      </p></li><li class="listitem"><p>
       <code class="command">LOCK</code> that explicitly requests a mode higher than <code class="literal">ROW EXCLUSIVE MODE</code>.
      </p></li><li class="listitem"><p>
       <code class="command">LOCK</code> in short default form, since it requests <code class="literal">ACCESS EXCLUSIVE MODE</code>.
      </p></li><li class="listitem"><p>
       Transaction management commands that explicitly set non-read-only state:
        </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: circle; "><li class="listitem"><p>
            <code class="command">BEGIN READ WRITE</code>,
            <code class="command">START TRANSACTION READ WRITE</code>
          </p></li><li class="listitem"><p>
            <code class="command">SET TRANSACTION READ WRITE</code>,
            <code class="command">SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE</code>
          </p></li><li class="listitem"><p>
           <code class="command">SET transaction_read_only = off</code>
          </p></li></ul></div><p>
      </p></li><li class="listitem"><p>
       Two-phase commit commands: <code class="command">PREPARE TRANSACTION</code>,
       <code class="command">COMMIT PREPARED</code>, <code class="command">ROLLBACK PREPARED</code>
       because even read-only transactions need to write WAL in the
       prepare phase (the first phase of two phase commit).
      </p></li><li class="listitem"><p>
       Sequence updates: <code class="function">nextval()</code>, <code class="function">setval()</code>
      </p></li><li class="listitem"><p>
       <code class="command">LISTEN</code>, <code class="command">NOTIFY</code>
      </p></li></ul></div><p>
   </p><p>
    In normal operation, <span class="quote"><span class="quote">read-only</span></span> transactions are allowed to
    use <code class="command">LISTEN</code> and <code class="command">NOTIFY</code>,
    so hot standby sessions operate under slightly tighter
    restrictions than ordinary read-only sessions.  It is possible that some
    of these restrictions might be loosened in a future release.
   </p><p>
    During hot standby, the parameter <code class="varname">transaction_read_only</code> is always
    true and may not be changed.  But as long as no attempt is made to modify
    the database, connections during hot standby will act much like any other
    database connection.  If failover or switchover occurs, the database will
    switch to normal processing mode.  Sessions will remain connected while the
    server changes mode.  Once hot standby finishes, it will be possible to
    initiate read-write transactions (even from a session begun during
    hot standby).
   </p><p>
    Users can determine whether hot standby is currently active for their
    session by issuing <code class="command">SHOW in_hot_standby</code>.
    (In server versions before 14, the <code class="varname">in_hot_standby</code>
    parameter did not exist; a workable substitute method for older servers
    is <code class="command">SHOW transaction_read_only</code>.)  In addition, a set of
    functions (<a class="xref" href="functions-admin.html#FUNCTIONS-RECOVERY-INFO-TABLE" title="Table 9.92. Recovery Information Functions">Table 9.92</a>) allow users to
    access information about the standby server. These allow you to write
    programs that are aware of the current state of the database. These
    can be used to monitor the progress of recovery, or to allow you to
    write complex programs that restore the database to particular states.
   </p></div><div class="sect2" id="HOT-STANDBY-CONFLICT"><div class="titlepage"><div><div><h3 class="title">27.4.2. Handling Query Conflicts <a href="#HOT-STANDBY-CONFLICT" class="id_link">#</a></h3></div></div></div><p>
    The primary and standby servers are in many ways loosely connected. Actions
    on the primary will have an effect on the standby. As a result, there is
    potential for negative interactions or conflicts between them. The easiest
    conflict to understand is performance: if a huge data load is taking place
    on the primary then this will generate a similar stream of WAL records on the
    standby, so standby queries may contend for system resources, such as I/O.
   </p><p>
    There are also additional types of conflict that can occur with hot standby.
    These conflicts are <span class="emphasis"><em>hard conflicts</em></span> in the sense that queries
    might need to be canceled and, in some cases, sessions disconnected to resolve them.
    The user is provided with several ways to handle these
    conflicts. Conflict cases include:

      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
         Access Exclusive locks taken on the primary server, including both
         explicit <code class="command">LOCK</code> commands and various <acronym class="acronym">DDL</acronym>
         actions, conflict with table accesses in standby queries.
        </p></li><li class="listitem"><p>
         Dropping a tablespace on the primary conflicts with standby queries
         using that tablespace for temporary work files.
        </p></li><li class="listitem"><p>
         Dropping a database on the primary conflicts with sessions connected
         to that database on the standby.
        </p></li><li class="listitem"><p>
         Application of a vacuum cleanup record from WAL conflicts with
         standby transactions whose snapshots can still <span class="quote"><span class="quote">see</span></span> any of
         the rows to be removed.
        </p></li><li class="listitem"><p>
         Application of a vacuum cleanup record from WAL conflicts with
         queries accessing the target page on the standby, whether or not
         the data to be removed is visible.
        </p></li></ul></div><p>
   </p><p>
    On the primary server, these cases simply result in waiting; and the
    user might choose to cancel either of the conflicting actions.  However,
    on the standby there is no choice: the WAL-logged action already occurred
    on the primary so the standby must not fail to apply it.  Furthermore,
    allowing WAL application to wait indefinitely may be very undesirable,
    because the standby's state will become increasingly far behind the
    primary's.  Therefore, a mechanism is provided to forcibly cancel standby
    queries that conflict with to-be-applied WAL records.
   </p><p>
    An example of the problem situation is an administrator on the primary
    server running <code class="command">DROP TABLE</code> on a table that is currently being
    queried on the standby server.  Clearly the standby query cannot continue
    if the <code class="command">DROP TABLE</code> is applied on the standby. If this situation
    occurred on the primary, the <code class="command">DROP TABLE</code> would wait until the
    other query had finished. But when <code class="command">DROP TABLE</code> is run on the
    primary, the primary doesn't have information about what queries are
    running on the standby, so it will not wait for any such standby
    queries. The WAL change records come through to the standby while the
    standby query is still running, causing a conflict.  The standby server
    must either delay application of the WAL records (and everything after
    them, too) or else cancel the conflicting query so that the <code class="command">DROP
    TABLE</code> can be applied.
   </p><p>
    When a conflicting query is short, it's typically desirable to allow it to
    complete by delaying WAL application for a little bit; but a long delay in
    WAL application is usually not desirable.  So the cancel mechanism has
    parameters, <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a>, that define the maximum
    allowed delay in WAL application.  Conflicting queries will be canceled
    once it has taken longer than the relevant delay setting to apply any
    newly-received WAL data.  There are two parameters so that different delay
    values can be specified for the case of reading WAL data from an archive
    (i.e., initial recovery from a base backup or <span class="quote"><span class="quote">catching up</span></span> a
    standby server that has fallen far behind) versus reading WAL data via
    streaming replication.
   </p><p>
    In a standby server that exists primarily for high availability, it's
    best to set the delay parameters relatively short, so that the server
    cannot fall far behind the primary due to delays caused by standby
    queries.  However, if the standby server is meant for executing
    long-running queries, then a high or even infinite delay value may be
    preferable.  Keep in mind however that a long-running query could
    cause other sessions on the standby server to not see recent changes
    on the primary, if it delays application of WAL records.
   </p><p>
    Once the delay specified by <code class="varname">max_standby_archive_delay</code> or
    <code class="varname">max_standby_streaming_delay</code> has been exceeded, conflicting
    queries will be canceled.  This usually results just in a cancellation
    error, although in the case of replaying a <code class="command">DROP DATABASE</code>
    the entire conflicting session will be terminated.  Also, if the conflict
    is over a lock held by an idle transaction, the conflicting session is
    terminated (this behavior might change in the future).
   </p><p>
    Canceled queries may be retried immediately (after beginning a new
    transaction, of course).  Since query cancellation depends on
    the nature of the WAL records being replayed, a query that was
    canceled may well succeed if it is executed again.
   </p><p>
    Keep in mind that the delay parameters are compared to the elapsed time
    since the WAL data was received by the standby server.  Thus, the grace
    period allowed to any one query on the standby is never more than the
    delay parameter, and could be considerably less if the standby has already
    fallen behind as a result of waiting for previous queries to complete, or
    as a result of being unable to keep up with a heavy update load.
   </p><p>
    The most common reason for conflict between standby queries and WAL replay
    is <span class="quote"><span class="quote">early cleanup</span></span>.  Normally, <span class="productname">PostgreSQL</span> allows
    cleanup of old row versions when there are no transactions that need to
    see them to ensure correct visibility of data according to MVCC rules.
    However, this rule can only be applied for transactions executing on the
    primary.  So it is possible that cleanup on the primary will remove row
    versions that are still visible to a transaction on the standby.
   </p><p>
    Row version cleanup isn't the only potential cause of conflicts with
    standby queries.  All index-only scans (including those that run on
    standbys) must use an <acronym class="acronym">MVCC</acronym> snapshot that
    <span class="quote"><span class="quote">agrees</span></span> with the visibility map.  Conflicts are therefore
    required whenever <code class="command">VACUUM</code> <a class="link" href="routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP" title="25.1.4. Updating the Visibility Map">sets a page as all-visible in the
     visibility map</a> containing one or more rows
    <span class="emphasis"><em>not</em></span> visible to all standby queries.  So even running
    <code class="command">VACUUM</code> against a table with no updated or deleted rows
    requiring cleanup might lead to conflicts.
   </p><p>
    Users should be clear that tables that are regularly and heavily updated
    on the primary server will quickly cause cancellation of longer running
    queries on the standby. In such cases the setting of a finite value for
    <code class="varname">max_standby_archive_delay</code> or
    <code class="varname">max_standby_streaming_delay</code> can be considered similar to
    setting <code class="varname">statement_timeout</code>.
   </p><p>
    Remedial possibilities exist if the number of standby-query cancellations
    is found to be unacceptable.  The first option is to set the parameter
    <code class="varname">hot_standby_feedback</code>, which prevents <code class="command">VACUUM</code> from
    removing recently-dead rows and so cleanup conflicts do not occur.
    If you do this, you
    should note that this will delay cleanup of dead rows on the primary,
    which may result in undesirable table bloat. However, the cleanup
    situation will be no worse than if the standby queries were running
    directly on the primary server, and you are still getting the benefit of
    off-loading execution onto the standby.
    If standby servers connect and disconnect frequently, you
    might want to make adjustments to handle the period when
    <code class="varname">hot_standby_feedback</code> feedback is not being provided.
    For example, consider increasing <code class="varname">max_standby_archive_delay</code>
    so that queries are not rapidly canceled by conflicts in WAL archive
    files during disconnected periods.  You should also consider increasing
    <code class="varname">max_standby_streaming_delay</code> to avoid rapid cancellations
    by newly-arrived streaming WAL entries after reconnection.
   </p><p>
    The number of query cancels and the reason for them can be viewed using
    the <code class="structname">pg_stat_database_conflicts</code> system view on the standby
    server. The <code class="structname">pg_stat_database</code> system view also contains
    summary information.
   </p><p>
    Users can control whether a log message is produced when WAL replay is waiting
    longer than <code class="varname">deadlock_timeout</code> for conflicts. This
    is controlled by the <a class="xref" href="runtime-config-logging.html#GUC-LOG-RECOVERY-CONFLICT-WAITS">log_recovery_conflict_waits</a> parameter.
   </p></div><div class="sect2" id="HOT-STANDBY-ADMIN"><div class="titlepage"><div><div><h3 class="title">27.4.3. Administrator's Overview <a href="#HOT-STANDBY-ADMIN" class="id_link">#</a></h3></div></div></div><p>
    If <code class="varname">hot_standby</code> is <code class="literal">on</code> in <code class="filename">postgresql.conf</code>
    (the default value) and there is a
    <a class="link" href="warm-standby.html#FILE-STANDBY-SIGNAL"><code class="filename">standby.signal</code></a><a id="id-1.6.14.18.7.2.5" class="indexterm"></a>
    file present, the server will run in hot standby mode.
    However, it may take some time for hot standby connections to be allowed,
    because the server will not accept connections until it has completed
    sufficient recovery to provide a consistent state against which queries
    can run.  During this period,
    clients that attempt to connect will be refused with an error message.
    To confirm the server has come up, either loop trying to connect from
    the application, or look for these messages in the server logs:

</p><pre class="programlisting">
LOG:  entering standby mode

... then some time later ...

LOG:  consistent recovery state reached
LOG:  database system is ready to accept read-only connections
</pre><p>

    Consistency information is recorded once per checkpoint on the primary.
    It is not possible to enable hot standby when reading WAL
    written during a period when <code class="varname">wal_level</code> was not set to
    <code class="literal">replica</code> or <code class="literal">logical</code> on the primary.  Reaching
    a consistent state can also be delayed in the presence of both of these
    conditions:

      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
         A write transaction has more than 64 subtransactions
        </p></li><li class="listitem"><p>
         Very long-lived write transactions
        </p></li></ul></div><p>

    If you are running file-based log shipping ("warm standby"), you might need
    to wait until the next WAL file arrives, which could be as long as the
    <code class="varname">archive_timeout</code> setting on the primary.
   </p><p>
    The settings of some parameters determine the size of shared memory for
    tracking transaction IDs, locks, and prepared transactions.  These shared
    memory structures must be no smaller on a standby than on the primary in
    order to ensure that the standby does not run out of shared memory during
    recovery.  For example, if the primary had used a prepared transaction but
    the standby had not allocated any shared memory for tracking prepared
    transactions, then recovery could not continue until the standby's
    configuration is changed.  The parameters affected are:

      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
         <code class="varname">max_connections</code>
        </p></li><li class="listitem"><p>
         <code class="varname">max_prepared_transactions</code>
        </p></li><li class="listitem"><p>
         <code class="varname">max_locks_per_transaction</code>
        </p></li><li class="listitem"><p>
         <code class="varname">max_wal_senders</code>
        </p></li><li class="listitem"><p>
         <code class="varname">max_worker_processes</code>
        </p></li></ul></div><p>

    The easiest way to ensure this does not become a problem is to have these
    parameters set on the standbys to values equal to or greater than on the
    primary.  Therefore, if you want to increase these values, you should do
    so on all standby servers first, before applying the changes to the
    primary server.  Conversely, if you want to decrease these values, you
    should do so on the primary server first, before applying the changes to
    all standby servers.  Keep in mind that when a standby is promoted, it
    becomes the new reference for the required parameter settings for the
    standbys that follow it.  Therefore, to avoid this becoming a problem
    during a switchover or failover, it is recommended to keep these settings
    the same on all standby servers.
   </p><p>
    The WAL tracks changes to these parameters on the
    primary.  If a hot standby processes WAL that indicates that the current
    value on the primary is higher than its own value, it will log a warning
    and pause recovery, for example:
</p><pre class="screen">
WARNING:  hot standby is not possible because of insufficient parameter settings
DETAIL:  max_connections = 80 is a lower setting than on the primary server, where its value was 100.
LOG:  recovery has paused
DETAIL:  If recovery is unpaused, the server will shut down.
HINT:  You can then restart the server after making the necessary configuration changes.
</pre><p>
    At that point, the settings on the standby need to be updated and the
    instance restarted before recovery can continue.  If the standby is not a
    hot standby, then when it encounters the incompatible parameter change, it
    will shut down immediately without pausing, since there is then no value
    in keeping it up.
   </p><p>
    It is important that the administrator select appropriate settings for
    <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a>.  The best choices vary
    depending on business priorities.  For example if the server is primarily
    tasked as a High Availability server, then you will want low delay
    settings, perhaps even zero, though that is a very aggressive setting. If
    the standby server is tasked as an additional server for decision support
    queries then it might be acceptable to set the maximum delay values to
    many hours, or even -1 which means wait forever for queries to complete.
   </p><p>
    Transaction status "hint bits" written on the primary are not WAL-logged,
    so data on the standby will likely re-write the hints again on the standby.
    Thus, the standby server will still perform disk writes even though
    all users are read-only; no changes occur to the data values
    themselves.  Users will still write large sort temporary files and
    re-generate relcache info files, so no part of the database
    is truly read-only during hot standby mode.
    Note also that writes to remote databases using
    <span class="application">dblink</span> module, and other operations outside the
    database using PL functions will still be possible, even though the
    transaction is read-only locally.
   </p><p>
    The following types of administration commands are not accepted
    during recovery mode:

      </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
         Data Definition Language (DDL): e.g., <code class="command">CREATE INDEX</code>
        </p></li><li class="listitem"><p>
         Privilege and Ownership: <code class="command">GRANT</code>, <code class="command">REVOKE</code>,
         <code class="command">REASSIGN</code>
        </p></li><li class="listitem"><p>
         Maintenance commands: <code class="command">ANALYZE</code>, <code class="command">VACUUM</code>,
         <code class="command">CLUSTER</code>, <code class="command">REINDEX</code>
        </p></li></ul></div><p>
   </p><p>
    Again, note that some of these commands are actually allowed during
    "read only" mode transactions on the primary.
   </p><p>
    As a result, you cannot create additional indexes that exist solely
    on the standby, nor statistics that exist solely on the standby.
    If these administration commands are needed, they should be executed
    on the primary, and eventually those changes will propagate to the
    standby.
   </p><p>
    <code class="function">pg_cancel_backend()</code>
    and <code class="function">pg_terminate_backend()</code> will work on user backends,
    but not the startup process, which performs
    recovery. <code class="structname">pg_stat_activity</code> does not show
    recovering transactions as active. As a result,
    <code class="structname">pg_prepared_xacts</code> is always empty during
    recovery. If you wish to resolve in-doubt prepared transactions, view
    <code class="literal">pg_prepared_xacts</code> on the primary and issue commands to
    resolve transactions there or resolve them after the end of recovery.
   </p><p>
    <code class="structname">pg_locks</code> will show locks held by backends,
    as normal. <code class="structname">pg_locks</code> also shows
    a virtual transaction managed by the startup process that owns all
    <code class="literal">AccessExclusiveLocks</code> held by transactions being replayed by recovery.
    Note that the startup process does not acquire locks to
    make database changes, and thus locks other than <code class="literal">AccessExclusiveLocks</code>
    do not show in <code class="structname">pg_locks</code> for the Startup
    process; they are just presumed to exist.
   </p><p>
    The <span class="productname">Nagios</span> plugin <span class="productname">check_pgsql</span> will
    work, because the simple information it checks for exists.
    The <span class="productname">check_postgres</span> monitoring script will also work,
    though some reported values could give different or confusing results.
    For example, last vacuum time will not be maintained, since no
    vacuum occurs on the standby.  Vacuums running on the primary
    do still send their changes to the standby.
   </p><p>
    WAL file control commands will not work during recovery,
    e.g., <code class="function">pg_backup_start</code>, <code class="function">pg_switch_wal</code> etc.
   </p><p>
    Dynamically loadable modules work, including <code class="structname">pg_stat_statements</code>.
   </p><p>
    Advisory locks work normally in recovery, including deadlock detection.
    Note that advisory locks are never WAL logged, so it is impossible for
    an advisory lock on either the primary or the standby to conflict with WAL
    replay. Nor is it possible to acquire an advisory lock on the primary
    and have it initiate a similar advisory lock on the standby. Advisory
    locks relate only to the server on which they are acquired.
   </p><p>
    Trigger-based replication systems such as <span class="productname">Slony</span>,
    <span class="productname">Londiste</span> and <span class="productname">Bucardo</span> won't run on the
    standby at all, though they will run happily on the primary server as
    long as the changes are not sent to standby servers to be applied.
    WAL replay is not trigger-based so you cannot relay from the
    standby to any system that requires additional database writes or
    relies on the use of triggers.
   </p><p>
    New OIDs cannot be assigned, though some <acronym class="acronym">UUID</acronym> generators may still
    work as long as they do not rely on writing new status to the database.
   </p><p>
    Currently, temporary table creation is not allowed during read-only
    transactions, so in some cases existing scripts will not run correctly.
    This restriction might be relaxed in a later release. This is
    both an SQL standard compliance issue and a technical issue.
   </p><p>
    <code class="command">DROP TABLESPACE</code> can only succeed if the tablespace is empty.
    Some standby users may be actively using the tablespace via their
    <code class="varname">temp_tablespaces</code> parameter. If there are temporary files in the
    tablespace, all active queries are canceled to ensure that temporary
    files are removed, so the tablespace can be removed and WAL replay
    can continue.
   </p><p>
    Running <code class="command">DROP DATABASE</code> or <code class="command">ALTER DATABASE ... SET
    TABLESPACE</code> on the primary
    will generate a WAL entry that will cause all users connected to that
    database on the standby to be forcibly disconnected. This action occurs
    immediately, whatever the setting of
    <code class="varname">max_standby_streaming_delay</code>. Note that
    <code class="command">ALTER DATABASE ... RENAME</code> does not disconnect users, which
    in most cases will go unnoticed, though might in some cases cause a
    program confusion if it depends in some way upon database name.
   </p><p>
    In normal (non-recovery) mode, if you issue <code class="command">DROP USER</code> or <code class="command">DROP ROLE</code>
    for a role with login capability while that user is still connected then
    nothing happens to the connected user — they remain connected. The user cannot
    reconnect however. This behavior applies in recovery also, so a
    <code class="command">DROP USER</code> on the primary does not disconnect that user on the standby.
   </p><p>
    The cumulative statistics system is active during recovery. All scans,
    reads, blocks, index usage, etc., will be recorded normally on the
    standby. However, WAL replay will not increment relation and database
    specific counters. I.e. replay will not increment pg_stat_all_tables
    columns (like n_tup_ins), nor will reads or writes performed by the
    startup process be tracked in the pg_statio views, nor will associated
    pg_stat_database columns be incremented.
   </p><p>
    Autovacuum is not active during recovery.  It will start normally at the
    end of recovery.
   </p><p>
    The checkpointer process and the background writer process are active during
    recovery. The checkpointer process will perform restartpoints (similar to
    checkpoints on the primary) and the background writer process will perform
    normal block cleaning activities. This can include updates of the hint bit
    information stored on the standby server.
    The <code class="command">CHECKPOINT</code> command is accepted during recovery,
    though it performs a restartpoint rather than a new checkpoint.
   </p></div><div class="sect2" id="HOT-STANDBY-PARAMETERS"><div class="titlepage"><div><div><h3 class="title">27.4.4. Hot Standby Parameter Reference <a href="#HOT-STANDBY-PARAMETERS" class="id_link">#</a></h3></div></div></div><p>
    Various parameters have been mentioned above in
    <a class="xref" href="hot-standby.html#HOT-STANDBY-CONFLICT" title="27.4.2. Handling Query Conflicts">Section 27.4.2</a> and
    <a class="xref" href="hot-standby.html#HOT-STANDBY-ADMIN" title="27.4.3. Administrator's Overview">Section 27.4.3</a>.
   </p><p>
    On the primary, the <a class="xref" href="runtime-config-wal.html#GUC-WAL-LEVEL">wal_level</a> parameter can be used.
    <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and
    <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a> have no effect if set on
    the primary.
   </p><p>
    On the standby, parameters <a class="xref" href="runtime-config-replication.html#GUC-HOT-STANDBY">hot_standby</a>,
    <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY">max_standby_archive_delay</a> and
    <a class="xref" href="runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY">max_standby_streaming_delay</a> can be used.
   </p></div><div class="sect2" id="HOT-STANDBY-CAVEATS"><div class="titlepage"><div><div><h3 class="title">27.4.5. Caveats <a href="#HOT-STANDBY-CAVEATS" class="id_link">#</a></h3></div></div></div><p>
    There are several limitations of hot standby.
    These can and probably will be fixed in future releases:

  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
     Full knowledge of running transactions is required before snapshots
     can be taken. Transactions that use large numbers of subtransactions
     (currently greater than 64) will delay the start of read-only
     connections until the completion of the longest running write transaction.
     If this situation occurs, explanatory messages will be sent to the server log.
    </p></li><li class="listitem"><p>
     Valid starting points for standby queries are generated at each
     checkpoint on the primary. If the standby is shut down while the primary
     is in a shutdown state, it might not be possible to re-enter hot standby
     until the primary is started up, so that it generates further starting
     points in the WAL logs.  This situation isn't a problem in the most
     common situations where it might happen. Generally, if the primary is
     shut down and not available anymore, that's likely due to a serious
     failure that requires the standby being converted to operate as
     the new primary anyway.  And in situations where the primary is
     being intentionally taken down, coordinating to make sure the standby
     becomes the new primary smoothly is also standard procedure.
    </p></li><li class="listitem"><p>
     At the end of recovery, <code class="literal">AccessExclusiveLocks</code> held by prepared transactions
     will require twice the normal number of lock table entries. If you plan
     on running either a large number of concurrent prepared transactions
     that normally take <code class="literal">AccessExclusiveLocks</code>, or you plan on having one
     large transaction that takes many <code class="literal">AccessExclusiveLocks</code>, you are
     advised to select a larger value of <code class="varname">max_locks_per_transaction</code>,
     perhaps as much as twice the value of the parameter on
     the primary server. You need not consider this at all if
     your setting of <code class="varname">max_prepared_transactions</code> is 0.
    </p></li><li class="listitem"><p>
     The Serializable transaction isolation level is not yet available in hot
     standby.  (See <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a> and
     <a class="xref" href="applevel-consistency.html#SERIALIZABLE-CONSISTENCY" title="13.4.1. Enforcing Consistency with Serializable Transactions">Section 13.4.1</a> for details.)
     An attempt to set a transaction to the serializable isolation level in
     hot standby mode will generate an error.
    </p></li></ul></div><p>

   </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="warm-standby-failover.html" title="27.3. Failover">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="high-availability.html" title="Chapter 27. High Availability, Load Balancing, and Replication">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="monitoring.html" title="Chapter 28. Monitoring Database Activity">Next</a></td></tr><tr><td width="40%" align="left" valign="top">27.3. Failover </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 28. Monitoring Database Activity</td></tr></table></div></body></html>