Инструкция по переносу данных между базами PostgreSQL

Всем реляционный привет!

Решил написать небольшой гайд о том, как скопировать данные таблиц между различными базами Постгреса, расположенными как на одном сервере, так и на разных серверах. Картинок здесь будет мало, т.к. весь процесс проходит в чёрной консоли (если у вас не чёрная, напишите про это в комментах).

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

Кто работает с Микрософтовым SQL, те знают, что там достаточно легко можно скопировать данные одной базы в другую, расположенную на этом же сервере через конструкцию, типа

INSERT INTO dst_table
(field1, field2, ..., fieldX)
SELECT field01, field02, ..., field0X
FROM other_db.dbo.src_table
...

Но Постгрес устроен по-другому, и в определенный момент времени вы работаете только с одной конкретной базой. Поэтому через "INSERT INTO SELECT" данные между базами НЕ перенесутся. Для этого надо создать "прилинкованный" сервер. Поможет нам в этом расширение "postgres_fdw".

Инструкция составлялась на основании экспериментов с Postgres Pro 1c (17.4), а также Linux Ubuntu 24.04.2 (но будет работать на любом постгресе и лине)

Подразумевается, что у вас Linux и PostgreSQL установлены без каких-либо кастомизированных настроек.

Для начала потребуется установить на клиентскую машину (с которой вы будете взаимодействовать с серверами СУБД) клиентский софт для подключения к серверу постгреса (в т.ч. утилиту psql):

sudo apt install postgrespro-1c-17-client

а также дополнительное ПО (в т.ч. утилиту pg_config):

sudo apt install postgrespro-1c-17-dev

Далее необходимо создать символическую ссылку:

sudo ln -s /opt/pgpro/1c-17/bin/pg_config /usr/bin/pg_config

(обратите внимание, что у вас вместо «1c-17» может быть «std-17» или «ent-17» или что-либо другое)

Далее выполняем скрипты в PostgreSQL (можно в любом менеджере, например, PGAdmin, DBeaver, <ПодставьСвоё> или в консольной утилите psql для текущей БД «current_db», в которую будут переноситься данные).

Подключим расширение:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Создадим внешний сервер:

CREATE SERVER IF NOT EXISTS pg_outer
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'my_db', host '127.0.0.1', port '5432');

(здесь «pg_outer» - имя создаваемого внешнего (прилинкованного) сервера, «my_db» - имя базы на внешнем или текущем сервере PostgreSQL, из которой будут копироваться данные; host и port для текущего сервера можно не прописывать)

Создадим сопоставление пользователя:

CREATE USER MAPPING IF NOT EXISTS FOR postgres
SERVER pg_outer 
OPTIONS (user 'postgres_outer', password 'YOUR_SECRET_PASSWORD');

(здесь мы связали пользователя postgres текущей базы с пользователем postgres_outer внешней базы)

Дадим права пользователю на доступ к данным на внешнем сервере:

GRANT USAGE ON FOREIGN SERVER pg_outer TO postgres;

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

Создадим внешние таблицы:

CREATE FOREIGN TABLE IF NOT EXISTS data_files (
_fld6789rref bytea NOT NULL,
_fld6790 numeric(10,0) NOT NULL,
_fld16791 timestamp without time zone NOT NULL,
_fld6792 timestamp without time zone NOT NULL,
_fld6793 numeric(16,0) NOT NULL,
_fld16246rref bytea NOT NULL,
_fld6794 bytea NOT NULL,
_fld7889 mvarchar(36) NOT NULL)
SERVER pg_outer
OPTIONS (schema_name 'public', table_name '_inforg4321');

(здесь приведён пример для одной таблицы, но вам надо создать связи для всех таблиц, из которых вы собираетесь копировать данные; каждая таблица создаётся по структуре такой же, как и исходная во внешней базе; «data_files» - имя таблицы, по которому мы будем обращаться к внешней таблице для чтения данных)

* здесь используется постгреспрошный тип данных mvarchar - чтобы его заюзать, установите расширение mchar (CREATE EXTENSION IF NOT EXISTS mchar;)

На этом шаге у нас завершаются подготовительные работы. Теперь можно перейти непосредственно к самому переносу данных из одной базы в другую.

Возвращаемся в терминал Linux

Переключимся на пользователя postgres:

sudo su - postgres

Теперь мы должны создать в текущей базе таблицы (если таковых ещё нет), куда мы будем копировать данные, например:

psql -d current_db -c 'CREATE TABLE IF NOT EXISTS files ( id bytea NOT NULL, file bytea NOT NULL, descr mvarchar(36) NOT NULL, CONSTRAINT files_pkey PRIMARY KEY (id) )'

Ну и наконец, финальный шаг, копирование данных:

psql -d current_db -c 'copy (select _fld6789rref, _fld6794, _fld7889 from data_files) to stdout;' | psql -d current_db -c 'copy files (id, file, descr) from stdin;'

Копирование идёт достаточно быстро, т.к. осуществляется через конвейер. Через некоторое время копирование выполнится с выводом результата о количестве скопированных записей.

На этом перенос завершён!

Если контент вам зашёл, не стесняйтесь голосовать. Так я увижу вашу заинтересованность и буду писать исчо. Например, подобную инструкцию по переносу из MSSQL в PostgreSQL .

Не забывайте навещать мой гитхаб

Всем быстрых запросов!