Skip to content

Commit 1495eff

Browse files
adunstanadunstan
committed
Non text modes for pg_dumpall, correspondingly change pg_restore
pg_dumpall acquires a new -F/--format option, with the same meanings as pg_dump. The default is p, meaning plain text. For any other value, a directory is created containing two files, globals.data and map.dat. The first contains SQL for restoring the global data, and the second contains a map from oids to database names. It will also contain a subdirectory called databases, inside which it will create archives in the specified format, named using the database oids. In these casess the -f argument is required. If pg_restore encounters a directory containing globals.dat, and no toc.dat, it restores the global settings and then restores each database. pg_restore acquires two new options: -g/--globals-only which suppresses restoration of any databases, and --exclude-database which inhibits restoration of particualr database(s) in the same way the same option works in pg_dumpall. Author: Mahendra Singh Thalor <mahi6run@gmail.com> Co-authored-by: Andrew Dunstan <andrew@dunslane.net> Reviewed-by: jian he <jian.universality@gmail.com> Reviewed-by: Srinath Reddy <srinath2133@gmail.com> Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org> Discussion: https://postgr.es/m/cb103623-8ee6-4ba5-a2c9-f32e3a4933fa@dunslane.net
1 parent 2b69afb commit 1495eff

11 files changed

+1201
-85
lines changed

‎doc/src/sgml/ref/pg_dumpall.sgml

Lines changed: 83 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ PostgreSQL documentation
1616

1717
<refnamediv>
1818
<refname>pg_dumpall</refname>
19-
<refpurpose>extract a <productname>PostgreSQL</productname> database cluster into a script file</refpurpose>
19+
<refpurpose>extract a <productname>PostgreSQL</productname> database cluster using a specified dump format</refpurpose>
2020
</refnamediv>
2121

2222
<refsynopsisdiv>
@@ -33,7 +33,7 @@ PostgreSQL documentation
3333
<para>
3434
<application>pg_dumpall</application> is a utility for writing out
3535
(<quote>dumping</quote>) all <productname>PostgreSQL</productname> databases
36-
of a cluster into one script file. The script file contains
36+
of a cluster into an archive. The archive contains
3737
<acronym>SQL</acronym> commands that can be used as input to <xref
3838
linkend="app-psql"/> to restore the databases. It does this by
3939
calling <xref linkend="app-pgdump"/> for each database in the cluster.
@@ -52,11 +52,16 @@ PostgreSQL documentation
5252
</para>
5353

5454
<para>
55-
The SQL script will be written to the standard output. Use the
55+
Plain text SQL scripts will be written to the standard output. Use the
5656
<option>-f</option>/<option>--file</option> option or shell operators to
5757
redirect it into a file.
5858
</para>
5959

