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
我想知道我错过了什么，才能成功地完成包含periods模式的pg_dump？
periods架构来自于使用此扩展https://github.com/xocolatl/periods
---评论部分讨论后的新编辑---
创建扩展时，会创建该扩展的表，但其中包含空数据。我的假设是，在livedb中调用扩展的函数会填充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/eriods/blob/master/periods-1.2.sql是否都有pg_extension_CONFIG_dump调用。
这可能无关紧要，但从我从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)
扩展所拥有的表通常不会被转储。当扩展本身存在时，它们可能会被重新创建。
有一种方法可以覆盖它，但你的扩展显然没有。
当被重写时，仍然只有当CREATE EXTENSION本身也被转储时，数据才会被转储，这在-n下不会发生。属于扩展基本上会覆盖属于模式。