Debian Codegraph @

doc/src/sgml/ref/pg_restore.sgml

raw
1 <!--
2 doc/src/sgml/ref/pg_restore.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="app-pgrestore">
7 <indexterm zone="app-pgrestore">
8 <primary>pg_restore</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>pg_restore</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>pg_restore</refname>
19
20 <refpurpose>
21 restore a <productname>PostgreSQL</productname> database from an
22 archive file created by <application>pg_dump</application>
23 </refpurpose>
24 </refnamediv>
25
26 <refsynopsisdiv>
27 <cmdsynopsis>
28 <command>pg_restore</command>
29 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
30 <arg rep="repeat"><replaceable>option</replaceable></arg>
31 <arg choice="opt"><replaceable>filename</replaceable></arg>
32 </cmdsynopsis>
33 </refsynopsisdiv>
34
35
36 <refsect1 id="app-pgrestore-description">
37 <title>Description</title>
38
39 <para>
40 <application>pg_restore</application> is a utility for restoring a
41 <productname>PostgreSQL</productname> database from an archive
42 created by <xref linkend="app-pgdump"/> in one of the non-plain-text
43 formats. It will issue the commands necessary to reconstruct the
44 database to the state it was in at the time it was saved. The
45 archive files also allow <application>pg_restore</application> to
46 be selective about what is restored, or even to reorder the items
47 prior to being restored. The archive files are designed to be
48 portable across architectures.
49 </para>
50
51 <para>
52 <application>pg_restore</application> can operate in two modes.
53 If a database name is specified, <application>pg_restore</application>
54 connects to that database and restores archive contents directly into
55 the database. Otherwise, a script containing the SQL
56 commands necessary to rebuild the database is created and written
57 to a file or standard output. This script output is equivalent to
58 the plain text output format of <application>pg_dump</application>.
59 Some of the options controlling the output are therefore analogous to
60 <application>pg_dump</application> options.
61 </para>
62
63 <para>
64 Obviously, <application>pg_restore</application> cannot restore information
65 that is not present in the archive file. For instance, if the
66 archive was made using the <quote>dump data as
67 <command>INSERT</command> commands</quote> option,
68 <application>pg_restore</application> will not be able to load the data
69 using <command>COPY</command> statements.
70 </para>
71
72 <warning>
73 <para>
74 Restoring a dump causes the destination to execute arbitrary code of the
75 source superusers' choice. Partial dumps and partial restores do not limit
76 that. If the source superusers are not trusted, the dumped SQL statements
77 must be inspected before restoring. Non-plain-text dumps can be inspected
78 by using <application>pg_restore</application>'s <option>--file</option>
79 option. Note that the client running the dump and restore need not trust
80 the source or destination superusers.
81 </para>
82 </warning>
83 </refsect1>
84
85 <refsect1 id="app-pgrestore-options">
86 <title>Options</title>
87
88 <para>
89 <application>pg_restore</application> accepts the following command
90 line arguments.
91
92 <variablelist>
93 <varlistentry>
94 <term><replaceable class="parameter">filename</replaceable></term>
95 <listitem>
96 <para>
97 Specifies the location of the archive file (or directory, for a
98 directory-format archive) to be restored.
99 If not specified, the standard input is used.
100 </para>
101 </listitem>
102 </varlistentry>
103
104 <varlistentry>
105 <term><option>-a</option></term>
106 <term><option>--data-only</option></term>
107 <listitem>
108 <para>
109 Restore only the data, not the schema (data definitions) or statistics.
110 Table data, large objects, and sequence values are restored,
111 if present in the archive.
112 </para>
113
114 <para>
115 This option is similar to, but for historical reasons not identical
116 to, specifying <option>--section=data</option>.
117 </para>
118 </listitem>
119 </varlistentry>
120
121 <varlistentry>
122 <term><option>-c</option></term>
123 <term><option>--clean</option></term>
124 <listitem>
125 <para>
126 Before restoring database objects, issue commands
127 to <command>DROP</command> all the objects that will be restored.
128 This option is useful for overwriting an existing database.
129 If any of the objects do not exist in the destination database,
130 ignorable error messages will be reported,
131 unless <option>--if-exists</option> is also specified.
132 </para>
133 </listitem>
134 </varlistentry>
135
136 <varlistentry>
137 <term><option>-C</option></term>
138 <term><option>--create</option></term>
139 <listitem>
140 <para>
141 Create the database before restoring into it.
142 If <option>--clean</option> is also specified, drop and
143 recreate the target database before connecting to it.
144 </para>
145
146 <para>
147 With <option>--create</option>, <application>pg_restore</application>
148 also restores the database's comment if any, and any configuration
149 variable settings that are specific to this database, that is,
150 any <command>ALTER DATABASE ... SET ...</command>
151 and <command>ALTER ROLE ... IN DATABASE ... SET ...</command>
152 commands that mention this database.
153 Access privileges for the database itself are also restored,
154 unless <option>--no-acl</option> is specified.
155 </para>
156
157 <para>
158 When this option is used, the database named with <option>-d</option>
159 is used only to issue the initial <command>DROP DATABASE</command> and
160 <command>CREATE DATABASE</command> commands. All data is restored into the
161 database name that appears in the archive.
162 </para>
163 </listitem>
164 </varlistentry>
165
166 <varlistentry>
167 <term><option>-d <replaceable class="parameter">dbname</replaceable></option></term>
168 <term><option>--dbname=<replaceable class="parameter">dbname</replaceable></option></term>
169 <listitem>
170 <para>
171 Connect to database <replaceable
172 class="parameter">dbname</replaceable> and restore directly
173 into the database. The <replaceable>dbname</replaceable> can
174 be a <link linkend="libpq-connstring">connection string</link>.
175 If so, connection string parameters will override any conflicting
176 command line options.
177 </para>
178 </listitem>
179 </varlistentry>
180
181 <varlistentry>
182 <term><option>-e</option></term>
183 <term><option>--exit-on-error</option></term>
184 <listitem>
185 <para>
186 Exit if an error is encountered while sending SQL commands to
187 the database. The default is to continue and to display a count of
188 errors at the end of the restoration.
189 </para>
190 </listitem>
191 </varlistentry>
192
193 <varlistentry>
194 <term><option>-f <replaceable>filename</replaceable></option></term>
195 <term><option>--file=<replaceable>filename</replaceable></option></term>
196 <listitem>
197 <para>
198 Specify output file for generated script, or for the listing
199 when used with <option>-l</option>. Use <literal>-</literal>
200 for <systemitem>stdout</systemitem>.
201 </para>
202 </listitem>
203 </varlistentry>
204
205 <varlistentry>
206 <term><option>-F <replaceable class="parameter">format</replaceable></option></term>
207 <term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
208 <listitem>
209 <para>
210 Specify format of the archive. It is not necessary to specify
211 the format, since <application>pg_restore</application> will
212 determine the format automatically. If specified, it can be
213 one of the following:
214
215 <variablelist>
216 <varlistentry>
217 <term><literal>c</literal></term>
218 <term><literal>custom</literal></term>
219 <listitem>
220 <para>
221 The archive is in the custom format of
222 <application>pg_dump</application>.
223 </para>
224 </listitem>
225 </varlistentry>
226
227 <varlistentry>
228 <term><literal>d</literal></term>
229 <term><literal>directory</literal></term>
230 <listitem>
231 <para>
232 The archive is a directory archive.
233 </para>
234 </listitem>
235 </varlistentry>
236
237 <varlistentry>
238 <term><literal>t</literal></term>
239 <term><literal>tar</literal></term>
240 <listitem>
241 <para>
242 The archive is a <command>tar</command> archive.
243 </para>
244 </listitem>
245 </varlistentry>
246 </variablelist></para>
247 </listitem>
248 </varlistentry>
249
250 <varlistentry>
251 <term><option>-I <replaceable class="parameter">index</replaceable></option></term>
252 <term><option>--index=<replaceable class="parameter">index</replaceable></option></term>
253 <listitem>
254 <para>
255 Restore definition of named index only. Multiple indexes
256 may be specified with multiple <option>-I</option> switches.
257 </para>
258 </listitem>
259 </varlistentry>
260
261 <varlistentry>
262 <term><option>-j <replaceable class="parameter">number-of-jobs</replaceable></option></term>
263 <term><option>--jobs=<replaceable class="parameter">number-of-jobs</replaceable></option></term>
264 <listitem>
265 <para>
266 Run the most time-consuming steps
267 of <application>pg_restore</application> &mdash; those that load data,
268 create indexes, or create constraints &mdash; concurrently, using up
269 to <replaceable class="parameter">number-of-jobs</replaceable>
270 concurrent sessions. This option can dramatically reduce the time
271 to restore a large database to a server running on a
272 multiprocessor machine. This option is ignored when emitting a script
273 rather than connecting directly to a database server.
274 </para>
275
276 <para>
277 Each job is one process or one thread, depending on the
278 operating system, and uses a separate connection to the
279 server.
280 </para>
281
282 <para>
283 The optimal value for this option depends on the hardware
284 setup of the server, of the client, and of the network.
285 Factors include the number of CPU cores and the disk setup. A
286 good place to start is the number of CPU cores on the server,
287 but values larger than that can also lead to faster restore
288 times in many cases. Of course, values that are too high will
289 lead to decreased performance because of thrashing.
290 </para>
291
292 <para>
293 Only the custom and directory archive formats are supported
294 with this option.
295 The input must be a regular file or directory (not, for example, a
296 pipe or standard input). Also, multiple
297 jobs cannot be used together with the
298 option <option>--single-transaction</option>.
299 </para>
300 </listitem>
301 </varlistentry>
302
303 <varlistentry>
304 <term><option>-l</option></term>
305 <term><option>--list</option></term>
306 <listitem>
307 <para>
308 List the table of contents of the archive. The output of this operation
309 can be used as input to the <option>-L</option> option. Note that
310 if filtering switches such as <option>-n</option> or <option>-t</option> are
311 used with <option>-l</option>, they will restrict the items listed.
312 </para>
313 </listitem>
314 </varlistentry>
315
316 <varlistentry>
317 <term><option>-L <replaceable class="parameter">list-file</replaceable></option></term>
318 <term><option>--use-list=<replaceable class="parameter">list-file</replaceable></option></term>
319 <listitem>
320 <para>
321 Restore only those archive elements that are listed in <replaceable
322 class="parameter">list-file</replaceable>, and restore them in the
323 order they appear in the file. Note that
324 if filtering switches such as <option>-n</option> or <option>-t</option> are
325 used with <option>-L</option>, they will further restrict the items restored.
326 </para>
327 <para><replaceable class="parameter">list-file</replaceable> is normally created by
328 editing the output of a previous <option>-l</option> operation.
329 Lines can be moved or removed, and can also
330 be commented out by placing a semicolon (<literal>;</literal>) at the
331 start of the line. See below for examples.
332 </para>
333 </listitem>
334 </varlistentry>
335
336 <varlistentry>
337 <term><option>-n <replaceable class="parameter">schema</replaceable></option></term>
338 <term><option>--schema=<replaceable class="parameter">schema</replaceable></option></term>
339 <listitem>
340 <para>
341 Restore only objects that are in the named schema. Multiple schemas
342 may be specified with multiple <option>-n</option> switches. This can be
343 combined with the <option>-t</option> option to restore just a
344 specific table.
345 </para>
346 </listitem>
347 </varlistentry>
348
349 <varlistentry>
350 <term><option>-N <replaceable class="parameter">schema</replaceable></option></term>
351 <term><option>--exclude-schema=<replaceable class="parameter">schema</replaceable></option></term>
352 <listitem>
353 <para>
354 Do not restore objects that are in the named schema. Multiple schemas
355 to be excluded may be specified with multiple <option>-N</option> switches.
356 </para>
357
358 <para>
359 When both <option>-n</option> and <option>-N</option> are given for the same
360 schema name, the <option>-N</option> switch wins and the schema is excluded.
361 </para>
362 </listitem>
363 </varlistentry>
364
365 <varlistentry>
366 <term><option>-O</option></term>
367 <term><option>--no-owner</option></term>
368 <listitem>
369 <para>
370 Do not output commands to set
371 ownership of objects to match the original database.
372 By default, <application>pg_restore</application> issues
373 <command>ALTER OWNER</command> or
374 <command>SET SESSION AUTHORIZATION</command>
375 statements to set ownership of created schema elements.
376 These statements will fail unless the initial connection to the
377 database is made by a superuser
378 (or the same user that owns all of the objects in the script).
379 With <option>-O</option>, any user name can be used for the
380 initial connection, and this user will own all the created objects.
381 </para>
382 </listitem>
383 </varlistentry>
384
385 <varlistentry>
386 <term><option>-P <replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term>
387 <term><option>--function=<replaceable class="parameter">function-name(argtype [, ...])</replaceable></option></term>
388 <listitem>
389 <para>
390 Restore the named function only. Be careful to spell the function
391 name and arguments exactly as they appear in the dump file's table
392 of contents. Multiple functions may be specified with multiple
393 <option>-P</option> switches.
394 </para>
395 </listitem>
396 </varlistentry>
397
398 <varlistentry>
399 <term><option>-R</option></term>
400 <term><option>--no-reconnect</option></term>
401 <listitem>
402 <para>
403 This option is obsolete but still accepted for backwards
404 compatibility.
405 </para>
406 </listitem>
407 </varlistentry>
408
409 <varlistentry>
410 <term><option>-s</option></term>
411 <term><option>--schema-only</option></term>
412 <listitem>
413 <para>
414 Restore only the schema (data definitions), not data,
415 to the extent that schema entries are present in the archive.
416 </para>
417 <para>
418 This option cannot be used with <option>--data-only</option>
419 or <option>--statistics-only</option>.
420 It is similar to, but for historical reasons not identical to,
421 specifying
422 <option>--section=pre-data --section=post-data --no-statistics</option>.
423 </para>
424 <para>
425 (Do not confuse this with the <option>--schema</option> option, which
426 uses the word <quote>schema</quote> in a different meaning.)
427 </para>
428 </listitem>
429 </varlistentry>
430
431 <varlistentry>
432 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
433 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
434 <listitem>
435 <para>
436 Specify the superuser user name to use when disabling triggers.
437 This is relevant only if <option>--disable-triggers</option> is used.
438 </para>
439 </listitem>
440 </varlistentry>
441
442 <varlistentry>
443 <term><option>-t <replaceable class="parameter">table</replaceable></option></term>
444 <term><option>--table=<replaceable class="parameter">table</replaceable></option></term>
445 <listitem>
446 <para>
447 Restore definition and/or data of only the named table.
448 For this purpose, <quote>table</quote> includes views, materialized views,
449 sequences, and foreign tables. Multiple tables
450 can be selected by writing multiple <option>-t</option> switches.
451 This option can be combined with the <option>-n</option> option to
452 specify table(s) in a particular schema.
453 </para>
454
455 <note>
456 <para>
457 When <option>-t</option> is specified, <application>pg_restore</application>
458 makes no attempt to restore any other database objects that the
459 selected table(s) might depend upon. Therefore, there is no
460 guarantee that a specific-table restore into a clean database will
461 succeed.
462 </para>
463 </note>
464
465 <note>
466 <para>
467 This flag does not behave identically to the <option>-t</option>
468 flag of <application>pg_dump</application>. There is not currently
469 any provision for wild-card matching in <application>pg_restore</application>,
470 nor can you include a schema name within its <option>-t</option>.
471 And, while <application>pg_dump</application>'s <option>-t</option>
472 flag will also dump subsidiary objects (such as indexes) of the
473 selected table(s),
474 <application>pg_restore</application>'s <option>-t</option>
475 flag does not include such subsidiary objects.
476 </para>
477 </note>
478
479 <note>
480 <para>
481 In versions prior to <productname>PostgreSQL</productname> 9.6, this flag
482 matched only tables, not any other type of relation.
483 </para>
484 </note>
485 </listitem>
486 </varlistentry>
487
488 <varlistentry>
489 <term><option>-T <replaceable class="parameter">trigger</replaceable></option></term>
490 <term><option>--trigger=<replaceable class="parameter">trigger</replaceable></option></term>
491 <listitem>
492 <para>
493 Restore named trigger only. Multiple triggers may be specified with
494 multiple <option>-T</option> switches.
495 </para>
496 </listitem>
497 </varlistentry>
498
499 <varlistentry>
500 <term><option>-v</option></term>
501 <term><option>--verbose</option></term>
502 <listitem>
503 <para>
504 Specifies verbose mode. This will cause
505 <application>pg_restore</application> to output detailed object
506 comments and start/stop times to the output file, and progress
507 messages to standard error.
508 Repeating the option causes additional debug-level messages
509 to appear on standard error.
510 </para>
511 </listitem>
512 </varlistentry>
513
514 <varlistentry>
515 <term><option>-V</option></term>
516 <term><option>--version</option></term>
517 <listitem>
518 <para>
519 Print the <application>pg_restore</application> version and exit.
520 </para>
521 </listitem>
522 </varlistentry>
523
524 <varlistentry>
525 <term><option>-x</option></term>
526 <term><option>--no-privileges</option></term>
527 <term><option>--no-acl</option></term>
528 <listitem>
529 <para>
530 Prevent restoration of access privileges (grant/revoke commands).
531 </para>
532 </listitem>
533 </varlistentry>
534
535 <varlistentry>
536 <term><option>-1</option></term>
537 <term><option>--single-transaction</option></term>
538 <listitem>
539 <para>
540 Execute the restore as a single transaction (that is, wrap the
541 emitted commands in <command>BEGIN</command>/<command>COMMIT</command>). This
542 ensures that either all the commands complete successfully, or no
543 changes are applied. This option implies
544 <option>--exit-on-error</option>.
545 </para>
546 </listitem>
547 </varlistentry>
548
549 <varlistentry>
550 <term><option>--disable-triggers</option></term>
551 <listitem>
552 <para>
553 This option is relevant only when performing a restore without schema.
554 It instructs <application>pg_restore</application> to execute commands
555 to temporarily disable triggers on the target tables while
556 the data is restored. Use this if you have referential
557 integrity checks or other triggers on the tables that you
558 do not want to invoke during data restore.
559 </para>
560
561 <para>
562 Presently, the commands emitted for
563 <option>--disable-triggers</option> must be done as superuser. So you
564 should also specify a superuser name with <option>-S</option> or,
565 preferably, run <application>pg_restore</application> as a
566 <productname>PostgreSQL</productname> superuser.
567 </para>
568 </listitem>
569 </varlistentry>
570
571 <varlistentry>
572 <term><option>--enable-row-security</option></term>
573 <listitem>
574 <para>
575 This option is relevant only when restoring the contents of a table
576 which has row security. By default, <application>pg_restore</application> will set
577 <xref linkend="guc-row-security"/> to off, to ensure
578 that all data is restored in to the table. If the user does not have
579 sufficient privileges to bypass row security, then an error is thrown.
580 This parameter instructs <application>pg_restore</application> to set
581 <xref linkend="guc-row-security"/> to on instead, allowing the user to attempt to restore
582 the contents of the table with row security enabled. This might still
583 fail if the user does not have the right to insert the rows from the
584 dump into the table.
585 </para>
586
587 <para>
588 Note that this option currently also requires the dump be in <command>INSERT</command>
589 format, as <command>COPY FROM</command> does not support row security.
590 </para>
591 </listitem>
592 </varlistentry>
593
594 <varlistentry>
595 <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
596 <listitem>
597 <para>
598 Specify a filename from which to read patterns for objects excluded
599 or included from restore. The patterns are interpreted according to the
600 same rules as
601 <option>-n</option>/<option>--schema</option> for including objects in schemas,
602 <option>-N</option>/<option>--exclude-schema</option> for excluding objects in schemas,
603 <option>-P</option>/<option>--function</option> for restoring named functions,
604 <option>-I</option>/<option>--index</option> for restoring named indexes,
605 <option>-t</option>/<option>--table</option> for restoring named tables
606 or <option>-T</option>/<option>--trigger</option> for restoring triggers.
607 To read from <literal>STDIN</literal>, use <filename>-</filename> as the
608 filename. The <option>--filter</option> option can be specified in
609 conjunction with the above listed options for including or excluding
610 objects, and can also be specified more than once for multiple filter
611 files.
612 </para>
613
614 <para>
615 The file lists one database pattern per row, with the following format:
616 <synopsis>
617 { include | exclude } { function | index | schema | table | trigger } <replaceable class="parameter">PATTERN</replaceable>
618 </synopsis>
619 </para>
620
621 <para>
622 The first keyword specifies whether the objects matched by the pattern
623 are to be included or excluded. The second keyword specifies the type
624 of object to be filtered using the pattern:
625 <itemizedlist>
626 <listitem>
627 <para>
628 <literal>function</literal>: functions, works like the
629 <option>-P</option>/<option>--function</option> option. This keyword
630 can only be used with the <literal>include</literal> keyword.
631 </para>
632 </listitem>
633 <listitem>
634 <para>
635 <literal>index</literal>: indexes, works like the
636 <option>-I</option>/<option>--indexes</option> option. This keyword
637 can only be used with the <literal>include</literal> keyword.
638 </para>
639 </listitem>
640 <listitem>
641 <para>
642 <literal>schema</literal>: schemas, works like the
643 <option>-n</option>/<option>--schema</option> and
644 <option>-N</option>/<option>--exclude-schema</option> options.
645 </para>
646 </listitem>
647 <listitem>
648 <para>
649 <literal>table</literal>: tables, works like the
650 <option>-t</option>/<option>--table</option> option. This keyword
651 can only be used with the <literal>include</literal> keyword.
652 </para>
653 </listitem>
654 <listitem>
655 <para>
656 <literal>trigger</literal>: triggers, works like the
657 <option>-T</option>/<option>--trigger</option> option. This keyword
658 can only be used with the <literal>include</literal> keyword.
659 </para>
660 </listitem>
661 </itemizedlist>
662 </para>
663
664 <para>
665 Lines starting with <literal>#</literal> are considered comments and
666 ignored. Comments can be placed after an object pattern row as well.
667 Blank lines are also ignored. See <xref linkend="app-psql-patterns"/>
668 for how to perform quoting in patterns.
669 </para>
670
671 </listitem>
672 </varlistentry>
673
674 <varlistentry>
675 <term><option>--if-exists</option></term>
676 <listitem>
677 <para>
678 Use <literal>DROP ... IF EXISTS</literal> commands to drop objects
679 in <option>--clean</option> mode. This suppresses <quote>does not
680 exist</quote> errors that might otherwise be reported. This
681 option is not valid unless <option>--clean</option> is also
682 specified.
683 </para>
684 </listitem>
685 </varlistentry>
686
687 <varlistentry>
688 <term><option>--no-comments</option></term>
689 <listitem>
690 <para>
691 Do not output commands to restore comments, even if the archive
692 contains them.
693 </para>
694 </listitem>
695 </varlistentry>
696
697 <varlistentry>
698 <term><option>--no-data</option></term>
699 <listitem>
700 <para>
701 Do not output commands to restore data, even if the archive
702 contains them.
703 </para>
704 </listitem>
705 </varlistentry>
706
707 <varlistentry>
708 <term><option>--no-data-for-failed-tables</option></term>
709 <listitem>
710 <para>
711 By default, table data is restored even if the creation command
712 for the table failed (e.g., because it already exists).
713 With this option, data for such a table is skipped.
714 This behavior is useful if the target database already
715 contains the desired table contents. For example,
716 auxiliary tables for <productname>PostgreSQL</productname> extensions
717 such as <productname>PostGIS</productname> might already be loaded in
718 the target database; specifying this option prevents duplicate
719 or obsolete data from being loaded into them.
720 </para>
721
722 <para>
723 This option is effective only when restoring directly into a
724 database, not when producing SQL script output.
725 </para>
726 </listitem>
727 </varlistentry>
728
729 <varlistentry>
730 <term><option>--no-policies</option></term>
731 <listitem>
732 <para>
733 Do not output commands to restore row security policies, even if
734 the archive contains them.
735 </para>
736 </listitem>
737 </varlistentry>
738
739 <varlistentry>
740 <term><option>--no-publications</option></term>
741 <listitem>
742 <para>
743 Do not output commands to restore publications, even if the archive
744 contains them.
745 </para>
746 </listitem>
747 </varlistentry>
748
749 <varlistentry>
750 <term><option>--no-schema</option></term>
751 <listitem>
752 <para>
753 Do not output commands to restore schema (data definitions), even if
754 the archive contains them.
755 </para>
756 </listitem>
757 </varlistentry>
758
759 <varlistentry>
760 <term><option>--no-security-labels</option></term>
761 <listitem>
762 <para>
763 Do not output commands to restore security labels,
764 even if the archive contains them.
765 </para>
766 </listitem>
767 </varlistentry>
768
769 <varlistentry>
770 <term><option>--no-statistics</option></term>
771 <listitem>
772 <para>
773 Do not output commands to restore statistics, even if the archive
774 contains them.
775 </para>
776 </listitem>
777 </varlistentry>
778
779 <varlistentry>
780 <term><option>--no-subscriptions</option></term>
781 <listitem>
782 <para>
783 Do not output commands to restore subscriptions, even if the archive
784 contains them.
785 </para>
786 </listitem>
787 </varlistentry>
788
789 <varlistentry>
790 <term><option>--no-table-access-method</option></term>
791 <listitem>
792 <para>
793 Do not output commands to select table access methods.
794 With this option, all objects will be created with whichever
795 table access method is the default during restore.
796 </para>
797 </listitem>
798 </varlistentry>
799
800 <varlistentry>
801 <term><option>--no-tablespaces</option></term>
802 <listitem>
803 <para>
804 Do not output commands to select tablespaces.
805 With this option, all objects will be created in whichever
806 tablespace is the default during restore.
807 </para>
808 </listitem>
809 </varlistentry>
810
811 <varlistentry>
812 <term><option>--restrict-key=<replaceable class="parameter">restrict_key</replaceable></option></term>
813 <listitem>
814 <para>
815 Use the provided string as the <application>psql</application>
816 <command>\restrict</command> key in the dump output. This can only be
817 specified for SQL script output, i.e., when the <option>--file</option>
818 option is used. If no restrict key is specified,
819 <application>pg_restore</application> will generate a random one as
820 needed. Keys may contain only alphanumeric characters.
821 </para>
822 <para>
823 This option is primarily intended for testing purposes and other
824 scenarios that require repeatable output (e.g., comparing dump files).
825 It is not recommended for general use, as a malicious server with
826 advance knowledge of the key may be able to inject arbitrary code that
827 will be executed on the machine that runs
828 <application>psql</application> with the dump output.
829 </para>
830 </listitem>
831 </varlistentry>
832
833 <varlistentry>
834 <term><option>--section=<replaceable class="parameter">sectionname</replaceable></option></term>
835 <listitem>
836 <para>
837 Only restore the named section. The section name can be
838 <option>pre-data</option>, <option>data</option>, or <option>post-data</option>.
839 This option can be specified more than once to select multiple
840 sections. The default is to restore all sections.
841 </para>
842 <para>
843 The data section contains actual table data as well as large-object
844 definitions.
845 Post-data items consist of definitions of indexes, triggers, rules
846 and constraints other than validated check constraints.
847 Pre-data items consist of all other data definition items.
848 </para>
849 </listitem>
850 </varlistentry>
851
852 <varlistentry>
853 <term><option>--statistics</option></term>
854 <listitem>
855 <para>
856 Output commands to restore statistics, if the archive contains them.
857 This is the default.
858 </para>
859 </listitem>
860 </varlistentry>
861
862 <varlistentry>
863 <term><option>--statistics-only</option></term>
864 <listitem>
865 <para>
866 Restore only the statistics, not schema (data definitions) or data.
867 </para>
868 </listitem>
869 </varlistentry>
870
871 <varlistentry>
872 <term><option>--strict-names</option></term>
873 <listitem>
874 <para>
875 Require that each schema
876 (<option>-n</option>/<option>--schema</option>) and table
877 (<option>-t</option>/<option>--table</option>) qualifier match at
878 least one schema/table in the file to be restored.
879 </para>
880 </listitem>
881 </varlistentry>
882
883 <varlistentry>
884 <term><option>--transaction-size=<replaceable class="parameter">N</replaceable></option></term>
885 <listitem>
886 <para>
887 Execute the restore as a series of transactions, each processing
888 up to <replaceable class="parameter">N</replaceable> database
889 objects. This option implies <option>--exit-on-error</option>.
890 </para>
891 <para>
892 <option>--transaction-size</option> offers an intermediate choice
893 between the default behavior (one transaction per SQL command)
894 and <option>-1</option>/<option>--single-transaction</option>
895 (one transaction for all restored objects).
896 While <option>--single-transaction</option> has the least
897 overhead, it may be impractical for large databases because the
898 transaction will take a lock on each restored object, possibly
899 exhausting the server's lock table space.
900 Using <option>--transaction-size</option> with a size of a few
901 thousand objects offers nearly the same performance benefits while
902 capping the amount of lock table space needed.
903 </para>
904 </listitem>
905 </varlistentry>
906
907 <varlistentry>
908 <term><option>--use-set-session-authorization</option></term>
909 <listitem>
910 <para>
911 Output SQL-standard <command>SET SESSION AUTHORIZATION</command> commands
912 instead of <command>ALTER OWNER</command> commands to determine object
913 ownership. This makes the dump more standards-compatible, but
914 depending on the history of the objects in the dump, might not restore
915 properly.
916 </para>
917 </listitem>
918 </varlistentry>
919
920 <varlistentry>
921 <term><option>-?</option></term>
922 <term><option>--help</option></term>
923 <listitem>
924 <para>
925 Show help about <application>pg_restore</application> command line
926 arguments, and exit.
927 </para>
928 </listitem>
929 </varlistentry>
930
931 </variablelist>
932 </para>
933
934 <para>
935 <application>pg_restore</application> also accepts
936 the following command line arguments for connection parameters:
937
938 <variablelist>
939 <varlistentry>
940 <term><option>-h <replaceable class="parameter">host</replaceable></option></term>
941 <term><option>--host=<replaceable class="parameter">host</replaceable></option></term>
942 <listitem>
943 <para>
944 Specifies the host name of the machine on which the server is
945 running. If the value begins with a slash, it is used as the
946 directory for the Unix domain socket. The default is taken
947 from the <envar>PGHOST</envar> environment variable, if set,
948 else a Unix domain socket connection is attempted.
949 </para>
950 </listitem>
951 </varlistentry>
952
953 <varlistentry>
954 <term><option>-p <replaceable class="parameter">port</replaceable></option></term>
955 <term><option>--port=<replaceable class="parameter">port</replaceable></option></term>
956 <listitem>
957 <para>
958 Specifies the TCP port or local Unix domain socket file
959 extension on which the server is listening for connections.
960 Defaults to the <envar>PGPORT</envar> environment variable, if
961 set, or a compiled-in default.
962 </para>
963 </listitem>
964 </varlistentry>
965
966 <varlistentry>
967 <term><option>-U <replaceable>username</replaceable></option></term>
968 <term><option>--username=<replaceable class="parameter">username</replaceable></option></term>
969 <listitem>
970 <para>
971 User name to connect as.
972 </para>
973 </listitem>
974 </varlistentry>
975
976 <varlistentry>
977 <term><option>-w</option></term>
978 <term><option>--no-password</option></term>
979 <listitem>
980 <para>
981 Never issue a password prompt. If the server requires
982 password authentication and a password is not available by
983 other means such as a <filename>.pgpass</filename> file, the
984 connection attempt will fail. This option can be useful in
985 batch jobs and scripts where no user is present to enter a
986 password.
987 </para>
988 </listitem>
989 </varlistentry>
990
991 <varlistentry>
992 <term><option>-W</option></term>
993 <term><option>--password</option></term>
994 <listitem>
995 <para>
996 Force <application>pg_restore</application> to prompt for a
997 password before connecting to a database.
998 </para>
999
1000 <para>
1001 This option is never essential, since
1002 <application>pg_restore</application> will automatically prompt
1003 for a password if the server demands password authentication.
1004 However, <application>pg_restore</application> will waste a
1005 connection attempt finding out that the server wants a password.
1006 In some cases it is worth typing <option>-W</option> to avoid the extra
1007 connection attempt.
1008 </para>
1009 </listitem>
1010 </varlistentry>
1011
1012 <varlistentry>
1013 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
1014 <listitem>
1015 <para>
1016 Specifies a role name to be used to perform the restore.
1017 This option causes <application>pg_restore</application> to issue a
1018 <command>SET ROLE</command> <replaceable class="parameter">rolename</replaceable>
1019 command after connecting to the database. It is useful when the
1020 authenticated user (specified by <option>-U</option>) lacks privileges
1021 needed by <application>pg_restore</application>, but can switch to a role with
1022 the required rights. Some installations have a policy against
1023 logging in directly as a superuser, and use of this option allows
1024 restores to be performed without violating the policy.
1025 </para>
1026 </listitem>
1027 </varlistentry>
1028
1029 </variablelist>
1030 </para>
1031 </refsect1>
1032
1033
1034 <refsect1>
1035 <title>Environment</title>
1036
1037 <variablelist>
1038 <varlistentry>
1039 <term><envar>PGHOST</envar></term>
1040 <term><envar>PGOPTIONS</envar></term>
1041 <term><envar>PGPORT</envar></term>
1042 <term><envar>PGUSER</envar></term>
1043
1044 <listitem>
1045 <para>
1046 Default connection parameters
1047 </para>
1048 </listitem>
1049 </varlistentry>
1050
1051 <varlistentry>
1052 <term><envar>PG_COLOR</envar></term>
1053 <listitem>
1054 <para>
1055 Specifies whether to use color in diagnostic messages. Possible values
1056 are <literal>always</literal>, <literal>auto</literal> and
1057 <literal>never</literal>.
1058 </para>
1059 </listitem>
1060 </varlistentry>
1061 </variablelist>
1062
1063 <para>
1064 This utility, like most other <productname>PostgreSQL</productname> utilities,
1065 also uses the environment variables supported by <application>libpq</application>
1066 (see <xref linkend="libpq-envars"/>). However, it does not read
1067 <envar>PGDATABASE</envar> when a database name is not supplied.
1068 </para>
1069
1070 </refsect1>
1071
1072
1073 <refsect1 id="app-pgrestore-diagnostics">
1074 <title>Diagnostics</title>
1075
1076 <para>
1077 When a direct database connection is specified using the
1078 <option>-d</option> option, <application>pg_restore</application>
1079 internally executes <acronym>SQL</acronym> statements. If you have
1080 problems running <application>pg_restore</application>, make sure
1081 you are able to select information from the database using, for
1082 example, <xref linkend="app-psql"/>. Also, any default connection
1083 settings and environment variables used by the
1084 <application>libpq</application> front-end library will apply.
1085 </para>
1086 </refsect1>
1087
1088
1089 <refsect1 id="app-pgrestore-notes">
1090 <title>Notes</title>
1091
1092 <para>
1093 If your installation has any local additions to the
1094 <literal>template1</literal> database, be careful to load the output of
1095 <application>pg_restore</application> into a truly empty database;
1096 otherwise you are likely to get errors due to duplicate definitions
1097 of the added objects. To make an empty database without any local
1098 additions, copy from <literal>template0</literal> not <literal>template1</literal>, for example:
1099 <programlisting>
1100 CREATE DATABASE foo WITH TEMPLATE template0;
1101 </programlisting>
1102 </para>
1103
1104 <para>
1105 The limitations of <application>pg_restore</application> are detailed below.
1106
1107 <itemizedlist>
1108 <listitem>
1109 <para>
1110 When restoring data to a pre-existing table and the option
1111 <option>--disable-triggers</option> is used,
1112 <application>pg_restore</application> emits commands
1113 to disable triggers on user tables before inserting the data, then emits commands to
1114 re-enable them after the data has been inserted. If the restore is stopped in the
1115 middle, the system catalogs might be left in the wrong state.
1116 </para>
1117 </listitem>
1118
1119 <listitem>
1120 <para><application>pg_restore</application> cannot restore large objects
1121 selectively; for instance, only those for a specific table. If
1122 an archive contains large objects, then all large objects will be
1123 restored, or none of them if they are excluded via <option>-L</option>,
1124 <option>-t</option>, or other options.
1125 </para>
1126 </listitem>
1127
1128 </itemizedlist>
1129 </para>
1130
1131 <para>
1132 See also the <xref linkend="app-pgdump"/> documentation for details on
1133 limitations of <application>pg_dump</application>.
1134 </para>
1135
1136 <para>
1137 By default, <command>pg_restore</command> will restore optimizer statistics
1138 if included in the dump file. If not all statistics were restored, it may
1139 be useful to run <command>ANALYZE</command> on each restored table so the
1140 optimizer has useful statistics; see <xref
1141 linkend="vacuum-for-statistics"/> and <xref linkend="autovacuum"/> for more
1142 information.
1143 </para>
1144
1145 </refsect1>
1146
1147
1148 <refsect1 id="app-pgrestore-examples">
1149 <title>Examples</title>
1150
1151 <para>
1152 Assume we have dumped a database called <literal>mydb</literal> into a
1153 custom-format dump file:
1154
1155 <screen>
1156 <prompt>$</prompt> <userinput>pg_dump -Fc mydb &gt; db.dump</userinput>
1157 </screen>
1158 </para>
1159
1160 <para>
1161 To drop the database and recreate it from the dump:
1162
1163 <screen>
1164 <prompt>$</prompt> <userinput>dropdb mydb</userinput>
1165 <prompt>$</prompt> <userinput>pg_restore -C -d postgres db.dump</userinput>
1166 </screen>
1167
1168 The database named in the <option>-d</option> switch can be any database existing
1169 in the cluster; <application>pg_restore</application> only uses it to issue the
1170 <command>CREATE DATABASE</command> command for <literal>mydb</literal>. With
1171 <option>-C</option>, data is always restored into the database name that appears
1172 in the dump file.
1173 </para>
1174
1175 <para>
1176 To restore the dump into a new database called <literal>newdb</literal>:
1177
1178 <screen>
1179 <prompt>$</prompt> <userinput>createdb -T template0 newdb</userinput>
1180 <prompt>$</prompt> <userinput>pg_restore -d newdb db.dump</userinput>
1181 </screen>
1182
1183 Notice we don't use <option>-C</option>, and instead connect directly to the
1184 database to be restored into. Also note that we clone the new database
1185 from <literal>template0</literal> not <literal>template1</literal>, to ensure it is
1186 initially empty.
1187 </para>
1188
1189 <para>
1190 To reorder database items, it is first necessary to dump the table of
1191 contents of the archive:
1192 <screen>
1193 <prompt>$</prompt> <userinput>pg_restore -l db.dump &gt; db.list</userinput>
1194 </screen>
1195 The listing file consists of a header and one line for each item, e.g.:
1196 <programlisting>
1197 ;
1198 ; Archive created at Mon Sep 14 13:55:39 2009
1199 ; dbname: DBDEMOS
1200 ; TOC Entries: 81
1201 ; Compression: 9
1202 ; Dump Version: 1.10-0
1203 ; Format: CUSTOM
1204 ; Integer: 4 bytes
1205 ; Offset: 8 bytes
1206 ; Dumped from database version: 8.3.5
1207 ; Dumped by pg_dump version: 8.3.8
1208 ;
1209 ;
1210 ; Selected TOC Entries:
1211 ;
1212 3; 2615 2200 SCHEMA - public pasha
1213 1861; 0 0 COMMENT - SCHEMA public pasha
1214 1862; 0 0 ACL - public pasha
1215 317; 1247 17715 TYPE public composite pasha
1216 319; 1247 25899 DOMAIN public domain0 pasha
1217 </programlisting>
1218 Semicolons start a comment, and the numbers at the start of lines refer to the
1219 internal archive ID assigned to each item.
1220 </para>
1221
1222 <para>
1223 Lines in the file can be commented out, deleted, and reordered. For example:
1224 <programlisting>
1225 10; 145433 TABLE map_resolutions postgres
1226 ;2; 145344 TABLE species postgres
1227 ;4; 145359 TABLE nt_header postgres
1228 6; 145402 TABLE species_records postgres
1229 ;8; 145416 TABLE ss_old postgres
1230 </programlisting>
1231 could be used as input to <application>pg_restore</application> and would only restore
1232 items 10 and 6, in that order:
1233 <screen>
1234 <prompt>$</prompt> <userinput>pg_restore -L db.list db.dump</userinput>
1235 </screen></para>
1236
1237 </refsect1>
1238
1239 <refsect1>
1240 <title>See Also</title>
1241
1242 <simplelist type="inline">
1243 <member><xref linkend="app-pgdump"/></member>
1244 <member><xref linkend="app-pg-dumpall"/></member>
1245 <member><xref linkend="app-psql"/></member>
1246 </simplelist>
1247 </refsect1>
1248 </refentry>