On Fri, 27 Feb 2026 at 11:57, Mahendra Singh Thalor <[email protected]> wrote: > > Hi, > Recently we committed a patch to dump a full cluster with pg_dumpall in > non-text archive format and then restore using pg_restore. > Here, I am proposing a patch to add the "pg_restore --no-globals" option so > that users can skip global objects. > > I made a patch for pg_restore --no-globals (only pg_restore option, not > needed with pg_dumpall as we will filter at restore end, or later we can add > for pg_dumpall also.) > > Brief: > pg_restore: add --no-globals option to skip globals > > Do not restore global objects (roles and tablespaces) and even don't > create new database if > --create or -C is not specified. > Note: database will be created only if -C is specified, otherwise > restore will be skipped for a particular database. If a db is already > created, then data of a particular db will be restored. > > Please review this patch and let me know your feedback. > > -- > Thanks and Regards > Mahendra Singh Thalor > EnterpriseDB: http://www.enterprisedb.com
Here, I am attaching an updated patch. -- Thanks and Regards Mahendra Singh Thalor EnterpriseDB: http://www.enterprisedb.com
From dd89d42a533f0762b1c211cc8acc0ae20c7cdf8b Mon Sep 17 00:00:00 2001 From: Mahendra Singh Thalor <[email protected]> Date: Fri, 27 Feb 2026 13:15:07 +0530 Subject: [PATCH] pg_restore: add --no-globals option to skip globals Do not restore global objects (roles and tablespaces) and even don't create new database if --create or -C is not specified. Note: database will be created only if -C is specified, otherwise restoration will be skipped for the particular database. If db is already created, the data for that database will be restored. --- doc/src/sgml/ref/pg_restore.sgml | 16 +++++++++++++++- src/bin/pg_dump/pg_restore.c | 27 +++++++++++++++++++++++---- src/bin/pg_dump/t/001_basic.pl | 6 ++++++ 3 files changed, 44 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 4a21a089840..1921c97b725 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -294,7 +294,21 @@ PostgreSQL documentation <option>--exit-on-error</option>, <option>--single-transaction</option>, <option>--clean</option>, or - <option>--transaction-size</option>. + <option>--transaction-size</option>, + <option>--no-globals</option>. + </para> + </listitem> + </varlistentry> + +<varlistentry> + <term><option>--no-globals</option></term> + <listitem> + <para> + Do not restore global objects (roles and tablespaces) and even don't create new database if + <option>--create</option> or <option>-C</option> is not specified. + </para> + <para> + This option is only relevant when restoring from a non-plain-text archive made using <application>pg_dumpall</application>. </para> </listitem> </varlistentry> diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index 14d886fc86e..8810f9215d4 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -108,6 +108,7 @@ main(int argc, char **argv) static int no_security_labels = 0; static int no_statistics = 0; static int no_subscriptions = 0; + static int no_globals = 0; static int strict_names = 0; static int statistics_only = 0; static int with_statistics = 0; @@ -171,6 +172,7 @@ main(int argc, char **argv) {"filter", required_argument, NULL, 4}, {"restrict-key", required_argument, NULL, 6}, {"exclude-database", required_argument, NULL, 7}, + {"no-globals", no_argument, &no_globals, 1}, {NULL, 0, NULL, 0} }; @@ -489,6 +491,10 @@ main(int argc, char **argv) pg_fatal("options %s and %s cannot be used together", "--statistics", "-g/--globals-only"); + if (no_globals && globals_only) + pg_fatal("options %s and %s cannot be used together", + "--no-globals", "-g/--globals-only"); + /* * -C is not compatible with -1, because we can't create a database inside * a transaction block. @@ -614,9 +620,10 @@ main(int argc, char **argv) /* * To restore from a pg_dumpall archive, -C (create database) option - * must be specified unless we are only restoring globals. + * must be specified unless we are only restoring globals or we are + * skiping globals. */ - if (!globals_only && opts->createDB != 1) + if (!no_globals && !globals_only && opts->createDB != 1) { pg_log_error("option %s must be specified when restoring an archive created by pg_dumpall", "-C/--create"); @@ -626,13 +633,17 @@ main(int argc, char **argv) } /* - * Always restore global objects, even if --exclude-database results + * Restore global objects, even if --exclude-database results * in zero databases to process. If 'globals-only' is set, exit * immediately. */ snprintf(global_path, MAXPGPATH, "%s/toc.glo", inputFileSpec); - n_errors = restore_global_objects(global_path, tmpopts); + if (!no_globals) + n_errors = restore_global_objects(global_path, tmpopts); + else + pg_log_info("toc.glo(globals) restoring skipped because option %s is specified", + "--no-globals"); if (globals_only) pg_log_info("database restoring skipped because option %s was specified", @@ -831,6 +842,7 @@ usage(const char *progname) printf(_(" --no-subscriptions do not restore subscriptions\n")); printf(_(" --no-table-access-method do not restore table access methods\n")); printf(_(" --no-tablespaces do not restore tablespace assignments\n")); + printf(_(" --no-globals do not restore global objects (roles and tablespaces)\n")); printf(_(" --restrict-key=RESTRICT_KEY use provided string as psql \\restrict key\n")); printf(_(" --section=SECTION restore named section (pre-data, data, or post-data)\n")); printf(_(" --statistics restore the statistics\n")); @@ -1349,6 +1361,13 @@ restore_all_databases(const char *inputFileSpec, } else { + if (!tmpopts->createDB) + { + pg_log_info("database %s restoring skipped because options %s and %s=false are specified with dump of pg_dumpall", + dbidname->str, "--no-globals", "-C/--create" ); + continue; + } + /* We'll have to create it */ tmpopts->createDB = 1; tmpopts->cparams.dbname = connected_db; diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl index a895bc314b0..e2d6b30e437 100644 --- a/src/bin/pg_dump/t/001_basic.pl +++ b/src/bin/pg_dump/t/001_basic.pl @@ -299,4 +299,10 @@ command_fails_like( qr/\Qpg_restore: error: option -g\/--globals-only can be used only when restoring an archive created by pg_dumpall\E/, 'When option --globals-only is used in pg_restore with the dump of pg_dump' ); + +command_fails_like( + [ 'pg_restore', '--globals-only', '--no-globals', '-d', 'xxx', 'dumpdir' ], + qr/\Qpg_restore: error: options --no-globals and -g\/--globals-only cannot be used together\E/, + 'pg_restore: error: options --no-globals and -g/--globals-only cannot be used together' +); done_testing(); -- 2.52.0
