pg_dump и таблицы расширений

Если расширение использует таблицы (например, для хранения настроек), то при выгрузке БД утилитой pg_dump, табличные данные не выгружаются.

Пример. Файлы расширения my_ext с таблицей.

$ cat `pg_config --sharedir`/extension/my_ext.control
default_version = '1.0'

$ cat `pg_config --sharedir`/extension/my_ext--1.0.sql
\echo Use CREATE EXTENSION my_ext to load this file. \quit
create table my_ext_table (
   id serial
  ,param_name text
  ,param_value text
);
create view my_ext_params as select param_name, param_value from my_ext_table;

Создаем расширение в отдельной БД:

postgres=# create database db;
CREATE DATABASE
postgres=# \c db
You are now connected to database "db" as user "postgres".
db=# create extension my_ext;
CREATE EXTENSION

Проверяем его состав:

db=# \dx+ my_ext
Objects in extension "my_ext"
      Object Description      
------------------------------
 sequence my_ext_table_id_seq
 table my_ext_table
 view my_ext_params
(3 rows)

Добавляем строку в таблицу:

db=# insert into my_ext_table (param_name, param_value) values ('Param1', 'Value1');
INSERT 0 1
db=# select * from my_ext_params; 

 param_name | param_value 
------------+-------------
 Param1     | Value1
(1 row)

Делаем dump и смотрим как выгружается расширение:

db=# \! pg_dump db|grep my_ext
-- Name: my_ext; Type: EXTENSION; Schema: -; Owner: 
CREATE EXTENSION IF NOT EXISTS my_ext WITH SCHEMA public;

В дампе только команда CREATE EXTENSION, которая запускает скрипт my_ext–1.0.sql. Добавленная в таблицу my_ext_table строка не выгружается и следовательно не будет восстановлена.

Как правильно написал Том Лэйн, это проблема разработчиков расширений. Внимательно читая документацию, находим функцию pg_extension_config_dump(regclass, text), специально предназначенную для регистрации таблиц, данные которых pg_dump должен выгружать. Таким образом, в скрипте на создание расширения my_ext—1.0.sql нужно было добавить вызов функции:

select pg_extension_config_dump('my_ext_table'::regclass, '');

Этого достаточно для того, чтобы в вывод pg_dump записывалась команда COPY для выгрузки табличных данных (CREATE TABLE не будет выгружаться. Создание таблицы, по-прежнему, выполняется в CREATE EXTENSION). Второй параметр функции позволяет задать предложение WHERE,  чтобы выгружать только строки удовлетворяющие условию. Это может понадобиться, чтобы избежать повторной вставки, если скрипт на создание расширения сам создает строки в таблице.
Функцию pg_extension_config_dump можно вызывать только в скрипте на создание расширения (в нашем случае my_ext–1.0.sql). Поэтому если разработчики расширения не предусмотрели выгрузку табличных данных, то просто вызвать эту функцию не получится, требуется обновить расширение.
Есть пара неожиданностей, связанных с использованием этой функции.
1. В таблице my_ext_table есть столбец id с типом serial, т.е. использующий последовательность для генерации значений. Совершенно не очевидно (но факт), что вызов функции pg_extension_config_dump('my_ext_table'::regclass, '') не обращает на эту последовательность никакого внимания. Поэтому после восстановления из дампа, выборка номеров последовательности my_ext_table_id_seq начнется с 1 и наверняка пересечется с уже существующими номерами в таблице.
2. Это к тому, что текущие значения последовательностей, входящих в состав расширения, pg_dump также должен выгружать. И совершенно неочевидно, что для этого опять нужна функция pg_extension_config_dump.
В нашем случае, если данные таблиц и последовательностей расширения должны выгружаться pg_dump, то скрипт на создание расширения должен выглядеть так:

cat `pg_config --sharedir`/extension/my_ext--1.0.sql
\echo Use CREATE EXTENSION my_ext to load this file. \quit
create table my_ext_table (
   id serial
  ,param_name text
  ,param_value text
);
create view my_ext_params as select param_name, param_value from my_ext_table;

select pg_extension_config_dump('my_ext_table'::regclass, '');
select pg_extension_config_dump('my_ext_table_id_seq'::regclass, '');

Расширение, созданное в таком виде, после вставки строк в my_ext_table, заставит pg_dump будет не просто пересоздавать расширение (CREATE EXTENSION), но и выгружать табличные данные (COPY), а также восстанавливать значение последовательности (SETVAL):

db=# drop extension my_ext; 

DROP EXTENSION

db=# create extension my_ext;

CREATE EXTENSION

db=# \! pg_dump db|grep my_ext

-- Name: my_ext; Type: EXTENSION; Schema: -; Owner: 
CREATE EXTENSION IF NOT EXISTS my_ext WITH SCHEMA public;
-- Data for Name: my_ext_table; Type: TABLE DATA; Schema: public; Owner: postgres
COPY my_ext_table (id, param_name, param_value) FROM stdin;
-- Name: my_ext_table_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
SELECT pg_catalog.setval('my_ext_table_id_seq', 1, true);

Информация о том, данные каких таблиц расширения должен выгружать pg_dump, сохраняется в системном каталоге pg_extension. В столбце extconfig - список OID таблиц, а в extcondition - список предложений WHERE (2-й параметр функции):

db=# select * from pg_extension where extname = 'my_ext';

 extname | extowner | extnamespace | extrelocatable | extversion |   extconfig   | extcondition

---------+----------+--------------+----------------+------------+---------------+--------------

 my_ext  |       10 |         2200 | f              | 1.0        | {25108,25106} | {"",""}