Penguin
Recent Changes
Annotated edit history of pg_dump(1) version 4, including all changes. View license author blame.
Rev Author # Line
1 perry 1 PG_DUMP
2 !!!PG_DUMP
3 NAME
4 SYNOPSIS
5 DESCRIPTION
6 DIAGNOSTICS
7 NOTES
8 EXAMPLES
9 HISTORY
10 SEE ALSO
11 ----
12 !!NAME
13
14
15 pg_dump - extract a PostgreSQL database into a script file or other archive file
16 !!SYNOPSIS
17
18
19 __pg_dump__ [[ __-a__ | __-s__ ] [[ __-b__ ] [[
20 __-c__ ] [[ __-C__ ] [[ __-d__ | __-D__ ] [[
21 __-f__ ''file'' ] [[ __-F__ ''format'' ] [[
22 __-i__ ] [[ __-n__ | __-N__ ] [[ __-o__ ] [[
23 __-O__ ] [[ __-R__ ] [[ __-S__ ] [[ __-t__
24 ''table'' ] [[ __-v__ ] [[ __-x__ ] [[ __-X__
25 ''keyword'' ] [[ __-Z__ ''0...9'' ] [[ __-h__
26 ''host'' ] [[ __-p__ ''port'' ] [[ __-U__
27 ''username'' ] [[ __-W__ ] ''dbname''
28 !!DESCRIPTION
29
30
31 __pg_dump__ is a utility for saving a PostgreSQL database
32 into a script or an archive file. The script files are in
33 plain-text format and contain the SQL commands required to
34 reconstruct the database to the state it was in at the time
35 it was saved. They can be used to reconstruct the database
36 even on other machines and other architectures, with some
37 modifications even on other RDBMS products. Furthermore,
38 there are alternative archive file formats that are meant to
4 perry 39 be used with pg_restore(1) to rebuild the database,
1 perry 40 and they also allow __pg_restore__ to be selective about
41 what is restored, or even to reorder the items prior to
42 being restored. The archive files are also designed to be
43 portable across architectures.
44
45
46 __pg_dump__ will save the information necessary to
47 re-generate all user-defined types, functions, tables,
48 indexes, aggregates, and operators. In addition, all the
49 data is copied out in text format so that it can be readily
50 copied in again, as well as imported into tools for
51 editing.
52
53
54 __pg_dump__ is useful for dumping out the contents of a
55 database to move from one PostgreSQL installation to
56 another.
57
58
59 When used with one of the archive file formats and combined
4 perry 60 with pg_restore(1), __pg_dump__ provides a
1 perry 61 flexible archival and transfer mechanism. __pg_dump__ can
62 be used to backup an entire database, then __pg_restore__
63 can be used to examine the archive and/or select which parts
64 of the database are to be restored. The most flexible output
65 file format is the ``custom'' format (__-Fc__). It allows
66 for selection and reordering of all archived items, and is
67 compressed by default. The ''tar'' format (__-Ft__) is
68 not compressed and it is not possible to reorder data when
69 loading, but it is otherwise quite flexible; moreover, it
70 can be manipulated with other tools such as
71 ''tar''.
72
73
74 While running __pg_dump__, one should examine the output
75 for any warnings (printed on standard error), especially in
76 light of the limitations listed below.
77
78
79 __pg_dump__ makes consistent backups even if the database
80 is being used concurrently. __pg_dump__ does not block
81 other users accessing the database (readers or
82 writers).
83
84
85 __OPTIONS__
86
87
88 __pg_dump__ accepts the following command line arguments.
89 (Long option forms are only available on some
90 platforms.)
91
92
93 ''dbname''
94
95
96 Specifies the name of the database to be
97 dumped.
98
99
100 __-a__
101
102
103 __--data-only__
104
105
106 Dump only the data, not the schema (data
107 definitions).
108
109
110 This option is only meaningful for the plain-text format.
111 For the other formats, you may specify the option when you
112 call __pg_restore__.
113
114
115 __-b__
116
117
118 __--blobs__
119
120
121 Include large objects in dump.
122
123
124 __-c__
125
126
127 __--clean__
128
129
130 Output commands to clean (drop) database objects prior to
131 (the commands for) creating them.
132
133
134 This option is only meaningful for the plain-text format.
135 For the other formats, you may specify the option when you
136 call __pg_restore__.
137
138
139 __-C__
140
141
142 __--create__
143
144
145 Begin the output with a command to create the database
146 itself and reconnect to the created database. (With a script
147 of this form, it doesn't matter which database you connect
148 to before running the script.)
149
150
151 This option is only meaningful for the plain-text format.
152 For the other formats, you may specify the option when you
153 call __pg_restore__.
154
155
156 __-d__
157
158
159 __--inserts__
160
161
162 Dump data as __INSERT__ commands (rather than
163 __COPY__). This will make restoration very slow, but it
164 makes the archives more portable to other RDBMS
165 packages.
166
167
168 __-D__
169
170
171 __--column-inserts__
172
173
174 __--attribute-inserts__
175
176
177 Dump data as __INSERT__ commands with explicit column
178 names (INSERT INTO ''table'' (''column'', ...) VALUES
179 ...). This will make restoration very slow, but it is
180 necessary if you desire to rearrange column
181 ordering.
182
183
184 __-f__ ''file''
185
186
187 __--file=__''file''
188
189
190 Send output to the specified file. If this is omitted, the
191 standard output is used.
192
193
194 __-F__ ''format''
195
196
197 __--format=__''format''
198
199
200 Selects the format of the output. ''format'' can be one
201 of the following:
202
203
204 __p__
205
206
207 Output a plain-text SQL script file (default)
208
209
210 __t__
211
212
213 Output a ''tar'' archive suitable for input into
214 __pg_restore__. Using this archive format allows
215 reordering and/or exclusion of schema elements at the time
216 the database is restored. It is also possible to limit which
217 data is reloaded at restore time.
218
219
220 __c__
221
222
223 Output a custom archive suitable for input into
224 __pg_restore__. This is the most flexible format in that
225 it allows reordering of data load as well as schema
226 elements. This format is also compressed by
227 default.
228
229
230 __-i__
231
232
233 __--ignore-version__
234
235
236 Ignore version mismatch between __pg_dump__ and the
237 database server. Since __pg_dump__ knows a great deal
238 about system catalogs, any given version of __pg_dump__
239 is only intended to work with the corresponding release of
240 the database server. Use this option if you need to override
241 the version check (and if __pg_dump__ then fails, don't
242 say you weren't warned).
243
244
245 __-n__
246
247
248 __--no-quotes__
249
250
251 Suppress double quotes around identifiers unless absolutely
252 necessary. This may cause trouble loading this dumped data
253 if there are reserved words used for identifiers. This was
254 the default behavior for __pg_dump__ prior to version
255 6.4.
256
257
258 __-N__
259
260
261 __--quotes__
262
263
264 Include double quotes around identifiers. This is the
265 default.
266
267
268 __-o__
269
270
271 __--oids__
272
273
274 Dump object identifiers (OIDs) for every table. Use this
275 option if your application references the OID columns in
276 some way (e.g., in a foreign key constraint). Otherwise,
277 this option should not be used.
278
279
280 __-O__
281
282
283 __--no-owner__
284
285
286 Do not output commands to set the object ownership to match
287 the original database. Typically, __pg_dump__ issues
288 (__psql__-specific) __connect__ statements to set
289 ownership of schema elements. See also under __-R__ and
290 __-X use-set-session-authorization__. Note that __-O__
291 does not prevent all reconnections to the database, only the
292 ones that are exclusively used for ownership
293 adjustments.
294
295
296 This option is only meaningful for the plain-text format.
297 For the other formats, you may specify the option when you
298 call __pg_restore__.
299
300
301 __-R__
302
303
304 __--no-reconnect__
305
306
307 Prohibit __pg_dump__ from outputting a script that would
308 require reconnections to the database while being restored.
309 An average restoration script usually has to reconnect
310 several times as different users to set the original
311 ownerships of the objects. This option is a rather blunt
312 instrument because it makes __pg_dump__ lose this
313 ownership information, __unless__ you use the __-X
314 use-set-session-authorization__ option.
315
316
317 One possible reason why reconnections during restore might
318 not be desired is if the access to the database requires
319 manual interaction (e.g., passwords).
320
321
322 This option is only meaningful for the plain-text format.
323 For the other formats, you may specify the option when you
324 call __pg_restore__.
325
326
327 __-s__
328
329
330 __--schema-only__
331
332
333 Dump only the schema (data definitions), no
334 data.
335
336
337 __-S__ ''username''
338
339
340 __--superuser=__''username''
341
342
343 The scripts or archives created by __pg_dump__ need to
344 have superuser access in certain cases, such as when
345 disabling triggers or setting ownership of schema elements.
346 This option specifies the user name to use for those
347 cases.
348
349
350 __-t__ ''table''
351
352
353 __--table=__''table''
354
355
356 Dump data for ''table'' only.
357
358
359 __-v__
360
361
362 __--verbose__
363
364
365 Specifies verbose mode.
366
367
368 __-x__
369
370
371 __--no-privileges__
372
373
374 __--no-acl__
375
376
377 Prevent dumping of access privileges (grant/revoke
378 commands).
379
380
381 __-X use-set-session-authorization__
382
383
384 __--use-set-session-authorization__
385
386
387 Normally, if a (plain-text mode) script generated by
388 __pg_dump__ must alter the current database user (e.g.,
389 to set correct object ownerships), it uses the
390 psql(1) __connect__ command. This command actually
391 opens a new connection, which might require manual
392 interaction (e.g., passwords). If you use the __-X
393 use-set-session-authorization__ option, then
394 __pg_dump__ will instead output SET SESSION AUTHORIZATION
395 [[__set_session_authorization__(l)] commands. This has the
396 same effect, but it requires that the user restoring the
397 database from the generated script be a database superuser.
398 This option effectively overrides the __-R__
399 option.
400
401
402 Since SET SESSION AUTHORIZATION
403 [[__set_session_authorization__(l)] is a standard SQL
404 command, whereas __connect__ only works in
405 psql(1), this option also enhances the theoretical
406 portability of the output script.
407
408
409 This option is only meaningful for the plain-text format.
410 For the other formats, you may specify the option when you
411 call __pg_restore__.
412
413
414 __-Z__ ''0..9''
415
416
417 __--compress=__''0..9''
418
419
420 Specify the compression level to use in archive formats that
421 support compression (currently only the custom archive
422 format supports compression).
423
424
425 __pg_dump__ also accepts the following command line
426 arguments for connection parameters:
427
428
429 __-h__ ''host''
430
431
432 __--host=__''host''
433
434
435 Specifies the host name of the machine on which the server
436 is running. If host begins with a slash, it is used as the
437 directory for the Unix domain socket.
438
439
440 __-p__ ''port''
441
442
443 __--port=__''port''
444
445
446 Specifies the Internet TCP/IP port or local Unix domain
447 socket file extension on which the server is listening for
448 connections. The port number defaults to 5432, or the value
449 of the __PGPORT__ environment variable (if
450 set).
451
452
453 __-U__ ''username''
454
455
456 Connect as the given user.
457
458
459 __-W__
460
461
462 Force a password prompt. This should happen automatically if
463 the server requires password authentication.
464 !!DIAGNOSTICS
465
466
467 Connection to database 'template1' failed.
468 connectDBStart() -- connect() failed: No such file or directory
469 Is the postmaster running locally
470 and accepting connections on Unix socket '/tmp/.s.PGSQL.5432'?
471 __pg_dump__ could not attach to the __postmaster__ process on the specified host and port. If you see this message, ensure that the __postmaster__ is running on the proper host and that you have specified the proper port.
472
473
474 __Note: pg_dump__ internally executes __SELECT__
475 statements. If you have problems running __pg_dump__,
476 make sure you are able to select information from the
477 database using, for example, psql(1).
478 !!NOTES
479
480
481 If your installation has any local additions to the
482 template1 database, be careful to restore the output of
483 __pg_dump__ into a truly empty database; otherwise you
484 are likely to get errors due to duplicate definitions of the
485 added objects. To make an empty database without any local
486 additions, copy from template0 not template1, for
487 example:
488
489
490 CREATE DATABASE foo WITH TEMPLATE = template0;
491 __pg_dump__ has a few limitations:
492
493
494 When dumping a single table or as plain text, __pg_dump__
495 does not handle large objects. Large objects must be dumped
496 in their entirety using one of the binary archive
497 formats.
498
499
500 When doing a data only dump, __pg_dump__ emits queries to
501 disable triggers on user tables before inserting the data
502 and queries to re-enable them after the data has been
503 inserted. If the restore is stopped in the middle, the
504 system catalogs may be left in the wrong state.
505 !!EXAMPLES
506
507
508 To dump a database:
509
510
511 $ __pg_dump mydb
512 __To reload this database:
513
514
515 $ __psql -d database -f db.out
516 __To dump a database called mydb that contains large objects to a ''tar'' file:
517
518
519 $ __pg_dump -Ft -b mydb
520 __To reload this database (with large objects) to an existing database called newdb:
521
522
523 $ __pg_restore -d newdb db.tar
524 __
525 !!HISTORY
526
527
528 The __pg_dump__ utility first appeared in
529 __Postgres95__ release 0.02. The non-plain-text output
530 formats were introduced in __PostgreSQL__ release
531 7.1.
532 !!SEE ALSO
533
534
4 perry 535 pg_dumpall(1), __pg_restore__(1), psql(1),
1 perry 536 ''PostgreSQL Administrator's Guide''
537 ----
This page is a man page (or other imported legacy content). We are unable to automatically determine the license status of this page.