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

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

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

Пристегнитесь и погнали, да пребудет с вами декларативный дух!

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

Инструкция составлялась на основании экспериментов с Postgres Pro 1c (17.4) на Linux Ubuntu 24.04.2 LTS и Microsoft SQL Server 2019 Developer Edition (15.0.4455.2) на Windows 11 Enterprise (10.0.26200.7840), но будет работать на любых MS и PG SQL-серверах и операционных системах.

Подразумевается, что у вас Windows, Linux, MSSQL 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» или что-либо другое)

Для взаимодействия с внешним сервером MSSQL со стороны PostgreSQL надо создать "прилинкованный" сервер. Поможет нам в этом расширение «tds_fdw». Но так как это расширение НЕ входит в дефолтный пакет при установке Постгреса, то нам придётся собрать расширение из исходников.

Погодите, не разбегайтесь, это не так страшно и для данного конкретного расширения совсем уж НЕ сложно! Серверные расширения можно установить и с помощью отдельного пакета *-contrib, но мы же не ищем лёгких путей, поэтому возьмём свежачок из репозитория.

«tds_fdw» - опенсорсное расширение, исходный код которого расположен на гитхабе . Выполним несколько шагов:

Установим пакеты для сборки:

sudo apt install gnupg gcc make

Установим библиотеки для взаимодействия с  MSSQL:

sudo apt install libsybdb5 freetds-dev freetds-common

Установим GIT:

sudo apt install git

Склонируем репозиторий:

git clone https://github.com/tds-fdw/tds_fdw.git

Перейдём в каталог с исходниками:

cd tds_fdw

Выполним сборку:

make USE_PGXS=1

Выполним установку:

sudo make USE_PGXS=1 install

Теперь у Постгреса есть расширение «tds_fdw».

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

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

CREATE EXTENSION IF NOT EXISTS tds_fdw;

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

CREATE SERVER IF NOT EXISTS mssql
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername '192.168.0.111', port '1433', database 'ms_files', tds_version '7.1');

(здесь «mssql» - назначаемое имя создаваемого внешнего (прилинкованного) сервера; значения '192.168.0.111', '1433', 'ms_files' - это IP/имя сервера MSSQL, его порт и имя базы соответственно, их нужно заменить на свои значения)

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

CREATE USER MAPPING FOR postgres
SERVER mssql
OPTIONS (username 'sa', password 'YOUR_SECRET_PASSWORD');

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

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

GRANT USAGE ON FOREIGN SERVER mssql TO postgres;

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

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

CREATE FOREIGN TABLE IF NOT EXISTS ms_data (
	_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 mssql
OPTIONS (table_name 'dbo._InfoRg4321', row_estimate_method 'showplan_all');

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

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

Можно выполнить запрос (SELECT * FROM ms_data LIMIT 0;) и убедиться, что вы движетесь в правильном направлении, если запрос отработал без ошибок.

Если на данном этапе в текущей базе «current_db» на Постгресе ещё не существуют таблицы для переноса данных, то создадим их:

CREATE TABLE IF NOT EXISTS public.pg_data (
	id bytea NOT NULL, 
	file bytea NOT NULL, 
	descr mvarchar(36) NOT NULL, 
	CONSTRAINT pg_data_pkey PRIMARY KEY (id) 
);

COMMENT ON TABLE public.pg_data IS 'Хранилище файлов';
COMMENT ON COLUMN public.pg_data.id IS 'Идентификатор';
COMMENT ON COLUMN public.pg_data.file IS 'Файл';
COMMENT ON COLUMN public.pg_data.descr IS 'Описание';

CREATE UNIQUE INDEX pg_data_idx ON public.pg_data (id);
ALTER TABLE public.pg_data CLUSTER ON pg_data_idx;

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

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

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

sudo su - postgres

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

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

(здесь «current_db» - имя текущей базы, «ms_data» - имя "прилинкованной" внешней таблицы, «pg_data» - имя результирующей локальной таблицы)

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

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

P.S. Операции вставки, обновления и удаления НЕ будут работать со сторонними таблицами.

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

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

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