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
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>File System Level Backup</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.1.5 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Backup and Restore"
HREF="backup.html"><LINK
REL="PREVIOUS"
TITLE="SQL Dump"
HREF="backup-dump.html"><LINK
REL="NEXT"
TITLE="Continuous Archiving and Point-in-Time Recovery (PITR)"
HREF="continuous-archiving.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2012-08-14T22:55:48"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.1.5 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="SQL Dump"
HREF="backup-dump.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="backup.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 24. Backup and Restore</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Continuous Archiving and Point-in-Time Recovery (PITR)"
HREF="continuous-archiving.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="BACKUP-FILE"
>24.2. File System Level Backup</A
></H1
><P
> An alternative backup strategy is to directly copy the files that
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> uses to store the data in the database;
<A
HREF="creating-cluster.html"
>Section 17.2</A
> explains where these files
are located. You can use whatever method you prefer
for doing file system backups; for example:
</P><PRE
CLASS="PROGRAMLISTING"
>tar -cf backup.tar /usr/local/pgsql/data</PRE
><P>
</P
><P
> There are two restrictions, however, which make this method
impractical, or at least inferior to the <SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
>
method:
<P
></P
></P><OL
TYPE="1"
><LI
><P
> The database server <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> be shut down in order to
get a usable backup. Half-way measures such as disallowing all
connections will <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> work
(in part because <TT
CLASS="COMMAND"
>tar</TT
> and similar tools do not take
an atomic snapshot of the state of the file system,
but also because of internal buffering within the server).
Information about stopping the server can be found in
<A
HREF="server-shutdown.html"
>Section 17.5</A
>. Needless to say, you
also need to shut down the server before restoring the data.
</P
></LI
><LI
><P
> If you have dug into the details of the file system layout of the
database, you might be tempted to try to back up or restore only certain
individual tables or databases from their respective files or
directories. This will <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> work because the
information contained in these files is not usable without
the commit log files,
<TT
CLASS="FILENAME"
>pg_clog/*</TT
>, which contain the commit status of
all transactions. A table file is only usable with this
information. Of course it is also impossible to restore only a
table and the associated <TT
CLASS="FILENAME"
>pg_clog</TT
> data
because that would render all other tables in the database
cluster useless. So file system backups only work for complete
backup and restoration of an entire database cluster.
</P
></LI
></OL
><P>
</P
><P
> An alternative file-system backup approach is to make a
<SPAN
CLASS="QUOTE"
>"consistent snapshot"</SPAN
> of the data directory, if the
file system supports that functionality (and you are willing to
trust that it is implemented correctly). The typical procedure is
to make a <SPAN
CLASS="QUOTE"
>"frozen snapshot"</SPAN
> of the volume containing the
database, then copy the whole data directory (not just parts, see
above) from the snapshot to a backup device, then release the frozen
snapshot. This will work even while the database server is running.
However, a backup created in this way saves
the database files in a state as if the database server was not
properly shut down; therefore, when you start the database server
on the backed-up data, it will think the previous server instance
crashed and will replay the WAL log. This is not a problem; just
be aware of it (and be sure to include the WAL files in your backup).
You can perform a <TT
CLASS="COMMAND"
>CHECKPOINT</TT
> before taking the
snapshot to reduce recovery time.
</P
><P
> If your database is spread across multiple file systems, there might not
be any way to obtain exactly-simultaneous frozen snapshots of all
the volumes. For example, if your data files and WAL log are on different
disks, or if tablespaces are on different file systems, it might
not be possible to use snapshot backup because the snapshots
<SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> be simultaneous.
Read your file system documentation very carefully before trusting
the consistent-snapshot technique in such situations.
</P
><P
> If simultaneous snapshots are not possible, one option is to shut down
the database server long enough to establish all the frozen snapshots.
Another option is to perform a continuous archiving base backup (<A
HREF="continuous-archiving.html#BACKUP-BASE-BACKUP"
>Section 24.3.2</A
>) because such backups are immune to file
system changes during the backup. This requires enabling continuous
archiving just during the backup process; restore is done using
continuous archive recovery (<A
HREF="continuous-archiving.html#BACKUP-PITR-RECOVERY"
>Section 24.3.3</A
>).
</P
><P
> Another option is to use <SPAN
CLASS="APPLICATION"
>rsync</SPAN
> to perform a file
system backup. This is done by first running <SPAN
CLASS="APPLICATION"
>rsync</SPAN
>
while the database server is running, then shutting down the database
server just long enough to do a second <SPAN
CLASS="APPLICATION"
>rsync</SPAN
>. The
second <SPAN
CLASS="APPLICATION"
>rsync</SPAN
> will be much quicker than the first,
because it has relatively little data to transfer, and the end result
will be consistent because the server was down. This method
allows a file system backup to be performed with minimal downtime.
</P
><P
> Note that a file system backup will typically be larger
than an SQL dump. (<SPAN
CLASS="APPLICATION"
>pg_dump</SPAN
> does not need to dump
the contents of indexes for example, just the commands to recreate
them.) However, taking a file system backup might be faster.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="backup-dump.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="continuous-archiving.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> Dump</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="backup.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Continuous Archiving and Point-in-Time Recovery (PITR)</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
|