Расширение Part_Import: частичный импорт файлов данных из внешней базы данных

О расширении Part_Import

Данное расширение содержит две функции:

partial_import(строка_подключения, имена_таблиц) — основная функция расширения, которая служит для копирования файлов, содержащих данные таблиц, из внешней базы данных в локальную.
Под таблицами локальной базы данных подразумеваются таблицы, доступные в текущем сеансе подключения, под таблицами внешней — те, доступ к которым мы получаем, воспользовавшись строкой_подключения. Имена таблиц для копирования передаются в параметре имена_таблиц. Этот параметр имеет тип строкового массива, где каждая строка содержит имя одной таблицы. Имя может быть как полным — т. е. включать пространство имен, так и неполным — т.е. без пространства имен. В последнем случае сервер должен понимать по неполному имени, о какой конкретно таблице идет речь.
table_script(oid) — функция, возвращающая SQL-скрипт, позволяющий создать таблицу, OID которой передан в параметре. Скрипт содержит индексы, внешние индексы, ограничения, последовательности, комментарии.

partial_import

Особенности работы частичного импорта:

Блокировки на внешние таблицы не накладываются, блокировки на локальные таблицы накладываются только перед переключением на "новые" файлы с данными, и блокировки предельно короткие. Перенесённые данные нескольких таблиц согласованы между собой.
Поскольку копируются файлы с данными - импорт заботится, чтобы эти файлы были корректными, для чего сверяет метаданные таблиц внешней и локальной БД. В процессе сверки убеждаемся, что названия, типы и порядок полей, включая удалённые колонки - должны полностью совпадать. Должны совпадать ограничения колонок, тип хранения колонки. TOAST-таблицы должны быть либо у обеих таблиц, либо ни у одной из них. Должно совпадать количество индексов, их наименования и состав колонок. Не имеет значение, если не совпадает табличное пространство. Допустим импорт материализованных табличных представлений, не должно быть проблем с переносом партиций.
В случае наличия ограничения внешнего ключа будут предложены скрипты по их удалению в локальной БД и восстановлению по окончании импорта. Если при импорте будет переноситься не только таблица с внешним ключом, но и таблица, на которую внешний ключ ссылается - никаких ограничений не возникнет, при этом данные будут согласованы.
Ввиду отсутствия блокировок внешних таблиц теоретически возможно выполнение операций DDL с внешней таблицей в период между завершением сверки метаданных и началом переноса файлов с данными таблиц. В этом случае результат работы непредсказуем.
В процессе работы переносятся только файлы с данными, поэтому рекомендуется пересобрать статистику по импортированными таблицам по окончанию работы импорта.
Хотя импорт и инициирует создание контрольной точки (checkpoint) на локальной БД по окончанию своей работы, теоретически возможно аварийное завершение работы БД между моментами переключения на новые файлы и завершением создания контрольной точки - в этом случае процедура аварийного восстановления с данными импортированных таблиц может дать непредсказуемые результаты.

Функция является адаптацией кода утилиты pg_dump. Взята часть, отвечающая исключительно за генерацию скриптов таблиц, индексов, ограничений и последовательностей. Вся остальная функциональность, посвященная подготовке содержимого таблиц к выгрузке, и сама выгрузка отброшены.

Тестовые сценарии

partial_import

Тестировать расширение можно и в рамках одного экземпляра базы данных, но полноценное тестирование следует проводить на разных экземплярах базы данных, причем в качестве внешней выбрать ту, в которой работа уже ведется продолжительное время. Последнее можно проверить, зайдя в папку с базой данных и посмотрев количество и размер файлов в подпапке pg_xact: если файлов несколько, значит, работа в базе ведется какое-то заметное время. Экземпляр с локальной базой (т. е. той, в которую следует производить импорт) лучше создать с нуля.

В локальной базе должно быть установлено расширение part_import, во внешней достаточно наличия правильных бинарных файлов нужной версии. Установить расширение можно командой:

CREATE EXTENSION part_import;

Теперь во внешней базе данных нужно создать таблицы и заполнить их данными. Для этого создадим три таблицы: type_a и type_b как обычные словари и еще одну таблицу для тестирования TOAST и сохранения работоспособности таблицы с HOT- оптимизацией. После первого этапа будет добавлена четвертая таблица many2many_ab как «точка пересечения» таблиц type_a и type_b (эмуляция отношения «многие ко многим»). Для создания таблиц type_a, type_b и accounts воспользуемся следующим скриптом (создавать таблицы следует и на внешнем и на локальном серверах баз данных):

CREATE TABLE public.type_a (
    id integer NOT NULL,
    type_id smallint NOT NULL,
    name_1 character varying(40) NOT NULL,
    date_ins date,
    name_hash_1 integer NOT NULL
);
ALTER TABLE ONLY public.type_a
    ADD CONSTRAINT pk_type_a PRIMARY KEY (id);
