CODEWITHSUNDEEP
postgresqlpg-dumppostgresql-14
Bertie
Bertie

Reputation: 17697

pg_dump on a specific schema outputs no tables and no data

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)

Upvotes: 1

Views: 1496

Answers (1)

jjanes
jjanes

Reputation: 44137

Tables owned by extensions do not ordinarily get dumped. They would presumably get re-created when the extension itself was.

There is a way to override that but your extension apparently does not.

When overridden, still the data is dumped only when the CREATE EXTENSION itself is also dumped, which does not happen under -n. Belonging to an extension basically overrides belonging to a schema.

Upvotes: 3

Related Questions