From 602ab638f701cf6cc1357babe60462d317937392 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Sat, 16 Mar 2024 17:21:10 -0400 Subject: [PATCH v31 03/11] Enable dumping of table/index stats in pg_dump. For each table/matview/index dumped, it will generate a statement that calls pg_set_relation_stats(), and it will generate a series of statements that call pg_set_attribute_stats(), one per attribute. These statements will restore the statistics of the current system onto the destination system. Adds the command-line options -X / --statistics-only, which are mutually exclusive to --schema-only and --data-only. Statistics are not dumped when --schema-only is specified, except during a binary upgrade. As is the pattern with pg_dump options, staistics can be disabled using --no-statistics. Table statistics are dumped in the data section. This is true even if dumping stats in a binary upgrade. Index and Materialized View statistics are dumped in the post-data section. --- src/bin/pg_dump/pg_backup.h | 4 + src/bin/pg_dump/pg_backup_archiver.c | 5 + src/bin/pg_dump/pg_dump.c | 395 ++++++++++++++++++++++++++- src/bin/pg_dump/pg_dump.h | 8 + src/bin/pg_dump/pg_dump_sort.c | 5 + src/bin/pg_dump/pg_dumpall.c | 5 + src/bin/pg_dump/pg_restore.c | 18 +- src/bin/pg_dump/t/001_basic.pl | 18 ++ doc/src/sgml/ref/pg_dump.sgml | 36 ++- doc/src/sgml/ref/pg_restore.sgml | 31 ++- 10 files changed, 509 insertions(+), 16 deletions(-) diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h index f74e848714..b03b69ff00 100644 --- a/src/bin/pg_dump/pg_backup.h +++ b/src/bin/pg_dump/pg_backup.h @@ -113,6 +113,7 @@ typedef struct _restoreOptions int no_publications; /* Skip publication entries */ int no_security_labels; /* Skip security label entries */ int no_subscriptions; /* Skip subscription entries */ + int no_statistics; /* Skip statistics import */ int strict_names; const char *filename; @@ -160,6 +161,7 @@ typedef struct _restoreOptions /* flags derived entirely from the user-settable flags */ bool dumpSchema; bool dumpData; + bool dumpStatistics; } RestoreOptions; typedef struct _dumpOptions @@ -182,6 +184,7 @@ typedef struct _dumpOptions int no_security_labels; int no_publications; int no_subscriptions; + int no_statistics; int no_toast_compression; int no_unlogged_table_data; int serializable_deferrable; @@ -208,6 +211,7 @@ typedef struct _dumpOptions /* flags derived entirely from the user-settable flags */ bool dumpSchema; bool dumpData; + bool dumpStatistics; } DumpOptions; /* diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index 32c8e588f5..4edcb4eca5 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -2958,6 +2958,10 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) if (ropt->no_subscriptions && strcmp(te->desc, "SUBSCRIPTION") == 0) return 0; + /* If it's a stats dump, maybe ignore it */ + if (ropt->no_statistics && strcmp(te->desc, "STATISTICS") == 0) + return 0; + /* Ignore it if section is not to be dumped/restored */ switch (curSection) { @@ -2987,6 +2991,7 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH) */ if (strcmp(te->desc, "ACL") == 0 || strcmp(te->desc, "COMMENT") == 0 || + strcmp(te->desc, "STATISTICS") == 0 || strcmp(te->desc, "SECURITY LABEL") == 0) { /* Database properties react to createDB, not selectivity options. */ diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 46e1da1856..25df8d56cf 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -429,6 +429,7 @@ main(int argc, char **argv) bool user_compression_defined = false; bool data_only = false; bool schema_only = false; + bool statistics_only = false; DataDirSyncMethod sync_method = DATA_DIR_SYNC_METHOD_FSYNC; static DumpOptions dopt; @@ -466,6 +467,7 @@ main(int argc, char **argv) {"encoding", required_argument, NULL, 'E'}, {"help", no_argument, NULL, '?'}, {"version", no_argument, NULL, 'V'}, + {"statistics-only", no_argument, NULL, 'X'}, /* * the following options don't have an equivalent short option letter @@ -494,6 +496,7 @@ main(int argc, char **argv) {"no-comments", no_argument, &dopt.no_comments, 1}, {"no-publications", no_argument, &dopt.no_publications, 1}, {"no-security-labels", no_argument, &dopt.no_security_labels, 1}, + {"no-statistics", no_argument, &dopt.no_statistics, 1}, {"no-subscriptions", no_argument, &dopt.no_subscriptions, 1}, {"no-toast-compression", no_argument, &dopt.no_toast_compression, 1}, {"no-unlogged-table-data", no_argument, &dopt.no_unlogged_table_data, 1}, @@ -539,7 +542,7 @@ main(int argc, char **argv) InitDumpOptions(&dopt); - while ((c = getopt_long(argc, argv, "abBcCd:e:E:f:F:h:j:n:N:Op:RsS:t:T:U:vwWxZ:", + while ((c = getopt_long(argc, argv, "abBcCd:e:E:f:F:h:j:n:N:Op:PRsS:t:T:U:vwWxXZ:", long_options, &optindex)) != -1) { switch (c) @@ -613,6 +616,10 @@ main(int argc, char **argv) dopt.cparams.pgport = pg_strdup(optarg); break; + case 'X': /* Dump statistics only */ + statistics_only = true; + break; + case 'R': /* no-op, still accepted for backwards compatibility */ break; @@ -784,6 +791,13 @@ main(int argc, char **argv) if (data_only && schema_only) pg_fatal("options -s/--schema-only and -a/--data-only cannot be used together"); + if (schema_only && statistics_only) + pg_fatal("options -s/--schema-only and -X/--statistics-only cannot be used together"); + if (data_only && statistics_only) + pg_fatal("options -a/--data-only and -X/--statistics-only cannot be used together"); + + if (statistics_only && dopt.no_statistics) + pg_fatal("options -X/--statistics-only and --no-statistics cannot be used together"); if (schema_only && foreign_servers_include_patterns.head != NULL) pg_fatal("options -s/--schema-only and --include-foreign-data cannot be used together"); @@ -798,8 +812,20 @@ main(int argc, char **argv) pg_fatal("option --if-exists requires option -c/--clean"); /* set derivative flags */ - dopt.dumpSchema = (!data_only); - dopt.dumpData = (!schema_only); + dopt.dumpData = data_only || (!schema_only && !statistics_only); + dopt.dumpSchema = schema_only || (!data_only && !statistics_only); + + if (statistics_only) + /* stats are only thing wanted */ + dopt.dumpStatistics = true; + else if (dopt.no_statistics) + /* stats specifically excluded */ + dopt.dumpStatistics = false; + else if (dopt.binary_upgrade) + /* binary upgrade and not specifically excluded */ + dopt.dumpStatistics = true; + else + dopt.dumpStatistics = (!data_only && !schema_only); /* * --inserts are already implied above if --column-inserts or @@ -1099,6 +1125,7 @@ main(int argc, char **argv) ropt->dropSchema = dopt.outputClean; ropt->dumpData = dopt.dumpData; ropt->dumpSchema = dopt.dumpSchema; + ropt->dumpStatistics = dopt.dumpStatistics; ropt->if_exists = dopt.if_exists; ropt->column_inserts = dopt.column_inserts; ropt->dumpSections = dopt.dumpSections; @@ -1177,7 +1204,7 @@ help(const char *progname) printf(_(" -?, --help show this help, then exit\n")); printf(_("\nOptions controlling the output content:\n")); - printf(_(" -a, --data-only dump only the data, not the schema\n")); + printf(_(" -a, --data-only dump only the data, not the schema or statistics\n")); printf(_(" -b, --large-objects include large objects in dump\n")); printf(_(" --blobs (same as --large-objects, deprecated)\n")); printf(_(" -B, --no-large-objects exclude large objects in dump\n")); @@ -1190,11 +1217,12 @@ help(const char *progname) printf(_(" -N, --exclude-schema=PATTERN do NOT dump the specified schema(s)\n")); printf(_(" -O, --no-owner skip restoration of object ownership in\n" " plain-text format\n")); - printf(_(" -s, --schema-only dump only the schema, no data\n")); + printf(_(" -s, --schema-only dump only the schema, no data or statistics\n")); printf(_(" -S, --superuser=NAME superuser user name to use in plain-text format\n")); printf(_(" -t, --table=PATTERN dump only the specified table(s)\n")); printf(_(" -T, --exclude-table=PATTERN do NOT dump the specified table(s)\n")); printf(_(" -x, --no-privileges do not dump privileges (grant/revoke)\n")); + printf(_(" -X, --statistics-only dump only the statistics, not schema or data\n")); printf(_(" --binary-upgrade for use by upgrade utilities only\n")); printf(_(" --column-inserts dump data as INSERT commands with column names\n")); printf(_(" --disable-dollar-quoting disable dollar quoting, use SQL standard quoting\n")); @@ -1221,6 +1249,7 @@ help(const char *progname) printf(_(" --no-comments do not dump comments\n")); printf(_(" --no-publications do not dump publications\n")); printf(_(" --no-security-labels do not dump security label assignments\n")); + printf(_(" --no-statistics do not dump statistics\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")); @@ -6780,6 +6809,42 @@ getFuncs(Archive *fout) destroyPQExpBuffer(query); } +/* + * getRelationStatistics + * register the statistics object as a dependent of the relation. + * + */ +static RelStatsInfo * +getRelationStatistics(Archive *fout, DumpableObject *rel, char relkind) +{ + if ((relkind == RELKIND_RELATION) || + (relkind == RELKIND_PARTITIONED_TABLE) || + (relkind == RELKIND_INDEX) || + (relkind == RELKIND_PARTITIONED_INDEX) || + (relkind == RELKIND_MATVIEW)) + { + RelStatsInfo *info = pg_malloc0(sizeof(RelStatsInfo)); + DumpableObject *dobj = &info->dobj; + + dobj->objType = DO_REL_STATS; + dobj->catId.tableoid = 0; + dobj->catId.oid = 0; + AssignDumpId(dobj); + dobj->dependencies = (DumpId *) pg_malloc(sizeof(DumpId)); + dobj->dependencies[0] = rel->dumpId; + dobj->nDeps = 1; + dobj->allocDeps = 1; + dobj->components |= DUMP_COMPONENT_STATISTICS; + dobj->dump = rel->dump; + dobj->name = pg_strdup(rel->name); + dobj->namespace = rel->namespace; + info->relkind = relkind; + + return info; + } + return NULL; +} + /* * getTables * read all the tables (no indexes) in the system catalogs, @@ -7157,6 +7222,7 @@ getTables(Archive *fout, int *numTables) /* Tables have data */ tblinfo[i].dobj.components |= DUMP_COMPONENT_DATA; + tblinfo[i].dobj.components |= DUMP_COMPONENT_STATISTICS; /* Mark whether table has an ACL */ if (!PQgetisnull(res, i, i_relacl)) @@ -7205,6 +7271,8 @@ getTables(Archive *fout, int *numTables) } } } + if (tblinfo[i].interesting) + getRelationStatistics(fout, &tblinfo[i].dobj, tblinfo[i].relkind); } if (query->len != 0) @@ -7649,11 +7717,14 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) for (int c = 0; c < numinds; c++, j++) { char contype; + char indexkind; + RelStatsInfo *relstats; indxinfo[j].dobj.objType = DO_INDEX; indxinfo[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid)); indxinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid)); AssignDumpId(&indxinfo[j].dobj); + indxinfo[j].dobj.components |= DUMP_COMPONENT_STATISTICS; indxinfo[j].dobj.dump = tbinfo->dobj.dump; indxinfo[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_indexname)); indxinfo[j].dobj.namespace = tbinfo->dobj.namespace; @@ -7676,7 +7747,14 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) { NULL, NULL }; + + if (indxinfo[j].parentidx == 0) + indexkind = RELKIND_INDEX; + else + indexkind = RELKIND_PARTITIONED_INDEX; + contype = *(PQgetvalue(res, j, i_contype)); + relstats = getRelationStatistics(fout, &indxinfo[j].dobj, indexkind); if (contype == 'p' || contype == 'u' || contype == 'x') { @@ -7710,6 +7788,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) constrinfo->separate = true; indxinfo[j].indexconstraint = constrinfo->dobj.dumpId; + if (relstats != NULL) + addObjectDependency(&relstats->dobj, constrinfo->dobj.dumpId); } else { @@ -10297,6 +10377,296 @@ dumpComment(Archive *fout, const char *type, catalogId, subid, dumpId, NULL); } +/* + * Tabular description of the parameters to pg_restore_relation_stats() + * required, param_name, param_type + */ +static const char *rel_stats_arginfo[][3] = { + {"f", "relation", "regclass"}, + {"f", "version", "integer"}, + {"f", "relpages", "integer"}, + {"f", "reltuples", "real"}, + {"f", "relallvisible", "integer"}, +}; + +/* + * Tabular description of the parameters to pg_restore_attribute_stats() + * required, param_name, param_type + */ +static const char *att_stats_arginfo[][3] = { + {"f", "relation", "regclass"}, + {"f", "attname", "name"}, + {"f", "inherited", "boolean"}, + {"f", "version", "integer"}, + {"f", "null_frac", "float4"}, + {"f", "avg_width", "integer"}, + {"f", "n_distinct", "float4"}, + {"f", "most_common_vals", "text"}, + {"f", "most_common_freqs", "float4[]"}, + {"f", "histogram_bounds", "text"}, + {"f", "correlation", "float4"}, + {"f", "most_common_elems", "text"}, + {"f", "most_common_elem_freqs", "float4[]"}, + {"f", "elem_count_histogram", "float4[]"}, + {"f", "range_length_histogram", "text"}, + {"f", "range_empty_frac", "float4"}, + {"f", "range_bounds_histogram", "text"}, +}; + +/* + * getRelStatsExportQuery -- + * + * Generate a query that will fetch all relation (e.g. pg_class) + * stats for a given relation. + */ +static void +getRelStatsExportQuery(PQExpBuffer query, Archive *fout, + const char *schemaname, const char *relname) +{ + resetPQExpBuffer(query); + appendPQExpBufferStr(query, + "SELECT c.oid::regclass AS relation, " + "current_setting('server_version_num') AS version, " + "c.relpages, c.reltuples, c.relallvisible " + "FROM pg_class c " + "JOIN pg_namespace n " + "ON n.oid = c.relnamespace " + "WHERE n.nspname = "); + appendStringLiteralAH(query, schemaname, fout); + appendPQExpBufferStr(query, " AND c.relname = "); + appendStringLiteralAH(query, relname, fout); +} + +/* + * getAttStatsExportQuery -- + * + * Generate a query that will fetch all attribute (e.g. pg_statistic) + * stats for a given relation. + */ +static void +getAttStatsExportQuery(PQExpBuffer query, Archive *fout, + const char *schemaname, const char *relname) +{ + resetPQExpBuffer(query); + appendPQExpBufferStr(query, + "SELECT c.oid::regclass AS relation, " + "s.attname," + "s.inherited," + "current_setting('server_version_num') AS version, " + "s.null_frac," + "s.avg_width," + "s.n_distinct," + "s.most_common_vals," + "s.most_common_freqs," + "s.histogram_bounds," + "s.correlation," + "s.most_common_elems," + "s.most_common_elem_freqs," + "s.elem_count_histogram,"); + + if (fout->remoteVersion >= 170000) + appendPQExpBufferStr(query, + "s.range_length_histogram," + "s.range_empty_frac," + "s.range_bounds_histogram "); + else + appendPQExpBufferStr(query, + "NULL AS range_length_histogram," + "NULL AS range_empty_frac," + "NULL AS range_bounds_histogram "); + + appendPQExpBufferStr(query, + "FROM pg_stats s " + "JOIN pg_namespace n " + "ON n.nspname = s.schemaname " + "JOIN pg_class c " + "ON c.relname = s.tablename " + "AND c.relnamespace = n.oid " + "WHERE s.schemaname = "); + appendStringLiteralAH(query, schemaname, fout); + appendPQExpBufferStr(query, " AND s.tablename = "); + appendStringLiteralAH(query, relname, fout); + appendPQExpBufferStr(query, " ORDER BY s.attname, s.inherited"); +} + + +/* + * appendNamedArgument -- + * + * Convenience routine for constructing parameters of the form: + * 'paraname', 'value'::type + */ +static void +appendNamedArgument(PQExpBuffer out, Archive *fout, + const char *argname, bool positional, + const char *argval, const char *argtype) +{ + appendPQExpBufferStr(out, "\t"); + + if (!positional) + { + appendStringLiteralAH(out, argname, fout); + appendPQExpBufferStr(out, ", "); + } + + appendStringLiteralAH(out, argval, fout); + appendPQExpBuffer(out, "::%s", argtype); +} + +/* + * appendRelStatsImport -- + * + * Append a formatted pg_restore_relation_stats statement. + */ +static void +appendRelStatsImport(PQExpBuffer out, Archive *fout, PGresult *res) +{ + const char *sep = ""; + + if (PQntuples(res) == 0) + return; + + appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_relation_stats(\n"); + + for (int argno = 0; argno < lengthof(rel_stats_arginfo); argno++) + { + bool positional = (rel_stats_arginfo[argno][0][0] == 't'); + const char *argname = rel_stats_arginfo[argno][1]; + const char *argtype = rel_stats_arginfo[argno][2]; + int fieldno = PQfnumber(res, argname); + + if (fieldno < 0) + pg_fatal("relation stats export query missing field '%s'", + argname); + + if (PQgetisnull(res, 0, fieldno)) + { + if (!positional) + pg_fatal("relation stats export query unexpected NULL in '%s'", + argname); + else + continue; + } + + appendPQExpBufferStr(out, sep); + appendNamedArgument(out, fout, argname, positional, + PQgetvalue(res, 0, fieldno), argtype); + + sep = ",\n"; + } + appendPQExpBufferStr(out, "\n);\n"); +} + +/* + * appendAttStatsImport -- + * + * Append a series of formatted pg_restore_attribute_stats statements. + */ +static void +appendAttStatsImport(PQExpBuffer out, Archive *fout, PGresult *res) +{ + for (int rownum = 0; rownum < PQntuples(res); rownum++) + { + const char *sep = ""; + + appendPQExpBufferStr(out, "SELECT * FROM pg_catalog.pg_restore_attribute_stats(\n"); + for (int argno = 0; argno < lengthof(att_stats_arginfo); argno++) + { + bool positional = (att_stats_arginfo[argno][0][0] == 't'); + const char *argname = att_stats_arginfo[argno][1]; + const char *argtype = att_stats_arginfo[argno][2]; + int fieldno = PQfnumber(res, argname); + + if (fieldno < 0) + pg_fatal("attribute stats export query missing field '%s'", + argname); + + if (PQgetisnull(res, rownum, fieldno)) + { + if (positional) + pg_fatal("attribute stats export query unexpected NULL in '%s'", + argname); + else + continue; + } + + appendPQExpBufferStr(out, sep); + appendNamedArgument(out, fout, argname, positional, + PQgetvalue(res, rownum, fieldno), argtype); + sep = ",\n"; + } + appendPQExpBufferStr(out, "\n);\n"); + } +} + +/* + * get statistics dump section, which depends on the parent object type + * + * objects created in SECTION_PRE_DATA have stats in SECTION_DATA + * objects created in SECTION_POST_DATA have stats in SECTION_POST_DATA + */ +static teSection +statisticsDumpSection(const RelStatsInfo *rsinfo) +{ + + if ((rsinfo->relkind == RELKIND_MATVIEW) || + (rsinfo->relkind == RELKIND_INDEX) || + (rsinfo->relkind == RELKIND_PARTITIONED_INDEX)) + return SECTION_POST_DATA; + + return SECTION_DATA; +} + +/* + * dumpRelationStats -- + * + * Dump command to import stats into the relation on the new database. + */ +static void +dumpRelationStats(Archive *fout, const RelStatsInfo *rsinfo) +{ + PGresult *res; + PQExpBuffer query; + PQExpBuffer out; + PQExpBuffer tag; + DumpableObject *dobj = (DumpableObject *) &rsinfo->dobj; + + /* nothing to do if we are not dumping statistics */ + if (!fout->dopt->dumpStatistics) + return; + + tag = createPQExpBuffer(); + appendPQExpBuffer(tag, "%s %s", "STATISTICS DATA", fmtId(dobj->name)); + + query = createPQExpBuffer(); + out = createPQExpBuffer(); + + getRelStatsExportQuery(query, fout, dobj->namespace->dobj.name, + dobj->name); + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + appendRelStatsImport(out, fout, res); + PQclear(res); + + getAttStatsExportQuery(query, fout, dobj->namespace->dobj.name, + dobj->name); + res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK); + appendAttStatsImport(out, fout, res); + PQclear(res); + + ArchiveEntry(fout, nilCatalogId, createDumpId(), + ARCHIVE_OPTS(.tag = tag->data, + .namespace = dobj->namespace->dobj.name, + .description = "STATISTICS DATA", + .section = statisticsDumpSection(rsinfo), + .createStmt = out->data, + .deps = dobj->dependencies, + .nDeps = dobj->nDeps)); + + destroyPQExpBuffer(query); + destroyPQExpBuffer(out); + destroyPQExpBuffer(tag); +} + /* * dumpTableComment -- * @@ -10745,6 +11115,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj) case DO_SUBSCRIPTION_REL: dumpSubscriptionTable(fout, (const SubRelInfo *) dobj); break; + case DO_REL_STATS: + dumpRelationStats(fout, (const RelStatsInfo *) dobj); + break; case DO_PRE_DATA_BOUNDARY: case DO_POST_DATA_BOUNDARY: /* never dumped, nothing to do */ @@ -17173,6 +17546,8 @@ dumpIndex(Archive *fout, const IndxInfo *indxinfo) free(indstatvalsarray); } + /* Comments and stats share same .dep */ + /* Dump Index Comments */ if (indxinfo->dobj.dump & DUMP_COMPONENT_COMMENT) dumpComment(fout, "INDEX", qindxname, @@ -18960,6 +19335,16 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs, /* must come after the pre-data boundary */ addObjectDependency(dobj, preDataBound->dumpId); break; + case DO_REL_STATS: + /* stats section varies by parent object type, DATA or POST */ + if (statisticsDumpSection((RelStatsInfo *) dobj) == SECTION_DATA) + { + addObjectDependency(dobj, preDataBound->dumpId); + addObjectDependency(postDataBound, dobj->dumpId); + } + else + addObjectDependency(dobj, postDataBound->dumpId); + break; } } } diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index d65f558565..ef555e9178 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -84,6 +84,7 @@ typedef enum DO_PUBLICATION_TABLE_IN_SCHEMA, DO_SUBSCRIPTION, DO_SUBSCRIPTION_REL, /* see note for SubRelInfo */ + DO_REL_STATS, } DumpableObjectType; /* @@ -101,6 +102,7 @@ typedef uint32 DumpComponents; #define DUMP_COMPONENT_ACL (1 << 4) #define DUMP_COMPONENT_POLICY (1 << 5) #define DUMP_COMPONENT_USERMAP (1 << 6) +#define DUMP_COMPONENT_STATISTICS (1 << 7) #define DUMP_COMPONENT_ALL (0xFFFF) /* @@ -421,6 +423,12 @@ typedef struct _indexAttachInfo IndxInfo *partitionIdx; /* link to index on partition */ } IndexAttachInfo; +typedef struct _relStatsInfo +{ + DumpableObject dobj; + char relkind; /* 'r', 'v', 'c', etc */ +} RelStatsInfo; + typedef struct _statsExtInfo { DumpableObject dobj; diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index 3c8f2eb808..989d20aa27 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -1500,6 +1500,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize) "POST-DATA BOUNDARY (ID %d)", obj->dumpId); return; + case DO_REL_STATS: + snprintf(buf, bufsize, + "RELATION STATISTICS FOR %s (ID %d OID %u)", + obj->name, obj->dumpId, obj->catId.oid); + return; } /* shouldn't get here */ snprintf(buf, bufsize, diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c index e3ad8fb295..1799a03ff1 100644 --- a/src/bin/pg_dump/pg_dumpall.c +++ b/src/bin/pg_dump/pg_dumpall.c @@ -103,6 +103,7 @@ static int use_setsessauth = 0; static int no_comments = 0; static int no_publications = 0; static int no_security_labels = 0; +static int no_statistics = 0; static int no_subscriptions = 0; static int no_toast_compression = 0; static int no_unlogged_table_data = 0; @@ -172,6 +173,7 @@ main(int argc, char *argv[]) {"no-role-passwords", no_argument, &no_role_passwords, 1}, {"no-security-labels", no_argument, &no_security_labels, 1}, {"no-subscriptions", no_argument, &no_subscriptions, 1}, + {"no-statistics", no_argument, &no_statistics, 1}, {"no-sync", no_argument, NULL, 4}, {"no-toast-compression", no_argument, &no_toast_compression, 1}, {"no-unlogged-table-data", no_argument, &no_unlogged_table_data, 1}, @@ -451,6 +453,8 @@ main(int argc, char *argv[]) appendPQExpBufferStr(pgdumpopts, " --no-publications"); if (no_security_labels) appendPQExpBufferStr(pgdumpopts, " --no-security-labels"); + if (no_statistics) + appendPQExpBufferStr(pgdumpopts, " --no-statistics"); if (no_subscriptions) appendPQExpBufferStr(pgdumpopts, " --no-subscriptions"); if (no_toast_compression) @@ -666,6 +670,7 @@ help(void) printf(_(" --no-publications do not dump publications\n")); printf(_(" --no-role-passwords do not dump passwords for roles\n")); printf(_(" --no-security-labels do not dump security label assignments\n")); + printf(_(" --no-statistics do not dump statistics\n")); printf(_(" --no-subscriptions do not dump subscriptions\n")); printf(_(" --no-sync do not wait for changes to be written safely to disk\n")); printf(_(" --no-table-access-method do not dump table access methods\n")); diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c index d3b6debcab..f8f16facc1 100644 --- a/src/bin/pg_dump/pg_restore.c +++ b/src/bin/pg_dump/pg_restore.c @@ -63,6 +63,7 @@ main(int argc, char **argv) int numWorkers = 1; Archive *AH; char *inputFileSpec; + bool statistics_only = false; static int disable_triggers = 0; static int enable_row_security = 0; static int if_exists = 0; @@ -74,6 +75,7 @@ main(int argc, char **argv) static int no_publications = 0; static int no_security_labels = 0; static int no_subscriptions = 0; + static int no_statistics = 0; static int strict_names = 0; bool data_only = false; @@ -109,6 +111,7 @@ main(int argc, char **argv) {"username", 1, NULL, 'U'}, {"verbose", 0, NULL, 'v'}, {"single-transaction", 0, NULL, '1'}, + {"statistics-only", no_argument, NULL, 'P'}, /* * the following options don't have an equivalent short option letter @@ -129,6 +132,7 @@ main(int argc, char **argv) {"no-security-labels", no_argument, &no_security_labels, 1}, {"no-subscriptions", no_argument, &no_subscriptions, 1}, {"filter", required_argument, NULL, 4}, + {"no-statistics", no_argument, &no_statistics, 1}, {NULL, 0, NULL, 0} }; @@ -272,6 +276,10 @@ main(int argc, char **argv) opts->aclsSkip = 1; break; + case 'X': /* Restore statistics only */ + statistics_only = true; + break; + case '1': /* Restore data in a single transaction */ opts->single_txn = true; opts->exit_on_error = true; @@ -344,6 +352,10 @@ main(int argc, char **argv) if (data_only && schema_only) pg_fatal("options -s/--schema-only and -a/--data-only cannot be used together"); + if (data_only && statistics_only) + pg_fatal("options -a/--data-only and -X/--statistics-only cannot be used together"); + if (schema_only && statistics_only) + pg_fatal("options -s/--schema-only and -X/--statistics-only cannot be used together"); if (data_only && opts->dropSchema) pg_fatal("options -c/--clean and -a/--data-only cannot be used together"); @@ -363,8 +375,9 @@ main(int argc, char **argv) pg_fatal("cannot specify both --single-transaction and multiple jobs"); /* set derivative flags */ - opts->dumpSchema = (!data_only); - opts->dumpData = (!schema_only); + opts->dumpSchema = (!data_only && !statistics_only); + opts->dumpData = (!schema_only && !statistics_only); + opts->dumpStatistics = (!no_statistics && !data_only && !schema_only); opts->disable_triggers = disable_triggers; opts->enable_row_security = enable_row_security; @@ -376,6 +389,7 @@ main(int argc, char **argv) opts->no_publications = no_publications; opts->no_security_labels = no_security_labels; opts->no_subscriptions = no_subscriptions; + opts->no_statistics = no_statistics; if (if_exists && !opts->dropSchema) pg_fatal("option --if-exists requires option -c/--clean"); diff --git a/src/bin/pg_dump/t/001_basic.pl b/src/bin/pg_dump/t/001_basic.pl index b9d13a0e1d..76ebf15f55 100644 --- a/src/bin/pg_dump/t/001_basic.pl +++ b/src/bin/pg_dump/t/001_basic.pl @@ -50,12 +50,30 @@ command_fails_like( 'pg_dump: options -s/--schema-only and -a/--data-only cannot be used together' ); +command_fails_like( + [ 'pg_dump', '-s', '-X' ], + qr/\Qpg_dump: error: options -s\/--schema-only and -X\/--statistics-only cannot be used together\E/, + 'pg_dump: error: options -s/--schema-only and -X/--statistics-only cannot be used together' +); + +command_fails_like( + [ 'pg_dump', '-a', '-X' ], + qr/\Qpg_dump: error: options -a\/--data-only and -X\/--statistics-only cannot be used together\E/, + 'pg_dump: error: options -a/--data-only and -X/--statistics-only cannot be used together' +); + command_fails_like( [ 'pg_dump', '-s', '--include-foreign-data=xxx' ], qr/\Qpg_dump: error: options -s\/--schema-only and --include-foreign-data cannot be used together\E/, 'pg_dump: options -s/--schema-only and --include-foreign-data cannot be used together' ); +command_fails_like( + [ 'pg_dump', '--statistics-only', '--no-statistics' ], + qr/\Qpg_dump: error: options -X\/--statistics-only and --no-statistics cannot be used together\E/, + 'pg_dump: options -X\/--statistics-only and --no-statistics cannot be used together' +); + command_fails_like( [ 'pg_dump', '-j2', '--include-foreign-data=xxx' ], qr/\Qpg_dump: error: option --include-foreign-data is not supported with parallel backup\E/, diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index ffc29b04fb..99f25e40c0 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -123,7 +123,7 @@ PostgreSQL documentation - Dump only the data, not the schema (data definitions). + Dump only the data, not the schema (data definitions) or statistics. Table data, large objects, and sequence values are dumped. @@ -141,8 +141,9 @@ PostgreSQL documentation Include large objects in the dump. This is the default behavior - except when , , or - is specified. The + except when , , + , or + is specified. The switch is therefore only useful to add large objects to dumps where a specific schema or table has been requested. Note that large objects are considered data and therefore will be included when @@ -516,10 +517,11 @@ PostgreSQL documentation - Dump only the object definitions (schema), not data. + Dump only the object definitions (schema), not data or statistics. - This option is the inverse of . + This option is mutually exclusive to + and . It is similar to, but for historical reasons not identical to, specifying . @@ -652,6 +654,18 @@ PostgreSQL documentation + + + + + + Dump only the statistics, not the schema (data definitions) or data. + Statistics for tables, materialized views, and indexes are dumped. + + + + + [:detail] @@ -833,7 +847,8 @@ PostgreSQL documentation though you do not need the data in it. - To exclude data for all tables in the database, see . + To exclude data for all tables in the database, see + or . @@ -1098,6 +1113,15 @@ PostgreSQL documentation + + + + + Do not dump statistics. + + + + diff --git a/doc/src/sgml/ref/pg_restore.sgml b/doc/src/sgml/ref/pg_restore.sgml index b8b27e1719..ff1441a243 100644 --- a/doc/src/sgml/ref/pg_restore.sgml +++ b/doc/src/sgml/ref/pg_restore.sgml @@ -94,7 +94,7 @@ PostgreSQL documentation - Restore only the data, not the schema (data definitions). + Restore only the data, not the schema (data definitions) or statistics. Table data, large objects, and sequence values are restored, if present in the archive. @@ -483,10 +483,11 @@ PostgreSQL documentation to the extent that schema entries are present in the archive. - This option is the inverse of . + This option is mutually exclusive of + and . It is similar to, but for historical reasons not identical to, specifying - . + . (Do not confuse this with the option, which @@ -599,6 +600,20 @@ PostgreSQL documentation + + + + + + Restore only the statistics, not schema or data. + + + (Do not confuse this with the option, which + uses the word schema in a different meaning.) + + + + @@ -723,6 +738,16 @@ PostgreSQL documentation + + + + + Do not output commands to restore statistics, even if the archive + contains them. + + + + -- 2.47.0