Debian Codegraph @

doc/src/sgml/maintenance.sgml

raw
1 <!-- doc/src/sgml/maintenance.sgml -->
2
3 <chapter id="maintenance">
4 <title>Routine Database Maintenance Tasks</title>
5
6 <indexterm zone="maintenance">
7 <primary>maintenance</primary>
8 </indexterm>
9
10 <indexterm zone="maintenance">
11 <primary>routine maintenance</primary>
12 </indexterm>
13
14 <para>
15 <productname>PostgreSQL</productname>, like any database software, requires that certain tasks
16 be performed regularly to achieve optimum performance. The tasks
17 discussed here are <emphasis>required</emphasis>, but they
18 are repetitive in nature and can easily be automated using standard
19 tools such as <application>cron</application> scripts or
20 Windows' <application>Task Scheduler</application>. It is the database
21 administrator's responsibility to set up appropriate scripts, and to
22 check that they execute successfully.
23 </para>
24
25 <para>
26 One obvious maintenance task is the creation of backup copies of the data on a
27 regular schedule. Without a recent backup, you have no chance of recovery
28 after a catastrophe (disk failure, fire, mistakenly dropping a critical
29 table, etc.). The backup and recovery mechanisms available in
30 <productname>PostgreSQL</productname> are discussed at length in
31 <xref linkend="backup"/>.
32 </para>
33
34 <para>
35 The other main category of maintenance task is periodic <quote>vacuuming</quote>
36 of the database. This activity is discussed in
37 <xref linkend="routine-vacuuming"/>. Closely related to this is updating
38 the statistics that will be used by the query planner, as discussed in
39 <xref linkend="vacuum-for-statistics"/>.
40 </para>
41
42 <para>
43 Another task that might need periodic attention is log file management.
44 This is discussed in <xref linkend="logfile-maintenance"/>.
45 </para>
46
47 <para>
48 <ulink
49 url="https://bucardo.org/check_postgres/"><application>check_postgres</application></ulink>
50 is available for monitoring database health and reporting unusual
51 conditions. <application>check_postgres</application> integrates with
52 Nagios and MRTG, but can be run standalone too.
53 </para>
54
55 <para>
56 <productname>PostgreSQL</productname> is low-maintenance compared
57 to some other database management systems. Nonetheless,
58 appropriate attention to these tasks will go far towards ensuring a
59 pleasant and productive experience with the system.
60 </para>
61
62 <sect1 id="routine-vacuuming">
63 <title>Routine Vacuuming</title>
64
65 <indexterm zone="routine-vacuuming">
66 <primary>vacuum</primary>
67 </indexterm>
68
69 <para>
70 <productname>PostgreSQL</productname> databases require periodic
71 maintenance known as <firstterm>vacuuming</firstterm>. For many installations, it
72 is sufficient to let vacuuming be performed by the <firstterm>autovacuum
73 daemon</firstterm>, which is described in <xref linkend="autovacuum"/>. You might
74 need to adjust the autovacuuming parameters described there to obtain best
75 results for your situation. Some database administrators will want to
76 supplement or replace the daemon's activities with manually-managed
77 <command>VACUUM</command> commands, which typically are executed according to a
78 schedule by <application>cron</application> or <application>Task
79 Scheduler</application> scripts. To set up manually-managed vacuuming properly,
80 it is essential to understand the issues discussed in the next few
81 subsections. Administrators who rely on autovacuuming may still wish
82 to skim this material to help them understand and adjust autovacuuming.
83 </para>
84
85 <sect2 id="vacuum-basics">
86 <title>Vacuuming Basics</title>
87
88 <para>
89 <productname>PostgreSQL</productname>'s
90 <link linkend="sql-vacuum"><command>VACUUM</command></link> command has to
91 process each table on a regular basis for several reasons:
92
93 <orderedlist>
94 <listitem>
95 <simpara>To recover or reuse disk space occupied by updated or deleted
96 rows.</simpara>
97 </listitem>
98
99 <listitem>
100 <simpara>To update data statistics used by the
101 <productname>PostgreSQL</productname> query planner.</simpara>
102 </listitem>
103
104 <listitem>
105 <simpara>To update the visibility map, which speeds
106 up <link linkend="indexes-index-only-scans">index-only
107 scans</link>.</simpara>
108 </listitem>
109
110 <listitem>
111 <simpara>To protect against loss of very old data due to
112 <firstterm>transaction ID wraparound</firstterm> or
113 <firstterm>multixact ID wraparound</firstterm>.</simpara>
114 </listitem>
115 </orderedlist>
116
117 Each of these reasons dictates performing <command>VACUUM</command> operations
118 of varying frequency and scope, as explained in the following subsections.
119 </para>
120
121 <para>
122 There are two variants of <command>VACUUM</command>: standard <command>VACUUM</command>
123 and <command>VACUUM FULL</command>. <command>VACUUM FULL</command> can reclaim more
124 disk space but runs much more slowly. Also,
125 the standard form of <command>VACUUM</command> can run in parallel with production
126 database operations. (Commands such as <command>SELECT</command>,
127 <command>INSERT</command>, <command>UPDATE</command>, and
128 <command>DELETE</command> will continue to function normally, though you
129 will not be able to modify the definition of a table with commands such as
130 <command>ALTER TABLE</command> while it is being vacuumed.)
131 <command>VACUUM FULL</command> requires an
132 <literal>ACCESS EXCLUSIVE</literal> lock on the table it is
133 working on, and therefore cannot be done in parallel with other use
134 of the table. Generally, therefore,
135 administrators should strive to use standard <command>VACUUM</command> and
136 avoid <command>VACUUM FULL</command>.
137 </para>
138
139 <para>
140 <command>VACUUM</command> creates a substantial amount of I/O
141 traffic, which can cause poor performance for other active sessions.
142 There are configuration parameters that can be adjusted to reduce the
143 performance impact of background vacuuming &mdash; see
144 <xref linkend="runtime-config-resource-vacuum-cost"/>.
145 </para>
146 </sect2>
147
148 <sect2 id="vacuum-for-space-recovery">
149 <title>Recovering Disk Space</title>
150
151 <indexterm zone="vacuum-for-space-recovery">
152 <primary>disk space</primary>
153 </indexterm>
154
155 <para>
156 In <productname>PostgreSQL</productname>, an
157 <command>UPDATE</command> or <command>DELETE</command> of a row does not
158 immediately remove the old version of the row.
159 This approach is necessary to gain the benefits of multiversion
160 concurrency control (<acronym>MVCC</acronym>, see <xref linkend="mvcc"/>): the row version
161 must not be deleted while it is still potentially visible to other
162 transactions. But eventually, an outdated or deleted row version is no
163 longer of interest to any transaction. The space it occupies must then be
164 reclaimed for reuse by new rows, to avoid unbounded growth of disk
165 space requirements. This is done by running <command>VACUUM</command>.
166 </para>
167
168 <para>
169 The standard form of <command>VACUUM</command> removes dead row
170 versions in tables and indexes and marks the space available for
171 future reuse. However, it will not return the space to the operating
172 system, except in the special case where one or more pages at the
173 end of a table become entirely free and an exclusive table lock can be
174 easily obtained. In contrast, <command>VACUUM FULL</command> actively compacts
175 tables by writing a complete new version of the table file with no dead
176 space. This minimizes the size of the table, but can take a long time.
177 It also requires extra disk space for the new copy of the table, until
178 the operation completes.
179 </para>
180
181 <para>
182 The usual goal of routine vacuuming is to do standard <command>VACUUM</command>s
183 often enough to avoid needing <command>VACUUM FULL</command>. The
184 autovacuum daemon attempts to work this way, and in fact will
185 never issue <command>VACUUM FULL</command>. In this approach, the idea
186 is not to keep tables at their minimum size, but to maintain steady-state
187 usage of disk space: each table occupies space equivalent to its
188 minimum size plus however much space gets used up between vacuum runs.
189 Although <command>VACUUM FULL</command> can be used to shrink a table back
190 to its minimum size and return the disk space to the operating system,
191 there is not much point in this if the table will just grow again in the
192 future. Thus, moderately-frequent standard <command>VACUUM</command> runs are a
193 better approach than infrequent <command>VACUUM FULL</command> runs for
194 maintaining heavily-updated tables.
195 </para>
196
197 <para>
198 Some administrators prefer to schedule vacuuming themselves, for example
199 doing all the work at night when load is low.
200 The difficulty with doing vacuuming according to a fixed schedule
201 is that if a table has an unexpected spike in update activity, it may
202 get bloated to the point that <command>VACUUM FULL</command> is really necessary
203 to reclaim space. Using the autovacuum daemon alleviates this problem,
204 since the daemon schedules vacuuming dynamically in response to update
205 activity. It is unwise to disable the daemon completely unless you
206 have an extremely predictable workload. One possible compromise is
207 to set the daemon's parameters so that it will only react to unusually
208 heavy update activity, thus keeping things from getting out of hand,
209 while scheduled <command>VACUUM</command>s are expected to do the bulk of the
210 work when the load is typical.
211 </para>
212
213 <para>
214 For those not using autovacuum, a typical approach is to schedule a
215 database-wide <command>VACUUM</command> once a day during a low-usage period,
216 supplemented by more frequent vacuuming of heavily-updated tables as
217 necessary. (Some installations with extremely high update rates vacuum
218 their busiest tables as often as once every few minutes.) If you have
219 multiple databases in a cluster, don't forget to
220 <command>VACUUM</command> each one; the program <xref
221 linkend="app-vacuumdb"/> might be helpful.
222 </para>
223
224 <tip>
225 <para>
226 Plain <command>VACUUM</command> may not be satisfactory when
227 a table contains large numbers of dead row versions as a result of
228 massive update or delete activity. If you have such a table and
229 you need to reclaim the excess disk space it occupies, you will need
230 to use <command>VACUUM FULL</command>, or alternatively
231 <link linkend="sql-cluster"><command>CLUSTER</command></link>
232 or one of the table-rewriting variants of
233 <link linkend="sql-altertable"><command>ALTER TABLE</command></link>.
234 These commands rewrite an entire new copy of the table and build
235 new indexes for it. All these options require an
236 <literal>ACCESS EXCLUSIVE</literal> lock. Note that
237 they also temporarily use extra disk space approximately equal to the size
238 of the table, since the old copies of the table and indexes can't be
239 released until the new ones are complete.
240 </para>
241 </tip>
242
243 <tip>
244 <para>
245 If you have a table whose entire contents are deleted on a periodic
246 basis, consider doing it with
247 <link linkend="sql-truncate"><command>TRUNCATE</command></link> rather
248 than using <command>DELETE</command> followed by
249 <command>VACUUM</command>. <command>TRUNCATE</command> removes the
250 entire content of the table immediately, without requiring a
251 subsequent <command>VACUUM</command> or <command>VACUUM
252 FULL</command> to reclaim the now-unused disk space.
253 The disadvantage is that strict MVCC semantics are violated.
254 </para>
255 </tip>
256 </sect2>
257
258 <sect2 id="vacuum-for-statistics">
259 <title>Updating Planner Statistics</title>
260
261 <indexterm zone="vacuum-for-statistics">
262 <primary>statistics</primary>
263 <secondary>of the planner</secondary>
264 </indexterm>
265
266 <indexterm zone="vacuum-for-statistics">
267 <primary>ANALYZE</primary>
268 </indexterm>
269
270 <para>
271 The <productname>PostgreSQL</productname> query planner relies on
272 statistical information about the contents of tables in order to
273 generate good plans for queries. These statistics are gathered by
274 the <link linkend="sql-analyze"><command>ANALYZE</command></link> command,
275 which can be invoked by itself or
276 as an optional step in <command>VACUUM</command>. It is important to have
277 reasonably accurate statistics, otherwise poor choices of plans might
278 degrade database performance.
279 </para>
280
281 <para>
282 The autovacuum daemon, if enabled, will automatically issue
283 <command>ANALYZE</command> commands whenever the content of a table has
284 changed sufficiently. However, administrators might prefer to rely
285 on manually-scheduled <command>ANALYZE</command> operations, particularly
286 if it is known that update activity on a table will not affect the
287 statistics of <quote>interesting</quote> columns. The daemon schedules
288 <command>ANALYZE</command> strictly as a function of the number of rows
289 inserted or updated; it has no knowledge of whether that will lead
290 to meaningful statistical changes.
291 </para>
292
293 <para>
294 Tuples changed in partitions and inheritance children do not trigger
295 analyze on the parent table. If the parent table is empty or rarely
296 changed, it may never be processed by autovacuum, and the statistics for
297 the inheritance tree as a whole won't be collected. It is necessary to
298 run <command>ANALYZE</command> on the parent table manually in order to
299 keep the statistics up to date.
300 </para>
301
302 <para>
303 As with vacuuming for space recovery, frequent updates of statistics
304 are more useful for heavily-updated tables than for seldom-updated
305 ones. But even for a heavily-updated table, there might be no need for
306 statistics updates if the statistical distribution of the data is
307 not changing much. A simple rule of thumb is to think about how much
308 the minimum and maximum values of the columns in the table change.
309 For example, a <type>timestamp</type> column that contains the time
310 of row update will have a constantly-increasing maximum value as
311 rows are added and updated; such a column will probably need more
312 frequent statistics updates than, say, a column containing URLs for
313 pages accessed on a website. The URL column might receive changes just
314 as often, but the statistical distribution of its values probably
315 changes relatively slowly.
316 </para>
317
318 <para>
319 It is possible to run <command>ANALYZE</command> on specific tables and even
320 just specific columns of a table, so the flexibility exists to update some
321 statistics more frequently than others if your application requires it.
322 In practice, however, it is usually best to just analyze the entire
323 database, because it is a fast operation. <command>ANALYZE</command> uses a
324 statistically random sampling of the rows of a table rather than reading
325 every single row.
326 </para>
327
328 <tip>
329 <para>
330 Although per-column tweaking of <command>ANALYZE</command> frequency might not be
331 very productive, you might find it worthwhile to do per-column
332 adjustment of the level of detail of the statistics collected by
333 <command>ANALYZE</command>. Columns that are heavily used in <literal>WHERE</literal>
334 clauses and have highly irregular data distributions might require a
335 finer-grain data histogram than other columns. See <command>ALTER TABLE
336 SET STATISTICS</command>, or change the database-wide default using the <xref
337 linkend="guc-default-statistics-target"/> configuration parameter.
338 </para>
339
340 <para>
341 Also, by default there is limited information available about
342 the selectivity of functions. However, if you create a statistics
343 object or an expression
344 index that uses a function call, useful statistics will be
345 gathered about the function, which can greatly improve query
346 plans that use the expression index.
347 </para>
348 </tip>
349
350 <tip>
351 <para>
352 The autovacuum daemon does not issue <command>ANALYZE</command> commands for
353 foreign tables, since it has no means of determining how often that
354 might be useful. If your queries require statistics on foreign tables
355 for proper planning, it's a good idea to run manually-managed
356 <command>ANALYZE</command> commands on those tables on a suitable schedule.
357 </para>
358 </tip>
359
360 <tip>
361 <para>
362 The autovacuum daemon does not issue <command>ANALYZE</command> commands
363 for partitioned tables. Inheritance parents will only be analyzed if the
364 parent itself is changed - changes to child tables do not trigger
365 autoanalyze on the parent table. If your queries require statistics on
366 parent tables for proper planning, it is necessary to periodically run
367 a manual <command>ANALYZE</command> on those tables to keep the statistics
368 up to date.
369 </para>
370 </tip>
371
372 </sect2>
373
374 <sect2 id="vacuum-for-visibility-map">
375 <title>Updating the Visibility Map</title>
376
377 <para>
378 Vacuum maintains a <link linkend="storage-vm">visibility map</link> for each
379 table to keep track of which pages contain only tuples that are known to be
380 visible to all active transactions (and all future transactions, until the
381 page is again modified). This has two purposes. First, vacuum
382 itself can skip such pages on the next run, since there is nothing to
383 clean up.
384 </para>
385
386 <para>
387 Second, it allows <productname>PostgreSQL</productname> to answer some
388 queries using only the index, without reference to the underlying table.
389 Since <productname>PostgreSQL</productname> indexes don't contain tuple
390 visibility information, a normal index scan fetches the heap tuple for each
391 matching index entry, to check whether it should be seen by the current
392 transaction.
393 An <link linkend="indexes-index-only-scans"><firstterm>index-only
394 scan</firstterm></link>, on the other hand, checks the visibility map first.
395 If it's known that all tuples on the page are
396 visible, the heap fetch can be skipped. This is most useful on
397 large data sets where the visibility map can prevent disk accesses.
398 The visibility map is vastly smaller than the heap, so it can easily be
399 cached even when the heap is very large.
400 </para>
401 </sect2>
402
403 <sect2 id="vacuum-for-wraparound">
404 <title>Preventing Transaction ID Wraparound Failures</title>
405
406 <indexterm zone="vacuum-for-wraparound">
407 <primary>transaction ID</primary>
408 <secondary>wraparound</secondary>
409 </indexterm>
410
411 <indexterm>
412 <primary>wraparound</primary>
413 <secondary>of transaction IDs</secondary>
414 </indexterm>
415
416 <para>
417 <productname>PostgreSQL</productname>'s
418 <link linkend="mvcc-intro">MVCC</link> transaction semantics
419 depend on being able to compare transaction ID (<acronym>XID</acronym>)
420 numbers: a row version with an insertion XID greater than the current
421 transaction's XID is <quote>in the future</quote> and should not be visible
422 to the current transaction. But since transaction IDs have limited size
423 (32 bits) a cluster that runs for a long time (more
424 than 4 billion transactions) would suffer <firstterm>transaction ID
425 wraparound</firstterm>: the XID counter wraps around to zero, and all of a sudden
426 transactions that were in the past appear to be in the future &mdash; which
427 means their output become invisible. In short, catastrophic data loss.
428 (Actually the data is still there, but that's cold comfort if you cannot
429 get at it.) To avoid this, it is necessary to vacuum every table
430 in every database at least once every two billion transactions.
431 </para>
432
433 <para>
434 The reason that periodic vacuuming solves the problem is that
435 <command>VACUUM</command> will mark rows as <emphasis>frozen</emphasis>, indicating that
436 they were inserted by a transaction that committed sufficiently far in
437 the past that the effects of the inserting transaction are certain to be
438 visible to all current and future transactions.
439 Normal XIDs are
440 compared using modulo-2<superscript>32</superscript> arithmetic. This means
441 that for every normal XID, there are two billion XIDs that are
442 <quote>older</quote> and two billion that are <quote>newer</quote>; another
443 way to say it is that the normal XID space is circular with no
444 endpoint. Therefore, once a row version has been created with a particular
445 normal XID, the row version will appear to be <quote>in the past</quote> for
446 the next two billion transactions, no matter which normal XID we are
447 talking about. If the row version still exists after more than two billion
448 transactions, it will suddenly appear to be in the future. To
449 prevent this, <productname>PostgreSQL</productname> reserves a special XID,
450 <literal>FrozenTransactionId</literal>, which does not follow the normal XID
451 comparison rules and is always considered older
452 than every normal XID.
453 Frozen row versions are treated as if the inserting XID were
454 <literal>FrozenTransactionId</literal>, so that they will appear to be
455 <quote>in the past</quote> to all normal transactions regardless of wraparound
456 issues, and so such row versions will be valid until deleted, no matter
457 how long that is.
458 </para>
459
460 <note>
461 <para>
462 In <productname>PostgreSQL</productname> versions before 9.4, freezing was
463 implemented by actually replacing a row's insertion XID
464 with <literal>FrozenTransactionId</literal>, which was visible in the
465 row's <structname>xmin</structname> system column. Newer versions just set a flag
466 bit, preserving the row's original <structname>xmin</structname> for possible
467 forensic use. However, rows with <structname>xmin</structname> equal
468 to <literal>FrozenTransactionId</literal> (2) may still be found
469 in databases <application>pg_upgrade</application>'d from pre-9.4 versions.
470 </para>
471 <para>
472 Also, system catalogs may contain rows with <structname>xmin</structname> equal
473 to <literal>BootstrapTransactionId</literal> (1), indicating that they were
474 inserted during the first phase of <application>initdb</application>.
475 Like <literal>FrozenTransactionId</literal>, this special XID is treated as
476 older than every normal XID.
477 </para>
478 </note>
479
480 <para>
481 <xref linkend="guc-vacuum-freeze-min-age"/>
482 controls how old an XID value has to be before rows bearing that XID will be
483 frozen. Increasing this setting may avoid unnecessary work if the
484 rows that would otherwise be frozen will soon be modified again,
485 but decreasing this setting increases
486 the number of transactions that can elapse before the table must be
487 vacuumed again.
488 </para>
489
490 <para>
491 <command>VACUUM</command> uses the <link linkend="storage-vm">visibility map</link>
492 to determine which pages of a table must be scanned. Normally, it
493 will skip pages that don't have any dead row versions even if those pages
494 might still have row versions with old XID values. Therefore, normal
495 <command>VACUUM</command>s won't always freeze every old row version in the table.
496 When that happens, <command>VACUUM</command> will eventually need to perform an
497 <firstterm>aggressive vacuum</firstterm>, which will freeze all eligible unfrozen
498 XID and MXID values, including those from all-visible but not all-frozen pages.
499 </para>
500
501 <para>
502 If a table is building up a backlog of all-visible but not all-frozen
503 pages, a normal vacuum may choose to scan skippable pages in an effort to
504 freeze them. Doing so decreases the number of pages the next aggressive
505 vacuum must scan. These are referred to as <firstterm>eagerly
506 scanned</firstterm> pages. Eager scanning can be tuned to attempt to freeze
507 more all-visible pages by increasing <xref
508 linkend="guc-vacuum-max-eager-freeze-failure-rate"/>. Even if eager
509 scanning has kept the number of all-visible but not all-frozen pages to a
510 minimum, most tables still require periodic aggressive vacuuming. However,
511 any pages successfully eager frozen may be skipped during an aggressive
512 vacuum, so eager freezing may minimize the overhead of aggressive vacuums.
513 </para>
514
515 <para>
516 <xref linkend="guc-vacuum-freeze-table-age"/>
517 controls when a table is aggressively vacuumed. All all-visible but not all-frozen
518 pages are scanned if the number of transactions that have passed since the
519 last such scan is greater than <varname>vacuum_freeze_table_age</varname> minus
520 <varname>vacuum_freeze_min_age</varname>. Setting
521 <varname>vacuum_freeze_table_age</varname> to 0 forces <command>VACUUM</command> to
522 always use its aggressive strategy.
523 </para>
524
525 <para>
526 The maximum time that a table can go unvacuumed is two billion
527 transactions minus the <varname>vacuum_freeze_min_age</varname> value at
528 the time of the last aggressive vacuum. If it were to go
529 unvacuumed for longer than
530 that, data loss could result. To ensure that this does not happen,
531 autovacuum is invoked on any table that might contain unfrozen rows with
532 XIDs older than the age specified by the configuration parameter <xref
533 linkend="guc-autovacuum-freeze-max-age"/>. (This will happen even if
534 autovacuum is disabled.)
535 </para>
536
537 <para>
538 This implies that if a table is not otherwise vacuumed,
539 autovacuum will be invoked on it approximately once every
540 <varname>autovacuum_freeze_max_age</varname> minus
541 <varname>vacuum_freeze_min_age</varname> transactions.
542 For tables that are regularly vacuumed for space reclamation purposes,
543 this is of little importance. However, for static tables
544 (including tables that receive inserts, but no updates or deletes),
545 there is no need to vacuum for space reclamation, so it can
546 be useful to try to maximize the interval between forced autovacuums
547 on very large static tables. Obviously one can do this either by
548 increasing <varname>autovacuum_freeze_max_age</varname> or decreasing
549 <varname>vacuum_freeze_min_age</varname>.
550 </para>
551
552 <para>
553 The effective maximum for <varname>vacuum_freeze_table_age</varname> is 0.95 *
554 <varname>autovacuum_freeze_max_age</varname>; a setting higher than that will be
555 capped to the maximum. A value higher than
556 <varname>autovacuum_freeze_max_age</varname> wouldn't make sense because an
557 anti-wraparound autovacuum would be triggered at that point anyway, and
558 the 0.95 multiplier leaves some breathing room to run a manual
559 <command>VACUUM</command> before that happens. As a rule of thumb,
560 <command>vacuum_freeze_table_age</command> should be set to a value somewhat
561 below <varname>autovacuum_freeze_max_age</varname>, leaving enough gap so that
562 a regularly scheduled <command>VACUUM</command> or an autovacuum triggered by
563 normal delete and update activity is run in that window. Setting it too
564 close could lead to anti-wraparound autovacuums, even though the table
565 was recently vacuumed to reclaim space, whereas lower values lead to more
566 frequent aggressive vacuuming.
567 </para>
568
569 <para>
570 The sole disadvantage of increasing <varname>autovacuum_freeze_max_age</varname>
571 (and <varname>vacuum_freeze_table_age</varname> along with it) is that
572 the <filename>pg_xact</filename> and <filename>pg_commit_ts</filename>
573 subdirectories of the database cluster will take more space, because it
574 must store the commit status and (if <varname>track_commit_timestamp</varname> is
575 enabled) timestamp of all transactions back to
576 the <varname>autovacuum_freeze_max_age</varname> horizon. The commit status uses
577 two bits per transaction, so if
578 <varname>autovacuum_freeze_max_age</varname> is set to its maximum allowed value
579 of two billion, <filename>pg_xact</filename> can be expected to grow to about half
580 a gigabyte and <filename>pg_commit_ts</filename> to about 20GB. If this
581 is trivial compared to your total database size,
582 setting <varname>autovacuum_freeze_max_age</varname> to its maximum allowed value
583 is recommended. Otherwise, set it depending on what you are willing to
584 allow for <filename>pg_xact</filename> and <filename>pg_commit_ts</filename> storage.
585 (The default, 200 million transactions, translates to about 50MB
586 of <filename>pg_xact</filename> storage and about 2GB of <filename>pg_commit_ts</filename>
587 storage.)
588 </para>
589
590 <para>
591 One disadvantage of decreasing <varname>vacuum_freeze_min_age</varname> is that
592 it might cause <command>VACUUM</command> to do useless work: freezing a row
593 version is a waste of time if the row is modified
594 soon thereafter (causing it to acquire a new XID). So the setting should
595 be large enough that rows are not frozen until they are unlikely to change
596 any more.
597 </para>
598
599 <para>
600 To track the age of the oldest unfrozen XIDs in a database,
601 <command>VACUUM</command> stores XID
602 statistics in the system tables <structname>pg_class</structname> and
603 <structname>pg_database</structname>. In particular,
604 the <structfield>relfrozenxid</structfield> column of a table's
605 <structname>pg_class</structname> row contains the oldest remaining unfrozen
606 XID at the end of the most recent <command>VACUUM</command> that successfully
607 advanced <structfield>relfrozenxid</structfield> (typically the most recent
608 aggressive VACUUM). Similarly, the
609 <structfield>datfrozenxid</structfield> column of a database's
610 <structname>pg_database</structname> row is a lower bound on the unfrozen XIDs
611 appearing in that database &mdash; it is just the minimum of the
612 per-table <structfield>relfrozenxid</structfield> values within the database.
613 A convenient way to
614 examine this information is to execute queries such as:
615
616 <programlisting>
617 SELECT c.oid::regclass as table_name,
618 greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
619 FROM pg_class c
620 LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
621 WHERE c.relkind IN ('r', 'm');
622
623 SELECT datname, age(datfrozenxid) FROM pg_database;
624 </programlisting>
625
626 The <literal>age</literal> column measures the number of transactions from the
627 cutoff XID to the current transaction's XID.
628 </para>
629
630 <tip>
631 <para>
632 When the <command>VACUUM</command> command's <literal>VERBOSE</literal>
633 parameter is specified, <command>VACUUM</command> prints various
634 statistics about the table. This includes information about how
635 <structfield>relfrozenxid</structfield> and
636 <structfield>relminmxid</structfield> advanced, and the number of
637 newly frozen pages. The same details appear in the server log when
638 autovacuum logging (controlled by <xref
639 linkend="guc-log-autovacuum-min-duration"/>) reports on a
640 <command>VACUUM</command> operation executed by autovacuum.
641 </para>
642 </tip>
643
644 <para>
645 While <command>VACUUM</command> scans mostly pages that have been
646 modified since the last vacuum, it may also eagerly scan some
647 all-visible but not all-frozen pages in an attempt to freeze them, but
648 the <structfield>relfrozenxid</structfield> will only be advanced when
649 every page of the table that might contain unfrozen XIDs is scanned.
650 This happens when
651 <structfield>relfrozenxid</structfield> is more than
652 <varname>vacuum_freeze_table_age</varname> transactions old, when
653 <command>VACUUM</command>'s <literal>FREEZE</literal> option is used, or when all
654 pages that are not already all-frozen happen to
655 require vacuuming to remove dead row versions. When <command>VACUUM</command>
656 scans every page in the table that is not already all-frozen, it should
657 set <literal>age(relfrozenxid)</literal> to a value just a little more than the
658 <varname>vacuum_freeze_min_age</varname> setting
659 that was used (more by the number of transactions started since the
660 <command>VACUUM</command> started). <command>VACUUM</command>
661 will set <structfield>relfrozenxid</structfield> to the oldest XID
662 that remains in the table, so it's possible that the final value
663 will be much more recent than strictly required.
664 If no <structfield>relfrozenxid</structfield>-advancing
665 <command>VACUUM</command> is issued on the table until
666 <varname>autovacuum_freeze_max_age</varname> is reached, an autovacuum will soon
667 be forced for the table.
668 </para>
669
670 <para>
671 If for some reason autovacuum fails to clear old XIDs from a table, the
672 system will begin to emit warning messages like this when the database's
673 oldest XIDs reach forty million transactions from the wraparound point:
674
675 <programlisting>
676 WARNING: database "mydb" must be vacuumed within 39985967 transactions
677 HINT: To avoid XID assignment failures, execute a database-wide VACUUM in that database.
678 </programlisting>
679
680 (A manual <command>VACUUM</command> should fix the problem, as suggested by the
681 hint; but note that the <command>VACUUM</command> should be performed by a
682 superuser, else it will fail to process system catalogs, which prevent it from
683 being able to advance the database's <structfield>datfrozenxid</structfield>.)
684 If these warnings are ignored, the system will refuse to assign new XIDs once
685 there are fewer than three million transactions left until wraparound:
686
687 <programlisting>
688 ERROR: database is not accepting commands that assign new transaction IDs to avoid wraparound data loss in database "mydb"
689 HINT: Execute a database-wide VACUUM in that database.
690 </programlisting>
691
692 In this condition any transactions already in progress can continue,
693 but only read-only transactions can be started. Operations that
694 modify database records or truncate relations will fail.
695 The <command>VACUUM</command> command can still be run normally.
696 Note that, contrary to what was sometimes recommended in earlier releases,
697 it is not necessary or desirable to stop the postmaster or enter single
698 user-mode in order to restore normal operation.
699 Instead, follow these steps:
700
701 <orderedlist>
702 <listitem>
703 <simpara>Resolve old prepared transactions. You can find these by checking
704 <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link> for rows where
705 <literal>age(transactionid)</literal> is large. Such transactions should be
706 committed or rolled back.</simpara>
707 </listitem>
708 <listitem>
709 <simpara>End long-running open transactions. You can find these by checking
710 <link linkend="monitoring-pg-stat-activity-view"><structname>pg_stat_activity</structname></link> for rows where
711 <literal>age(backend_xid)</literal> or <literal>age(backend_xmin)</literal> is
712 large. Such transactions should be committed or rolled back, or the session
713 can be terminated using <literal>pg_terminate_backend</literal>.</simpara>
714 </listitem>
715 <listitem>
716 <simpara>Drop any old replication slots. Use
717 <link linkend="view-pg-replication-slots"><structname>pg_replication_slots</structname></link> to
718 find slots where <literal>age(xmin)</literal> or <literal>age(catalog_xmin)</literal>
719 is large. In many cases, such slots were created for replication to servers that no
720 longer exist, or that have been down for a long time. If you drop a slot for a server
721 that still exists and might still try to connect to that slot, that replica may
722 need to be rebuilt.</simpara>
723 </listitem>
724 <listitem>
725 <simpara>Execute <command>VACUUM</command> in the target database. A database-wide
726 <literal>VACUUM</literal> is simplest; to reduce the time required, it as also possible
727 to issue manual <command>VACUUM</command> commands on the tables where
728 <structfield>relminxid</structfield> is oldest. Do not use <literal>VACUUM FULL</literal>
729 in this scenario, because it requires an XID and will therefore fail, except in super-user
730 mode, where it will instead consume an XID and thus increase the risk of transaction ID
731 wraparound. Do not use <literal>VACUUM FREEZE</literal> either, because it will do
732 more than the minimum amount of work required to restore normal operation.</simpara>
733 </listitem>
734 <listitem>
735 <simpara>Once normal operation is restored, ensure that autovacuum is properly configured
736 in the target database in order to avoid future problems.</simpara>
737 </listitem>
738 </orderedlist>
739 </para>
740
741 <note>
742 <para>
743 In earlier versions, it was sometimes necessary to stop the postmaster and
744 <command>VACUUM</command> the database in a single-user mode. In typical scenarios, this
745 is no longer necessary, and should be avoided whenever possible, since it involves taking
746 the system down. It is also riskier, since it disables transaction ID wraparound safeguards
747 that are designed to prevent data loss. The only reason to use single-user mode in this
748 scenario is if you wish to <command>TRUNCATE</command> or <command>DROP</command> unneeded
749 tables to avoid needing to <command>VACUUM</command> them. The three-million-transaction
750 safety margin exists to let the administrator do this. See the
751 <xref linkend="app-postgres"/> reference page for details about using single-user mode.
752 </para>
753 </note>
754
755 <sect3 id="vacuum-for-multixact-wraparound">
756 <title>Multixacts and Wraparound</title>
757
758 <indexterm>
759 <primary>MultiXactId</primary>
760 </indexterm>
761
762 <indexterm>
763 <primary>wraparound</primary>
764 <secondary>of multixact IDs</secondary>
765 </indexterm>
766
767 <para>
768 <firstterm>Multixact IDs</firstterm> are used to support row locking by
769 multiple transactions. Since there is only limited space in a tuple
770 header to store lock information, that information is encoded as
771 a <quote>multiple transaction ID</quote>, or multixact ID for short,
772 whenever there is more than one transaction concurrently locking a
773 row. Information about which transaction IDs are included in any
774 particular multixact ID is stored separately in
775 the <filename>pg_multixact</filename> subdirectory, and only the multixact ID
776 appears in the <structfield>xmax</structfield> field in the tuple header.
777 Like transaction IDs, multixact IDs are implemented as a
778 32-bit counter and corresponding storage, all of which requires
779 careful aging management, storage cleanup, and wraparound handling.
780 There is a separate storage area which holds the list of members in
781 each multixact, which also uses a 32-bit counter and which must also
782 be managed. The system function
783 <function>pg_get_multixact_members()</function> described in
784 <xref linkend="functions-pg-snapshot"/> can be used to examine the
785 transaction IDs associated with a multixact ID.
786 </para>
787
788 <para>
789 Whenever <command>VACUUM</command> scans any part of a table, it will replace
790 any multixact ID it encounters which is older than
791 <xref linkend="guc-vacuum-multixact-freeze-min-age"/>
792 by a different value, which can be the zero value, a single
793 transaction ID, or a newer multixact ID. For each table,
794 <structname>pg_class</structname>.<structfield>relminmxid</structfield> stores the oldest
795 possible multixact ID still appearing in any tuple of that table.
796 If this value is older than
797 <xref linkend="guc-vacuum-multixact-freeze-table-age"/>, an aggressive
798 vacuum is forced. As discussed in the previous section, an aggressive
799 vacuum means that only those pages which are known to be all-frozen will
800 be skipped. <function>mxid_age()</function> can be used on
801 <structname>pg_class</structname>.<structfield>relminmxid</structfield> to find its age.
802 </para>
803
804 <para>
805 Aggressive <command>VACUUM</command>s, regardless of what causes
806 them, are <emphasis>guaranteed</emphasis> to be able to advance
807 the table's <structfield>relminmxid</structfield>.
808 Eventually, as all tables in all databases are scanned and their
809 oldest multixact values are advanced, on-disk storage for older
810 multixacts can be removed.
811 </para>
812
813 <para>
814 As a safety device, an aggressive vacuum scan will
815 occur for any table whose multixact-age is greater than <xref
816 linkend="guc-autovacuum-multixact-freeze-max-age"/>. Also, if the
817 storage occupied by multixacts members exceeds about 10GB, aggressive vacuum
818 scans will occur more often for all tables, starting with those that
819 have the oldest multixact-age. Both of these kinds of aggressive
820 scans will occur even if autovacuum is nominally disabled. The members storage
821 area can grow up to about 20GB before reaching wraparound.
822 </para>
823
824 <para>
825 Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
826 system will begin to emit warning messages when the database's oldest MXIDs reach forty
827 million transactions from the wraparound point. And, just as in the XID case, if these
828 warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
829 than three million left until wraparound.
830 </para>
831
832 <para>
833 Normal operation when MXIDs are exhausted can be restored in much the same way as
834 when XIDs are exhausted. Follow the same steps in the previous section, but with the
835 following differences:
836
837 <orderedlist>
838 <listitem>
839 <simpara>Running transactions and prepared transactions can be ignored if there
840 is no chance that they might appear in a multixact.</simpara>
841 </listitem>
842 <listitem>
843 <simpara>Unlike transaction ID wraparound, replication slots do not
844 directly hold back multixact cleanup. Dropping stale replication
845 slots is therefore not usually relevant to resolving multixact ID
846 wraparound problems.</simpara>
847 </listitem>
848 <listitem>
849 <simpara>MXID information is not directly visible in system views such as
850 <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
851 way of determining which transactions are causing MXID wraparound problems.</simpara>
852 </listitem>
853 <listitem>
854 <simpara>XID exhaustion will block all write transactions, but MXID exhaustion will
855 only block a subset of write transactions, specifically those that involve
856 row locks that require an MXID.</simpara>
857 </listitem>
858 </orderedlist>
859 </para>
860
861 </sect3>
862 </sect2>
863
864 <sect2 id="autovacuum">
865 <title>The Autovacuum Daemon</title>
866
867 <indexterm>
868 <primary>autovacuum</primary>
869 <secondary>general information</secondary>
870 </indexterm>
871 <para>
872 <productname>PostgreSQL</productname> has an optional but highly
873 recommended feature called <firstterm>autovacuum</firstterm>,
874 whose purpose is to automate the execution of
875 <command>VACUUM</command> and <command>ANALYZE</command> commands.
876 When enabled, autovacuum checks for
877 tables that have had a large number of inserted, updated or deleted
878 tuples. These checks use the statistics collection facility;
879 therefore, autovacuum cannot be used unless <xref
880 linkend="guc-track-counts"/> is set to <literal>true</literal>.
881 In the default configuration, autovacuuming is enabled and the related
882 configuration parameters are appropriately set.
883 </para>
884
885 <para>
886 The <quote>autovacuum daemon</quote> actually consists of multiple processes.
887 There is a persistent daemon process, called the
888 <firstterm>autovacuum launcher</firstterm>, which is in charge of starting
889 <firstterm>autovacuum worker</firstterm> processes for all databases. The
890 launcher will distribute the work across time, attempting to start one
891 worker within each database every <xref linkend="guc-autovacuum-naptime"/>
892 seconds. (Therefore, if the installation has <replaceable>N</replaceable> databases,
893 a new worker will be launched every
894 <varname>autovacuum_naptime</varname>/<replaceable>N</replaceable> seconds.)
895 A maximum of <xref linkend="guc-autovacuum-max-workers"/> worker processes
896 are allowed to run at the same time. If there are more than
897 <varname>autovacuum_max_workers</varname> databases to be processed,
898 the next database will be processed as soon as the first worker finishes.
899 Each worker process will check each table within its database and
900 execute <command>VACUUM</command> and/or <command>ANALYZE</command> as needed.
901 <xref linkend="guc-log-autovacuum-min-duration"/> can be set to monitor
902 autovacuum workers' activity.
903 </para>
904
905 <para>
906 If several large tables all become eligible for vacuuming in a short
907 amount of time, all autovacuum workers might become occupied with
908 vacuuming those tables for a long period. This would result
909 in other tables and databases not being vacuumed until a worker becomes
910 available. There is no limit on how many workers might be in a
911 single database, but workers do try to avoid repeating work that has
912 already been done by other workers. Note that the number of running
913 workers does not count towards <xref linkend="guc-max-connections"/> or
914 <xref linkend="guc-superuser-reserved-connections"/> limits.
915 </para>
916
917 <para>
918 Tables whose <structfield>relfrozenxid</structfield> value is more than
919 <xref linkend="guc-autovacuum-freeze-max-age"/> transactions old are always
920 vacuumed (this also applies to those tables whose freeze max age has
921 been modified via storage parameters; see below). Otherwise, if the
922 number of tuples obsoleted since the last
923 <command>VACUUM</command> exceeds the <quote>vacuum threshold</quote>, the
924 table is vacuumed. The vacuum threshold is defined as:
925 <programlisting>
926 vacuum threshold = Minimum(vacuum max threshold, vacuum base threshold + vacuum scale factor * number of tuples)
927 </programlisting>
928 where the vacuum max threshold is
929 <xref linkend="guc-autovacuum-vacuum-max-threshold"/>,
930 the vacuum base threshold is
931 <xref linkend="guc-autovacuum-vacuum-threshold"/>,
932 the vacuum scale factor is
933 <xref linkend="guc-autovacuum-vacuum-scale-factor"/>,
934 and the number of tuples is
935 <structname>pg_class</structname>.<structfield>reltuples</structfield>.
936 </para>
937
938 <para>
939 The table is also vacuumed if the number of tuples inserted since the last
940 vacuum has exceeded the defined insert threshold, which is defined as:
941 <programlisting>
942 vacuum insert threshold = vacuum base insert threshold + vacuum insert scale factor * number of tuples * percent of table not frozen
943 </programlisting>
944 where the vacuum insert base threshold is
945 <xref linkend="guc-autovacuum-vacuum-insert-threshold"/>,
946 the vacuum insert scale factor is
947 <xref linkend="guc-autovacuum-vacuum-insert-scale-factor"/>,
948 the number of tuples is
949 <structname>pg_class</structname>.<structfield>reltuples</structfield>,
950 and the percent of the table not frozen is
951 <literal>1 - pg_class.relallfrozen / pg_class.relpages</literal>.
952 Such vacuums may allow portions of the table to be marked as
953 <firstterm>all visible</firstterm> and also allow tuples to be frozen, which
954 can reduce the work required in subsequent vacuums.
955 For tables which receive <command>INSERT</command> operations but no or
956 almost no <command>UPDATE</command>/<command>DELETE</command> operations,
957 it may be beneficial to lower the table's
958 <xref linkend="reloption-autovacuum-freeze-min-age"/> as this may allow
959 tuples to be frozen by earlier vacuums. The number of obsolete tuples and
960 the number of inserted tuples are obtained from the cumulative statistics system;
961 it is an eventually-consistent count updated by each <command>UPDATE</command>,
962 <command>DELETE</command> and <command>INSERT</command> operation.
963 If the <structfield>relfrozenxid</structfield> value of the table
964 is more than <varname>vacuum_freeze_table_age</varname> transactions old,
965 an aggressive vacuum is performed to freeze old tuples and advance
966 <structfield>relfrozenxid</structfield>.
967 </para>
968
969 <para>
970 For analyze, a similar condition is used: the threshold, defined as:
971 <programlisting>
972 analyze threshold = analyze base threshold + analyze scale factor * number of tuples
973 </programlisting>
974 is compared to the total number of tuples inserted, updated, or deleted
975 since the last <command>ANALYZE</command>.
976 </para>
977
978 <para>
979 Partitioned tables do not directly store tuples and consequently
980 are not processed by autovacuum. (Autovacuum does process table
981 partitions just like other tables.) Unfortunately, this means that
982 autovacuum does not run <command>ANALYZE</command> on partitioned
983 tables, and this can cause suboptimal plans for queries that reference
984 partitioned table statistics. You can work around this problem by
985 manually running <command>ANALYZE</command> on partitioned tables
986 when they are first populated, and again whenever the distribution
987 of data in their partitions changes significantly.
988 </para>
989
990 <para>
991 Temporary tables cannot be accessed by autovacuum. Therefore,
992 appropriate vacuum and analyze operations should be performed via
993 session SQL commands.
994 </para>
995
996 <para>
997 The default thresholds and scale factors are taken from
998 <filename>postgresql.conf</filename>, but it is possible to override them
999 (and many other autovacuum control parameters) on a per-table basis; see
1000 <xref linkend="sql-createtable-storage-parameters"/> for more information.
1001 If a setting has been changed via a table's storage parameters, that value
1002 is used when processing that table; otherwise the global settings are
1003 used. See <xref linkend="runtime-config-autovacuum"/> for more details on
1004 the global settings.
1005 </para>
1006
1007 <para>
1008 When multiple workers are running, the autovacuum cost delay parameters
1009 (see <xref linkend="runtime-config-resource-vacuum-cost"/>) are
1010 <quote>balanced</quote> among all the running workers, so that the
1011 total I/O impact on the system is the same regardless of the number
1012 of workers actually running. However, any workers processing tables whose
1013 per-table <literal>autovacuum_vacuum_cost_delay</literal> or
1014 <literal>autovacuum_vacuum_cost_limit</literal> storage parameters have been set
1015 are not considered in the balancing algorithm.
1016 </para>
1017
1018 <para>
1019 Autovacuum workers generally don't block other commands. If a process
1020 attempts to acquire a lock that conflicts with the
1021 <literal>SHARE UPDATE EXCLUSIVE</literal> lock held by autovacuum, lock
1022 acquisition will interrupt the autovacuum. For conflicting lock modes,
1023 see <xref linkend="table-lock-compatibility"/>. However, if the autovacuum
1024 is running to prevent transaction ID wraparound (i.e., the autovacuum query
1025 name in the <structname>pg_stat_activity</structname> view ends with
1026 <literal>(to prevent wraparound)</literal>), the autovacuum is not
1027 automatically interrupted.
1028 </para>
1029
1030 <warning>
1031 <para>
1032 Regularly running commands that acquire locks conflicting with a
1033 <literal>SHARE UPDATE EXCLUSIVE</literal> lock (e.g., ANALYZE) can
1034 effectively prevent autovacuums from ever completing.
1035 </para>
1036 </warning>
1037 </sect2>
1038 </sect1>
1039
1040
1041 <sect1 id="routine-reindex">
1042 <title>Routine Reindexing</title>
1043
1044 <indexterm zone="routine-reindex">
1045 <primary>reindex</primary>
1046 </indexterm>
1047
1048 <para>
1049 In some situations it is worthwhile to rebuild indexes periodically
1050 with the <xref linkend="sql-reindex"/> command or a series of individual
1051 rebuilding steps.
1052
1053 </para>
1054
1055 <para>
1056 B-tree index pages that have become completely empty are reclaimed for
1057 re-use. However, there is still a possibility
1058 of inefficient use of space: if all but a few index keys on a page have
1059 been deleted, the page remains allocated. Therefore, a usage
1060 pattern in which most, but not all, keys in each range are eventually
1061 deleted will see poor use of space. For such usage patterns,
1062 periodic reindexing is recommended.
1063 </para>
1064
1065 <para>
1066 The potential for bloat in non-B-tree indexes has not been well
1067 researched. It is a good idea to periodically monitor the index's physical
1068 size when using any non-B-tree index type.
1069 </para>
1070
1071 <para>
1072 Also, for B-tree indexes, a freshly-constructed index is slightly faster to
1073 access than one that has been updated many times because logically
1074 adjacent pages are usually also physically adjacent in a newly built index.
1075 (This consideration does not apply to non-B-tree indexes.) It
1076 might be worthwhile to reindex periodically just to improve access speed.
1077 </para>
1078
1079 <para>
1080 <xref linkend="sql-reindex"/> can be used safely and easily in all cases.
1081 This command requires an <literal>ACCESS EXCLUSIVE</literal> lock by
1082 default, hence it is often preferable to execute it with its
1083 <literal>CONCURRENTLY</literal> option, which requires only a
1084 <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
1085 </para>
1086 </sect1>
1087
1088
1089 <sect1 id="logfile-maintenance">
1090 <title>Log File Maintenance</title>
1091
1092 <indexterm zone="logfile-maintenance">
1093 <primary>server log</primary>
1094 <secondary>log file maintenance</secondary>
1095 </indexterm>
1096
1097 <para>
1098 It is a good idea to save the database server's log output
1099 somewhere, rather than just discarding it via <filename>/dev/null</filename>.
1100 The log output is invaluable when diagnosing
1101 problems.
1102 </para>
1103
1104 <note>
1105 <para>
1106 The server log can contain sensitive information and needs to be protected,
1107 no matter how or where it is stored, or the destination to which it is routed.
1108 For example, some DDL statements might contain plaintext passwords or other
1109 authentication details. Logged statements at the <literal>ERROR</literal>
1110 level might show the SQL source code for applications
1111 and might also contain some parts of data rows. Recording data, events and
1112 related information is the intended function of this facility, so this is
1113 not a leakage or a bug. Please ensure the server logs are visible only to
1114 appropriately authorized people.
1115 </para>
1116 </note>
1117
1118 <para>
1119 Log output tends to be voluminous
1120 (especially at higher debug levels) so you won't want to save it
1121 indefinitely. You need to <emphasis>rotate</emphasis> the log files so that
1122 new log files are started and old ones removed after a reasonable
1123 period of time.
1124 </para>
1125
1126 <para>
1127 If you simply direct the <systemitem>stderr</systemitem> of
1128 <command>postgres</command> into a
1129 file, you will have log output, but
1130 the only way to truncate the log file is to stop and restart
1131 the server. This might be acceptable if you are using
1132 <productname>PostgreSQL</productname> in a development environment,
1133 but few production servers would find this behavior acceptable.
1134 </para>
1135
1136 <para>
1137 A better approach is to send the server's
1138 <systemitem>stderr</systemitem> output to some type of log rotation program.
1139 There is a built-in log rotation facility, which you can use by
1140 setting the configuration parameter <varname>logging_collector</varname> to
1141 <literal>true</literal> in <filename>postgresql.conf</filename>. The control
1142 parameters for this program are described in <xref
1143 linkend="runtime-config-logging-where"/>. You can also use this approach
1144 to capture the log data in machine readable <acronym>CSV</acronym>
1145 (comma-separated values) format.
1146 </para>
1147
1148 <para>
1149 Alternatively, you might prefer to use an external log rotation
1150 program if you have one that you are already using with other
1151 server software. For example, the <application>rotatelogs</application>
1152 tool included in the <productname>Apache</productname> distribution
1153 can be used with <productname>PostgreSQL</productname>. One way to
1154 do this is to pipe the server's
1155 <systemitem>stderr</systemitem> output to the desired program.
1156 If you start the server with
1157 <command>pg_ctl</command>, then <systemitem>stderr</systemitem>
1158 is already redirected to <systemitem>stdout</systemitem>, so you just need a
1159 pipe command, for example:
1160
1161 <programlisting>
1162 pg_ctl start | rotatelogs /var/log/pgsql_log 86400
1163 </programlisting>
1164 </para>
1165
1166 <para>
1167 You can combine these approaches by setting up <application>logrotate</application>
1168 to collect log files produced by <productname>PostgreSQL</productname> built-in
1169 logging collector. In this case, the logging collector defines the names and
1170 location of the log files, while <application>logrotate</application>
1171 periodically archives these files. When initiating log rotation,
1172 <application>logrotate</application> must ensure that the application
1173 sends further output to the new file. This is commonly done with a
1174 <literal>postrotate</literal> script that sends a <literal>SIGHUP</literal>
1175 signal to the application, which then reopens the log file.
1176 In <productname>PostgreSQL</productname>, you can run <command>pg_ctl</command>
1177 with the <literal>logrotate</literal> option instead. When the server receives
1178 this command, the server either switches to a new log file or reopens the
1179 existing file, depending on the logging configuration
1180 (see <xref linkend="runtime-config-logging-where"/>).
1181 </para>
1182
1183 <note>
1184 <para>
1185 When using static log file names, the server might fail to reopen the log
1186 file if the max open file limit is reached or a file table overflow occurs.
1187 In this case, log messages are sent to the old log file until a
1188 successful log rotation. If <application>logrotate</application> is
1189 configured to compress the log file and delete it, the server may lose
1190 the messages logged in this time frame. To avoid this issue, you can
1191 configure the logging collector to dynamically assign log file names
1192 and use a <literal>prerotate</literal> script to ignore open log files.
1193 </para>
1194 </note>
1195
1196 <para>
1197 Another production-grade approach to managing log output is to
1198 send it to <application>syslog</application> and let
1199 <application>syslog</application> deal with file rotation. To do this, set the
1200 configuration parameter <varname>log_destination</varname> to <literal>syslog</literal>
1201 (to log to <application>syslog</application> only) in
1202 <filename>postgresql.conf</filename>. Then you can send a <literal>SIGHUP</literal>
1203 signal to the <application>syslog</application> daemon whenever you want to force it
1204 to start writing a new log file. If you want to automate log
1205 rotation, the <application>logrotate</application> program can be
1206 configured to work with log files from
1207 <application>syslog</application>.
1208 </para>
1209
1210 <para>
1211 On many systems, however, <application>syslog</application> is not very reliable,
1212 particularly with large log messages; it might truncate or drop messages
1213 just when you need them the most. Also, on <productname>Linux</productname>,
1214 <application>syslog</application> will flush each message to disk, yielding poor
1215 performance. (You can use a <quote><literal>-</literal></quote> at the start of the file name
1216 in the <application>syslog</application> configuration file to disable syncing.)
1217 </para>
1218
1219 <para>
1220 Note that all the solutions described above take care of starting new
1221 log files at configurable intervals, but they do not handle deletion
1222 of old, no-longer-useful log files. You will probably want to set
1223 up a batch job to periodically delete old log files. Another possibility
1224 is to configure the rotation program so that old log files are overwritten
1225 cyclically.
1226 </para>
1227
1228 <para>
1229 <ulink url="https://pgbadger.darold.net/"><productname>pgBadger</productname></ulink>
1230 is an external project that does sophisticated log file analysis.
1231 <ulink
1232 url="https://bucardo.org/check_postgres/"><productname>check_postgres</productname></ulink>
1233 provides Nagios alerts when important messages appear in the log
1234 files, as well as detection of many other extraordinary conditions.
1235 </para>
1236 </sect1>
1237 </chapter>