特定模式上的 pg_dump 不输出表和数据

Question

mydb=# select version();
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.2 (Debian 14.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)

我有一个奇怪的问题，特定模式上的 pg_dump 没有输出表，也没有数据。 这是从 postgres 用户执行时的 pg_dump 命令和输出：

pg_dump -n periods -d mydb
--
-- PostgreSQL database dump
--

-- Dumped from database version 14.1 (Debian 14.1-1.pgdg110+1)
-- Dumped by pg_dump version 14.1 (Debian 14.1-1.pgdg110+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- PostgreSQL database dump complete
--

请注意，表没有被转储。

这是我的命令，用于列出该期间模式的所有表：

psql -d mydb -c '\dt+ periods'
Schema  |        Name         | Type  |  Owner   | Persistence | Access method |    Size    |                                                   Description                   >
---------+---------------------+-------+----------+-------------+---------------+------------+--------------------------------------------------------------------------------->
 periods | for_portion_views   | table | postgres | permanent   | heap          | 0 bytes    | 
 periods | foreign_keys        | table | postgres | permanent   | heap          | 8192 bytes | A registry of foreign keys using periods WITHOUT OVERLAPS
 periods | periods             | table | postgres | permanent   | heap          | 64 kB      | The main catalog for periods.  All "DDL" operations for periods must first take >
 periods | system_time_periods | table | postgres | permanent   | heap          | 80 kB      | 
 periods | system_versioning   | table | postgres | permanent   | heap          | 88 kB      | A registry of tables with SYSTEM VERSIONING
 periods | unique_keys         | table | postgres | permanent   | heap          | 8192 bytes | A registry of UNIQUE/PRIMARY keys using periods WITHOUT OVERLAPS

我想知道为了成功执行包含期间模式的 pg_dump 我错过了什么？

周期模式来自使用此扩展 https://github.com/xocolatl/periods

--- 评论部分讨论后的新编辑 ---

在创建扩展时，会创建该扩展的表，但数据为空。我的假设是，在实时数据库中调用扩展的函数会填充 period.* 表，并且数据不会转储到转储中，这会导致扩展在完全恢复后在我的测试应用程序中出错。

在 @jjanes 和 @LaurenzAlbe 对 https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES，我检查了github.com/xocolatl/periods/blob/master/periods--1.1.sql 和 github.com/xocolatl/periods/blob/master/periods--1.2.sql 都有 pg_extension_config_dump 调用。

这可能是无关紧要的，但从我的 select * from pg_extension 来看，它表明周期仍然是 1.1 版本。我尝试更改扩展周期更新；它说版本已经是 1.1。我尝试 SELECT * FROM pg_available_extension_versions WHERE name ='periods';，它列出了 1.0 和 1.1 版本。

以下是有关我的系统的更多信息：

Linux localdebian 4.19.0-18-amd64 #1 SMP Debian 4.19.208-1 (2021-09-29) x86_64 GNU/Linux
Debian 10 Buster

# dpkg -L postgresql-14-periods
/.
/usr
/usr/lib
/usr/lib/postgresql
/usr/lib/postgresql/14
/usr/lib/postgresql/14/lib
/usr/lib/postgresql/14/lib/bitcode
/usr/lib/postgresql/14/lib/bitcode/periods
/usr/lib/postgresql/14/lib/bitcode/periods/periods.bc
/usr/lib/postgresql/14/lib/bitcode/periods.index.bc
/usr/lib/postgresql/14/lib/periods.so
/usr/share
/usr/share/doc
/usr/share/doc/postgresql-14-periods
/usr/share/doc/postgresql-14-periods/README.md.gz
/usr/share/doc/postgresql-14-periods/changelog.Debian.amd64.gz
/usr/share/doc/postgresql-14-periods/changelog.Debian.gz
/usr/share/doc/postgresql-14-periods/changelog.gz
/usr/share/doc/postgresql-14-periods/copyright
/usr/share/doc/postgresql-doc-14
/usr/share/doc/postgresql-doc-14/extension
/usr/share/doc/postgresql-doc-14/extension/README.periods.gz
/usr/share/postgresql
/usr/share/postgresql/14
/usr/share/postgresql/14/extension
/usr/share/postgresql/14/extension/periods--1.0--1.1.sql
/usr/share/postgresql/14/extension/periods--1.0.sql
/usr/share/postgresql/14/extension/periods--1.1--1.2.sql
/usr/share/postgresql/14/extension/periods--1.1.sql
/usr/share/postgresql/14/extension/periods--1.2.sql
/usr/share/postgresql/14/extension/periods.control
/usr/share/doc/postgresql-14-periods/README.periods.gz

# select * from pg_extension where extname = 'periods';
   oid   | extname | extowner | extnamespace | extrelocatable | extversion |                     extconfig                     |    extcondition     
---------+---------+----------+--------------+----------------+------------+---------------------------------------------------+---------------------
 2406036 | periods |       10 |         2200 | f              | 1.1        | {2406063,2406069,2406089,2406101,2406114,2406137} | {"","","","","",""}
(1 row)

# alter extension periods update;
NOTICE:  version "1.1" of extension "periods" is already installed
ALTER EXTENSION

# SELECT * FROM pg_available_extension_versions WHERE name ='periods';
  name   | version | installed | superuser | relocatable | schema |   requires   |                               comment                                
---------+---------+-----------+-----------+-------------+--------+--------------+----------------------------------------------------------------------
 periods | 1.0     | f         | t         | f           |        | {btree_gist} | Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
 periods | 1.1     | t         | t         | f           |        | {btree_gist} | Provide Standard SQL functionality for PERIODs and SYSTEM VERSIONING
(2 rows)

Answer 1

扩展拥有的表通常不会被转储。当扩展本身创建时，它们可能会被重新创建。

有一种方法可以覆盖它 但你的扩展显然没有。

覆盖后，仅当 CREATE EXTENSION 本身也被转储时，数据才会转储，而在 -n 下不会发生这种情况。属于扩展基本上会覆盖属于模式。