Всем доброго дня и прекрасного настроения.
Я работаю в крупной торговой компании и поддерживаю принадлежащий компании интернет-магазин (ИМ).
Практически каждый час цены и другая информация в нашем ИМ должны обновляться. При этом из 1С цены и информация по товарам выгружается в неудобном формате с идентификаторами 1С. Данная информация выгружается в большом объеме и требует серьезной предварительной обработки. Соответственно когда мы с командой реализовывали этот проект, была выявлена проблема большой загруженности сервера сайта при обработке выгрузок из 1С. Страдала производительность сайта и соответственно качество обслуживания клиентов.
Было принято решение обрабатывать данные из 1С с помощью процедур plpgsql в базе Postgresql на отдельном сервере в специальной базе-обработчике, а затем передавать обработанные данные выгрузки из 1С на сервер сайта. При подготовке данных в этой базе-обработчике я использую внешние таблицы (foreign tables) из модуля расширения postgres_fdw.
Версия PostgreSQL - 9.3.1, версия postgres_fdw - 1.0
Примерный код создания одной из внешних таблиц ft_goods, позволяющей получить доступ к таблице goods на site_server:
CREATE SERVER site_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.0.100', dbname '#####', port '#####');
create user mapping for public server site_server OPTIONS (user '#####', password '#####');
CREATE FOREIGN TABLE ft_goods
(
id integer,
name character varying(300),
code character varying(20),
clear_code character varying(20),
brand_id integer,
description character varying(1000))
SERVER site_server OPTIONS (schema_name 'public', table_name 'goods');
Недавно возникла задача по реализации быстрой смены в базе-обработчике адреса сервера сайта (для возможности горячего переключения).
Вначале показалось, что это достаточно просто. Не нужно пересоздавать все внешние таблицы. Достаточно выполнить 2 простые команды
ALTER SERVER site_server OPTIONS (set host 'new IP address',set dbname 'new dbname',set port 'new port');
ALTER USER MAPPING FOR public SERVER site_server OPTIONS (set user 'new user name', set password 'new password');
Тем самым мы меняем настройки соединения сервера для внешних таблиц. Все так и было сделано, но не тут-то было.
Внешние таблицы, несмотря на смены настроек, упорно были привязаны к прежнему серверу.
Запрос вида
select * from ft_goods
возвращал данные со старого сервера привязки, команда ALTER SERVER не произвела никакого эффекта.
Возникли следующие пути решения задачи:
1. сменить у всех внеших таблиц SERVER
Выяснилось, что не существует команды
ALTER FOREIGN TABLE ft_goods
SET SERVER new_site_server;
2. пересоздать объект сервер (CREATE SERVER) и пересоздать все внешние таблицы (CREATE FOREIGN TABLE)
Это неудобное решение поскольку нужно каждый раз при смене основного site_server на new_site_server (настройка в таблице параметров соединения с сервером назначения) выполнять скрипт пересоздания всех этих внешних объектов. Скрипт должен содержать
все внешние таблицы, иначе при дальнейшей работе будут ошибки.
Гораздо удобнее было бы найти в системных таблицах связь между объектом SERVER и foreign tables и сменить привязку. Я стал исследовать эту возможность и нашел нужные системные таблицы pg_foreign_server, pg_foreign_table.
Посмотреть информацию по объектам SERVER можно запросом:
select oid,* from pg_foreign_server
Привязка внешней таблицы к серверу выявляется другим запросом:
select * from pg_foreign_table
Столбец ft_server - это как раз oid актуального сервера из таблицы pg_foreign_server.
Для решения проблемы я принял решение создавать новый объект SERVER и привязывать к нему существующие внешние таблицы через обновление столбца ft_server в pg_foreign_table.
--создаем новый объект
CREATE SERVER new_site_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'new IP address', dbname 'new dbname', port 'new port');
create user mapping for public server new_site_server OPTIONS (user 'new user name', password 'new password');
--теперь все таблицы привязываем к новому серверу
update pg_foreign_table set ftserver=(select oid from pg_foreign_server where srvname='new_site_server' limit 1);
После этих манипуляций стало возможно переключать внешние таблицы на другой сервер БД и соответственно быстро осуществлять выгрузку данных на новый сервер баз данных для сайта.