60+
<para>
61+
Archives in other formats will be placed in a directory named using the
62+
<option>-f</option>/<option>--file</option>, which is required in this case.
63+
</para>
64+
6065
<para>
6166
<application>pg_dumpall</application> needs to connect several
6267
times to the <productname>PostgreSQL</productname> server (once per
@@ -121,10 +126,85 @@ PostgreSQL documentation
121126
<para>
122127
Send output to the specified file. If this is omitted, the
123128
standard output is used.
129+
Note: This option can only be omitted when <option>--format</option> is plain
124130
</para>
125131
</listitem>
126132
</varlistentry>
127133

134+
<varlistentry>
135+
<term><option>-F <replaceable class="parameter">format</replaceable></option></term>
136+
<term><option>--format=<replaceable class="parameter">format</replaceable></option></term>
137+
<listitem>
138+
<para>
139+
Specify the format of dump files. In plain format, all the dump data is
140+
sent in a single text stream. This is the default.
141+
142+
In all other modes, <application>pg_dumpall</application> first creates two files:
143+
<filename>global.dat</filename> and <filename>map.dat</filename>, in the directory
144+
specified by <option>--file</option>.
145+
The first file contains global data, such as roles and tablespaces. The second
146+
contains a mapping between database oids and names. These files are used by
147+
<application>pg_restore</application>. Data for individual databases is placed in
148+
<filename>databases</filename> subdirectory, named using the database's <type>oid</type>.
149+
150+
<variablelist>
151+
<varlistentry>
152+
<term><literal>d</literal></term>
153+
<term><literal>directory</literal></term>
154+
<listitem>
155+
<para>
156+
Output directory-format archives for each database,
157+
suitable for input into pg_restore. The directory
158+
will have database <type>oid</type> as its name.
159+
</para>
160+
</listitem>
161+
</varlistentry>
162+
163+
<varlistentry>
164+
<term><literal>p</literal></term>
165+
<term><literal>plain</literal></term>
166+
<listitem>
167+
<para>
168+
Output a plain-text SQL script file (the default).
169+
</para>
170+
</listitem>
171+
</varlistentry>
172+
173+
<varlistentry>
174+
<term><literal>c</literal></term>
175+
<term><literal>custom</literal></term>
176+
<listitem>
177+
<para>
178+
Output a custom-format archive for each database,
179+
suitable for input into pg_restore. The archive
180+
will be named <filename>dboid.dmp</filename> where <type>dboid</type> is the
181+
<type>oid</type> of the database.
182+
</para>
183+
</listitem>
184+
</varlistentry>
185+
186+
<varlistentry>
187+
<term><literal>t</literal></term>
188+
<term><literal>tar</literal></term>
189+
<listitem>
190+
<para>
191+
Output a tar-format archive for each database,
192+
suitable for input into pg_restore. The archive
193+
will be named <filename>dboid.tar</filename> where <type>dboid</type> is the
194+
<type>oid</type> of the database.
195+
</para>
196+
</listitem>
197+
</varlistentry>
198+
199+
</variablelist>
200+
201+
Note: see <xref linkend="app-pgdump"/> for details
202+
of how the various non plain text archives work.
203+
204+
</para>
205+
</listitem>
206+
</varlistentry>
207+
128208
<varlistentry>
129209
<term><option>--filter=<replaceable class="parameter">filename</replaceable></option></term>
130210
<listitem>

‎doc/src/sgml/ref/pg_restore.sgml

Lines changed: 56 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -18,8 +18,9 @@ PostgreSQL documentation
1818
<refname>pg_restore</refname>
1919

2020
<refpurpose>
21-
restore a <productname>PostgreSQL</productname> database from an
22-
archive file created by <application>pg_dump</application>
21+
restore a <productname>PostgreSQL</productname> database or cluster
22+
from an archive created by <application>pg_dump</application> or
23+
<application>pg_dumpall</application>
2324
</refpurpose>
2425
</refnamediv>
2526

@@ -38,24 +39,32 @@ PostgreSQL documentation
3839

3940
<para>
4041
<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
42+
<productname>PostgreSQL</productname> database or cluster from an archive
43+
created by <xref linkend="app-pgdump"/> or
44+
<xref linkend="app-pg-dumpall"/> in one of the non-plain-text
4345
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+
database or cluster to the state it was in at the time it was saved. The
47+
archives also allow <application>pg_restore</application> to
4648
be selective about what is restored, or even to reorder the items
47-
prior to being restored. The archive files are designed to be
49+
prior to being restored. The archive formats are designed to be
4850
portable across architectures.
4951
</para>
5052

5153
<para>
5254
<application>pg_restore</application> can operate in two modes.
5355
If a database name is specified, <application>pg_restore</application>
5456
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+
the database.
58+
When restoring from a dump made by<application>pg_dumpall</application>,
59+
each database will be created and then the restoration will be run in that
60+
database.
61+
62+
Otherwise, when a database name is not specified, a script containing the SQL
63+
commands necessary to rebuild the database or cluster is created and written
5764
to a file or standard output. This script output is equivalent to
58-
the plain text output format of <application>pg_dump</application>.
65+
the plain text output format of <application>pg_dump</application> or
66+
<application>pg_dumpall</application>.
67+
5968
Some of the options controlling the output are therefore analogous to
6069
<application>pg_dump</application> options.
6170
</para>
@@ -140,6 +149,8 @@ PostgreSQL documentation
140149
commands that mention this database.
141150
Access privileges for the database itself are also restored,
142151
unless <option>--no-acl</option> is specified.
152+
<option>--create</option> is required when restoring multiple databases
153+
from an archive created by <application>pg_dumpall</application>.
143154
</para>
144155

145156
<para>
@@ -166,6 +177,28 @@ PostgreSQL documentation
166177
</listitem>
167178
</varlistentry>
168179

180+
<varlistentry>
181+
<term><option>--exclude-database=<replaceable class="parameter">pattern</replaceable></option></term>
182+
<listitem>
183+
<para>
184+
Do not restore databases whose name matches
185+
<replaceable class="parameter">pattern</replaceable>.
186+
Multiple patterns can be excluded by writing multiple
187+
<option>--exclude-database</option> switches. The
188+
<replaceable class="parameter">pattern</replaceable> parameter is
189+
interpreted as a pattern according to the same rules used by
190+
<application>psql</application>'s <literal>\d</literal>
191+
commands (see <xref linkend="app-psql-patterns"/>),
192+
so multiple databases can also be excluded by writing wildcard
193+
characters in the pattern. When using wildcards, be careful to
194+
quote the pattern if needed to prevent shell wildcard expansion.
195+
</para>
196+
<para>
197+
This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>.
198+
</para>
199+
</listitem>
200+
</varlistentry>
201+
169202
<varlistentry>
170203
<term><option>-e</option></term>
171204
<term><option>--exit-on-error</option></term>
@@ -315,6 +348,19 @@ PostgreSQL documentation
315348
</listitem>
316349
</varlistentry>
317350

351+
<varlistentry>
352+
<term><option>-g</option></term>
353+
<term><option>--globals-only</option></term>
354+
<listitem>
355+
<para>
356+
Restore only global objects (roles and tablespaces), no databases.
357+
</para>
358+
<para>
359+
This option is only relevant when restoring from an archive made using <application>pg_dumpall</application>.
360+
</para>
361+
</listitem>
362+
</varlistentry>
363+
318364
<varlistentry>
319365
<term><option>-I <replaceable class="parameter">index</replaceable></option></term>
320366
<term><option>--index=<replaceable class="parameter">index</replaceable></option></term>

‎src/bin/pg_dump/parallel.c

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -333,6 +333,16 @@ on_exit_close_archive(Archive *AHX)
333333
on_exit_nicely(archive_close_connection, &shutdown_info);
334334
}
335335

