1PG_DUMP(1) PostgreSQL Client Applications PG_DUMP(1)
2
6 pg_dump - extract a PostgreSQL database into a script file or other ar‐
7 chive file
8
11 pg_dump [ option... ] [ dbname ]
12
14 pg_dump is a utility for backing up a PostgreSQL database. It makes
15 consistent backups even if the database is being used concurrently.
16 pg_dump does not block other users accessing the database (readers or
17 writers).
18 Dumps can be output in script or archive file formats. Script dumps are
20 plain-text files containing the SQL commands required to reconstruct
21 the database to the state it was in at the time it was saved. To
22 restore from such a script, feed it to psql(1). Script files can be
23 used to reconstruct the database even on other machines and other
24 architectures; with some modifications even on other SQL database prod‐
25 ucts.
26 The alternative archive file formats must be used with pg_restore(1) to
28 rebuild the database. They allow pg_restore to be selective about what
29 is restored, or even to reorder the items prior to being restored. The
30 archive file formats are designed to be portable across architectures.
31 When used with one of the archive file formats and combined with
33 pg_restore, pg_dump provides a flexible archival and transfer mecha‐
34 nism. pg_dump can be used to backup an entire database, then pg_restore
35 can be used to examine the archive and/or select which parts of the
36 database are to be restored. The most flexible output file format is
37 the ``custom'' format (-Fc). It allows for selection and reordering of
38 all archived items, and is compressed by default. The tar format (-Ft)
39 is not compressed and it is not possible to reorder data when loading,
40 but it is otherwise quite flexible; moreover, it can be manipulated
41 with standard Unix tools such as tar.
42 While running pg_dump, one should examine the output for any warnings
44 (printed on standard error), especially in light of the limitations
45 listed below.
46
48 The following command-line options control the content and format of
49 the output.
50 dbname Specifies the name of the database to be dumped. If this is not
52 specified, the environment variable PGDATABASE is used. If that
53 is not set, the user name specified for the connection is used.
54 -a
56 --data-only
58 Dump only the data, not the schema (data definitions).
59 This option is only meaningful for the plain-text format. For
61 the archive formats, you can specify the option when you call
62 pg_restore.
63 -b
65 --blobs
67 Include large objects in the dump. This is the default behavior
68 except when --schema, --table, or --schema-only is specified, so
69 the -b switch is only useful to add large objects to selective
70 dumps.
71 -c
73 --clean
75 Output commands to clean (drop) database objects prior to (the
76 commands for) creating them.
77 This option is only meaningful for the plain-text format. For
79 the archive formats, you can specify the option when you call
80 pg_restore.
81 -C
83 --create
85 Begin the output with a command to create the database itself
86 and reconnect to the created database. (With a script of this
87 form, it doesn't matter which database you connect to before
88 running the script.)
89 This option is only meaningful for the plain-text format. For
91 the archive formats, you can specify the option when you call
92 pg_restore.
93 -E encoding
95 --encoding=encoding
97 Create the dump in the specified character set encoding. By
98 default, the dump is created in the database encoding. (Another
99 way to get the same result is to set the PGCLIENTENCODING envi‐
100 ronment variable to the desired dump encoding.)
101 -f file
103 --file=file
105 Send output to the specified file. If this is omitted, the stan‐
106 dard output is used.
107 -F format
109 --format=format
111 Selects the format of the output. format can be one of the fol‐
112 lowing:
113 p
115 plain Output a plain-text SQL script file (the default).
117 c
119 custom Output a custom archive suitable for input into
121 pg_restore. This is the most flexible format in that it
122 allows reordering of loading data as well as object defi‐
123 nitions. This format is also compressed by default.
124 t
126 tar Output a tar archive suitable for input into pg_restore.
128 Using this archive format allows reordering and/or exclu‐
129 sion of database objects at the time the database is
130 restored. It is also possible to limit which data is
131 reloaded at restore time.
132 -i
134 --ignore-version
136 A deprecated option that is now ignored.
137 -n schema
139 --schema=schema
141 Dump only schemas matching schema; this selects both the schema
142 itself, and all its contained objects. When this option is not
143 specified, all non-system schemas in the target database will be
144 dumped. Multiple schemas can be selected by writing multiple -n
145 switches. Also, the schema parameter is interpreted as a pattern
146 according to the same rules used by psql's \d commands (see Pat‐
147 terns [psql(1)]), so multiple schemas can also be selected by
148 writing wildcard characters in the pattern. When using wild‐
149 cards, be careful to quote the pattern if needed to prevent the
150 shell from expanding the wildcards.
151 Note: When -n is specified, pg_dump makes no attempt to dump any
153 other database objects that the selected schema(s) might depend
154 upon. Therefore, there is no guarantee that the results of a
155 specific-schema dump can be successfully restored by themselves
156 into a clean database.
157 Note: Non-schema objects such as blobs are not dumped when -n is
160 specified. You can add blobs back to the dump with the --blobs
161 switch.
162 -N schema
165 --exclude-schema=schema
167 Do not dump any schemas matching the schema pattern. The pattern
168 is interpreted according to the same rules as for -n. -N can be
169 given more than once to exclude schemas matching any of several
170 patterns.
171 When both -n and -N are given, the behavior is to dump just the
173 schemas that match at least one -n switch but no -N switches. If
174 -N appears without -n, then schemas matching -N are excluded
175 from what is otherwise a normal dump.
176 -o
178 --oids Dump object identifiers (OIDs) as part of the data for every ta‐
180 ble. Use this option if your application references the OID col‐
181 umns in some way (e.g., in a foreign key constraint). Other‐
182 wise, this option should not be used.
183 -O
185 --no-owner
187 Do not output commands to set ownership of objects to match the
188 original database. By default, pg_dump issues ALTER OWNER or
189 SET SESSION AUTHORIZATION statements to set ownership of created
190 database objects. These statements will fail when the script is
191 run unless it is started by a superuser (or the same user that
192 owns all of the objects in the script). To make a script that
193 can be restored by any user, but will give that user ownership
194 of all the objects, specify -O.
195 This option is only meaningful for the plain-text format. For
197 the archive formats, you can specify the option when you call
198 pg_restore.
199 -R
201 --no-reconnect
203 This option is obsolete but still accepted for backwards compat‐
204 ibility.
205 -s
207 --schema-only
209 Dump only the object definitions (schema), not data.
210 -S username
212 --superuser=username
214 Specify the superuser user name to use when disabling triggers.
215 This is only relevant if --disable-triggers is used. (Usually,
216 it's better to leave this out, and instead start the resulting
217 script as superuser.)
218 -t table
220 --table=table
222 Dump only tables (or views or sequences) matching table. Multi‐
223 ple tables can be selected by writing multiple -t switches.
224 Also, the table parameter is interpreted as a pattern according
225 to the same rules used by psql's \d commands (see Patterns
226 [psql(1)]), so multiple tables can also be selected by writing
227 wildcard characters in the pattern. When using wildcards, be
228 careful to quote the pattern if needed to prevent the shell from
229 expanding the wildcards.
230 The -n and -N switches have no effect when -t is used, because
232 tables selected by -t will be dumped regardless of those
233 switches, and non-table objects will not be dumped.
234 Note: When -t is specified, pg_dump makes no attempt to dump any
236 other database objects that the selected table(s) might depend
237 upon. Therefore, there is no guarantee that the results of a
238 specific-table dump can be successfully restored by themselves
239 into a clean database.
240 Note: The behavior of the -t switch is not entirely upward com‐
243 patible with pre-8.2 PostgreSQL versions. Formerly, writing -t
244 tab would dump all tables named tab, but now it just dumps
245 whichever one is visible in your default search path. To get the
246 old behavior you can write -t '*.tab'. Also, you must write
247 something like -t sch.tab to select a table in a particular
248 schema, rather than the old locution of -n sch -t tab.
249 -T table
252 --exclude-table=table
254 Do not dump any tables matching the table pattern. The pattern
255 is interpreted according to the same rules as for -t. -T can be
256 given more than once to exclude tables matching any of several
257 patterns.
258 When both -t and -T are given, the behavior is to dump just the
260 tables that match at least one -t switch but no -T switches. If
261 -T appears without -t, then tables matching -T are excluded from
262 what is otherwise a normal dump.
263 -v
265 --verbose
267 Specifies verbose mode. This will cause pg_dump to output
268 detailed object comments and start/stop times to the dump file,
269 and progress messages to standard error.
270 -x
272 --no-privileges
274 --no-acl
276 Prevent dumping of access privileges (grant/revoke commands).
277 -Z 0..9
279 --compress=0..9
281 Specify the compression level to use. Zero means no compression.
282 For the custom archive format, this specifies compression of
283 individual table-data segments, and the default is to compress
284 at a moderate level. For plain text output, setting a nonzero
285 compression level causes the entire output file to be com‐
286 pressed, as though it had been fed through gzip; but the default
287 is not to compress. The tar archive format currently does not
288 support compression at all.
289 --binary-upgrade
291 This option is for use by in-place upgrade utilities. Its use
292 for other purposes is not recommended or supported. The behavior
293 of the option may change in future releases without notice.
294 --inserts
296 Dump data as INSERT commands (rather than COPY). This will make
297 restoration very slow; it is mainly useful for making dumps that
298 can be loaded into non-PostgreSQL databases. Also, since this
299 option generates a separate command for each row, an error in
300 reloading a row causes only that row to be lost rather than the
301 entire table contents. Note that the restore might fail alto‐
302 gether if you have rearranged column order. The --column-
303 inserts option is safe against column order changes, though even
304 slower.
305 --column-inserts
307 --attribute-inserts
309 Dump data as INSERT commands with explicit column names (INSERT
310 INTO table (column, ...) VALUES ...). This will make restoration
311 very slow; it is mainly useful for making dumps that can be
312 loaded into non-PostgreSQL databases. Also, since this option
313 generates a separate command for each row, an error in reloading
314 a row causes only that row to be lost rather than the entire ta‐
315 ble contents.
316 --disable-dollar-quoting
318 This option disables the use of dollar quoting for function bod‐
319 ies, and forces them to be quoted using SQL standard string syn‐
320 tax.
321 --disable-triggers
323 This option is only relevant when creating a data-only dump. It
324 instructs pg_dump to include commands to temporarily disable
325 triggers on the target tables while the data is reloaded. Use
326 this if you have referential integrity checks or other triggers
327 on the tables that you do not want to invoke during data reload.
328 Presently, the commands emitted for --disable-triggers must be
330 done as superuser. So, you should also specify a superuser name
331 with -S, or preferably be careful to start the resulting script
332 as a superuser.
333 This option is only meaningful for the plain-text format. For
335 the archive formats, you can specify the option when you call
336 pg_restore.
337 --lock-wait-timeout=timeout
339 Do not wait forever to acquire shared table locks at the begin‐
340 ning of the dump. Instead fail if unable to lock a table within
341 the specified timeout. The timeout may be specified in any of
342 the formats accepted by SET statement_timeout. (Allowed values
343 vary depending on the server version you are dumping from, but
344 an integer number of milliseconds is accepted by all versions
345 since 7.3. This option is ignored when dumping from a pre-7.3
346 server.)
347 --no-tablespaces
349 Do not output commands to select tablespaces. With this option,
350 all objects will be created in whichever tablespace is the
351 default during restore.
352 This option is only meaningful for the plain-text format. For
354 the archive formats, you can specify the option when you call
355 pg_restore.
356 --use-set-session-authorization
358 Output SQL-standard SET SESSION AUTHORIZATION commands instead
359 of ALTER OWNER commands to determine object ownership. This
360 makes the dump more standards compatible, but depending on the
361 history of the objects in the dump, might not restore properly.
362 Also, a dump using SET SESSION AUTHORIZATION will certainly
363 require superuser privileges to restore correctly, whereas ALTER
364 OWNER requires lesser privileges.
365 The following command-line options control the database connection
367 parameters.
368 -h host
370 --host=host
372 Specifies the host name of the machine on which the server is
373 running. If the value begins with a slash, it is used as the
374 directory for the Unix domain socket. The default is taken from
375 the PGHOST environment variable, if set, else a Unix domain
376 socket connection is attempted.
377 -p port
379 --port=port
381 Specifies the TCP port or local Unix domain socket file exten‐
382 sion on which the server is listening for connections. Defaults
383 to the PGPORT environment variable, if set, or a compiled-in
384 default.
385 -U username
387 --username=username
389 User name to connect as.
390 -w
392 --no-password
394 Never issue a password prompt. If the server requires password
395 authentication and a password is not available by other means
396 such as a .pgpass file, the connection attempt will fail. This
397 option can be useful in batch jobs and scripts where no user is
398 present to enter a password.
399 -W
401 --password
403 Force pg_dump to prompt for a password before connecting to a
404 database.
405 This option is never essential, since pg_dump will automatically
407 prompt for a password if the server demands password authentica‐
408 tion. However, pg_dump will waste a connection attempt finding
409 out that the server wants a password. In some cases it is worth
410 typing -W to avoid the extra connection attempt.
411 --role=rolename
413 Specifies a role name to be used to create the dump. This
414 option causes pg_dump to issue a SET ROLE rolename command after
415 connecting to the database. It is useful when the authenticated
416 user (specified by -U) lacks privileges needed by pg_dump, but
417 can switch to a role with the required rights. Some installa‐
418 tions have a policy against logging in directly as a superuser,
419 and use of this option allows dumps to be made without violating
420 the policy.
421
423 PGDATABASE
424 PGHOST
426 PGOPTIONS
428 PGPORT
430 PGUSER Default connection parameters.
432 This utility, like most other PostgreSQL utilities, also uses the envi‐
434 ronment variables supported by libpq (see in the documentation).
435
437 pg_dump internally executes SELECT statements. If you have problems
438 running pg_dump, make sure you are able to select information from the
439 database using, for example, psql(1). Also, any default connection set‐
440 tings and environment variables used by the libpq front-end library
441 will apply.
442 The database activity of pg_dump is normally collected by the statis‐
444 tics collector. If this is undesirable, you can set parameter
445 track_counts to false via PGOPTIONS or the ALTER USER command.
446
448 If your database cluster has any local additions to the template1 data‐
449 base, be careful to restore the output of pg_dump into a truly empty
450 database; otherwise you are likely to get errors due to duplicate defi‐
451 nitions of the added objects. To make an empty database without any
452 local additions, copy from template0 not template1, for example:
453 CREATE DATABASE foo WITH TEMPLATE template0;
455 When a data-only dump is chosen and the option --disable-triggers is
458 used, pg_dump emits commands to disable triggers on user tables before
459 inserting the data, and then commands to re-enable them after the data
460 has been inserted. If the restore is stopped in the middle, the system
461 catalogs might be left in the wrong state.
462 Members of tar archives are limited to a size less than 8 GB. (This is
464 an inherent limitation of the tar file format.) Therefore this format
465 cannot be used if the textual representation of any one table exceeds
466 that size. The total size of a tar archive and any of the other output
467 formats is not limited, except possibly by the operating system.
468 The dump file produced by pg_dump does not contain the statistics used
470 by the optimizer to make query planning decisions. Therefore, it is
471 wise to run ANALYZE after restoring from a dump file to ensure good
472 performance; see in the documentation and in the documentation for more
473 information. The dump file also does not contain any ALTER DATABASE
474 ... SET commands; these settings are dumped by pg_dumpall(1), along
475 with database users and other installation-wide settings.
476 Because pg_dump is used to transfer data to newer versions of Post‐
478 greSQL, the output of pg_dump can be expected to load into PostgreSQL
479 server versions newer than pg_dump's version. pg_dump can also dump
480 from PostgreSQL servers older than its own version. (Currently,
481 servers back to version 7.0 are supported.) However, pg_dump cannot
482 dump from PostgreSQL servers newer than its own major version; it will
483 refuse to even try, rather than risk making an invalid dump. Also, it
484 is not guaranteed that pg_dump's output can be loaded into a server of
485 an older major version — not even if the dump was taken from a server
486 of that version. Loading a dump file into an older server may require
487 manual editing of the dump file to remove syntax not understood by the
488 older server.
489
491 To dump a database called mydb into a SQL-script file:
492 $ pg_dump mydb > db.sql
494 To reload such a script into a (freshly created) database named newdb:
497 $ psql -d newdb -f db.sql
499 To dump a database into a custom-format archive file:
502 $ pg_dump -Fc mydb > db.dump
504 To reload an archive file into a (freshly created) database named
507 newdb:
508 $ pg_restore -d newdb db.dump
510 To dump a single table named mytab:
513 $ pg_dump -t mytab mydb > db.sql
515 To dump all tables whose names start with emp in the detroit schema,
518 except for the table named employee_log:
519 $ pg_dump -t 'detroit.emp*' -T detroit.employee_log mydb > db.sql
521 To dump all schemas whose names start with east or west and end in gsm,
524 excluding any schemas whose names contain the word test:
525 $ pg_dump -n 'east*gsm' -n 'west*gsm' -N '*test*' mydb > db.sql
527 The same, using regular expression notation to consolidate the
530 switches:
531 $ pg_dump -n '(east|west)*gsm' -N '*test*' mydb > db.sql
533 To dump all database objects except for tables whose names begin with
536 ts_:
537 $ pg_dump -T 'ts_*' mydb > db.sql
539 To specify an upper-case or mixed-case name in -t and related switches,
542 you need to double-quote the name; else it will be folded to lower case
543 (see Patterns [psql(1)]). But double quotes are special to the shell,
544 so in turn they must be quoted. Thus, to dump a single table with a
545 mixed-case name, you need something like
546 $ pg_dump -t '"MixedCaseName"' mydb > mytab.sql
548
551 pg_dumpall(1), pg_restore(1), psql(1)
552Application 2014-02-17 PG_DUMP(1)