1 <!-- $PostgreSQL: pgsql/doc/src/sgml/ref/pg_restore.sgml,v 1.60 2006/08/01 18:21:44 momjian Exp $ -->
3 <refentry id="APP-PGRESTORE">
5 <refentrytitle>pg_restore</refentrytitle>
6 <manvolnum>1</manvolnum>
7 <refmiscinfo>Application</refmiscinfo>
11 <refname>pg_restore</refname>
14 restore a <productname>PostgreSQL</productname> database from an archive file created by pg_dump
18 <indexterm zone="app-pgrestore">
19 <primary>pg_restore</primary>
24 <command>pg_restore</command>
25 <arg rep="repeat"><replaceable>option</replaceable></arg>
26 <arg><replaceable>filename</replaceable></arg>
31 <refsect1 id="app-pgrestore-description">
32 <title>Description</title>
35 <application>pg_restore</application> is a utility for restoring a
36 <productname>PostgreSQL</productname> database from an archive
37 created by <xref linkend="app-pgdump"> in one of the non-plain-text
38 formats. It will issue the commands necessary to reconstruct the
39 database to the state it was in at the time it was saved. The
40 archive files also allow <application>pg_restore</application> to
41 be selective about what is restored, or even to reorder the items
42 prior to being restored. The archive files are designed to be
43 portable across architectures.
47 <application>pg_restore</application> can operate in two modes.
48 If a database name is specified, the archive is restored directly into
49 the database. Otherwise, a script containing the SQL
50 commands necessary to rebuild the database is created and written
51 to a file or standard output. The script output is equivalent to
52 the plain text output format of <application>pg_dump</application>.
53 Some of the options controlling the output are therefore analogous to
54 <application>pg_dump</application> options.
58 Obviously, <application>pg_restore</application> cannot restore information
59 that is not present in the archive file. For instance, if the
60 archive was made using the <quote>dump data as
61 <command>INSERT</command> commands</quote> option,
62 <application>pg_restore</application> will not be able to load the data
63 using <command>COPY</command> statements.
67 <refsect1 id="app-pgrestore-options">
68 <title>Options</title>
71 <application>pg_restore</application> accepts the following command
76 <term><replaceable class="parameter">filename</replaceable></term>
79 Specifies the location of the archive file to be restored.
80 If not specified, the standard input is used.
86 <term><option>-a</option></term>
87 <term><option>--data-only</option></term>
90 Restore only the data, not the schema (data definitions).
96 <term><option>-c</option></term>
97 <term><option>--clean</option></term>
100 Clean (drop) database objects before recreating them.
106 <term><option>-C</option></term>
107 <term><option>--create</option></term>
110 Create the database before restoring into it. (When this
111 option is used, the database named with <option>-d</option> is
112 used only to issue the initial <command>CREATE DATABASE</>
113 command. All data is restored into the database name that
114 appears in the archive.)
120 <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
121 <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></option></term>
124 Connect to database <replaceable
125 class="parameter">dbname</replaceable> and restore directly
132 <term><option>-e</option></term>
133 <term><option>--exit-on-error</option></term>
136 Exit if an error is encountered while sending SQL commands to
137 the database. The default is to continue and to display a count of
138 errors at the end of the restoration.
144 <term><option>-f <replaceable>filename</replaceable></option></term>
145 <term><option>--file=<replaceable>filename</replaceable></option></term>
148 Specify output file for generated script, or for the listing
149 when used with <option>-l</option>. Default is the standard
156 <term><option>-F <replaceable class="parameter">format</replaceable></option></term>
157 <term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
160 Specify format of the archive. It is not necessary to specify
161 the format, since <application>pg_restore</application> will
162 determine the format automatically. If specified, it can be
163 one of the following:
167 <term><literal>t</></term>
168 <term><literal>tar</></term>
171 The archive is a <command>tar</command> archive. Using this
172 archive format allows reordering and/or exclusion of schema
173 elements at the time the database is restored. It is also
174 possible to limit which data is reloaded at restore time.
180 <term><literal>c</></term>
181 <term><literal>custom</></term>
184 The archive is in the custom format of
185 <application>pg_dump</application>. This is the most
186 flexible format in that it allows reordering of data load
187 as well as schema elements. This format is also compressed
198 <term><option>-i</option></term>
199 <term><option>--ignore-version</option></term>
202 Ignore database version checks.
208 <term><option>-I <replaceable class="parameter">index</replaceable></option></term>
209 <term><option>--index=<replaceable class="parameter">index</replaceable></option></term>
212 Restore definition of named index only.
218 <term><option>-l</option></term>
219 <term><option>--list</option></term>
222 List the contents of the archive. The output of this operation
223 can be used with the <option>-L</option> option to restrict
224 and reorder the items that are restored.
230 <term><option>-L <replaceable class="parameter">list-file</replaceable></option></term>
231 <term><option>--use-list=<replaceable class="parameter">list-file</replaceable></option></term>
234 Restore elements in <replaceable class="PARAMETER">
235 list-file</replaceable> only, and in the
236 order they appear in the file. Lines can be moved and may also
237 be commented out by placing a <literal>;</literal> at the
238 start of the line. (See below for examples.)
244 <term><option>-n <replaceable class="parameter">namespace</replaceable></option></term>
245 <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
248 Restore only objects that are in the named schema. This can be
249 combined with the <option>-t</option> option to restore just a
256 <term><option>-O</option></term>
257 <term><option>--no-owner</option></term>
260 Do not output commands to set
261 ownership of objects to match the original database.
262 By default, <application>pg_restore</application> issues
263 <command>ALTER OWNER</> or
264 <command>SET SESSION AUTHORIZATION</command>
265 statements to set ownership of created schema elements.
266 These statements will fail unless the initial connection to the
267 database is made by a superuser
268 (or the same user that owns all of the objects in the script).
269 With <option>-O</option>, any user name can be used for the
270 initial connection, and this user will own all the created objects.
276 <term><option>-P <replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term>
277 <term><option>--function=<replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term>
280 Restore the named function only. Be careful to spell the function
281 name and arguments exactly as they appear in the dump file's table
288 <term><option>-R</option></term>
289 <term><option>--no-reconnect</option></term>
292 This option is obsolete but still accepted for backwards
299 <term><option>-s</option></term>
300 <term><option>--schema-only</option></term>
303 Restore only the schema (data definitions), not the data (table
304 contents). Sequence current values will not be restored, either.
305 (Do not confuse this with the <option>--schema</> option, which
306 uses the word <quote>schema</> in a different meaning.)
312 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
313 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
316 Specify the superuser user name to use when disabling triggers.
317 This is only relevant if <option>--disable-triggers</> is used.
323 <term><option>-t <replaceable class="parameter">table</replaceable></option></term>
324 <term><option>--table=<replaceable class="parameter">table</replaceable></option></term>
327 Restore definition and/or data of named table only.
333 <term><option>-T <replaceable class="parameter">trigger</replaceable></option></term>
334 <term><option>--trigger=<replaceable class="parameter">trigger</replaceable></option></term>
337 Restore named trigger only.
343 <term><option>-v</option></term>
344 <term><option>--verbose</option></term>
347 Specifies verbose mode.
353 <term><option>-x</option></term>
354 <term><option>--no-privileges</option></term>
355 <term><option>--no-acl</option></term>
358 Prevent restoration of access privileges (grant/revoke commands).
364 <term><option>-X disable-triggers</></term>
365 <term><option>--disable-triggers</></term>
368 This option is only relevant when performing a data-only restore.
369 It instructs <application>pg_restore</application> to execute commands
370 to temporarily disable triggers on the target tables while
371 the data is reloaded. Use this if you have referential
372 integrity checks or other triggers on the tables that you
373 do not want to invoke during data reload.
377 Presently, the commands emitted for
378 <option>--disable-triggers</> must be done as superuser. So, you
379 should also specify a superuser name with <option>-S</>, or
380 preferably run <application>pg_restore</application> as a
381 <productname>PostgreSQL</> superuser.
387 <term><option>-X use-set-session-authorization</option></term>
388 <term><option>--use-set-session-authorization</option></term>
391 Output SQL-standard <command>SET SESSION AUTHORIZATION</> commands
392 instead of <command>ALTER OWNER</> commands to determine object
393 ownership. This makes the dump more standards compatible, but
394 depending on the history of the objects in the dump, may not restore
401 <term><option>-X no-data-for-failed-tables</></term>
404 By default, table data objects are restored even if the
405 associated table could not be successfully created (e. g.
406 because it already exists). With this option, such table
407 data is silently ignored. This is useful for dumping and
408 restoring databases with tables which contain auxiliary data
409 for PostgreSQL extensions (e. g. PostGIS).
418 <application>pg_restore</application> also accepts
419 the following command line arguments for connection parameters:
423 <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
424 <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
427 Specifies the host name of the machine on which the server is
428 running. If the value begins with a slash, it is used as the
429 directory for the Unix domain socket. The default is taken
430 from the <envar>PGHOST</envar> environment variable, if set,
431 else a Unix domain socket connection is attempted.
437 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
438 <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
441 Specifies the TCP port or local Unix domain socket file
442 extension on which the server is listening for connections.
443 Defaults to the <envar>PGPORT</envar> environment variable, if
444 set, or a compiled-in default.
450 <term><option>-U <replaceable>username</replaceable></option></term>
453 Connect as the given user
459 <term><option>-W</option></term>
462 Force a password prompt. This should happen automatically if
463 the server requires password authentication.
469 <term><option>-1</option></term>
470 <term><option>--single-transaction</option></term>
473 Execute the restore as a single transaction (that is, wrap the
474 emitted commands in <command>BEGIN</>/<command>COMMIT</>). This
475 ensures that either all the commands complete successfully, or no
476 changes are applied. This option implies
477 <option>--exit-on-error</>.
488 <title>Environment</title>
492 <term><envar>PGHOST</envar></term>
493 <term><envar>PGPORT</envar></term>
494 <term><envar>PGUSER</envar></term>
498 Default connection parameters
506 <refsect1 id="app-pgrestore-diagnostics">
507 <title>Diagnostics</title>
510 When a direct database connection is specified using the
511 <option>-d</option> option, <application>pg_restore</application>
512 internally executes <acronym>SQL</acronym> statements. If you have
513 problems running <application>pg_restore</application>, make sure
514 you are able to select information from the database using, for
515 example, <xref linkend="app-psql">.
520 <refsect1 id="app-pgrestore-notes">
524 If your installation has any local additions to the
525 <literal>template1</> database, be careful to load the output of
526 <application>pg_restore</application> into a truly empty database;
527 otherwise you are likely to get errors due to duplicate definitions
528 of the added objects. To make an empty database without any local
529 additions, copy from <literal>template0</> not <literal>template1</>, for example:
531 CREATE DATABASE foo WITH TEMPLATE template0;
536 The limitations of <application>pg_restore</application> are detailed below.
541 When restoring data to a pre-existing table and the option
542 <option>--disable-triggers</> is used,
543 <application>pg_restore</application> emits commands
544 to disable triggers on user tables before inserting the data then emits commands to
545 re-enable them after the data has been inserted. If the restore is stopped in the
546 middle, the system catalogs may be left in the wrong state.
552 <application>pg_restore</application> will not restore large objects for a single table. If
553 an archive contains large objects, then all large objects will be restored.
561 See also the <xref linkend="app-pgdump"> documentation for details on
562 limitations of <application>pg_dump</application>.
566 Once restored, it is wise to run <command>ANALYZE</> on each
567 restored table so the optimizer has useful statistics.
573 <refsect1 id="app-pgrestore-examples">
574 <title>Examples</title>
577 To dump a database called <literal>mydb</> to a <filename>tar</filename>
581 <prompt>$</prompt> <userinput>pg_dump -Ft mydb > db.tar</userinput>
586 To reload this dump into an
587 existing database called <literal>newdb</>:
590 <prompt>$</prompt> <userinput>pg_restore -d newdb db.tar</userinput>
595 To reorder database items, it is first necessary to dump the table of
596 contents of the archive:
598 <prompt>$</prompt> <userinput>pg_restore -l archive.file > archive.list</userinput>
600 The listing file consists of a header and one line for each item, e.g.,
603 ; Archive created at Fri Jul 28 22:28:36 2000
607 ; Dump Version: 1.4-0
611 ; Selected TOC Entries:
613 2; 145344 TABLE species postgres
614 3; 145344 ACL species
615 4; 145359 TABLE nt_header postgres
616 5; 145359 ACL nt_header
617 6; 145402 TABLE species_records postgres
618 7; 145402 ACL species_records
619 8; 145416 TABLE ss_old postgres
621 10; 145433 TABLE map_resolutions postgres
622 11; 145433 ACL map_resolutions
623 12; 145443 TABLE hs_old postgres
624 13; 145443 ACL hs_old
626 Semicolons start a comment, and the numbers at the start of lines refer to the
627 internal archive ID assigned to each item.
631 Lines in the file can be commented out, deleted, and reordered. For example,
633 10; 145433 TABLE map_resolutions postgres
634 ;2; 145344 TABLE species postgres
635 ;4; 145359 TABLE nt_header postgres
636 6; 145402 TABLE species_records postgres
637 ;8; 145416 TABLE ss_old postgres
639 could be used as input to <application>pg_restore</application> and would only restore
640 items 10 and 6, in that order:
642 <prompt>$</prompt> <userinput>pg_restore -L archive.list archive.file</userinput>
649 <title>History</title>
652 The <application>pg_restore</application> utility first appeared in
653 <productname>PostgreSQL</productname> 7.1.
658 <title>See Also</title>
660 <simplelist type="inline">
661 <member><xref linkend="app-pgdump"></member>
662 <member><xref linkend="app-pg-dumpall"></member>
663 <member><xref linkend="app-psql"></member>
664 <member>Environment Variables (<xref linkend="libpq-envars">)</member>
669 <!-- Keep this comment at the end of the file
674 sgml-minimize-attributes:nil
675 sgml-always-quote-attributes:t
678 sgml-parent-document:nil
679 sgml-default-dtd-file:"../reference.ced"
680 sgml-exposed-tags:nil
681 sgml-local-catalogs:"/usr/lib/sgml/catalog"
682 sgml-local-ecat-files:nil