F.17. dump_stat — functions to backup and recover thepg_statistic table | ||||
|---|---|---|---|---|
| Prev | Up | Appendix F. Additional Supplied Modules and Extensions Shipped inpostgrespro-std-17-contrib | Home | Next |
F.17. dump_stat — functions to backup and recover thepg_statistic table#
Thedump_stat module provides functions that allow you to backup and recover the contents of thepg_statistic table. When performing a dump/restore, you can usedump_stat to migrate the original statistics to the new server instead of running theANALYZE command for the whole database cluster, which can significantly reduce downtime for large databases. Thedump_statistic function generatesINSERT statements which can later be applied to a compatible database. To successfully restore statistical data, you must install the extension on both the original and the recipient servers since these statements rely on the provideddump_stat functions.
Note that the definition of thepg_statistic table might change occasionally, which means that generated dump might be incompatible with future releases ofPostgres Pro.
F.17.1. Installation#
Thedump_stat extension is included into Postgres Pro. Once you have Postgres Pro installed, you must execute theCREATE EXTENSION command to enabledump_stat, as follows:
CREATE EXTENSION dump_stat;
F.17.2. Functions#
anyarray_to_text(array anyarray) returns textReturns the given array as
text.dump_statistic() returns setof textdump_statisticdumps the contents of thepg_statisticsystem catalog. It produces anINSERTstatement per each tuple of thepg_statistic, excluding the ones that contain statistical data for tables in theinformation_schemaandpg_catalogschemas.The
INSERTstatement takes form ofWITH upsert as ( UPDATE pg_catalog.pg_statistic SET
column_name=expression[, ...] WHERE starelid =t_relname::regclass AND to_attname(t_relname, staattnum) =t_attnameAND to_atttype(t_relname, staattnum) =t_atttypeAND stainherit =t_stainheritRETURNING *)ins as ( SELECTexpression[, ...] WHERE NOT EXISTS (SELECT * FROM upsert) AND to_attnum(t_relname,t_attname) IS NOT NULL AND to_atttype(t_relname,t_attname) =t_atttype)INSERT INTO pg_catalog.pg_statistic SELECT * FROM ins;whereexpressioncan be one of:array_in(array_text,type_name::regtype::oid, -1)value::type_nameTo save the produced statements, redirect thepsql output into a file using standardpsql options. For details on the availablepsql options, seepsql. Meta-commands starting with a backslash are not supported.
For example, to save statistics for the
dbnamedatabase into adump_stat.sqlfile, run:$ psql -XAtq -c "SELECT dump_statistic()"
dbname> dump_stat.sqldump_statistic(schema_name text) returns setof textdump_statisticdumps the contents of thepg_statisticsystem catalog. It produces anINSERTstatement per each tuple of thepg_statisticthat relates to some table in theschema_nameschema.dump_statistic(schema_name text, table_name text) returns setof textdump_statisticdumps the contents of thepg_statisticsystem catalog. It produces anINSERTstatement per each tuple of thepg_statisticthat relates to the specifiedschema_name.table_nametable.dump_statistic(relation regclass) returns setof textdump_statisticdumps the contents of thepg_statisticsystem catalog. It produces anINSERTstatement per each tuple of thepg_statisticthat contains statistical data for the specifiedrelation.to_schema_qualified_operator(opid oid) returns textFetches the schema-qualified operator name by operator id
opid. For example:test=# SELECT to_schema_qualified_operator('+(int,int)'::regoperator); to_schema_qualified_operator ------------------------------------------------ pg_catalog.+(pg_catalog.int4, pg_catalog.int4)(1 row)to_schema_qualified_type(typid oid) returns textFetches the schema-qualified type name by type id
typid.to_schema_qualified_relation(relid oid) returns textFetches the schema-qualified relation name by relation id
relid.anyarray_elemtype(arr anyarray) returns oidReturns the element type of the given array as
oid. For example:test=# SELECT anyarray_elemtype(array_in('{1,2,3}', 'int'::regtype, -1)); anyarray_elemtype ------------------- 23(1 row)to_attname(relation regclass, colnum int2) returns textGiven a relation name
relationand a column numbercolnum, returns the column name astext.to_attnum(relation regclass, col text) returns int2Given a relation name
relationand a column namecol, returns the column number asint2.to_atttype(relation regclass, col text) returns textGiven a relation name
relationand a column namecol, returns the schema-qualified column type astext.to_atttype(relation regclass, colnum int2) returns textGiven a relation name
relationand a column numbercolnum, returns the schema-qualified column type astext.to_namespace(nsp text) returns oidto_namespaceduplicates the behavior of the cast to theregnamespacetype, which is not present in thePostgreSQL 9.4 release (and prior releases). This function returns theoidof the given schema.get_namespace(relation oid) returns oidget_namespacereturns the schema of the given relation asoid.