<refnamediv>

<refname>pg_dumpall</refname>

<refpurpose>

export a <productname>PostgreSQL</productname> database cluster as an SQL script or to other formats

</refpurpose>

<refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>

</refnamediv>

<refsynopsisdiv>

<para>

<application>pg_dumpall</application> is a utility for writing out

(<quote>dumping</quote>) all <productname>PostgreSQL</productname> databases

of a cluster intoan SQLscript file or an archive. Theoutput contains

of a cluster intoonescript file. Thescript file contains

<acronym>SQL</acronym> commands that can be used as input to <xref

linkend="app-psql"/> to restore the databases. It does this by

calling <xref linkend="app-pgdump"/> for each database in the cluster.

</para>

<para>

Plain textSQLscripts will be written to the standard output. Use the

TheSQLscript will be written to the standard output. Use the

<option>-f</option>/<option>--file</option> option or shell operators to

redirect it into a file.

</para>

<para>

Archives in other formats will be placed in a directory named using the

<option>-f</option>/<option>--file</option>, which is required in this case.

</para>

<para>

<application>pg_dumpall</application> needs to connect several

times to the <productname>PostgreSQL</productname> server (once per

<para>

Send output to the specified file. If this is omitted, the

standard output is used.

Note: This option can only be omitted when <option>--format</option> is plain

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><option>-F <replaceable class="parameter">format</replaceable></option></term>

<term><option>--format=<replaceable class="parameter">format</replaceable></option></term>

<listitem>

<para>

Specify the format of dump files. In plain format, all the dump data is

sent in a single text stream. This is the default.

In all other modes, <application>pg_dumpall</application> first creates two files:

<filename>global.dat</filename> and <filename>map.dat</filename>, in the directory

specified by <option>--file</option>.

The first file contains global data, such as roles and tablespaces. The second

contains a mapping between database oids and names. These files are used by

<application>pg_restore</application>. Data for individual databases is placed in

<filename>databases</filename> subdirectory, named using the database's <type>oid</type>.

<variablelist>

<varlistentry>

<term><literal>d</literal></term>

<term><literal>directory</literal></term>

<listitem>

<para>

Output directory-format archives for each database,

suitable for input into pg_restore. The directory

will have database <type>oid</type> as its name.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>p</literal></term>

<term><literal>plain</literal></term>

<listitem>

<para>

Output a plain-text SQL script file (the default).

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>c</literal></term>

<term><literal>custom</literal></term>

<listitem>

<para>

Output a custom-format archive for each database,

suitable for input into pg_restore. The archive

will be named <filename>dboid.dmp</filename> where <type>dboid</type> is the

<type>oid</type> of the database.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><literal>t</literal></term>

<term><literal>tar</literal></term>

<listitem>

<para>

Output a tar-format archive for each database,

suitable for input into pg_restore. The archive

will be named <filename>dboid.tar</filename> where <type>dboid</type> is the

<type>oid</type> of the database.

</para>

</listitem>

</varlistentry>

</variablelist>

Note: see <xref linkend="app-pgdump"/> for details

of how the various non plain text archives work.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><option>-g</option></term>

<term><option>--globals-only</option></term>

<refname>pg_restore</refname>

<refpurpose>

restore <productname>PostgreSQL</productname> databases from archives

created by <application>pg_dump</application> or

<application>pg_dumpall</application>

restore a <productname>PostgreSQL</productname> database from an

archive file created by <application>pg_dump</application>

</refpurpose>

</refnamediv>

<para>

<application>pg_restore</application> is a utility for restoring a

<productname>PostgreSQL</productname> database or cluster from an archive

created by <xref linkend="app-pgdump"/> or

<xref linkend="app-pg-dumpall"/> in one of the non-plain-text

<productname>PostgreSQL</productname> database from an archive

created by <xref linkend="app-pgdump"/> in one of the non-plain-text

formats. It will issue the commands necessary to reconstruct the

databaseor clusterto the state it was in at the time it was saved. The

archives also allow <application>pg_restore</application> to

database to the state it was in at the time it was saved. The

archive files also allow <application>pg_restore</application> to

be selective about what is restored, or even to reorder the items

prior to being restored. The archiveformats are designed to be

prior to being restored. The archivefiles are designed to be

portable across architectures.

</para>

<para>

<application>pg_restore</application> can operate in two modes.

If a database name is specified, <application>pg_restore</application>

connects to that database and restores archive contents directly into

the database.

When restoring from a dump made by <application>pg_dumpall</application>,

each database will be created and then the restoration will be run in that

database.

Otherwise, when a database name is not specified, a script containing the SQL

commands necessary to rebuild the database or cluster is created and written

the database. Otherwise, a script containing the SQL

commands necessary to rebuild the database is created and written

to a file or standard output. This script output is equivalent to

the plain text output format of <application>pg_dump</application> or

<application>pg_dumpall</application>.

the plain text output format of <application>pg_dump</application>.

Some of the options controlling the output are therefore analogous to

<application>pg_dump</application> options.

</para>

commands that mention this database.

Access privileges for the database itself are also restored,

unless <option>--no-acl</option> is specified.

<option>--create</option> is required when restoring multiple databases

from an archive created by <application>pg_dumpall</application>.

</para>

<para>

</listitem>

</varlistentry>

<varlistentry>

<term><option>-g</option></term>

<term><option>--globals-only</option></term>

<listitem>

<para>

Restore only global objects (roles and tablespaces), no databases.

</para>

<para>

This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><option>-I <replaceable class="parameter">index</replaceable></option></term>

<term><option>--index=<replaceable class="parameter">index</replaceable></option></term>

</listitem>

</varlistentry>

<varlistentry>

<term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term>

<listitem>

<para>

Do not restore databases whose name matches

<replaceable class="parameter">pattern</replaceable>.

Multiple patterns can be excluded by writing multiple

<option>--exclude-database</option> switches. The

<replaceable class="parameter">pattern</replaceable> parameter is

interpreted as a pattern according to the same rules used by

<application>psql</application>'s <literal>\d</literal>

commands (see <xref linkend="app-psql-patterns"/>),

so multiple databases can also be excluded by writing wildcard

characters in the pattern. When using wildcards, be careful to

quote the pattern if needed to prevent shell wildcard expansion.

</para>

<para>

This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>.

</para>

</listitem>

</varlistentry>

<varlistentry>

<term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>

<listitem>

't/003_pg_dump_with_server.pl',

't/004_pg_dump_parallel.pl',

't/005_pg_dump_filterfile.pl',

't/006_pg_dumpall.pl',

't/010_dump_connstr.pl',

],

},

