Всем привет. На днях пришлось вспомнить магию Postgres, задача была решена, по результатам написал инструкцию в корпоративную базу знаний, чтобы в следующий раз не тратить время на. Думаю на Хабре от тоже будет польза.
Ниже речь будет идти о чтении данных одной БД из другой БД. В частности я решал такую проблему:
нашей Системе данные о пользователях записаны в одной БД, а данные об их торгах в другой, без дополнительных настроек Postgres не позволяет использовать в одном запросе данные из разных БД.
То есть запрос вида:
select a.id from auth.public.user a join trade.public.tenders t on a.id = t.user_id;
Вызовет ошибку "[0A000] ERROR: cross-database references are not implemented".
Что делать ?
Необходимо создать обёртку для внешней таблицы, о том как это сделать читайте ниже.
Настраиваем внешнюю БД
Подключаемся к СУБД под супер пользователем postgres, подключаемся к БД, которая является внешней по отношению к БД из которой будем делать запрос.
docker exec -it postgres psql -U postgres \c trade
Создаём нужный нам источник данных, в нашем случае это представление
create or replace view tender_users AS select t.id_user from tender t group by id_user order by id_user ;
Даём права на созданный источник (да, права тотально на всё, у нас пока так можно)
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO trade;
Кроме того, это нелогичная команда, потому что она нужна даже если мы создаём представления под пользователем trade, но без неё не работает, выдаёт ошибку не достаточно прав (когда делаем запрос через FOREIGN DATA WRAPPER).
Настраиваем собственную БД
Переключаемся на БД из которой будем делать запросы
\c prod
Подключаем расширение для запросов во внешнюю БД
create extension postgres_fdw;
Проверяем что расширение включилось
\dx
Должны увидеть:
List of installed extensions Name | Version | Schema | Description --------------+---------+------------+---------------------------------------------------- plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0 | public | foreign-data wrapper for remote PostgreSQL servers (2 rows)
Нам нужна вторая строка, первая - "приблудная".
Создаём внешний сервер
CREATE SERVER tenders FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'trade', host 'postgres', port '5432');
Выдаём права на использование расширения и сервера
GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw to prod; GRANT USAGE ON FOREIGN SERVER tenders TO prod;
Теперь самое главное, задаём учётные данные для подключения к внешней БД
CREATE USER MAPPING for prod SERVER tenders OPTIONS (user 'trade', password '***');
Подключаем источник данных
Теперь можно в ручную создать внешние таблицы, можно автоматически. Как создать источник данных вручную можно почитать по ссылкам из списка литературы в конце статьи. Если автоматически, то:
IMPORT FOREIGN SCHEMA public LIMIT TO (tender_users) FROM SERVER tenders INTO public;
Проверяем, что данные доступны для чтения
prod=# select count(*) from tender_users; count ------- 0 (1 row)
Ошибок чтения нет, значит связь успешно установлена. Теперь можно использовать данные внешней таблицы в запросах к нашим внутренним таблицам.
Успех !
Postscriptum
Последний раз я это делал 5 лет назад, и возможно мои знания устарели, как эту проблему решают в актуальных версиях Postgres ? напишите в комментариях.
Версия СУБД
prod=# select version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 13.15 on x86_64-pc-linux-musl, compiled by gcc (Alpine 13.2.1_git20240309) 13.2.1 20240309, 64-bit (1 row)
Список литературы