Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n7Uwj-0006aK-E4 for pgsql-hackers@arkaria.postgresql.org; Wed, 12 Jan 2022 04:09:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1n7Uwi-0003Ry-9K for pgsql-hackers@arkaria.postgresql.org; Wed, 12 Jan 2022 04:09:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1n7Uwh-0003Ro-VH for pgsql-hackers@lists.postgresql.org; Wed, 12 Jan 2022 04:09:12 +0000 Received: from mail-il1-x12a.google.com ([2607:f8b0:4864:20::12a]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1n7Uwf-0002nU-R7 for pgsql-hackers@postgresql.org; Wed, 12 Jan 2022 04:09:11 +0000 Received: by mail-il1-x12a.google.com with SMTP id d14so1164098ila.1 for ; Tue, 11 Jan 2022 20:09:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=PJpl4aS6cmYxzBKTtzyfMcEdT2nCWhmVJA3obm7+cow=; b=7Mp0EdTWXLqYrGmWi6zRA9OcarVPgh3RsIcSz4UYIjUsxsQ+tl34qfnvAQOP34VRy5 9VzMIgV3YZ4yNf/ktJGrqzf7UxQmf2E1EvG2rmUR+g6L1Nm18Leo0GyIrLwLvrR1wmND aJSx63je6mY8gzrkBgoXr0ex1mFLSkIG6cUvM2Deeyqe3bW+Sj5QN3lJnqzXNH92sfVq H6gJGzlVCa3GsBeWYi8Ry188dtgVth0zneQpfpofkhSwZiQ8/QsiKMoJ8ayv9fCpJQ/p tZGJ1IfOctphHjDUSD1sQO3gsVZ03E/oQj/bL14sr9pIBZk+etKPePVSJrCYWQndqqtr nlUQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=PJpl4aS6cmYxzBKTtzyfMcEdT2nCWhmVJA3obm7+cow=; b=RyYc61/IPqTwynrPSIZBLRWyVD2wjwJrRCdq9NJYJZveIj1HM9tRl1sFO4z3EPGQHl /pLFHgFZaXMI4X2ekswatGYI1E2xlQ/U0rpt0PGAHSBoWY+JP6nmNulh4dwe+xAvRxHU 4K97giJgl5+Ucxw1aV6YcaKdX6b4aGVVnyZfbVIE5aPtMAP6ks/BpANXf0Pk9L/fJxlR Dc4WIlTwmJbpakNMlbvPDiY5v3Zrtl9CMeejEaLp1EWkRAI1FUXXso6N56qz+30XJr3v Dr/7ary7YxH0/JJgNoS4dSLqfIeKRT1PQR4589lNmHew/bqX3xmiVF5WCmTh288Fcb6G aYGQ== X-Gm-Message-State: AOAM532jwBLdU288VCajvjg1imu3tTU5/qAoeoPhgRkTbchhb60zgz3o ksXuWtQGM4vmcmz+jtw9ENormQ== X-Google-Smtp-Source: ABdhPJyutqwzm/extUV36funoAwXr7snGfxDvFnr5APRbhCo1xiuC5OUnH515PJJv8y03AwEAJYzDQ== X-Received: by 2002:a05:6e02:1b88:: with SMTP id h8mr4220302ili.97.1641960548336; Tue, 11 Jan 2022 20:09:08 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id g5sm6690308ioo.21.2022.01.11.20.09.07 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 11 Jan 2022 20:09:07 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 399D9804867; Tue, 11 Jan 2022 22:09:07 -0600 (CST) Date: Tue, 11 Jan 2022 22:09:07 -0600 From: Justin Pryzby To: Michael Paquier Cc: pgsql-hackers@postgresql.org, Andres Freund Subject: Re: pg_dump/restore --no-tableam Message-ID: <20220112040906.GE14051@telsasoft.com> References: <20200128133317.GZ13621@telsasoft.com> <20211207153930.GR17618@telsasoft.com> <20220103214424.GO24477@telsasoft.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="dRgc669pgRVB2OqT" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --dRgc669pgRVB2OqT Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Tue, Jan 11, 2022 at 04:50:23PM +0900, Michael Paquier wrote: > On Mon, Jan 03, 2022 at 03:44:24PM -0600, Justin Pryzby wrote: > > + > > + > > + > > + > > + > > + Do not output commands to select table access methods. > > + With this option, all objects will be created with whichever > > + table access method is the default during restore. > > + > > Hmm. --no-table-am may not be the best choice. Should this be called > --no-table-access-method instead? I suppose you're right - I had previously renamed it from no-tableam. > > - no_toast_compression => { > > - dump_cmd => [ > > - 'pg_dump', '--no-sync', > > - "--file=$tempdir/no_toast_compression.sql", > > - '--no-toast-compression', 'postgres', > > - ], > > - }, > > Why is this command moved down? Because it looks like this is intended to be mostly alphabetical, but that wasn't preserved by 63db0ac3f. It's most apparent in "my %full_runs". The same could be said of no-privs, defaults_custom_format, pg_dumpall_globals, section_data, but they've been that way forever. -- Justin --dRgc669pgRVB2OqT Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0001-Add-pg_dump-restore-no-table-access-method.patch" From 38da310269c1959994f2d7ab1cec9614209b60bb Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Sun, 7 Mar 2021 19:35:37 -0600 Subject: [PATCH] Add pg_dump/restore --no-table-access-method.. This was for some reason omitted from 3b925e905. --- doc/src/sgml/ref/pg_dump.sgml | 17 ++++++++++++++ doc/src/sgml/ref/pg_restore.sgml | 11 +++++++++ src/bin/pg_dump/pg_backup.h | 2 ++ src/bin/pg_dump/pg_backup_archiver.c | 12 ++++++++++ src/bin/pg_dump/pg_dump.c | 3 +++ src/bin/pg_dump/pg_restore.c | 4 ++++ src/bin/pg_dump/t/002_pg_dump.pl | 34 ++++++++++++++++++---------- 7 files changed, 71 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index 0e1cfe0f8d6..2f0042fd968 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -931,6 +931,23 @@ PostgreSQL documentation + + + + + Do not output commands to select table access methods. + With this option, all objects will be created with whichever + table access method is the default during restore. + + + + This option is ignored when emitting an archive (non-text) output + file. For the archive formats, you can specify the option when you + call pg_restore. + + + + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index 93ea937ac8e..526986eadb1 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -649,6 +649,17 @@ PostgreSQL documentation + + + + + Do not output commands to select table access methods. + With this option, all objects will be created with whichever + access method is the default during restore. + + + + diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index 753252e05e0..47741ad6406 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -93,6 +93,7 @@ typedef struct _restoreOptions { int createDB; /* Issue commands to create the database */ int noOwner; /* Don't try to match original object owner */ + int noTableAm; /* Don't issue tableAM-related commands */ int noTablespace; /* Don't issue tablespace-related commands */ int disable_triggers; /* disable triggers during data-only * restore */ @@ -179,6 +180,7 @@ typedef struct _dumpOptions int no_unlogged_table_data; int serializable_deferrable; int disable_triggers; + int outputNoTableAm; int outputNoTablespaces; int use_setsessauth; int enable_row_security; diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 8903a694ae9..49bf0907cd2 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -194,6 +194,7 @@ dumpOptionsFromRestoreOptions(RestoreOptions *ropt) dopt->outputSuperuser = ropt->superuser; dopt->outputCreateDB = ropt->createDB; dopt->outputNoOwner = ropt->noOwner; + dopt->outputNoTableAm = ropt->noTableAm; dopt->outputNoTablespaces = ropt->noTablespace; dopt->disable_triggers = ropt->disable_triggers; dopt->use_setsessauth = ropt->use_setsessauth; @@ -3171,6 +3172,11 @@ _reconnectToDB(ArchiveHandle *AH, const char *dbname) if (AH->currSchema) free(AH->currSchema); AH->currSchema = NULL; + + if (AH->currTableAm) + free(AH->currTableAm); + AH->currTableAm = NULL; + if (AH->currTablespace) free(AH->currTablespace); AH->currTablespace = NULL; @@ -3340,10 +3346,15 @@ _selectTablespace(ArchiveHandle *AH, const char *tablespace) static void _selectTableAccessMethod(ArchiveHandle *AH, const char *tableam) { + RestoreOptions *ropt = AH->public.ropt; PQExpBuffer cmd; const char *want, *have; + /* do nothing in --no-table-access-method mode */ + if (ropt->noTableAm) + return; + have = AH->currTableAm; want = tableam; @@ -4770,6 +4781,7 @@ CloneArchive(ArchiveHandle *AH) clone->connCancel = NULL; clone->currUser = NULL; clone->currSchema = NULL; + clone->currTableAm = NULL; clone->currTablespace = NULL; /* savedPassword must be local in case we change it while connecting */ diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 92ab95724d2..c5f231118bd 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -389,6 +389,7 @@ main(int argc, char **argv) {"if-exists", no_argument, &dopt.if_exists, 1}, {"inserts", no_argument, NULL, 9}, {"lock-wait-timeout", required_argument, NULL, 2}, + {"no-table-access-method", no_argument, &dopt.outputNoTableAm, 1}, {"no-tablespaces", no_argument, &dopt.outputNoTablespaces, 1}, {"quote-all-identifiers", no_argument, "e_all_identifiers, 1}, {"load-via-partition-root", no_argument, &dopt.load_via_partition_root, 1}, @@ -933,6 +934,7 @@ main(int argc, char **argv) ropt->superuser = dopt.outputSuperuser; ropt->createDB = dopt.outputCreateDB; ropt->noOwner = dopt.outputNoOwner; + ropt->noTableAm = dopt.outputNoTableAm; ropt->noTablespace = dopt.outputNoTablespaces; ropt->disable_triggers = dopt.disable_triggers; ropt->use_setsessauth = dopt.use_setsessauth; @@ -1038,6 +1040,7 @@ help(const char *progname) printf(_(" --no-publications do not dump publications\n")); printf(_(" --no-security-labels do not dump security label assignments\n")); printf(_(" --no-subscriptions do not dump subscriptions\n")); + printf(_(" --no-table-access-method do not dump table access methods\n")); printf(_(" --no-tablespaces do not dump tablespace assignments\n")); printf(_(" --no-toast-compression do not dump TOAST compression methods\n")); printf(_(" --no-unlogged-table-data do not dump unlogged table data\n")); diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index 64aaa80eeee..55bf1b69755 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -66,6 +66,7 @@ main(int argc, char **argv) static int enable_row_security = 0; static int if_exists = 0; static int no_data_for_failed_tables = 0; + static int outputNoTableAm = 0; static int outputNoTablespaces = 0; static int use_setsessauth = 0; static int no_comments = 0; @@ -112,6 +113,7 @@ main(int argc, char **argv) {"enable-row-security", no_argument, &enable_row_security, 1}, {"if-exists", no_argument, &if_exists, 1}, {"no-data-for-failed-tables", no_argument, &no_data_for_failed_tables, 1}, + {"no-table-access-method", no_argument, &outputNoTableAm, 1}, {"no-tablespaces", no_argument, &outputNoTablespaces, 1}, {"role", required_argument, NULL, 2}, {"section", required_argument, NULL, 3}, @@ -358,6 +360,7 @@ main(int argc, char **argv) opts->disable_triggers = disable_triggers; opts->enable_row_security = enable_row_security; opts->noDataForFailedTables = no_data_for_failed_tables; + opts->noTableAm = outputNoTableAm; opts->noTablespace = outputNoTablespaces; opts->use_setsessauth = use_setsessauth; opts->no_comments = no_comments; @@ -487,6 +490,7 @@ usage(const char *progname) printf(_(" --no-publications do not restore publications\n")); printf(_(" --no-security-labels do not restore security labels\n")); 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(_(" --section=SECTION restore named section (pre-data, data, or post-data)\n")); printf(_(" --strict-names require table and/or schema include patterns to\n" diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 4200bcb0d71..87eaeeb8c31 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -247,13 +247,6 @@ my %pgdump_runs = ( '--exclude-database', '*dump_test*', '--no-sync', ], }, - no_toast_compression => { - dump_cmd => [ - 'pg_dump', '--no-sync', - "--file=$tempdir/no_toast_compression.sql", - '--no-toast-compression', 'postgres', - ], - }, no_blobs => { dump_cmd => [ 'pg_dump', '--no-sync', @@ -275,6 +268,20 @@ my %pgdump_runs = ( 'postgres', ], }, + no_table_am => { + dump_cmd => [ + 'pg_dump', '--no-sync', + "--file=$tempdir/no_table_am.sql", + '--no-table-access-method', 'postgres', + ], + }, + no_toast_compression => { + dump_cmd => [ + 'pg_dump', '--no-sync', + "--file=$tempdir/no_toast_compression.sql", + '--no-toast-compression', 'postgres', + ], + }, only_dump_test_schema => { dump_cmd => [ 'pg_dump', '--no-sync', @@ -422,10 +429,11 @@ my %full_runs = ( exclude_dump_test_schema => 1, exclude_test_table => 1, exclude_test_table_data => 1, - no_toast_compression => 1, no_blobs => 1, no_owner => 1, no_privs => 1, + no_table_am => 1, + no_toast_compression => 1, pg_dumpall_dbprivs => 1, pg_dumpall_exclude => 1, schema_only => 1,); @@ -2968,10 +2976,11 @@ my %tests = ( defaults => 1, exclude_test_table => 1, exclude_test_table_data => 1, - no_toast_compression => 1, no_blobs => 1, no_privs => 1, no_owner => 1, + no_table_am => 1, + no_toast_compression => 1, only_dump_test_schema => 1, pg_dumpall_dbprivs => 1, pg_dumpall_exclude => 1, @@ -3041,10 +3050,11 @@ my %tests = ( exclude_dump_test_schema => 1, exclude_test_table => 1, exclude_test_table_data => 1, - no_toast_compression => 1, no_blobs => 1, no_privs => 1, no_owner => 1, + no_table_am => 1, + no_toast_compression => 1, pg_dumpall_dbprivs => 1, pg_dumpall_exclude => 1, role => 1, @@ -3648,7 +3658,7 @@ my %tests = ( like => { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, - unlike => { exclude_dump_test_schema => 1 }, + unlike => { exclude_dump_test_schema => 1, no_table_am => 1 }, }, 'CREATE MATERIALIZED VIEW regress_pg_dump_matview_am' => { @@ -3668,7 +3678,7 @@ my %tests = ( like => { %full_runs, %dump_test_schema_runs, section_pre_data => 1, }, - unlike => { exclude_dump_test_schema => 1 }, + unlike => { exclude_dump_test_schema => 1, no_table_am => 1 }, }); ######################################### -- 2.17.1 --dRgc669pgRVB2OqT--