F.18. dump_stat — functions to backup and recover the pg

F.18. dump_stat — functions to backup and recover the `pg_statistic` table
Prev	Up	Appendix F. Additional Supplied Modules and Extensions Shipped in `postgrespro-std-16-contrib`	Home	Next

F.18. dump_stat — functions to backup and recover the `pg_statistic` table #

F.18.1. Installation
F.18.2. Functions

The dump_stat module provides functions that allow you to backup and recover the contents of the pg_statistic table. When performing a dump/restore, you can use dump_stat to migrate the original statistics to the new server instead of running the ANALYZE command for the whole database cluster, which can significantly reduce downtime for large databases. The dump_statistic function generates INSERT 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 provided dump_stat functions.

Note that the definition of the pg_statistic table might change occasionally, which means that generated dump might be incompatible with future releases of Postgres Pro.

F.18.1. Installation #

The dump_stat extension is included into Postgres Pro. Once you have Postgres Pro installed, you must execute the CREATE EXTENSION command to enable dump_stat, as follows:

CREATE EXTENSION dump_stat;

F.18.2. Functions #

anyarray_to_text(array anyarray) returns text

Returns the given array as text.

dump_statistic() returns setof text

dump_statistic dumps the contents of the pg_statistic system catalog. It produces an INSERT statement per each tuple of the pg_statistic, excluding the ones that contain statistical data for tables in the information_schema and pg_catalog schemas.

The INSERT statement takes form of

WITH upsert as (
  UPDATE pg_catalog.pg_statistic SET column_name = expression [, ...]
  WHERE starelid = t_relname::regclass
    AND to_attname(t_relname, staattnum) = t_attname
    AND to_atttype(t_relname, staattnum) = t_atttype
    AND stainherit = t_stainherit
  RETURNING *)
ins as (
  SELECT expression [, ...]
  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;

where expression can be one of:

array_in(array_text, type_name::regtype::oid, -1)
value::type_name

To save the produced statements, redirect the psql output into a file using standard psql options. For details on the available psql options, see psql. Meta-commands starting with a backslash are not supported.

For example, to save statistics for the dbname database into a dump_stat.sql file, run:

$ psql -XAtq -c "SELECT dump_statistic()" dbname > dump_stat.sql

dump_statistic(schema_name text) returns setof text

dump_statistic dumps the contents of the pg_statistic system catalog. It produces an INSERT statement per each tuple of the pg_statistic that relates to some table in the schema_name schema.

dump_statistic(schema_name text, table_name text) returns setof text

dump_statistic dumps the contents of the pg_statistic system catalog. It produces an INSERT statement per each tuple of the pg_statistic that relates to the specified schema_name.table_name table.

dump_statistic(relation regclass) returns setof text

dump_statistic dumps the contents of the pg_statistic system catalog. It produces an INSERT statement per each tuple of the pg_statistic that contains statistical data for the specified relation.

to_schema_qualified_operator(opid oid) returns text

Fetches 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 text

Fetches the schema-qualified type name by type id typid.

to_schema_qualified_relation(relid oid) returns text

Fetches the schema-qualified relation name by relation id relid.

anyarray_elemtype(arr anyarray) returns oid

Returns 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 text

Given a relation name relation and a column number colnum, returns the column name as text.

to_attnum(relation regclass, col text) returns int2

Given a relation name relation and a column name col, returns the column number as int2.

to_atttype(relation regclass, col text) returns text

Given a relation name relation and a column name col, returns the schema-qualified column type as text.

to_atttype(relation regclass, colnum int2) returns text

Given a relation name relation and a column number colnum, returns the schema-qualified column type as text.

to_namespace(nsp text) returns oid

to_namespace duplicates the behavior of the cast to the regnamespace type, which is not present in the PostgreSQL 9.4 release (and prior releases). This function returns the oid of the given schema.

get_namespace(relation oid) returns oid

get_namespace returns the schema of the given relation as oid.

Prev	Up	Next
F.17. dict_xsyn — example synonym full-text search dictionary	Home	F.19. earthdistance — calculate great-circle distances