Categories: PostgreSQL

postgresql-fdw/

Foreign-Data Wrappers (FDW) — это такая фича в PostgreSQL, позволяющая обращаться к внешним СУБД, а также файлам, веб-сервисам, да и вообще к чему угодно. В настоящее время существует много готовых FDW , в том числе для Oracle, MySQL, Redis , MongoDB , ClickHouse, Kafka, Cassandra и RocksDB . Если нужный FDW еще не написан, вы можете реализовать его самостоятельно . Сегодня мы рассмотрим основы использования FDW, на примере доступа к одному серверу PostgreSQL с другого.

Для эксперимента был использован PostgreSQL 13.3. Первый сервер был запущен на виртуалке с Ubuntu Linux 20.04 LTS. На хост-системе под управлением MacOS Catalina был установлен второй сервер. С него мы будем ходить на первый сервер по FDW.

Сервер на виртуалке был настроен, как обычно . Был создан пользователь и принадлежащая ему БД. Файлы postgresql.conf и pg_hba.conf были изменены так, чтобы к БД можно было получить доступ по сети. Также была создана такая таблица:

CREATE TABLE phonebook (
id SERIAL PRIMARY KEY ,
name VARCHAR ( 64 ) ,
phone VARCHAR ( 64 ) ) ;

Проверяем, что к первому серверу (на виртуалке) можно получить доступ со второго (на хосте):

$ psql -h 127.0.0.1 -p 5454 test_database test_user

В данном случае используется проброс портов средствами VirtualBox, отсюда такие IP и порт.

На втором сервере также создаем непривилегированного пользователя и его БД:

CREATE DATABASE mac_db;
CREATE USER mac_user WITH password ‘AppleForever’ ;
GRANT ALL ON DATABASE mac_db TO mac_user;

Будучи все еще под суперпользователем, подключаемся к mac_db и включаем расширение postgres_fdw:

c mac_db
CREATE EXTENSION postgres_fdw;

Расширение идет вместе с PostgreSQL и отдельно скачивать / компилировать его не нужно.

Указываем параметры подключения к серверу:

CREATE SERVER ubuntu_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS ( dbname ‘test_database’ , host ‘127.0.0.1’ , port ‘5454’ ) ;

Проверяем:

eax=# des
List of foreign servers
Name | Owner | Foreign-data wrapper
—————+——-+———————-
ubuntu_server | eax | postgres_fdw
(1 row)

eax=# select * from pg_foreign_server;
-[ RECORD 1 ]————————————————
oid | 37539
srvname | ubuntu_server
srvowner | 10
srvfdw | 37532
srvtype |
srvversion |
srvacl |
srvoptions | {dbname=test_database,host=127.0.0.1,port=5454}

Говорим, в какого пользователя должен отображаться mac_user для доступа к удаленному серверу:

CREATE USER MAPPING FOR mac_user
SERVER ubuntu_server
OPTIONS ( USER ‘test_user’ , password ‘qwerty’ ) ;

Проверяем:

eax=# select * from pg_user_mappings;
-[ RECORD 1 ]——————————-
umid | 37542
srvid | 37539
srvname | ubuntu_server
umuser | 37541
usename | mac_user
umoptions | {user=test_user,password=qwerty}

Разрешаем mac_user ходить на удаленный сервер:

GRANT USAGE ON FOREIGN SERVER ubuntu_server TO mac_user;

Теперь заходим под mac_user:

$ psql -U mac_user -d mac_db

Важно! Имейте ввиду, что mac_user может прочитать пароли и явки для подключения к удаленному серверу, используя приведенные выше запросы.

Для доступа к таблице phonebook говорим:

IMPORT FOREIGN SCHEMA public LIMIT TO ( phonebook )
FROM SERVER ubuntu_server INTO public;

… или:

CREATE FOREIGN TABLE fdw_phonebook
( id SERIAL , name VARCHAR ( 64 ) , phone VARCHAR ( 64 ) )
SERVER ubuntu_server
OPTIONS ( schema_name ‘public’ , table_name ‘phonebook’ ) ;

Теперь с таблицей phonebook (или fdw_phonebook) можно работать, как если бы она была локальной. Все изменения с таблицей, которые мы делаем на втором сервере, будут видны на первом, и наоборот.

Если таблица больше не нужна, от нее можно избавиться так:

DROP FOREIGN TABLE phonebook;

При этом на удаленном сервере таблица продолжит существовать.

Foreign-Data Wrappers обеспечивают доступ к разным СУБД через одну точку доступа. Это удобно при выполнении аналитических запросов, а также с некоторой долей успеха позволяет горизонтально масштабировать PostgreSQL на запись. Еще FDW полезен при миграции на PostgreSQL с неправильных СУБД.

admin