1.2.5
by Martin Pitt
Import upstream version 8.4.5 |
1 |
<!-- $PostgreSQL: pgsql/doc/src/sgml/pgstandby.sgml,v 2.10.2.1 2010/08/17 04:49:33 petere Exp $ -->
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
2 |
|
3 |
<sect1 id="pgstandby"> |
|
4 |
<title>pg_standby</title> |
|
5 |
||
6 |
<indexterm zone="pgstandby"> |
|
7 |
<primary>pg_standby</primary> |
|
8 |
</indexterm>
|
|
9 |
||
10 |
<para>
|
|
11 |
<application>pg_standby</> supports creation of a <quote>warm standby</> |
|
12 |
database server. It is designed to be a production-ready program, as well |
|
13 |
as a customizable template should you require specific modifications. |
|
14 |
</para>
|
|
15 |
||
16 |
<para>
|
|
17 |
<application>pg_standby</> is designed to be a waiting |
|
18 |
<literal>restore_command</literal>, which is needed to turn a standard |
|
19 |
archive recovery into a warm standby operation. Other |
|
20 |
configuration is required as well, all of which is described in the main |
|
21 |
server manual (see <xref linkend="warm-standby">). |
|
22 |
</para>
|
|
23 |
||
24 |
<para>
|
|
25 |
<application>pg_standby</application> features include: |
|
26 |
</para>
|
|
27 |
<itemizedlist>
|
|
28 |
<listitem>
|
|
29 |
<para>
|
|
30 |
Written in C, so very portable and easy to install |
|
31 |
</para>
|
|
32 |
</listitem>
|
|
33 |
<listitem>
|
|
34 |
<para>
|
|
35 |
Easy-to-modify source code, with specifically designated |
|
36 |
sections to modify for your own needs |
|
37 |
</para>
|
|
38 |
</listitem>
|
|
39 |
<listitem>
|
|
40 |
<para>
|
|
41 |
Already tested on Linux and Windows |
|
42 |
</para>
|
|
43 |
</listitem>
|
|
44 |
</itemizedlist>
|
|
45 |
||
46 |
<sect2>
|
|
47 |
<title>Usage</title> |
|
48 |
||
49 |
<para>
|
|
50 |
To configure a standby |
|
51 |
server to use <application>pg_standby</>, put this into its |
|
52 |
<filename>recovery.conf</filename> configuration file: |
|
53 |
</para>
|
|
54 |
<programlisting>
|
|
55 |
restore_command = 'pg_standby <replaceable>archiveDir</> %f %p %r' |
|
56 |
</programlisting>
|
|
57 |
<para>
|
|
58 |
where <replaceable>archiveDir</> is the directory from which WAL segment |
|
59 |
files should be restored. |
|
60 |
</para>
|
|
61 |
<para>
|
|
62 |
The full syntax of <application>pg_standby</>'s command line is |
|
63 |
</para>
|
|
64 |
<synopsis>
|
|
65 |
pg_standby <optional> <replaceable>option</> ... </optional> <replaceable>archivelocation</> <replaceable>nextwalfile</> <replaceable>xlogfilepath</> <optional> <replaceable>restartwalfile</> </optional> |
|
66 |
</synopsis>
|
|
67 |
<para>
|
|
68 |
When used within <literal>restore_command</literal>, the <literal>%f</> and |
|
69 |
<literal>%p</> macros should be specified for <replaceable>nextwalfile</> |
|
70 |
and <replaceable>xlogfilepath</> respectively, to provide the actual file |
|
71 |
and path required for the restore. |
|
72 |
</para>
|
|
73 |
<para>
|
|
74 |
If <replaceable>restartwalfile</> is specified, normally by using the |
|
75 |
<literal>%r</literal> macro, then all WAL files logically preceding this |
|
76 |
file will be removed from <replaceable>archivelocation</>. This minimizes |
|
77 |
the number of files that need to be retained, while preserving |
|
78 |
crash-restart capability. Use of this parameter is appropriate if the |
|
79 |
<replaceable>archivelocation</> is a transient staging area for this |
|
80 |
particular standby server, but <emphasis>not</> when the |
|
81 |
<replaceable>archivelocation</> is intended as a long-term WAL archive area. |
|
82 |
</para>
|
|
83 |
<para>
|
|
84 |
<application>pg_standby</application> assumes that |
|
85 |
<replaceable>archivelocation</> is a directory readable by the |
|
86 |
server-owning user. If <replaceable>restartwalfile</> (or <literal>-k</>) |
|
87 |
is specified, |
|
88 |
the <replaceable>archivelocation</> directory must be writable too. |
|
89 |
</para>
|
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
90 |
<para>
|
91 |
There are two ways to fail over to a <quote>warm standby</> database server |
|
92 |
when the master server fails: |
|
93 |
||
94 |
<variablelist>
|
|
95 |
<varlistentry>
|
|
96 |
<term>Smart Failover</term> |
|
97 |
<listitem>
|
|
98 |
<para>
|
|
99 |
In smart failover, the server is brought up after applying all WAL |
|
100 |
files available in the archive. This results in zero data loss, even if |
|
101 |
the standby server has fallen behind, but if there is a lot of |
|
102 |
unapplied WAL it can be a long time before the standby server becomes |
|
103 |
ready. To trigger a smart failover, create a trigger file containing |
|
104 |
the word <literal>smart</>, or just create it and leave it empty. |
|
105 |
</para>
|
|
106 |
</listitem>
|
|
107 |
</varlistentry>
|
|
108 |
<varlistentry>
|
|
109 |
<term>Fast Failover</term> |
|
110 |
<listitem>
|
|
111 |
<para>
|
|
112 |
In fast failover, the server is brought up immediately. Any WAL files |
|
113 |
in the archive that have not yet been applied will be ignored, and |
|
114 |
all transactions in those files are lost. To trigger a fast failover, |
|
115 |
create a trigger file and write the word <literal>fast</> into it. |
|
116 |
<application>pg_standby</> can also be configured to execute a fast |
|
117 |
failover automatically if no new WAL file appears within a defined |
|
118 |
interval. |
|
119 |
</para>
|
|
120 |
</listitem>
|
|
121 |
</varlistentry>
|
|
122 |
</variablelist>
|
|
123 |
</para>
|
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
124 |
|
125 |
<table>
|
|
126 |
<title><application>pg_standby</> options</title> |
|
127 |
<tgroup cols="3"> |
|
128 |
<thead>
|
|
129 |
<row>
|
|
130 |
<entry>Option</entry> |
|
131 |
<entry>Default</entry> |
|
132 |
<entry>Description</entry> |
|
133 |
</row>
|
|
134 |
</thead>
|
|
135 |
<tbody>
|
|
136 |
<row>
|
|
137 |
<entry><literal>-c</></entry> |
|
138 |
<entry>yes</entry> |
|
139 |
<entry>
|
|
140 |
Use <literal>cp</> or <literal>copy</> command to restore WAL files |
|
141 |
from archive. |
|
142 |
</entry>
|
|
143 |
</row>
|
|
144 |
<row>
|
|
145 |
<entry><literal>-d</></entry> |
|
146 |
<entry>no</entry> |
|
147 |
<entry>Print lots of debug logging output on <filename>stderr</>.</entry> |
|
148 |
</row>
|
|
149 |
<row>
|
|
150 |
<entry><literal>-k</> <replaceable>numfiles</></entry> |
|
151 |
<entry>0</entry> |
|
152 |
<entry>
|
|
153 |
Remove files from <replaceable>archivelocation</replaceable> so that |
|
154 |
no more than this many WAL files before the current one are kept in the |
|
155 |
archive. Zero (the default) means not to remove any files from |
|
156 |
<replaceable>archivelocation</replaceable>. |
|
157 |
This parameter will be silently ignored if |
|
158 |
<replaceable>restartwalfile</replaceable> is specified, since that |
|
159 |
specification method is more accurate in determining the correct |
|
160 |
archive cut-off point. |
|
161 |
Use of this parameter is <emphasis>deprecated</> as of |
|
162 |
<productname>PostgreSQL</> 8.3; it is safer and more efficient to |
|
163 |
specify a <replaceable>restartwalfile</replaceable> parameter. A too |
|
164 |
small setting could result in removal of files that are still needed |
|
165 |
for a restart of the standby server, while a too large setting wastes |
|
166 |
archive space. |
|
167 |
</entry>
|
|
168 |
</row>
|
|
169 |
<row>
|
|
170 |
<entry><literal>-r</> <replaceable>maxretries</></entry> |
|
171 |
<entry>3</entry> |
|
172 |
<entry>
|
|
1.1.4
by Martin Pitt
Import upstream version 8.4.0 |
173 |
Set the maximum number of times to retry the copy command if it |
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
174 |
fails. After each failure, we wait for <replaceable>sleeptime</> * |
175 |
<replaceable>num_retries</> |
|
176 |
so that the wait time increases progressively. So by default, |
|
177 |
we will wait 5 secs, 10 secs, then 15 secs before reporting |
|
178 |
the failure back to the standby server. This will be |
|
179 |
interpreted as end of recovery and the standby will come |
|
180 |
up fully as a result. |
|
181 |
</entry>
|
|
182 |
</row>
|
|
183 |
<row>
|
|
184 |
<entry><literal>-s</> <replaceable>sleeptime</></entry> |
|
185 |
<entry>5</entry> |
|
186 |
<entry>
|
|
187 |
Set the number of seconds (up to 60) to sleep between tests to see |
|
188 |
if the WAL file to be restored is available in the archive yet. |
|
189 |
The default setting is not necessarily recommended; |
|
190 |
consult <xref linkend="warm-standby"> for discussion. |
|
191 |
</entry>
|
|
192 |
</row>
|
|
193 |
<row>
|
|
194 |
<entry><literal>-t</> <replaceable>triggerfile</></entry> |
|
195 |
<entry>none</entry> |
|
196 |
<entry>
|
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
197 |
Specify a trigger file whose presence should cause failover. |
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
198 |
It is recommended that you use a structured filename to |
199 |
avoid confusion as to which server is being triggered |
|
200 |
when multiple servers exist on the same system; for example |
|
201 |
<filename>/tmp/pgsql.trigger.5432</>. |
|
202 |
</entry>
|
|
203 |
</row>
|
|
204 |
<row>
|
|
205 |
<entry><literal>-w</> <replaceable>maxwaittime</></entry> |
|
206 |
<entry>0</entry> |
|
207 |
<entry>
|
|
208 |
Set the maximum number of seconds to wait for the next WAL file, |
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
209 |
after which a fast failover will be performed. |
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
210 |
A setting of zero (the default) means wait forever. |
211 |
The default setting is not necessarily recommended; |
|
212 |
consult <xref linkend="warm-standby"> for discussion. |
|
213 |
</entry>
|
|
214 |
</row>
|
|
215 |
</tbody>
|
|
216 |
</tgroup>
|
|
217 |
</table>
|
|
218 |
</sect2>
|
|
219 |
||
220 |
<sect2>
|
|
221 |
<title>Examples</title> |
|
222 |
||
223 |
<para>On Linux or Unix systems, you might use:</para> |
|
224 |
||
225 |
<programlisting>
|
|
226 |
archive_command = 'cp %p .../archive/%f' |
|
227 |
||
1.1.4
by Martin Pitt
Import upstream version 8.4.0 |
228 |
restore_command = 'pg_standby -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log' |
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
229 |
|
230 |
recovery_end_command = 'rm -f /tmp/pgsql.trigger.5442' |
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
231 |
</programlisting>
|
232 |
<para>
|
|
233 |
where the archive directory is physically located on the standby server, |
|
234 |
so that the <literal>archive_command</> is accessing it across NFS, |
|
235 |
but the files are local to the standby (enabling use of <literal>ln</>). |
|
236 |
This will: |
|
237 |
</para>
|
|
238 |
<itemizedlist>
|
|
239 |
<listitem>
|
|
240 |
<para>
|
|
241 |
produce debugging output in <filename>standby.log</> |
|
242 |
</para>
|
|
243 |
</listitem>
|
|
244 |
<listitem>
|
|
245 |
<para>
|
|
246 |
sleep for 2 seconds between checks for next WAL file availability |
|
247 |
</para>
|
|
248 |
</listitem>
|
|
249 |
<listitem>
|
|
250 |
<para>
|
|
251 |
stop waiting only when a trigger file called |
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
252 |
<filename>/tmp/pgsql.trigger.5442</> appears, |
253 |
and perform failover according to its content |
|
254 |
</para>
|
|
255 |
</listitem>
|
|
256 |
<listitem>
|
|
257 |
<para>
|
|
258 |
remove the trigger file when recovery ends |
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
259 |
</para>
|
260 |
</listitem>
|
|
261 |
<listitem>
|
|
262 |
<para>
|
|
263 |
remove no-longer-needed files from the archive directory |
|
264 |
</para>
|
|
265 |
</listitem>
|
|
266 |
</itemizedlist>
|
|
267 |
||
268 |
<para>On Windows, you might use:</para> |
|
269 |
||
270 |
<programlisting>
|
|
271 |
archive_command = 'copy %p ...\\archive\\%f' |
|
272 |
||
273 |
restore_command = 'pg_standby -d -s 5 -t C:\pgsql.trigger.5442 ...\archive %f %p %r 2>>standby.log' |
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
274 |
|
275 |
recovery_end_command = 'del C:\pgsql.trigger.5442' |
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
276 |
</programlisting>
|
277 |
<para>
|
|
278 |
Note that backslashes need to be doubled in the |
|
279 |
<literal>archive_command</>, but <emphasis>not</emphasis> in the |
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
280 |
<literal>restore_command</> or <literal>recovery_end_command</>. |
281 |
This will: |
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
282 |
</para>
|
283 |
<itemizedlist>
|
|
284 |
<listitem>
|
|
285 |
<para>
|
|
286 |
use the <literal>copy</> command to restore WAL files from archive |
|
287 |
</para>
|
|
288 |
</listitem>
|
|
289 |
<listitem>
|
|
290 |
<para>
|
|
291 |
produce debugging output in <filename>standby.log</> |
|
292 |
</para>
|
|
293 |
</listitem>
|
|
294 |
<listitem>
|
|
295 |
<para>
|
|
296 |
sleep for 5 seconds between checks for next WAL file availability |
|
297 |
</para>
|
|
298 |
</listitem>
|
|
299 |
<listitem>
|
|
300 |
<para>
|
|
301 |
stop waiting only when a trigger file called |
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
302 |
<filename>C:\pgsql.trigger.5442</> appears, |
303 |
and perform failover according to its content |
|
304 |
</para>
|
|
305 |
</listitem>
|
|
306 |
<listitem>
|
|
307 |
<para>
|
|
308 |
remove the trigger file when recovery ends |
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
309 |
</para>
|
310 |
</listitem>
|
|
311 |
<listitem>
|
|
312 |
<para>
|
|
313 |
remove no-longer-needed files from the archive directory |
|
314 |
</para>
|
|
315 |
</listitem>
|
|
316 |
</itemizedlist>
|
|
317 |
||
318 |
<para>
|
|
319 |
The <literal>copy</> command on Windows sets the final file size |
|
1.2.5
by Martin Pitt
Import upstream version 8.4.5 |
320 |
before the file is completely copied, which would ordinarily confuse |
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
321 |
<application>pg_standby</application>. Therefore |
322 |
<application>pg_standby</application> waits <literal>sleeptime</> |
|
323 |
seconds once it sees the proper file size. GNUWin32's <literal>cp</> |
|
324 |
sets the file size only after the file copy is complete. |
|
325 |
</para>
|
|
326 |
||
327 |
<para>
|
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
328 |
Since the Windows example uses <literal>copy</> at both ends, either |
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
329 |
or both servers might be accessing the archive directory across the |
330 |
network. |
|
331 |
</para>
|
|
332 |
||
333 |
</sect2>
|
|
334 |
||
335 |
<sect2>
|
|
336 |
<title>Supported server versions</title> |
|
337 |
||
338 |
<para>
|
|
339 |
<application>pg_standby</application> is designed to work with |
|
340 |
<productname>PostgreSQL</> 8.2 and later. |
|
341 |
</para>
|
|
342 |
<para>
|
|
343 |
<productname>PostgreSQL</> 8.3 provides the <literal>%r</literal> macro, |
|
344 |
which is designed to let <application>pg_standby</application> know the |
|
345 |
last file it needs to keep. With <productname>PostgreSQL</> 8.2, the |
|
346 |
<literal>-k</literal> option must be used if archive cleanup is |
|
347 |
required. This option remains available in 8.3, but its use is deprecated. |
|
348 |
</para>
|
|
1.1.3
by Martin Pitt
Import upstream version 8.4~beta2 |
349 |
<para>
|
350 |
<productname>PostgreSQL</> 8.4 provides the |
|
351 |
<literal>recovery_end_command</literal> option. Without this option |
|
352 |
a leftover trigger file can be hazardous. |
|
353 |
</para>
|
|
1
by Martin Pitt
Import upstream version 8.4~0cvs20090328 |
354 |
</sect2>
|
355 |
||
356 |
<sect2>
|
|
357 |
<title>Author</title> |
|
358 |
||
359 |
<para>
|
|
360 |
Simon Riggs <email>simon@2ndquadrant.com</email> |
|
361 |
</para>
|
|
362 |
</sect2>
|
|
363 |
||
364 |
</sect1>
|