CREATE INDEX idx_type_a_name_1 ON public.type_a USING btree (name_1);
CREATE INDEX idx_type_a_name_hash_1 ON public.type_a USING btree (name_hash_1);

CREATE TABLE public.type_b (
    id integer NOT NULL,
    name character varying(40) NOT NULL,
    date_ins date,
    name_hash integer NOT NULL
);
ALTER TABLE ONLY public.type_b
    ADD CONSTRAINT pk_type_b PRIMARY KEY (id);
CREATE INDEX idx_type_b_name ON public.type_b USING btree (name);
CREATE INDEX idx_type_b_name_hash ON public.type_b USING btree (name_hash);

CREATE TABLE public.accounts (
    id integer NOT NULL,
    number text,
    client text,
    amount numeric,
    CONSTRAINT amount_is_positive CHECK ((amount >= 0.0))
);
ALTER TABLE ONLY public.accounts ALTER COLUMN number SET STORAGE EXTERNAL;
ALTER TABLE public.accounts ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.accounts_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);
ALTER TABLE ONLY public.accounts
    ADD CONSTRAINT accounts_number_key UNIQUE (number);
ALTER TABLE ONLY public.accounts
    ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);

Далее следует заполнить таблицы внешней базы данных данными. В случае таблиц type_a и type_b это могут быть практически любые данные, желательно только, чтобы параметр id был линейным и начинался со значения 1, а количества данных было достаточно для того, чтобы файлов хранения было несколько сегментов хотя бы у одной из таблиц. Примерная оценка объема данных для таблицы type_a — 10 миллионов строк. Для таблицы type_b вполне достаточно 10 тысяч строк. Таблицу accounts нужно заполнить так, чтобы строки обязательно попали в TOAST- таблицу. По количеству строк особых требований нет, так как по этому параметру достаточно тестирования таблицы type_a. Для заполнения и изменения данных таблицы accounts воспользуемся следующим скриптом (по мотивам статьи Егора Рогова):

INSERT INTO accounts(id, "number", client, amount) VALUES(1, 1000, 'masha', 1000.),
    (2, 2001, 'vasya', 2000.), (3, 2002, 'misha', 1200.), (4, 2003, 'irina', 1300.);
UPDATE accounts SET client = (
  SELECT string_agg( chr(trunc(65+random()*26)::integer), '') FROM generate_series(1,2000)
);
UPDATE accounts SET amount = amount + 100 + id;
UPDATE accounts SET amount = amount + 100 + id;
UPDATE accounts SET amount = amount + 100 + id;
UPDATE accounts SET amount = amount + 100 + id;

После этого в таблице accounts должны появиться 4 (видимые) строки. Последние четыре команды UPDATE не меняют данные в индексируемых столбцах, поэтому должна быть задействована оптимизация HOT-обновления.

Теперь импортируем данные таблиц type_a, type_b и accounts на локальный сервер, для чего выполним команду:

SELECT partial_import('host=<external_db_adderss> user=<external_db_user> dbname=<external_db_name>', ARRAY['type_a', 'type_b', 'accounts']::text[]);

Первый параметр — это строка соединения с внешней базой данных. С этими параметрами у пользователя должно быть достаточно прав для чтения системных каталогов и выполнения команды BASE_BACKUP.

По окончании работы функции в локальной базе данных заполнение таблиц type_a, type_b и accounts должно совпадать с таковыми во внешней базе. Также следует убедиться, что запросы:

EXPLAIN ANALYZE SELECT * FROM type_a WHERE id = 7;
EXPLAIN ANALYZE SELECT * FROM type_b WHERE id = 7;
EXPLAIN ANALYZE SELECT * FROM accounts WHERE id = 2;

строят индексный план извлечения данных, например, такой:

Index Scan using pk_type_a on type_a  (cost=0.42..8.44 rows=1 width=55) (actual time=0.019..0.020 rows=1 loops=1)
  Index Cond: (id = 7)
Planning Time: 0.057 ms
Execution Time: 0.032 ms

а запросы:

SELECT * FROM type_a WHERE id = 7;
SELECT * FROM type_b WHERE id = 7;
SELECT * FROM accounts WHERE id = 2;

действительно извлекают данные (подразумевается, что строки с указанными значениями id действительно существуют).

Описанный сценарий можно расширить следующим образом:

Заполнить таблицы type_a, type_b и accounts, как описано выше;
Запомнить количество строк каждой из таблиц;
Запустить скрипт дополнительного заполнения данными таблицы type_a или type_b и параллельно запустить импорт;
По окончании работы импорта убедиться, что количество строк в таблице, в которую параллельно заливались данные, больше, чем на начало импорта (в теории это должно быть количество на момент LSN-окончания снимка, но точный подсчет затруднен).

Создадим во внешней и локальной базах данных таблицу many2many_ab с помощью следующего скрипта:

CREATE TABLE public.many2many_ab (
    ida integer NOT NULL,
    idb integer NOT NULL
);
ALTER TABLE ONLY public.many2many_ab
    ADD CONSTRAINT pk_many2many_ab PRIMARY KEY (ida, idb);
