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)
I have a strange issue where pg_dump on a specific schema outputs no tables and no data at all. This is the pg_dump command and output when executed from postgres user:
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
--
Notice that the tables were not dumped.
And this is my cmd to list all tables for this periods schema:
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
I wonder what did I miss in order to do a successful pg_dump that also includes the periods schema ?
The periods schema comes from using this extension https://github.com/xocolatl/periods
--- new edits after discussions in the comment section ---
When doing create extension, the tables for that extension get created, but with empty data. My assumption is that invoking the extension's functions in live db populates the periods.* tables, and the data was not carried over into the dump, which causes the extension to error in my testing-application after a full restore.
After a good pointer from both @jjanes and @LaurenzAlbe on https://www.postgresql.org/docs/current/extend-extensions.html#EXTEND-EXTENSIONS-CONFIG-TABLES, I checked that both github.com/xocolatl/periods/blob/master/periods--1.1.sql and github.com/xocolatl/periods/blob/master/periods--1.2.sql have the pg_extension_config_dump call.
And this might be irrelevant, but from my select * from pg_extension, it shows that the periods is still at 1.1 version. I tried alter extension periods update;, it says version is already at 1.1. I tried SELECT * FROM pg_available_extension_versions WHERE name ='periods';, it lists 1.0 and 1.1 version.
Below is further information on my system:
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)
