1PG_STANDBY(1) PostgreSQL 12.2 Documentation PG_STANDBY(1)
2
6 pg_standby - supports the creation of a PostgreSQL warm standby server
7
9 pg_standby [option...] archivelocation nextwalfile walfilepath
10 [restartwalfile]
11
13 pg_standby supports creation of a “warm standby” database server. It is
14 designed to be a production-ready program, as well as a customizable
15 template should you require specific modifications.
16 pg_standby is designed to be a waiting restore_command, which is needed
18 to turn a standard archive recovery into a warm standby operation.
19 Other configuration is required as well, all of which is described in
20 the main server manual (see Section 26.2).
21 To configure a standby server to use pg_standby, put this into its
23 postgresql.conf configuration file:
24 restore_command = 'pg_standby archiveDir %f %p %r'
26 where archiveDir is the directory from which WAL segment files should
28 be restored.
29 If restartwalfile is specified, normally by using the %r macro, then
31 all WAL files logically preceding this file will be removed from
32 archivelocation. This minimizes the number of files that need to be
33 retained, while preserving crash-restart capability. Use of this
34 parameter is appropriate if the archivelocation is a transient staging
35 area for this particular standby server, but not when the
36 archivelocation is intended as a long-term WAL archive area.
37 pg_standby assumes that archivelocation is a directory readable by the
39 server-owning user. If restartwalfile (or -k) is specified, the
40 archivelocation directory must be writable too.
41 There are two ways to fail over to a “warm standby” database server
43 when the master server fails:
44 Smart Failover
46 In smart failover, the server is brought up after applying all WAL
47 files available in the archive. This results in zero data loss,
48 even if the standby server has fallen behind, but if there is a lot
49 of unapplied WAL it can be a long time before the standby server
50 becomes ready. To trigger a smart failover, create a trigger file
51 containing the word smart, or just create it and leave it empty.
52 Fast Failover
54 In fast failover, the server is brought up immediately. Any WAL
55 files in the archive that have not yet been applied will be
56 ignored, and all transactions in those files are lost. To trigger a
57 fast failover, create a trigger file and write the word fast into
58 it. pg_standby can also be configured to execute a fast failover
59 automatically if no new WAL file appears within a defined interval.
60
62 pg_standby accepts the following command-line arguments:
63 -c
65 Use cp or copy command to restore WAL files from archive. This is
66 the only supported behavior so this option is useless.
67 -d
69 Print lots of debug logging output on stderr.
70 -k
72 Remove files from archivelocation so that no more than this many
73 WAL files before the current one are kept in the archive. Zero (the
74 default) means not to remove any files from archivelocation. This
75 parameter will be silently ignored if restartwalfile is specified,
76 since that specification method is more accurate in determining the
77 correct archive cut-off point. Use of this parameter is deprecated
78 as of PostgreSQL 8.3; it is safer and more efficient to specify a
79 restartwalfile parameter. A too small setting could result in
80 removal of files that are still needed for a restart of the standby
81 server, while a too large setting wastes archive space.
82 -r maxretries
84 Set the maximum number of times to retry the copy command if it
85 fails (default 3). After each failure, we wait for sleeptime *
86 num_retries so that the wait time increases progressively. So by
87 default, we will wait 5 secs, 10 secs, then 15 secs before
88 reporting the failure back to the standby server. This will be
89 interpreted as end of recovery and the standby will come up fully
90 as a result.
91 -s sleeptime
93 Set the number of seconds (up to 60, default 5) to sleep between
94 tests to see if the WAL file to be restored is available in the
95 archive yet. The default setting is not necessarily recommended;
96 consult Section 26.2 for discussion.
97 -t triggerfile
99 Specify a trigger file whose presence should cause failover. It is
100 recommended that you use a structured file name to avoid confusion
101 as to which server is being triggered when multiple servers exist
102 on the same system; for example /tmp/pgsql.trigger.5432.
103 -V
105 --version
106 Print the pg_standby version and exit.
107 -w maxwaittime
109 Set the maximum number of seconds to wait for the next WAL file,
110 after which a fast failover will be performed. A setting of zero
111 (the default) means wait forever. The default setting is not
112 necessarily recommended; consult Section 26.2 for discussion.
113 -?
115 --help
116 Show help about pg_standby command line arguments, and exit.
117
119 pg_standby is designed to work with PostgreSQL 8.2 and later.
120 PostgreSQL 8.3 provides the %r macro, which is designed to let
122 pg_standby know the last file it needs to keep. With PostgreSQL 8.2,
123 the -k option must be used if archive cleanup is required. This option
124 remains available in 8.3, but its use is deprecated.
125 PostgreSQL 8.4 provides the recovery_end_command option. Without this
127 option a leftover trigger file can be hazardous.
128 pg_standby is written in C and has an easy-to-modify source code, with
130 specifically designated sections to modify for your own needs
131
133 On Linux or Unix systems, you might use:
134 archive_command = 'cp %p .../archive/%f'
136 restore_command = 'pg_standby -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log'
138 recovery_end_command = 'rm -f /tmp/pgsql.trigger.5442'
140 where the archive directory is physically located on the standby
142 server, so that the archive_command is accessing it across NFS, but the
143 files are local to the standby (enabling use of ln). This will:
144 · produce debugging output in standby.log
146 · sleep for 2 seconds between checks for next WAL file availability
148 · stop waiting only when a trigger file called
150 /tmp/pgsql.trigger.5442 appears, and perform failover according to
151 its content
152 · remove the trigger file when recovery ends
154 · remove no-longer-needed files from the archive directory
156 On Windows, you might use:
158 archive_command = 'copy %p ...\\archive\\%f'
160 restore_command = 'pg_standby -d -s 5 -t C:\pgsql.trigger.5442 ...\archive %f %p %r 2>>standby.log'
162 recovery_end_command = 'del C:\pgsql.trigger.5442'
164 Note that backslashes need to be doubled in the archive_command, but
166 not in the restore_command or recovery_end_command. This will:
167 · use the copy command to restore WAL files from archive
169 · produce debugging output in standby.log
171 · sleep for 5 seconds between checks for next WAL file availability
173 · stop waiting only when a trigger file called C:\pgsql.trigger.5442
175 appears, and perform failover according to its content
176 · remove the trigger file when recovery ends
178 · remove no-longer-needed files from the archive directory
180 The copy command on Windows sets the final file size before the file is
182 completely copied, which would ordinarily confuse pg_standby. Therefore
183 pg_standby waits sleeptime seconds once it sees the proper file size.
184 GNUWin32's cp sets the file size only after the file copy is complete.
185 Since the Windows example uses copy at both ends, either or both
187 servers might be accessing the archive directory across the network.
188
190 Simon Riggs <simon@2ndquadrant.com>
191
193 pg_archivecleanup(1)
194PostgreSQL 12.2 2020 PG_STANDBY(1)