336+
/*
337+
* When pg_restore restores multiple databases, then update already added entry
338+
* into array for cleanup.
339+
*/
340+
void
341+
replace_on_exit_close_archive(Archive *AHX)
342+
{
343+
shutdown_info.AHX = AHX;
344+
}
345+
336346
/*
337347
* on_exit_nicely handler for shutting down database connections and
338348
* worker processes cleanly.

‎src/bin/pg_dump/pg_backup.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -315,7 +315,7 @@ extern void SetArchiveOptions(Archive *AH, DumpOptions *dopt, RestoreOptions *ro
315315

316316
extern void ProcessArchiveRestoreOptions(Archive *AHX);
317317

318-
extern void RestoreArchive(Archive *AHX);
318+
extern void RestoreArchive(Archive *AHX, bool append_data);
319319

320320
/* Open an existing archive */
321321
extern Archive *OpenArchive(const char *FileSpec, const ArchiveFormat fmt);

‎src/bin/pg_dump/pg_backup_archiver.c

Lines changed: 13 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -85,7 +85,7 @@ static int RestoringToDB(ArchiveHandle *AH);
8585
static void dump_lo_buf(ArchiveHandle *AH);
8686
static void dumpTimestamp(ArchiveHandle *AH, const char *msg, time_t tim);
8787
static void SetOutput(ArchiveHandle *AH, const char *filename,
88-
const pg_compress_specification compression_spec);
88+
const pg_compress_specification compression_spec, bool append_data);
8989
static CompressFileHandle *SaveOutput(ArchiveHandle *AH);
9090
static void RestoreOutput(ArchiveHandle *AH, CompressFileHandle *savedOutput);
9191

