Материал подойдет для студентов и тех кто только начинает создавать распределенные базы данных.
Всем доброго дня, дорогие Хабровчане! Решил поделиться созданием связаннх серверов, поскольку информации в интеренете много, но не везде описываются все мелочи.
И так нам понадобится:
SQL Server Management Studio (SSMS)
pgAdmin
ODBC Driver
Основная база данных у меня от MS (Microsoft) поэтому связанный сервер я буду учинять в SQL Server Management Studio (SSMS).
Шаг 1. Создание суперпользователя
И так вопервых нам необходимо создать в нашем любимом pgAdmin суперпользователя, обязательно с паролем, иначе будите получать ошибки всюду про отсутвие пароля.
Правой кнопочкой красиво, как на рисунке 1 создаем нашего пользователя.
Указываем имя, тут какое вам нравится больше на ваш вкус, как на Рисунке 2.
Обязательно указываем пароль, рисунок 3.
Ну и конечно же не забываем про привелегии. Поскольку он у нас супер - везде Yes.
Шаг 2. Настройка источника данных в ODBC Driver
Ну первым делом вам нужно будет скачать сам ODBC Driver, выбираем любой версии, все они работают, лично проверял:
В данной статье использую эту версию, см. рисунок 5.
Открываем наш скаченный и установленный ODBC Driver и переходим во вкладочку «Системный DSN» см. рисунок 6. (Именно системный, для связанного сервера. Нужно что бы сервер был доступен для всех, иначе будете получать ошибки при некоторых запросах).
Тут у вас буде пусто, у меня уже есть два источника данных, так что пустоту в вашем списке внимание не обращайте. Жмякаем «Добавить».
Выбираем Postgre Unicod(x64) как на рисуночке 7. Драйвер Unicode предназначен для современных приложений с более широкими наборами символов и для баз данных Postgresql, закодированных с помощью UTF-8/Unicode. Так что обращаем внимание на кодировочку, иначе будут ошибки.
И так у нас появляется форма ввода как на рисунке 8. Тут у нас следующие поля:
Data Sourse — Тут указываем как будет называться наш источник данных (как нравится — так и называйте);
Database — Тут мы пишем название базы данных к которой мы хотим присоединиться в Postgres. В моем случа база данных, которая находится в pgAdmin называется BDLinkedS;
Server — Я указывать буду «localhost» поскольку у меня две БД и они же на одном и том же компьютере.
User name — тут имя нашего супер пользователя, которого мы создавали в Postgre.
Description — описание источника данных, тут я обычно ничего не пишу. Можете написать описание источника, по крайней мере я никогда не получал ошибок из‑за этого поля, так что тут без подводных камней.
SSL Mode — Тут нужно отключить проверку сертификата, т. е. ставим режим «disable»
Port — тут порт нашего Postgre. При установке по умолчанию стоит 5432.
-
Password — пароль нашего суперпользователя.
Рисунок 8 Нажимаем кнопку Test и радуемся успешному соединению. Рисунок 9.
Рисунок 9
Но это еще не все. Вы можете получать ошибки при некоторых запросах. Они связанных с типами данных их длинной и разного рода ошибки преобразования. Причины их — не правильно созданный источник данных. Обратите внимание, что в настройках есть еще кнопка Datasourse, рисунок 10.
Жмякаем и откроется окно дополнительных параметров для источника данных. Здесь вы можете указать, как DSN будет обрабатывать данные, получаемые из источника. Рисунок 11.
Тут можете указать, как драйвер будет обрабатывать определенные типы данных. Для этого необходимо снять флажок «Bools as Char» в параметрах типа данных и рассматривать текст как LongVarChar. Значения Max Varchar и MaxLongVarChar можно оставить по умолчанию. Но если у вас возникли проблемы с длиной типов данных varchar, вы можете изменить их.
Шаг 3. Создаем связанный сервер на MSSQL
Идем в MS SQL Management Studio и там во вкладке «Обькты сервера» жмякаем правой кнопкой по папке «Связанные серверы» и создаем наш связанный сервер. См. рисунок 11.
При создании указываем в поле 1 — название какое будет у нашего связанного сервера. Далее выбираем другой источник данный и из выпадающего списка (на рис 12 цифра 2) выбираем ODBC Drivers. Название продукта (поле 3 на рисунке 12) указываем как называется продукт, тут пишем название какое вам нравится. И последнее поле 4 или источник данных указываем название наших данных, которое мы заполняли на шаге 2., берем из поля Data Sourse.
Теперь тут же идем на вкладку безопасность и указываем там логи и пароль нашего суперпользователя. Рисунок 13.
Жмякаем ОК. Если все сделано по инструкции ошибок не выйдет. В нашей папке теперь появился связанный сервер рисунок 14. Жмякнем правой клавишей по нему и выберем «Проверка соединения». Если все ОК — получите сообщение, что соединение установленно успешно. Все теперь мы может отправлять распределенные запросы из MSSQL в нашу базу данных на Postgre.
Написав запрос из MSSQL к базе данных в Postgre я получаю ответ в сам MSSQL, рисунок 15. Ну и на этом моменте все, а распределнные запросы и как их правильно писать это уже другая история.
Усли есть необходимость вызова хранимых процедур на связанном сервере то обратите внимание на RPC (не путать с РПЦ) тут флажки должны быть True, рисунок 16.
Еще немножечко про настройки (рисунок 17.1 и 17.2):
P. S. Если статья полезна, я очень рад, если есть ошибки или нашли, что я где‑то написал что‑то не то — буду очень рад комментам. Если надо исправить граматические ошибки — исправлять не буду, читайте как написанно, главное смыл же понятен и БДшки работают.
Комментарии (19)
ptr128
27.03.2024 10:21Эта конструкция работает очень медленно и не стабильно. Для продуктивного использования подходит только через RPC. Для больших объемов данных приходится доставать левой ногой правое ухо. Передавать с MS SQL данные можно в JSON параметрах процедур или через FDW на стороне PostgreSQL. Обратно - заливая их bcp в глобальную временную таблицу. Вариант через Питон и sp_execute_external_script проще, но менее производительный.
В итоге перешли на интеграцию сервисами и Кафкой.
Vladmk
27.03.2024 10:21Используем линкованные сервера в связке с PostgreSQL на ежедневной основе в качестве основного механизма репликации данных с MS SQL уже два года, полет нормальный. Записей достаточно много (десятки миллионов). Механизм очень удобный, особенно при необходимости внесения изменений в большое количество записей одновременно на MS SQL и PostgreSQL. update работает стабильно, insert приходится разбивать.
ptr128
27.03.2024 10:21+1Вы меня очень удивили. Давно это было, так что пришлось проверять заново. MS SQL и PostgreSQL взял на одинаковых 32-х ядерных виртуалках с 256 ГБ RAM. Взял всего миллион строк, а не десятки миллионов, как у Вас.
DECLARE @sql_str nvarchar(max), @proxy_account sysname='ssrs_proxy', @proxy_password sysname='************', @start_time datetime, @ms_sql_time_ms int SELECT @sql_str=' DROP TABLE IF EXISTS ##test_'+CONVERT(nvarchar(max),@@SPID)+' CREATE TABLE ##test_'+CONVERT(nvarchar(max),@@SPID)+' ( ID int NOT NULL, FromId varchar(9) NOT NULL, ToId varchar(9) NOT NULL, Std float(53) NOT NULL, Forecast float(53) NOT NULL )' EXEC (@sql_str) SELECT @sql_str=' DROP TABLE IF EXISTS _test0; CREATE TABLE _test0 AS SELECT G.n AS ID, (G.n+1)::text AS FromId, (G.n+10)::text AS ToId, G.n::double precision/3 AS Std, G.n::double precision/7 AS Forecast FROM generate_series(1,999999) G(n);' EXEC (@sql_str) AT ELIS_DELTA SELECT @sql_str=' INSERT INTO ##test_'+CONVERT(nvarchar(max),@@SPID) +' (Id, FromId, ToId, Std, Forecast) SELECT Id, FromId, ToId, Std, Forecast FROM ELIS_DELTA.elis_delta.[public]._test0' SELECT @start_time=CURRENT_TIMESTAMP EXEC (@sql_str) SELECT @ms_sql_time_ms=DATEDIFF(ms,@start_time,CURRENT_TIMESTAMP) SELECT @sql_str=' COPY ( SELECT Id, FromId, ToId, Std, Forecast FROM _test0 ) TO PROGRAM $pgm$ EXIT_STATUS=0; tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); ' +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##test_'+CONVERT(nvarchar(max),@@SPID) +''' in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')+' -U '+@proxy_account+' -P '''+@proxy_password +''' -c -b 10000000 -a 65535 || EXIT_STATUS=$?; ' +'rm $tmp_file; exit $EXIT_STATUS $pgm$ NULL $nil$$nil$;' SELECT @start_time=CURRENT_TIMESTAMP EXEC (@sql_str) AT ELIS_DELTA SELECT @ms_sql_time_ms AS MSSQL, DATEDIFF(ms,@start_time,CURRENT_TIMESTAMP) AS PGSQL, CONVERT(float,@ms_sql_time_ms) /DATEDIFF(ms,@start_time,CURRENT_TIMESTAMP) AS Ratio -- 23917 3213 7.443821973233738Разница почти в 7.5 раз, даже не смотря на издержки преобразования вывода в текстовый формат BCP и закачки его через bash.
Как Вы с этим живете на десятках миллионах записей, если даже выборка миллиона весьма коротких строк заняла 24 секунды на довольно мощных серверах?
BlackSN Автор
27.03.2024 10:21Тут надо отметить, что вариант подключения не продовский, а для начинающих. Большие данные Postrgre и MS SQL дата инженеры не юзают, по крайней мере у нас. Но кстати как вариант хорошее подспорье для улучшения и разработки своего драйвера. Если сделать норм - будет мощно!
Vladmk
27.03.2024 10:21Для наших целей в целом подходит. Репликация 1 раз в час, среднее время выполнения представленного ниже запроса не больше одной минуты.
update a set ... from [Postgres].[db1].[public].[table1] a -- 1 000 000 записей
inner join [db2].[dbo].[table2] b -- 1 500 000 записей
on a.field = b.field and ...не быстро конечно, но в нашем случае очень даже удобно )
ptr128
27.03.2024 10:21не больше одной минуты
Вам не кажется, что минута потенциального ожидания в блокировке других процессов - это очень много?
-- 1 000 000 записей
Записей достаточно много (десятки миллионов)
Чему из этого верить?
не быстро конечно, но в нашем случае очень даже удобно )
Догадайтесь с трех раз, что скажет лид разработчику, если тот так будет защищать свое решение в PR )
Vladmk
27.03.2024 10:21Postgres вроде бы не блокирует записи при обновлении (мы пока с блокировками по вине репликации не сталкивались) . Десятки миллионов находятся в таблицах куда идет вставка записей, но по вставке обычно за раз тысячи записей или десятки тысяч (зависит от обновления записей в источнике). Что касается защиты, то тут вопрос баланса (как с нормализацией, монолитом и т.д.). В данном случае чем сложнее решение, тем больше вероятность различных внештатных ситуаций. Линкованные сервера для нас привычны. Больше десятка SQL серверов, сотни различных БД, терабайты данных, транзакционная и снимочная репликации между всеми серверами, плюс линкованные сервера. Все стараемся применять по месту. Но мы свой подход никому не навязываем )
ptr128
27.03.2024 10:21Postgres вроде бы не блокирует записи при обновлении
Вы заблуждаетесь. При конкурентном обновлении записей в таблице даже deadlock можно схлопотать.
по вставке обычно за раз тысячи записей или десятки тысяч
Это действительно очень мало. У нас есть топики, по которым несколько сотен миллионов "записей" за сутки пролетает.
Больше десятка SQL серверов, сотни различных БД, терабайты данных, транзакционная и снимочная репликации между всеми серверами
Исходя из этого Вам все равно потребуется уходить от PostgreSQL ODBC + MS SQL Linked Server при росте объемов данных. Причем очень скоро.
Vladmk
27.03.2024 10:21Обновление данных у нас по принципу однонаправленной репликации (SQL->PG, PG->SQL), поэтому deadlock вряд ли получим. Задача максимум от MS SQL Server отказаться полностью )
BlackSN Автор
27.03.2024 10:21Да, согласен. Статья для начинающих, не для коммерческого прода, вначале указал это. Тут статья подготовлена для CRUD приложухи с распред базой, во многих универах требуют. Так что статья больше студенческая, нежели для профи.
BlackSN Автор
27.03.2024 10:21Да, согласен. Но статья студенческая, для прода не походит. Во многих универах требуются, вначале статьи указывал. Сам был студентом, стучался в свое время, но вот решил поделиться опытом.
bot1no4ek
27.03.2024 10:21А вы вычитывали статью перед публикацией? А то в глаза бросаются явные ошибки
Vladmk
При использовании линкованных серверов MS SQL для работы с PostgreSQL столкнулись с любопытной проблемой. Если через линкованный сервер в PostgreSQL вставлять большое количество записей (100 тысяч и больше) одним запросом в стиле 'insert into ... select ... from ...', то часть записей до адресата не доходит. И ошибок при этом не возникает. Если делить вставку на блоки, то все записи вставляются. При использовании линкованных серверов для связи между разными экземплярами MS SQL таких проблем не возникает при любых объемах.
BlackSN Автор
Там есть батчи (информация отправляется частями - батчами) в настройках, возможно дело в них. А вообще связанные серверы плохо работают с большим количеством данных и не очень эффективны. Поэтому, да с Вами полностью согласен, проблема актуальная.