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
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>pg_dump</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="reference-client.html" title="PostgreSQL Client Applications">
<link rel="prev" href="app-pgconfig.html" title="pg_config">
<link rel="next" href="app-pg-dumpall.html" title="pg_dumpall">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="app-pgdump"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>pg_dump — extract a <span class="productname">PostgreSQL</span> database into a script file or other archive file
</p>
</div>
<a name="id796418"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<div class="cmdsynopsis"><p><code class="command">pg_dump</code> [<em class="replaceable"><code>option</code></em>...] [<em class="replaceable"><code>dbname</code></em>]</p></div>
</div>
<div class="refsect1" lang="en">
<a name="pg-dump-description"></a><h2> Description
</h2>
<p> <span class="application">pg_dump</span> is a utility for backing up a
<span class="productname">PostgreSQL</span> database. It makes consistent
backups even if the database is being used concurrently.
<span class="application">pg_dump</span> does not block other users
accessing the database (readers or writers).
</p>
<p> Dumps can be output in script or archive file formats. Script
dumps are plain-text files containing the SQL commands required
to reconstruct the database to the state it was in at the time it was
saved. To restore from such a script, feed it to <a href="app-psql.html" title="psql"><span class="refentrytitle"><a name="app-psql-title"></a><span class="application">psql</span></span></a>. Script files
can be used to reconstruct the database even on other machines and
other architectures; with some modifications even on other SQL
database products.
</p>
<p> The alternative archive file formats must be used with
<a href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle">pg_restore</span></a> to rebuild the database. They
allow <span class="application">pg_restore</span> to be selective about
what is restored, or even to reorder the items prior to being
restored.
The archive file formats are designed to be portable across
architectures.
</p>
<p> When used with one of the archive file formats and combined with
<span class="application">pg_restore</span>,
<span class="application">pg_dump</span> provides a flexible archival and
transfer mechanism. <span class="application">pg_dump</span> can be used to
backup an entire database, then <span class="application">pg_restore</span>
can be used to examine the archive and/or select which parts of the
database are to be restored. The most flexible output file format is
the “<span class="quote">custom</span>” format (<code class="option">-Fc</code>). It allows
for selection and reordering of all archived items, and is compressed
by default. The <span class="application">tar</span> format
(<code class="option">-Ft</code>) is not compressed and it is not possible to
reorder data when loading, but it is otherwise quite flexible;
moreover, it can be manipulated with standard Unix tools such as
<code class="command">tar</code>.
</p>
<p> While running <span class="application">pg_dump</span>, one should examine the
output for any warnings (printed on standard error), especially in
light of the limitations listed below.
</p>
</div>
<div class="refsect1" lang="en">
<a name="pg-dump-options"></a><h2>Options</h2>
<p> The following command-line options control the content and
format of the output.
</p>
<div class="variablelist"><dl>
<dt><span class="term"><em class="replaceable"><code>dbname</code></em></span></dt>
<dd><p> Specifies the name of the database to be dumped. If this is
not specified, the environment variable
<code class="envar">PGDATABASE</code> is used. If that is not set, the
user name specified for the connection is used.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-a</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--data-only</code></span>
</dt>
<dd>
<p> Dump only the data, not the schema (data definitions).
</p>
<p> This option is only meaningful for the plain-text format. For
the archive formats, you may specify the option when you
call <code class="command">pg_restore</code>.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-c</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--clean</code></span>
</dt>
<dd>
<p> Output commands to clean (drop)
database objects prior to (the commands for) creating them.
</p>
<p> This option is only meaningful for the plain-text format. For
the archive formats, you may specify the option when you
call <code class="command">pg_restore</code>.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-C</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--create</code></span>
</dt>
<dd>
<p> Begin the output with a command to create the
database itself and reconnect to the created database. (With a
script of this form, it doesn't matter which database you connect
to before running the script.)
</p>
<p> This option is only meaningful for the plain-text format. For
the archive formats, you may specify the option when you
call <code class="command">pg_restore</code>.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-d</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--inserts</code></span>
</dt>
<dd><p> Dump data as <code class="command">INSERT</code> commands (rather
than <code class="command">COPY</code>). This will make restoration very slow;
it is mainly useful for making dumps that can be loaded into
non-<span class="productname">PostgreSQL</span> databases. Note that
the restore may fail altogether if you have rearranged column order.
The <code class="option">-D</code> option is safer, though even slower.
Also, while this option generates errors for invalid data,
it allows other <code class="command">INSERT</code>s to continue loading
data into the table.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-D</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">--column-inserts</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--attribute-inserts</code></span>
</dt>
<dd><p> Dump data as <code class="command">INSERT</code> commands with explicit
column names (<code class="literal">INSERT INTO
<em class="replaceable"><code>table</code></em>
(<em class="replaceable"><code>column</code></em>, ...) VALUES
...</code>). This will make restoration very slow; it is mainly
useful for making dumps that can be loaded into
non-<span class="productname">PostgreSQL</span> databases.
Also, while this option generates errors for invalid data,
it allows other <code class="command">INSERT</code>s to continue loading
data into the table.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-E <em class="replaceable"><code>encoding</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--encoding=<em class="replaceable"><code>encoding</code></em></code></span>
</dt>
<dd><p> Create the dump in the specified character set encoding. By default,
the dump is created in the database encoding. (Another way to get the
same result is to set the <code class="envar">PGCLIENTENCODING</code> environment
variable to the desired dump encoding.)
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-f <em class="replaceable"><code>file</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--file=<em class="replaceable"><code>file</code></em></code></span>
</dt>
<dd><p> Send output to the specified file. If this is omitted, the
standard output is used.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-F <em class="replaceable"><code>format</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--format=<em class="replaceable"><code>format</code></em></code></span>
</dt>
<dd>
<p> Selects the format of the output.
<em class="replaceable"><code>format</code></em> can be one of the following:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><code class="literal">p</code></span></dt>
<dd><p> Output a plain-text <acronym class="acronym">SQL</acronym> script file (default)
</p></dd>
<dt><span class="term"><code class="literal">t</code></span></dt>
<dd><p> Output a <code class="command">tar</code> archive suitable for input into
<span class="application">pg_restore</span>. Using this archive format
allows reordering and/or exclusion of database objects
at the time the database is restored. It is also possible to limit
which data is reloaded at restore time.
</p></dd>
<dt><span class="term"><code class="literal">c</code></span></dt>
<dd><p> Output a custom archive suitable for input into
<span class="application">pg_restore</span>. This is the most flexible
format in that it allows reordering of loading data as well
as object definitions. This format is also compressed by default.
</p></dd>
</dl></div>
<p>
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-i</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--ignore-version</code></span>
</dt>
<dd>
<p> Ignore version mismatch between
<span class="application">pg_dump</span> and the database server.
</p>
<p> <span class="application">pg_dump</span> can handle databases from
previous releases of <span class="productname">PostgreSQL</span>, but very old
versions are not supported anymore (currently prior to 7.0).
Use this option if you need to override the version check (and
if <span class="application">pg_dump</span> then fails, don't say
you weren't warned).
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-n <em class="replaceable"><code>schema</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--schema=<em class="replaceable"><code>schema</code></em></code></span>
</dt>
<dd>
<p> Dump the contents of <em class="replaceable"><code>schema</code></em>
only. If this option is not specified, all non-system schemas
in the target database will be dumped.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> In this mode, <span class="application">pg_dump</span> makes no
attempt to dump any other database objects that objects in the
selected schema may depend upon. Therefore, there is no
guarantee that the results of a single-schema dump can be
successfully restored by themselves into a clean database.
</p>
</div>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-o</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--oids</code></span>
</dt>
<dd><p> Dump object identifiers (<acronym class="acronym">OID</acronym>s) as part of the
data for every table. Use this option if your application references
the <acronym class="acronym">OID</acronym>
columns in some way (e.g., in a foreign key constraint).
Otherwise, this option should not be used.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-O</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--no-owner</code></span>
</dt>
<dd>
<p> Do not output commands to set
ownership of objects to match the original database.
By default, <span class="application">pg_dump</span> issues
<code class="command">ALTER OWNER</code> or
<code class="command">SET SESSION AUTHORIZATION</code>
statements to set ownership of created database objects.
These statements
will fail when the script is run unless it is started by a superuser
(or the same user that owns all of the objects in the script).
To make a script that can be restored by any user, but will give
that user ownership of all the objects, specify <code class="option">-O</code>.
</p>
<p> This option is only meaningful for the plain-text format. For
the archive formats, you may specify the option when you
call <code class="command">pg_restore</code>.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-R</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--no-reconnect</code></span>
</dt>
<dd><p> This option is obsolete but still accepted for backwards
compatibility.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-s</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--schema-only</code></span>
</dt>
<dd><p> Dump only the object definitions (schema), not data.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-S <em class="replaceable"><code>username</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--superuser=<em class="replaceable"><code>username</code></em></code></span>
</dt>
<dd><p> Specify the superuser user name to use when disabling triggers.
This is only relevant if <code class="option">--disable-triggers</code> is used.
(Usually, it's better to leave this out, and instead start the
resulting script as superuser.)
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-t <em class="replaceable"><code>table</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--table=<em class="replaceable"><code>table</code></em></code></span>
</dt>
<dd>
<p> Dump data for <em class="replaceable"><code>table</code></em>
only. It is possible for there to be
multiple tables with the same name in different schemas; if that
is the case, all matching tables will be dumped. Specify both
<code class="option">--schema</code> and <code class="option">--table</code> to select just one table.
</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p> In this mode, <span class="application">pg_dump</span> makes no
attempt to dump any other database objects that the selected table
may depend upon. Therefore, there is no guarantee
that the results of a single-table dump can be successfully
restored by themselves into a clean database.
</p>
</div>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-v</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--verbose</code></span>
</dt>
<dd><p> Specifies verbose mode. This will cause
<span class="application">pg_dump</span> to output detailed object
comments and start/stop times to the dump file, and progress
messages to standard error.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-x</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">--no-privileges</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--no-acl</code></span>
</dt>
<dd><p> Prevent dumping of access privileges (grant/revoke commands).
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-X disable-dollar-quoting</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--disable-dollar-quoting</code></span>
</dt>
<dd><p> This option disables the use of dollar quoting for function bodies,
and forces them to be quoted using SQL standard string syntax.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-X disable-triggers</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--disable-triggers</code></span>
</dt>
<dd>
<p> This option is only relevant when creating a data-only dump.
It instructs <span class="application">pg_dump</span> to include commands
to temporarily disable triggers on the target tables while
the data is reloaded. Use this if you have referential
integrity checks or other triggers on the tables that you
do not want to invoke during data reload.
</p>
<p> Presently, the commands emitted for <code class="option">--disable-triggers</code>
must be done as superuser. So, you should also specify
a superuser name with <code class="option">-S</code>, or preferably be careful to
start the resulting script as a superuser.
</p>
<p> This option is only meaningful for the plain-text format. For
the archive formats, you may specify the option when you
call <code class="command">pg_restore</code>.
</p>
</dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-X use-set-session-authorization</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--use-set-session-authorization</code></span>
</dt>
<dd><p> Output SQL-standard <code class="command">SET SESSION AUTHORIZATION</code> commands
instead of <code class="command">ALTER OWNER</code> commands to determine object
ownership. This makes the dump more standards compatible, but
depending on the history of the objects in the dump, may not restore
properly. Also, a dump using <code class="command">SET SESSION AUTHORIZATION</code>
will certainly require superuser privileges to restore correctly,
whereas <code class="command">ALTER OWNER</code> requires lesser privileges.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-Z <em class="replaceable"><code>0..9</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--compress=<em class="replaceable"><code>0..9</code></em></code></span>
</dt>
<dd><p> Specify the compression level to use in archive formats that
support compression. (Currently only the custom archive
format supports compression.)
</p></dd>
</dl></div>
<p>
</p>
<p> The following command-line options control the database connection parameters.
</p>
<div class="variablelist"><dl>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-h <em class="replaceable"><code>host</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--host=<em class="replaceable"><code>host</code></em></code></span>
</dt>
<dd><p> Specifies the host name of the machine on which the server is
running. If the value begins with a slash, it is used as the
directory for the Unix domain socket. The default is taken
from the <code class="envar">PGHOST</code> environment variable, if set,
else a Unix domain socket connection is attempted.
</p></dd>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="option">-p <em class="replaceable"><code>port</code></em></code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="option">--port=<em class="replaceable"><code>port</code></em></code></span>
</dt>
<dd><p> Specifies the TCP port or local Unix domain socket file
extension on which the server is listening for connections.
Defaults to the <code class="envar">PGPORT</code> environment variable, if
set, or a compiled-in default.
</p></dd>
<dt><span class="term"><code class="option">-U <em class="replaceable"><code>username</code></em></code></span></dt>
<dd><p> Connect as the given user
</p></dd>
<dt><span class="term"><code class="option">-W</code></span></dt>
<dd><p> Force a password prompt. This should happen automatically if
the server requires password authentication.
</p></dd>
</dl></div>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id797546"></a><h2>Environment</h2>
<div class="variablelist"><dl>
<dt>
<span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="envar">PGDATABASE</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="envar">PGHOST</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="envar">PGPORT</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="envar">PGUSER</code></span>
</dt>
<dd><p> Default connection parameters.
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="app-pgdump-diagnostics"></a><h2>Diagnostics</h2>
<p> <span class="application">pg_dump</span> internally executes
<code class="command">SELECT</code> statements. If you have problems running
<span class="application">pg_dump</span>, make sure you are able to
select information from the database using, for example, <a href="app-psql.html" title="psql"><span class="refentrytitle"><a name="app-psql-title"></a><span class="application">psql</span></span></a>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="pg-dump-notes"></a><h2>Notes</h2>
<p> If your database cluster has any local additions to the <code class="literal">template1</code> database,
be careful to restore the output of <span class="application">pg_dump</span> into a
truly empty database; otherwise you are likely to get errors due to
duplicate definitions of the added objects. To make an empty database
without any local additions, copy from <code class="literal">template0</code> not <code class="literal">template1</code>,
for example:
</p>
<pre class="programlisting">CREATE DATABASE foo WITH TEMPLATE template0;</pre>
<p>
</p>
<p> <span class="application">pg_dump</span> has a few limitations:
</p>
<div class="itemizedlist"><ul type="disc"><li><p> When a data-only dump is chosen and the option
<code class="option">--disable-triggers</code> is used,
<span class="application">pg_dump</span> emits commands to disable
triggers on user tables before inserting the data and commands
to re-enable them after the data has been inserted. If the
restore is stopped in the middle, the system catalogs may be
left in the wrong state.
</p></li></ul></div>
<p>
</p>
<p> Members of tar archives are limited to a size less than 8 GB.
(This is an inherent limitation of the tar file format.) Therefore
this format cannot be used if the textual representation of any one table
exceeds that size. The total size of a tar archive and any of the
other output formats is not limited, except possibly by the
operating system.
</p>
<p> The dump file produced by <span class="application">pg_dump</span> does
not contain the statistics used by the optimizer to make query
planning decisions. Therefore, it is wise to run
<code class="command">ANALYZE</code> after restoring from a dump file to
ensure good performance.
</p>
<p> Because <span class="application">pg_dump</span> is used to tranfer data
to newer versions of <span class="productname">PostgreSQL</span>, the output of
<span class="application">pg_dump</span> can be loaded into
newer <span class="productname">PostgreSQL</span> databases. It also can read older
<span class="productname">PostgreSQL</span> databases. However, it usually cannot
read newer <span class="productname">PostgreSQL</span> databases or produce dump output
that can be loaded into older database versions. To do this, manual
editing of the dump file might be required.
</p>
</div>
<div class="refsect1" lang="en">
<a name="pg-dump-examples"></a><h2>Examples</h2>
<p> To dump a database:
</p>
<pre class="screen"><code class="prompt">$</code> <strong class="userinput"><code>pg_dump mydb > db.out</code></strong></pre>
<p>
</p>
<p> To reload this database:
</p>
<pre class="screen"><code class="prompt">$</code> <strong class="userinput"><code>psql -d database -f db.out</code></strong></pre>
<p>
</p>
<p> To dump a database called <code class="literal">mydb</code> to a <code class="filename">tar</code>
file:
</p>
<pre class="screen"><code class="prompt">$</code> <strong class="userinput"><code>pg_dump -Ft mydb > db.tar</code></strong></pre>
<p>
</p>
<p> To reload this dump into an existing database called <code class="literal">newdb</code>:
</p>
<pre class="screen"><code class="prompt">$</code> <strong class="userinput"><code>pg_restore -d newdb db.tar</code></strong></pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id797876"></a><h2>History</h2>
<p> The <span class="application">pg_dump</span> utility first appeared in
<span class="application">Postgres95</span> release 0.02. The
non-plain-text output formats were introduced in
<span class="productname">PostgreSQL</span> release 7.1.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id797904"></a><h2>See Also</h2>
<span class="simplelist"><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>, <a href="app-pgrestore.html" title="pg_restore"><span class="refentrytitle">pg_restore</span></a>, <a href="app-psql.html" title="psql"><span class="refentrytitle"><a name="app-psql-title"></a><span class="application">psql</span></span></a>, Environment Variables (<a href="libpq-envars.html" title="28.11.Environment Variables">Section28.11, “Environment Variables”</a>)</span>
</div>
</div></body>
</html>
|
