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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Chapter23.Backup and Restore</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="admin.html" title="PartIII.Server Administration">
<link rel="prev" href="logfile-maintenance.html" title="22.3.Log File Maintenance">
<link rel="next" href="backup-file.html" title="23.2.File system level backup">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="chapter" lang="en" id="backup">
<div class="titlepage"><div><div><h2 class="title">
<a name="backup"></a>Chapter23.Backup and Restore</h2></div></div></div>
<div class="toc">
<p><b>Table of Contents</b></p>
<dl>
<dt><span class="sect1"><a href="backup.html#backup-dump">23.1. <acronym class="acronym">SQL</acronym> Dump</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="backup.html#backup-dump-restore">23.1.1. Restoring the dump</a></span></dt>
<dt><span class="sect2"><a href="backup.html#backup-dump-all">23.1.2. Using <span class="application">pg_dumpall</span></a></span></dt>
<dt><span class="sect2"><a href="backup.html#backup-dump-large">23.1.3. Handling large databases</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="backup-file.html">23.2. File system level backup</a></span></dt>
<dt><span class="sect1"><a href="backup-online.html">23.3. On-line backup and point-in-time recovery (PITR)</a></span></dt>
<dd><dl>
<dt><span class="sect2"><a href="backup-online.html#backup-archiving-wal">23.3.1. Setting up WAL archiving</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-base-backup">23.3.2. Making a Base Backup</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-pitr-recovery">23.3.3. Recovering with an On-line Backup</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-timelines">23.3.4. Timelines</a></span></dt>
<dt><span class="sect2"><a href="backup-online.html#backup-online-caveats">23.3.5. Caveats</a></span></dt>
</dl></dd>
<dt><span class="sect1"><a href="migration.html">23.4. Migration Between Releases</a></span></dt>
</dl>
</div>
<a name="id668374"></a><p> As with everything that contains valuable data, <span class="productname">PostgreSQL</span>
databases should be backed up regularly. While the procedure is
essentially simple, it is important to have a basic understanding of
the underlying techniques and assumptions.
</p>
<p> There are three fundamentally different approaches to backing up
<span class="productname">PostgreSQL</span> data:
</p>
<div class="itemizedlist"><ul type="disc">
<li><p><acronym class="acronym">SQL</acronym> dump</p></li>
<li><p>File system level backup</p></li>
<li><p>On-line backup</p></li>
</ul></div>
<p>
Each has its own strengths and weaknesses.
</p>
<div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="backup-dump"></a>23.1.<acronym class="acronym">SQL</acronym> Dump</h2></div></div></div>
<p> The idea behind the SQL-dump method is to generate a text file with SQL
commands that, when fed back to the server, will recreate the
database in the same state as it was at the time of the dump.
<span class="productname">PostgreSQL</span> provides the utility program
<a href="app-pgdump.html" title="pg_dump"><span class="refentrytitle">pg_dump</span></a> for this purpose. The basic usage of this
command is:
</p>
<pre class="synopsis">pg_dump <em class="replaceable"><code>dbname</code></em> > <em class="replaceable"><code>outfile</code></em></pre>
<p>
As you see, <span class="application">pg_dump</span> writes its results to the
standard output. We will see below how this can be useful.
</p>
<p> <span class="application">pg_dump</span> is a regular <span class="productname">PostgreSQL</span>
client application (albeit a particularly clever one). This means
that you can do this backup procedure from any remote host that has
access to the database. But remember that <span class="application">pg_dump</span>
does not operate with special permissions. In particular, it must
have read access to all tables that you want to back up, so in
practice you almost always have to run it as a database superuser.
</p>
<p> To specify which database server <span class="application">pg_dump</span> should
contact, use the command line options <code class="option">-h
<em class="replaceable"><code>host</code></em></code> and <code class="option">-p <em class="replaceable"><code>port</code></em></code>. The
default host is the local host or whatever your
<code class="envar">PGHOST</code> environment variable specifies. Similarly,
the default port is indicated by the <code class="envar">PGPORT</code>
environment variable or, failing that, by the compiled-in default.
(Conveniently, the server will normally have the same compiled-in
default.)
</p>
<p> As any other <span class="productname">PostgreSQL</span> client application,
<span class="application">pg_dump</span> will by default connect with the database
user name that is equal to the current operating system user name. To override
this, either specify the <code class="option">-U</code> option or set the
environment variable <code class="envar">PGUSER</code>. Remember that
<span class="application">pg_dump</span> connections are subject to the normal
client authentication mechanisms (which are described in <a href="client-authentication.html" title="Chapter20.Client Authentication">Chapter20, <i>Client Authentication</i></a>).
</p>
<p> Dumps created by <span class="application">pg_dump</span> are internally consistent,
that is, updates to the database while <span class="application">pg_dump</span> is
running will not be in the dump. <span class="application">pg_dump</span> does not
block other operations on the database while it is working.
(Exceptions are those operations that need to operate with an
exclusive lock, such as <code class="command">VACUUM FULL</code>.)
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Important</h3>
<p> When your database schema relies on OIDs (for instance as foreign
keys) you must instruct <span class="application">pg_dump</span> to dump the OIDs
as well. To do this, use the <code class="option">-o</code> command line
option.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="backup-dump-restore"></a>23.1.1.Restoring the dump</h3></div></div></div>
<p> The text files created by <span class="application">pg_dump</span> are intended to
be read in by the <span class="application">psql</span> program. The
general command form to restore a dump is
</p>
<pre class="synopsis">psql <em class="replaceable"><code>dbname</code></em> < <em class="replaceable"><code>infile</code></em></pre>
<p>
where <em class="replaceable"><code>infile</code></em> is what
you used as <em class="replaceable"><code>outfile</code></em>
for the <span class="application">pg_dump</span> command. The database <em class="replaceable"><code>dbname</code></em> will not be created by this
command, you must create it yourself from <code class="literal">template0</code> before executing
<span class="application">psql</span> (e.g., with <code class="literal">createdb -T template0
<em class="replaceable"><code>dbname</code></em></code>).
<span class="application">psql</span> supports options similar to <span class="application">pg_dump</span>
for controlling the database server location and the user name. See
<a href="app-psql.html" title="psql"><span class="refentrytitle"><a name="app-psql-title"></a><span class="application">psql</span></span></a>'s reference page for more information.
</p>
<p> Not only must the target database already exist before starting to
run the restore, but so must all the users who own objects in the
dumped database or were granted permissions on the objects. If they
do not, then the restore will fail to recreate the objects with the
original ownership and/or permissions. (Sometimes this is what you want,
but usually it is not.)
</p>
<p> Once restored, it is wise to run <a href="sql-analyze.html">ANALYZE</a> on each database so the optimizer has
useful statistics. An easy way to do this is to run
<code class="command">vacuumdb -a -z</code> to
<code class="command">VACUUM ANALYZE</code> all databases; this is equivalent to
running <code class="command">VACUUM ANALYZE</code> manually.
</p>
<p> The ability of <span class="application">pg_dump</span> and <span class="application">psql</span> to
write to or read from pipes makes it possible to dump a database
directly from one server to another; for example:
</p>
<pre class="programlisting">pg_dump -h <em class="replaceable"><code>host1</code></em> <em class="replaceable"><code>dbname</code></em> | psql -h <em class="replaceable"><code>host2</code></em> <em class="replaceable"><code>dbname</code></em></pre>
<p>
</p>
<div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Important</h3>
<p> The dumps produced by <span class="application">pg_dump</span> are relative to
<code class="literal">template0</code>. This means that any languages, procedures,
etc. added to <code class="literal">template1</code> will also be dumped by
<span class="application">pg_dump</span>. As a result, when restoring, if you are
using a customized <code class="literal">template1</code>, you must create the
empty database from <code class="literal">template0</code>, as in the example
above.
</p>
</div>
<p> For advice on how to load large amounts of data into
<span class="productname">PostgreSQL</span> efficiently, refer to <a href="populate.html" title="13.4.Populating a Database">Section13.4, “Populating a Database”</a>.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="backup-dump-all"></a>23.1.2.Using <span class="application">pg_dumpall</span></h3></div></div></div>
<p> The above mechanism is cumbersome and inappropriate when backing
up an entire database cluster. For this reason the <a href="app-pg-dumpall.html" title="pg_dumpall"><span class="refentrytitle"><a name="app-pg-dumpall-title"></a><span class="application">pg_dumpall</span></span></a> program is provided.
<span class="application">pg_dumpall</span> backs up each database in a given
cluster, and also preserves cluster-wide data such as users and
groups. The basic usage of this command is:
</p>
<pre class="synopsis">pg_dumpall > <em class="replaceable"><code>outfile</code></em></pre>
<p>
The resulting dump can be restored with <span class="application">psql</span>:
</p>
<pre class="synopsis">psql -f <em class="replaceable"><code>infile</code></em> postgres</pre>
<p>
(Actually, you can specify any existing database name to start from,
but if you are reloading in an empty cluster then <code class="literal">postgres</code>
should generally be used.) It is always necessary to have
database superuser access when restoring a <span class="application">pg_dumpall</span>
dump, as that is required to restore the user and group information.
</p>
</div>
<div class="sect2" lang="en">
<div class="titlepage"><div><div><h3 class="title">
<a name="backup-dump-large"></a>23.1.3.Handling large databases</h3></div></div></div>
<p> Since <span class="productname">PostgreSQL</span> allows tables larger
than the maximum file size on your system, it can be problematic
to dump such a table to a file, since the resulting file will likely
be larger than the maximum size allowed by your system. Since
<span class="application">pg_dump</span> can write to the standard output, you can
just use standard Unix tools to work around this possible problem.
</p>
<p><b>Use compressed dumps.</b> You can use your favorite compression program, for example
<span class="application">gzip</span>.
</p>
<pre class="programlisting">pg_dump <em class="replaceable"><code>dbname</code></em> | gzip > <em class="replaceable"><code>filename</code></em>.gz</pre>
<p>
Reload with
</p>
<pre class="programlisting">createdb <em class="replaceable"><code>dbname</code></em>
gunzip -c <em class="replaceable"><code>filename</code></em>.gz | psql <em class="replaceable"><code>dbname</code></em></pre>
<p>
or
</p>
<pre class="programlisting">cat <em class="replaceable"><code>filename</code></em>.gz | gunzip | psql <em class="replaceable"><code>dbname</code></em></pre>
<p>
</p>
<p><b>Use <code class="command">split</code>.</b> The <code class="command">split</code> command
allows you to split the output into pieces that are
acceptable in size to the underlying file system. For example, to
make chunks of 1 megabyte:
</p>
<pre class="programlisting">pg_dump <em class="replaceable"><code>dbname</code></em> | split -b 1m - <em class="replaceable"><code>filename</code></em></pre>
<p>
Reload with
</p>
<pre class="programlisting">createdb <em class="replaceable"><code>dbname</code></em>
cat <em class="replaceable"><code>filename</code></em>* | psql <em class="replaceable"><code>dbname</code></em></pre>
<p>
</p>
<p><b>Use the custom dump format.</b> If <span class="productname">PostgreSQL</span> was built on a system with the
<span class="application">zlib</span> compression library installed, the custom dump
format will compress data as it writes it to the output file. This will
produce dump file sizes similar to using <code class="command">gzip</code>, but it
has the added advantage that tables can be restored selectively. The
following command dumps a database using the custom dump format:
</p>
<pre class="programlisting">pg_dump -Fc <em class="replaceable"><code>dbname</code></em> > <em class="replaceable"><code>filename</code></em></pre>
<p>
A custom-format dump is not a script for <span class="application">psql</span>, but
instead must be restored with <span class="application">pg_restore</span>.
See the <a href="app-pgdump.html" title="pg_dump"><span class="refentrytitle">pg_dump</span></a> and <a href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle">pg_restore</span></a> reference pages for details.
</p>
</div>
</div>
</div></body>
</html>
|