@@ -337,9 +337,14 @@ ProcessArchiveRestoreOptions(Archive *AHX)
337337
StrictNamesCheck(ropt);
338338
}
339339

340-
/* Public */
340+
/*
341+
* RestoreArchive
342+
*
343+
* If append_data is set, then append data into file as we are restoring dump
344+
* of multiple databases which was taken by pg_dumpall.
345+
*/
341346
void
342-
RestoreArchive(Archive *AHX)
347+
RestoreArchive(Archive *AHX, bool append_data)
343348
{
344349
ArchiveHandle *AH = (ArchiveHandle *) AHX;
345350
RestoreOptions *ropt = AH->public.ropt;
@@ -456,7 +461,7 @@ RestoreArchive(Archive *AHX)
456461
*/
457462
sav = SaveOutput(AH);
458463
if (ropt->filename || ropt->compression_spec.algorithm != PG_COMPRESSION_NONE)
459-
SetOutput(AH, ropt->filename, ropt->compression_spec);
464+
SetOutput(AH, ropt->filename, ropt->compression_spec, append_data);
460465

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

@@ -1295,7 +1300,7 @@ PrintTOCSummary(Archive *AHX)
12951300

12961301
sav = SaveOutput(AH);
12971302
if (ropt->filename)
1298-
SetOutput(AH, ropt->filename, out_compression_spec);
1303+
SetOutput(AH, ropt->filename, out_compression_spec, false);
12991304

13001305
if (strftime(stamp_str, sizeof(stamp_str), PGDUMP_STRFTIME_FMT,
13011306
localtime(&AH->createDate)) == 0)
@@ -1674,7 +1679,8 @@ archprintf(Archive *AH, const char *fmt,...)
16741679

16751680
static void
16761681
SetOutput(ArchiveHandle *AH, const char *filename,
1677-
const pg_compress_specification compression_spec)
1682+
const pg_compress_specification compression_spec,
1683+
bool append_data)
16781684
{
16791685
CompressFileHandle *CFH;
16801686
const char *mode;
@@ -1694,7 +1700,7 @@ SetOutput(ArchiveHandle *AH, const char *filename,
16941700
else
16951701
fn = fileno(stdout);
16961702

1697-
if (AH->mode == archModeAppend)
1703+
if (append_data || AH->mode == archModeAppend)
16981704
mode = PG_BINARY_A;
16991705
else
17001706
mode = PG_BINARY_W;

‎src/bin/pg_dump/pg_backup_archiver.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -390,6 +390,7 @@ struct _tocEntry
390390

391391
extern int parallel_restore(ArchiveHandle *AH, TocEntry *te);
392392
extern void on_exit_close_archive(Archive *AHX);
393+
extern void replace_on_exit_close_archive(Archive *AHX);
393394

394395
extern void warn_or_exit_horribly(ArchiveHandle *AH, const char *fmt,...) pg_attribute_printf(2, 3);
395396

‎src/bin/pg_dump/pg_backup_tar.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -826,7 +826,7 @@ _CloseArchive(ArchiveHandle *AH)
826826
savVerbose = AH->public.verbose;
827827
AH->public.verbose = 0;
828828

829-
RestoreArchive((Archive *) AH);
829+
RestoreArchive((Archive *) AH, false);
830830

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

‎src/bin/pg_dump/pg_dump.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1222,7 +1222,7 @@ main(int argc, char **argv)
12221222
* right now.
12231223
*/
12241224
if (plainText)
1225-
RestoreArchive(fout);
1225+
RestoreArchive(fout, false);
12261226

12271227
CloseArchive(fout);
12281228

0 commit comments

Comments
 (0)