on_exit_nicely(archive_close_connection,&shutdown_info);

}

replace_on_exit_close_archive(Archive*AHX)

{

shutdown_info.AHX=AHX;

}

externvoidProcessArchiveRestoreOptions(Archive*AHX);

externvoidRestoreArchive(Archive*AHX,boolappend_data);

externvoidRestoreArchive(Archive*AHX);

externArchive*OpenArchive(constchar*FileSpec,constArchiveFormatfmt);

staticvoiddump_lo_buf(ArchiveHandle*AH);

staticvoiddumpTimestamp(ArchiveHandle*AH,constchar*msg,time_ttim);

staticvoidSetOutput(ArchiveHandle*AH,constchar*filename,

constpg_compress_specificationcompression_spec,boolappend_data);

constpg_compress_specificationcompression_spec);

staticCompressFileHandle*SaveOutput(ArchiveHandle*AH);

staticvoidRestoreOutput(ArchiveHandle*AH,CompressFileHandle*savedOutput);

StrictNamesCheck(ropt);

}

RestoreArchive(Archive*AHX,boolappend_data)

RestoreArchive(Archive*AHX)

{

ArchiveHandle*AH= (ArchiveHandle*)AHX;

RestoreOptions*ropt=AH->public.ropt;

sav=SaveOutput(AH);

if (ropt->filename||ropt->compression_spec.algorithm!=PG_COMPRESSION_NONE)

SetOutput(AH,ropt->filename,ropt->compression_spec,append_data);

SetOutput(AH,ropt->filename,ropt->compression_spec);

ahprintf(AH,"--\n-- PostgreSQL database dump\n--\n\n");

sav=SaveOutput(AH);

if (ropt->filename)

SetOutput(AH,ropt->filename,out_compression_spec, false);

SetOutput(AH,ropt->filename,out_compression_spec);

if (strftime(stamp_str,sizeof(stamp_str),PGDUMP_STRFTIME_FMT,

localtime(&AH->createDate))==0)

SetOutput(ArchiveHandle*AH,constchar*filename,

constpg_compress_specificationcompression_spec,

boolappend_data)

constpg_compress_specificationcompression_spec)

{

CompressFileHandle*CFH;

constchar*mode;

fn=fileno(stdout);

if (append_data||AH->mode==archModeAppend)

if (AH->mode==archModeAppend)

mode=PG_BINARY_A;

mode=PG_BINARY_W;

externintparallel_restore(ArchiveHandle*AH,TocEntry*te);

externvoidon_exit_close_archive(Archive*AHX);

externvoidreplace_on_exit_close_archive(Archive*AHX);

externvoidwarn_or_exit_horribly(ArchiveHandle*AH,constchar*fmt,...)pg_attribute_printf(2,3);

savVerbose=AH->public.verbose;

AH->public.verbose=0;

RestoreArchive((Archive*)AH, false);

RestoreArchive((Archive*)AH);

SetArchiveOptions((Archive*)AH,savDopt,savRopt);

* right now.

*/

if (plainText)

RestoreArchive(fout, false);

RestoreArchive(fout);

CloseArchive(fout);