CREATE INDEX many2many_ab_idb ON public.many2many_ab USING btree (idb);
ALTER TABLE ONLY public.many2many_ab
    ADD CONSTRAINT fk_net_ab_type_a_id FOREIGN KEY (ida) REFERENCES public.type_a(id);
ALTER TABLE ONLY public.many2many_ab
    ADD CONSTRAINT fk_net_ab_type_b_id FOREIGN KEY (idb) REFERENCES public.type_b(id);

При попытке выполнить предыдущую команду частичного импорта, а именно:

SELECT partial_import('host=<external_db_adderss> user=<external_db_user> dbname=<external_db_name>', ARRAY['type_a', 'type_b', 'accounts']::text[]);

мы должны получить сообщение об ошибке:

ERROR:  Input tables list refer to 2 foreign keys, which not import from remote server.
For guarantee reference integrity you must drop this foreign keys before running
partial import and recreate it when done in an "not valid" state, and than try validate.
The scripts below will help you perform the described actions.
-- ОШИБКА: Список вводимых таблиц ссылается на 2 внешних ключа, которые не были импортированы с внешнего сервера.
-- Для гарантии ссылочной целостности вам следует удалить эти внешние ключи, прежде
-- чем запустить частичный импорт, и воссоздать их в состоянии «недопустимые», а затем попробовать их валидировать.
-- Следующие далее скрипты помогут вам выполнить описанные действия.

BEGIN;
  -- Этот код удалит все мешающие внешние ключи --
  ALTER TABLE IF EXISTS ONLY public.many2many_ab DROP CONSTRAINT IF EXISTS fk_net_ab_type_a_id;
  ALTER TABLE IF EXISTS ONLY public.many2many_ab DROP CONSTRAINT IF EXISTS fk_net_ab_type_b_id;
END;

------------------------------------------------------------------------------------
---- Здесь вы должны запустить процедуру "partial_import" с теми же параметрами, ----
---- что и в предыдущий раз.                                                   ----
------------------------------------------------------------------------------------

BEGIN;
  -- Этот код воссоздаст удаленные ранее внешние ключи в состоянии «недопустимые» --
  ALTER TABLE ONLY public.many2many_ab
    ADD CONSTRAINT fk_net_ab_type_a_id FOREIGN KEY (ida) REFERENCES type_a(id) NOT VALID;

  ALTER TABLE ONLY public.many2many_ab
    ADD CONSTRAINT fk_net_ab_type_b_id FOREIGN KEY (idb) REFERENCES type_b(id) NOT VALID;
END;

BEGIN;
  -- Этот код попытается валидировать удаленные, а затем воссозданные внешние ключи --
  ALTER TABLE IF EXISTS ONLY public.many2many_ab VALIDATE CONSTRAINT fk_net_ab_type_a_id;
  ALTER TABLE IF EXISTS ONLY public.many2many_ab VALIDATE CONSTRAINT fk_net_ab_type_b_id;
END;

SQL state: XX000

Сообщение об ошибке говорит нам, что у нас есть внешние ключи, целостность которых мы не можем гарантировать по окончании импорта, и предлагает последовательность действий, благодаря которым можно выполнить импорт, хотя и ценой потери временного контроля целостности ключа. Однако если мы добавим в импорт таблицу, ставшую причиной ошибки, то он отработает без ошибок:

SELECT partial_import('host=<external_db_adderss> user=<external_db_user> dbname=<external_db_name>', ARRAY['type_a', 'type_b', 'many2many_ab', 'accounts']::text[]);

Импорт устойчив к расхождениям физического хранения атрибутов, расхождениям в их количестве, их порядку, но завершается ошибкой на стадии верификации таблиц. Выполним на локальной базе данных следующий скрипт для таблицы accounts:

ALTER TABLE public.accounts ADD amount2 numeric;
ALTER TABLE public.accounts DROP COLUMN amount;
ALTER TABLE public.accounts RENAME COLUMN amount2 TO amount;
ALTER TABLE public.accounts ADD CONSTRAINT amount_is_positive CHECK ((amount >= 0.0));

После его выполнения декларативное объявление таблиц на внешнем и локальном серверах выглядят идентично, однако попытка выполнить команду импорта:

SELECT partial_import('host=<external_db_adderss> user=<external_db_user> dbname=<external_db_name>', ARRAY['accounts']::text[]);

приведет к ошибке:

ERROR:  Remote table public.accounts2 has 4 columns, but local one has 5. It is possible that some attributes in one or both of the tables have been deleted earlier.
-- ОШИБКА: У внешней таблицы public.accounts2 имеется 4 столбца, однако у локальной таблицы 5 столбцов. Возможно, некоторые атрибуты в одной или обеих таблицах ранее были удалены.
SQL state: XX000

Расширение Part_Import: частичный импорт файлов данных из внешней базы данных

О расширении Part_Import

partial_import

table_script

Тестовые сценарии

partial_import