Debian Codegraph @

doc/src/sgml/ref/pg_dumpall.sgml

raw
1 <!--
2 doc/src/sgml/ref/pg_dumpall.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="app-pg-dumpall">
7 <indexterm zone="app-pg-dumpall">
8 <primary>pg_dumpall</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle><application>pg_dumpall</application></refentrytitle>
13 <manvolnum>1</manvolnum>
14 <refmiscinfo>Application</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>pg_dumpall</refname>
19 <refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <cmdsynopsis>
24 <command>pg_dumpall</command>
25 <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
26 <arg rep="repeat"><replaceable>option</replaceable></arg>
27 </cmdsynopsis>
28 </refsynopsisdiv>
29
30 <refsect1 id="app-pg-dumpall-description">
31 <title>Description</title>
32
33 <para>
34 <application>pg_dumpall</application> is a utility for writing out
35 (<quote>dumping</quote>) all <productname>PostgreSQL</productname> databases
36 of a cluster into one script file. The script file contains
37 <acronym>SQL</acronym> commands that can be used as input to <xref
38 linkend="app-psql"/> to restore the databases. It does this by
39 calling <xref linkend="app-pgdump"/> for each database in the cluster.
40 <application>pg_dumpall</application> also dumps global objects
41 that are common to all databases, namely database roles, tablespaces,
42 and privilege grants for configuration parameters.
43 (<application>pg_dump</application> does not save these objects.)
44 </para>
45
46 <para>
47 Since <application>pg_dumpall</application> reads tables from all
48 databases you will most likely have to connect as a database
49 superuser in order to produce a complete dump. Also you will need
50 superuser privileges to execute the saved script in order to be
51 allowed to add roles and create databases.
52 </para>
53
54 <para>
55 The SQL script will be written to the standard output. Use the
56 <option>-f</option>/<option>--file</option> option or shell operators to
57 redirect it into a file.
58 </para>
59
60 <para>
61 <application>pg_dumpall</application> needs to connect several
62 times to the <productname>PostgreSQL</productname> server (once per
63 database). If you use password authentication it will ask for
64 a password each time. It is convenient to have a
65 <filename>~/.pgpass</filename> file in such cases. See <xref
66 linkend="libpq-pgpass"/> for more information.
67 </para>
68
69 <warning>
70 <para>
71 Restoring a dump causes the destination to execute arbitrary code of the
72 source superusers' choice. Partial dumps and partial restores do not limit
73 that. If the source superusers are not trusted, the dumped SQL statements
74 must be inspected before restoring. Note that the client running the dump
75 and restore need not trust the source or destination superusers.
76 </para>
77 </warning>
78
79 </refsect1>
80
81 <refsect1>
82 <title>Options</title>
83
84 <para>
85 The following command-line options control the content and
86 format of the output.
87
88 <variablelist>
89 <varlistentry>
90 <term><option>-a</option></term>
91 <term><option>--data-only</option></term>
92 <listitem>
93 <para>
94 Dump only the data, not the schema (data definitions) or statistics.
95 </para>
96 </listitem>
97 </varlistentry>
98
99 <varlistentry>
100 <term><option>-c</option></term>
101 <term><option>--clean</option></term>
102 <listitem>
103 <para>
104 Emit SQL commands to <command>DROP</command> all the dumped
105 databases, roles, and tablespaces before recreating them.
106 This option is useful when the restore is to overwrite an existing
107 cluster. If any of the objects do not exist in the destination
108 cluster, ignorable error messages will be reported during
109 restore, unless <option>--if-exists</option> is also specified.
110 </para>
111 </listitem>
112 </varlistentry>
113
114 <varlistentry>
115 <term><option>-E <replaceable class="parameter">encoding</replaceable></option></term>
116 <term><option>--encoding=<replaceable class="parameter">encoding</replaceable></option></term>
117 <listitem>
118 <para>
119 Create the dump in the specified character set encoding. By default,
120 the dump is created in the database encoding. (Another way to get the
121 same result is to set the <envar>PGCLIENTENCODING</envar> environment
122 variable to the desired dump encoding.)
123 </para>
124 </listitem>
125 </varlistentry>
126
127 <varlistentry>
128 <term><option>-f <replaceable class="parameter">filename</replaceable></option></term>
129 <term><option>--file=<replaceable class="parameter">filename</replaceable></option></term>
130 <listitem>
131 <para>
132 Send output to the specified file. If this is omitted, the
133 standard output is used.
134 </para>
135 </listitem>
136 </varlistentry>
137
138 <varlistentry>
139 <term><option>-g</option></term>
140 <term><option>--globals-only</option></term>
141 <listitem>
142 <para>
143 Dump only global objects (roles and tablespaces), no databases.
144 </para>
145 </listitem>
146 </varlistentry>
147
148 <varlistentry>
149 <term><option>-O</option></term>
150 <term><option>--no-owner</option></term>
151 <listitem>
152 <para>
153 Do not output commands to set
154 ownership of objects to match the original database.
155 By default, <application>pg_dumpall</application> issues
156 <command>ALTER OWNER</command> or
157 <command>SET SESSION AUTHORIZATION</command>
158 statements to set ownership of created schema elements.
159 These statements
160 will fail when the script is run unless it is started by a superuser
161 (or the same user that owns all of the objects in the script).
162 To make a script that can be restored by any user, but will give
163 that user ownership of all the objects, specify <option>-O</option>.
164 </para>
165 </listitem>
166 </varlistentry>
167
168 <varlistentry>
169 <term><option>-r</option></term>
170 <term><option>--roles-only</option></term>
171 <listitem>
172 <para>
173 Dump only roles, no databases or tablespaces.
174 </para>
175 </listitem>
176 </varlistentry>
177
178 <varlistentry>
179 <term><option>-s</option></term>
180 <term><option>--schema-only</option></term>
181 <listitem>
182 <para>
183 Dump only the object definitions (schema), not data.
184 </para>
185 </listitem>
186 </varlistentry>
187
188 <varlistentry>
189 <term><option>-S <replaceable class="parameter">username</replaceable></option></term>
190 <term><option>--superuser=<replaceable class="parameter">username</replaceable></option></term>
191 <listitem>
192 <para>
193 Specify the superuser user name to use when disabling triggers.
194 This is relevant only if <option>--disable-triggers</option> is used.
195 (Usually, it's better to leave this out, and instead start the
196 resulting script as superuser.)
197 </para>
198 </listitem>
199 </varlistentry>
200
201 <varlistentry>
202 <term><option>-t</option></term>
203 <term><option>--tablespaces-only</option></term>
204 <listitem>
205 <para>
206 Dump only tablespaces, no databases or roles.
207 </para>
208 </listitem>
209 </varlistentry>
210
211 <varlistentry>
212 <term><option>-v</option></term>
213 <term><option>--verbose</option></term>
214 <listitem>
215 <para>
216 Specifies verbose mode. This will cause
217 <application>pg_dumpall</application> to output start/stop
218 times to the dump file, and progress messages to standard error.
219 Repeating the option causes additional debug-level messages
220 to appear on standard error.
221 The option is also passed down to <application>pg_dump</application>.
222 </para>
223 </listitem>
224 </varlistentry>
225
226 <varlistentry>
227 <term><option>-V</option></term>
228 <term><option>--version</option></term>
229 <listitem>
230 <para>
231 Print the <application>pg_dumpall</application> version and exit.
232 </para>
233 </listitem>
234 </varlistentry>
235
236 <varlistentry>
237 <term><option>-x</option></term>
238 <term><option>--no-privileges</option></term>
239 <term><option>--no-acl</option></term>
240 <listitem>
241 <para>
242 Prevent dumping of access privileges (grant/revoke commands).
243 </para>
244 </listitem>
245 </varlistentry>
246
247 <varlistentry>
248 <term><option>--binary-upgrade</option></term>
249 <listitem>
250 <para>
251 This option is for use by in-place upgrade utilities. Its use
252 for other purposes is not recommended or supported. The
253 behavior of the option may change in future releases without
254 notice.
255 </para>
256 </listitem>
257 </varlistentry>
258
259 <varlistentry>
260 <term><option>--column-inserts</option></term>
261 <term><option>--attribute-inserts</option></term>
262 <listitem>
263 <para>
264 Dump data as <command>INSERT</command> commands with explicit
265 column names (<literal>INSERT INTO
266 <replaceable>table</replaceable>
267 (<replaceable>column</replaceable>, ...) VALUES
268 ...</literal>). This will make restoration very slow; it is mainly
269 useful for making dumps that can be loaded into
270 non-<productname>PostgreSQL</productname> databases.
271 </para>
272 </listitem>
273 </varlistentry>
274
275 <varlistentry>
276 <term><option>--disable-dollar-quoting</option></term>
277 <listitem>
278 <para>
279 This option disables the use of dollar quoting for function bodies,
280 and forces them to be quoted using SQL standard string syntax.
281 </para>
282 </listitem>
283 </varlistentry>
284
285 <varlistentry>
286 <term><option>--disable-triggers</option></term>
287 <listitem>
288 <para>
289 This option is relevant only when creating a dump with data and without schema.
290 It instructs <application>pg_dumpall</application> to include commands
291 to temporarily disable triggers on the target tables while
292 the data is restored. Use this if you have referential
293 integrity checks or other triggers on the tables that you
294 do not want to invoke during data restore.
295 </para>
296
297 <para>
298 Presently, the commands emitted for <option>--disable-triggers</option>
299 must be done as superuser. So, you should also specify
300 a superuser name with <option>-S</option>, or preferably be careful to
301 start the resulting script as a superuser.
302 </para>
303 </listitem>
304 </varlistentry>
305
306 <varlistentry>
307 <term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term>
308 <listitem>
309 <para>
310 Do not dump databases whose name matches
311 <replaceable class="parameter">pattern</replaceable>.
312 Multiple patterns can be excluded by writing multiple
313 <option>--exclude-database</option> switches. The
314 <replaceable class="parameter">pattern</replaceable> parameter is
315 interpreted as a pattern according to the same rules used by
316 <application>psql</application>'s <literal>\d</literal>
317 commands (see <xref linkend="app-psql-patterns"/>),
318 so multiple databases can also be excluded by writing wildcard
319 characters in the pattern. When using wildcards, be careful to
320 quote the pattern if needed to prevent shell wildcard expansion.
321 </para>
322 </listitem>
323 </varlistentry>
324
325 <varlistentry>
326 <term><option>--extra-float-digits=<replaceable class="parameter">ndigits</replaceable></option></term>
327 <listitem>
328 <para>
329 Use the specified value of extra_float_digits when dumping
330 floating-point data, instead of the maximum available precision.
331 Routine dumps made for backup purposes should not use this option.
332 </para>
333 </listitem>
334 </varlistentry>
335
336 <varlistentry>
337 <term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
338 <listitem>
339 <para>
340 Specify a filename from which to read patterns for databases excluded
341 from the dump. The patterns are interpreted according to the same rules
342 as <option>--exclude-database</option>.
343 To read from <literal>STDIN</literal>, use <filename>-</filename> as the
344 filename. The <option>--filter</option> option can be specified in
345 conjunction with <option>--exclude-database</option> for excluding
346 databases, and can also be specified more than once for multiple filter
347 files.
348 </para>
349
350 <para>
351 The file lists one database pattern per row, with the following format:
352 <synopsis>
353 exclude database <replaceable class="parameter">PATTERN</replaceable>
354 </synopsis>
355 </para>
356
357 <para>
358 Lines starting with <literal>#</literal> are considered comments and
359 ignored. Comments can be placed after an object pattern row as well.
360 Blank lines are also ignored. See <xref linkend="app-psql-patterns"/>
361 for how to perform quoting in patterns.
362 </para>
363
364 </listitem>
365 </varlistentry>
366
367 <varlistentry>
368 <term><option>--if-exists</option></term>
369 <listitem>
370 <para>
371 Use <literal>DROP ... IF EXISTS</literal> commands to drop objects
372 in <option>--clean</option> mode. This suppresses <quote>does not
373 exist</quote> errors that might otherwise be reported. This
374 option is not valid unless <option>--clean</option> is also
375 specified.
376 </para>
377 </listitem>
378 </varlistentry>
379
380 <varlistentry>
381 <term><option>--inserts</option></term>
382 <listitem>
383 <para>
384 Dump data as <command>INSERT</command> commands (rather
385 than <command>COPY</command>). This will make restoration very slow;
386 it is mainly useful for making dumps that can be loaded into
387 non-<productname>PostgreSQL</productname> databases. Note that
388 the restore might fail altogether if you have rearranged column order.
389 The <option>--column-inserts</option> option is safer, though even
390 slower.
391 </para>
392 </listitem>
393 </varlistentry>
394
395 <varlistentry>
396 <term><option>--load-via-partition-root</option></term>
397 <listitem>
398 <para>
399 When dumping data for a table partition, make
400 the <command>COPY</command> or <command>INSERT</command> statements
401 target the root of the partitioning hierarchy that contains it, rather
402 than the partition itself. This causes the appropriate partition to
403 be re-determined for each row when the data is loaded. This may be
404 useful when restoring data on a server where rows do not always fall
405 into the same partitions as they did on the original server. That
406 could happen, for example, if the partitioning column is of type text
407 and the two systems have different definitions of the collation used
408 to sort the partitioning column.
409 </para>
410 </listitem>
411 </varlistentry>
412
413 <varlistentry>
414 <term><option>--lock-wait-timeout=<replaceable class="parameter">timeout</replaceable></option></term>
415 <listitem>
416 <para>
417 Do not wait forever to acquire shared table locks at the beginning of
418 the dump. Instead, fail if unable to lock a table within the specified
419 <replaceable class="parameter">timeout</replaceable>. The timeout may be
420 specified in any of the formats accepted by <command>SET
421 statement_timeout</command>.
422 </para>
423 </listitem>
424 </varlistentry>
425
426 <varlistentry>
427 <term><option>--no-comments</option></term>
428 <listitem>
429 <para>
430 Do not dump <command>COMMENT</command> commands.
431 </para>
432 </listitem>
433 </varlistentry>
434
435 <varlistentry>
436 <term><option>--no-data</option></term>
437 <listitem>
438 <para>
439 Do not dump data.
440 </para>
441 </listitem>
442 </varlistentry>
443
444 <varlistentry>
445 <term><option>--no-policies</option></term>
446 <listitem>
447 <para>
448 Do not dump row security policies.
449 </para>
450 </listitem>
451 </varlistentry>
452
453 <varlistentry>
454 <term><option>--no-publications</option></term>
455 <listitem>
456 <para>
457 Do not dump publications.
458 </para>
459 </listitem>
460 </varlistentry>
461
462 <varlistentry>
463 <term><option>--no-role-passwords</option></term>
464 <listitem>
465 <para>
466 Do not dump passwords for roles. When restored, roles will have a
467 null password, and password authentication will always fail until the
468 password is set. Since password values aren't needed when this option
469 is specified, the role information is read from the catalog
470 view <structname>pg_roles</structname> instead
471 of <structname>pg_authid</structname>. Therefore, this option also
472 helps if access to <structname>pg_authid</structname> is restricted by
473 some security policy.
474 </para>
475 </listitem>
476 </varlistentry>
477
478 <varlistentry>
479 <term><option>--no-schema</option></term>
480 <listitem>
481 <para>
482 Do not dump schema (data definitions).
483 </para>
484 </listitem>
485 </varlistentry>
486
487 <varlistentry>
488 <term><option>--no-security-labels</option></term>
489 <listitem>
490 <para>
491 Do not dump security labels.
492 </para>
493 </listitem>
494 </varlistentry>
495
496 <varlistentry>
497 <term><option>--no-statistics</option></term>
498 <listitem>
499 <para>
500 Do not dump statistics. This is the default.
501 </para>
502 </listitem>
503 </varlistentry>
504
505 <varlistentry>
506 <term><option>--no-subscriptions</option></term>
507 <listitem>
508 <para>
509 Do not dump subscriptions.
510 </para>
511 </listitem>
512 </varlistentry>
513
514 <varlistentry>
515 <term><option>--no-sync</option></term>
516 <listitem>
517 <para>
518 By default, <command>pg_dumpall</command> will wait for all files
519 to be written safely to disk. This option causes
520 <command>pg_dumpall</command> to return without waiting, which is
521 faster, but means that a subsequent operating system crash can leave
522 the dump corrupt. Generally, this option is useful for testing
523 but should not be used when dumping data from production installation.
524 </para>
525 </listitem>
526 </varlistentry>
527
528 <varlistentry>
529 <term><option>--no-table-access-method</option></term>
530 <listitem>
531 <para>
532 Do not output commands to select table access methods.
533 With this option, all objects will be created with whichever
534 table access method is the default during restore.
535 </para>
536 </listitem>
537 </varlistentry>
538
539 <varlistentry>
540 <term><option>--no-tablespaces</option></term>
541 <listitem>
542 <para>
543 Do not output commands to create tablespaces nor select tablespaces
544 for objects.
545 With this option, all objects will be created in whichever
546 tablespace is the default during restore.
547 </para>
548 </listitem>
549 </varlistentry>
550
551 <varlistentry>
552 <term><option>--no-toast-compression</option></term>
553 <listitem>
554 <para>
555 Do not output commands to set <acronym>TOAST</acronym> compression
556 methods.
557 With this option, all columns will be restored with the default
558 compression setting.
559 </para>
560 </listitem>
561 </varlistentry>
562
563 <varlistentry>
564 <term><option>--no-unlogged-table-data</option></term>
565 <listitem>
566 <para>
567 Do not dump the contents of unlogged tables. This option has no
568 effect on whether or not the table definitions (schema) are dumped;
569 it only suppresses dumping the table data.
570 </para>
571 </listitem>
572 </varlistentry>
573
574 <varlistentry>
575 <term><option>--on-conflict-do-nothing</option></term>
576 <listitem>
577 <para>
578 Add <literal>ON CONFLICT DO NOTHING</literal> to
579 <command>INSERT</command> commands.
580 This option is not valid unless <option>--inserts</option> or
581 <option>--column-inserts</option> is also specified.
582 </para>
583 </listitem>
584 </varlistentry>
585
586 <varlistentry>
587 <term><option>--quote-all-identifiers</option></term>
588 <listitem>
589 <para>
590 Force quoting of all identifiers. This option is recommended when
591 dumping a database from a server whose <productname>PostgreSQL</productname>
592 major version is different from <application>pg_dumpall</application>'s, or when
593 the output is intended to be loaded into a server of a different
594 major version. By default, <application>pg_dumpall</application> quotes only
595 identifiers that are reserved words in its own major version.
596 This sometimes results in compatibility issues when dealing with
597 servers of other versions that may have slightly different sets
598 of reserved words. Using <option>--quote-all-identifiers</option> prevents
599 such issues, at the price of a harder-to-read dump script.
600 </para>
601 </listitem>
602 </varlistentry>
603
604 <varlistentry>
605 <term><option>--restrict-key=<replaceable class="parameter">restrict_key</replaceable></option></term>
606 <listitem>
607 <para>
608 Use the provided string as the <application>psql</application>
609 <command>\restrict</command> key in the dump output. If no restrict
610 key is specified, <application>pg_dumpall</application> will generate a
611 random one as needed. Keys may contain only alphanumeric characters.
612 </para>
613 <para>
614 This option is primarily intended for testing purposes and other
615 scenarios that require repeatable output (e.g., comparing dump files).
616 It is not recommended for general use, as a malicious server with
617 advance knowledge of the key may be able to inject arbitrary code that
618 will be executed on the machine that runs
619 <application>psql</application> with the dump output.
620 </para>
621 </listitem>
622 </varlistentry>
623
624 <varlistentry>
625 <term><option>--rows-per-insert=<replaceable class="parameter">nrows</replaceable></option></term>
626 <listitem>
627 <para>
628 Dump data as <command>INSERT</command> commands (rather than
629 <command>COPY</command>). Controls the maximum number of rows per
630 <command>INSERT</command> command. The value specified must be a
631 number greater than zero. Any error during restoring will cause only
632 rows that are part of the problematic <command>INSERT</command> to be
633 lost, rather than the entire table contents.
634 </para>
635 </listitem>
636 </varlistentry>
637
638 <varlistentry>
639 <term><option>--statistics</option></term>
640 <listitem>
641 <para>
642 Dump optimizer statistics.
643 </para>
644 </listitem>
645 </varlistentry>
646
647 <varlistentry>
648 <term><option>--statistics-only</option></term>
649 <listitem>
650 <para>
651 Dump only the statistics, not the schema (data definitions) or data.
652 Optimizer statistics for tables, materialized views, foreign tables,
653 and indexes are dumped.
654 </para>
655 </listitem>
656 </varlistentry>
657
658 <varlistentry>
659 <term><option>--sequence-data</option></term>
660 <listitem>
661 <para>
662 Include sequence data in the dump. This is the default behavior except
663 when <option>--no-data</option>, <option>--schema-only</option>, or
664 <option>--statistics-only</option> is specified.
665 </para>
666 </listitem>
667 </varlistentry>
668
669 <varlistentry>
670 <term><option>--use-set-session-authorization</option></term>
671 <listitem>
672 <para>
673 Output SQL-standard <command>SET SESSION AUTHORIZATION</command> commands
674 instead of <command>ALTER OWNER</command> commands to determine object
675 ownership. This makes the dump more standards compatible, but
676 depending on the history of the objects in the dump, might not restore
677 properly.
678 </para>
679 </listitem>
680 </varlistentry>
681
682 <varlistentry>
683 <term><option>-?</option></term>
684 <term><option>--help</option></term>
685 <listitem>
686 <para>
687 Show help about <application>pg_dumpall</application> command line
688 arguments, and exit.
689 </para>
690 </listitem>
691 </varlistentry>
692
693 </variablelist>
694 </para>
695
696 <para>
697 The following command-line options control the database connection parameters.
698
699 <variablelist>
700 <varlistentry>
701 <term><option>-d <replaceable class="parameter">connstr</replaceable></option></term>
702 <term><option>--dbname=<replaceable class="parameter">connstr</replaceable></option></term>
703 <listitem>
704 <para>
705 Specifies parameters used to connect to the server, as a <link
706 linkend="libpq-connstring">connection string</link>; these
707 will override any conflicting command line options.
708 </para>
709 <para>
710 The option is called <literal>--dbname</literal> for consistency with other
711 client applications, but because <application>pg_dumpall</application>
712 needs to connect to many databases, the database name in the
713 connection string will be ignored. Use the <literal>-l</literal>
714 option to specify the name of the database used for the initial
715 connection, which will dump global objects and discover what other
716 databases should be dumped.
717 </para>
718 </listitem>
719 </varlistentry>
720
721 <varlistentry>
722 <term><option>-h <replaceable>host</replaceable></option></term>
723 <term><option>--host=<replaceable>host</replaceable></option></term>
724 <listitem>
725 <para>
726 Specifies the host name of the machine on which the database
727 server is running. If the value begins with a slash, it is
728 used as the directory for the Unix domain socket. The default
729 is taken from the <envar>PGHOST</envar> environment variable,
730 if set, else a Unix domain socket connection is attempted.
731 </para>
732 </listitem>
733 </varlistentry>
734
735 <varlistentry>
736 <term><option>-l <replaceable>dbname</replaceable></option></term>
737 <term><option>--database=<replaceable>dbname</replaceable></option></term>
738 <listitem>
739 <para>
740 Specifies the name of the database to connect to for dumping global
741 objects and discovering what other databases should be dumped. If
742 not specified, the <literal>postgres</literal> database will be used,
743 and if that does not exist, <literal>template1</literal> will be used.
744 </para>
745 </listitem>
746 </varlistentry>
747
748 <varlistentry>
749 <term><option>-p <replaceable>port</replaceable></option></term>
750 <term><option>--port=<replaceable>port</replaceable></option></term>
751 <listitem>
752 <para>
753 Specifies the TCP port or local Unix domain socket file
754 extension on which the server is listening for connections.
755 Defaults to the <envar>PGPORT</envar> environment variable, if
756 set, or a compiled-in default.
757 </para>
758 </listitem>
759 </varlistentry>
760
761 <varlistentry>
762 <term><option>-U <replaceable>username</replaceable></option></term>
763 <term><option>--username=<replaceable>username</replaceable></option></term>
764 <listitem>
765 <para>
766 User name to connect as.
767 </para>
768 </listitem>
769 </varlistentry>
770
771 <varlistentry>
772 <term><option>-w</option></term>
773 <term><option>--no-password</option></term>
774 <listitem>
775 <para>
776 Never issue a password prompt. If the server requires
777 password authentication and a password is not available by
778 other means such as a <filename>.pgpass</filename> file, the
779 connection attempt will fail. This option can be useful in
780 batch jobs and scripts where no user is present to enter a
781 password.
782 </para>
783 </listitem>
784 </varlistentry>
785
786 <varlistentry>
787 <term><option>-W</option></term>
788 <term><option>--password</option></term>
789 <listitem>
790 <para>
791 Force <application>pg_dumpall</application> to prompt for a
792 password before connecting to a database.
793 </para>
794
795 <para>
796 This option is never essential, since
797 <application>pg_dumpall</application> will automatically prompt
798 for a password if the server demands password authentication.
799 However, <application>pg_dumpall</application> will waste a
800 connection attempt finding out that the server wants a password.
801 In some cases it is worth typing <option>-W</option> to avoid the extra
802 connection attempt.
803 </para>
804
805 <para>
806 Note that the password prompt will occur again for each database
807 to be dumped. Usually, it's better to set up a
808 <filename>~/.pgpass</filename> file than to rely on manual password entry.
809 </para>
810 </listitem>
811 </varlistentry>
812
813 <varlistentry>
814 <term><option>--role=<replaceable class="parameter">rolename</replaceable></option></term>
815 <listitem>
816 <para>
817 Specifies a role name to be used to create the dump.
818 This option causes <application>pg_dumpall</application> to issue a
819 <command>SET ROLE</command> <replaceable class="parameter">rolename</replaceable>
820 command after connecting to the database. It is useful when the
821 authenticated user (specified by <option>-U</option>) lacks privileges
822 needed by <application>pg_dumpall</application>, but can switch to a role with
823 the required rights. Some installations have a policy against
824 logging in directly as a superuser, and use of this option allows
825 dumps to be made without violating the policy.
826 </para>
827 </listitem>
828 </varlistentry>
829 </variablelist>
830 </para>
831 </refsect1>
832
833
834 <refsect1>
835 <title>Environment</title>
836
837 <variablelist>
838 <varlistentry>
839 <term><envar>PGHOST</envar></term>
840 <term><envar>PGOPTIONS</envar></term>
841 <term><envar>PGPORT</envar></term>
842 <term><envar>PGUSER</envar></term>
843
844 <listitem>
845 <para>
846 Default connection parameters
847 </para>
848 </listitem>
849 </varlistentry>
850
851 <varlistentry>
852 <term><envar>PG_COLOR</envar></term>
853 <listitem>
854 <para>
855 Specifies whether to use color in diagnostic messages. Possible values
856 are <literal>always</literal>, <literal>auto</literal> and
857 <literal>never</literal>.
858 </para>
859 </listitem>
860 </varlistentry>
861 </variablelist>
862
863 <para>
864 This utility, like most other <productname>PostgreSQL</productname> utilities,
865 also uses the environment variables supported by <application>libpq</application>
866 (see <xref linkend="libpq-envars"/>).
867 </para>
868
869 </refsect1>
870
871
872 <refsect1>
873 <title>Notes</title>
874
875 <para>
876 Since <application>pg_dumpall</application> calls
877 <application>pg_dump</application> internally, some diagnostic
878 messages will refer to <application>pg_dump</application>.
879 </para>
880
881 <para>
882 The <option>--clean</option> option can be useful even when your
883 intention is to restore the dump script into a fresh cluster. Use of
884 <option>--clean</option> authorizes the script to drop and re-create the
885 built-in <literal>postgres</literal> and <literal>template1</literal>
886 databases, ensuring that those databases will retain the same properties
887 (for instance, locale and encoding) that they had in the source cluster.
888 Without the option, those databases will retain their existing
889 database-level properties, as well as any pre-existing contents.
890 </para>
891
892 <para>
893 When <option>--statistics</option> is specified,
894 <command>pg_dumpall</command> will include most optimizer statistics in the
895 resulting dump file. This does not include all statistics, such as
896 those created explicitly with <xref linkend="sql-createstatistics"/>,
897 custom statistics added by an extension, or statistics collected by the
898 cumulative statistics system. Therefore, it may still be useful to
899 run <command>ANALYZE</command> on each database after restoring from a dump
900 file to ensure optimal performance. You can also run <command>vacuumdb -a
901 -z</command> to analyze all databases.
902 </para>
903
904 <para>
905 The dump script should not be expected to run completely without errors.
906 In particular, because the script will issue <command>CREATE ROLE</command>
907 for every role existing in the source cluster, it is certain to get a
908 <quote>role already exists</quote> error for the bootstrap superuser,
909 unless the destination cluster was initialized with a different bootstrap
910 superuser name. This error is harmless and should be ignored. Use of
911 the <option>--clean</option> option is likely to produce additional
912 harmless error messages about non-existent objects, although you can
913 minimize those by adding <option>--if-exists</option>.
914 </para>
915
916 <para>
917 <application>pg_dumpall</application> requires all needed
918 tablespace directories to exist before the restore; otherwise,
919 database creation will fail for databases in non-default
920 locations.
921 </para>
922
923 <para>
924 It is generally recommended to use the <option>-X</option>
925 (<option>--no-psqlrc</option>) option when restoring a database from a
926 <application>pg_dumpall</application> script to ensure a clean restore
927 process and prevent potential conflicts with non-default
928 <application>psql</application> configurations. Additionally, because
929 the <application>pg_dumpall</application> script may
930 include <application>psql</application> meta-commands, it may be
931 incompatible with clients other than <application>psql</application>.
932 </para>
933 </refsect1>
934
935
936 <refsect1 id="app-pg-dumpall-ex">
937 <title>Examples</title>
938 <para>
939 To dump all databases:
940
941 <screen>
942 <prompt>$</prompt> <userinput>pg_dumpall &gt; db.out</userinput>
943 </screen>
944 </para>
945
946 <para>
947 To restore database(s) from this file, you can use:
948 <screen>
949 <prompt>$</prompt> <userinput>psql -X -f db.out -d postgres</userinput>
950 </screen>
951 It is not important which database you connect to here since the
952 script file created by <application>pg_dumpall</application> will
953 contain the appropriate commands to create and connect to the saved
954 databases. An exception is that if you specified <option>--clean</option>,
955 you must connect to the <literal>postgres</literal> database initially;
956 the script will attempt to drop other databases immediately, and that
957 will fail for the database you are connected to.
958 </para>
959 </refsect1>
960
961 <refsect1>
962 <title>See Also</title>
963
964 <para>
965 Check <xref linkend="app-pgdump"/> for details on possible
966 error conditions.
967 </para>
968 </refsect1>
969
970 </refentry>