§ PostgreSQL foreign data wrapper.

Postgres_FDW: Foreign data wrapper for remote PostgreSQL databases.

В PostgreSQL 9.3 появился отличный модуль postgres_fdw. Этот модуль используется для обеспечения доступа к данным которые находятся в других базах данных. Функциональность модуля очень похожа на ту что предоставляет модуль dblink, однако postgres_fdw обеспечивает более прозрачный и стандартный способ доступа к удаленным таблицам. Кроме того в большинстве случаев, postgres_fdw обеспечивает большую производительность.
В этой статье будет показано как в одной базе настроить доступ к другой базе под выделенным пользователем.
Общий план работ выглядит следующим образом:

1. Создадим тестовые базы и роли c помощью CREATE DATABASE и CREATE ROLE.
2. Устанавливаем расширение postgres_fdw с помощью CREATE EXTENSION.
3. Создаем объект с помощью CREATE SERVER для отображения удаленного сервера.
4. Создаем отображение пользователя с CREATE USER MAPPING для тех пользователей которым мы хотим предоставить доступ.
5. Создаем внешние таблицы с CREATE FOREIGN TABLE для каждой из удаленных таблиц к которым мы хотим иметь доступ.

Для более удобного понимания всего происходящего, вся процедура будет выполняться с нуля.
Создаем тестовую роль которая и базу которая будет у нас источником данных
postgres=# CREATE ROLE fobos WITH LOGIN ENCRYPTED PASSWORD 'fobospw';
CREATE ROLE
postgres=# CREATE DATABASE fobosdb WITH OWNER fobos;
CREATE DATABASE

Внутри базы создаем таблицу которую затем будем импортировать во вторую базу
postgres=# \c fobosdb fobos
You are now connected to database "fobosdb" as user "fobos".
fobosdb=> CREATE TABLE craters AS SELECT id, md5(random()::text) AS data FROM (SELECT * FROM generate_series(1,1000) AS id) AS x;
SELECT 1000

Теперь переходим ко второму серверу и создаем тестовую роль и базу, после чего переключаемся в нее.
postgres=# CREATE ROLE deimos WITH LOGIN ENCRYPTED PASSWORD 'deimospw';
CREATE ROLE
postgres=# CREATE DATABASE deimosdb WITH OWNER deimos;
CREATE DATABASE
postgres=# \c deimosdb
You are now connected to database "deimosdb" as user "postgres".

Теперь в этой базе мы будем выполнять настройку и подключение таблицы которая хранится в базе fobosdb. Сначала подключаем расширение postgres_fdw.
deimosdb=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION

Теперь создаем внешний сервер с помощью CREATE SERVER. Владельцем этого сервера мы сделаем нашего пользователя deimos, ведь именно в его базу мы будем подключать удаленную таблицу. Дополнительно даем право USAGE на использование внешнего сервера. Это необходимо для создания пользовательских отображений с помощью USER MAPPING.
При создании внешнего сервера нам нужно указать параметры удаленной базы: адрес, порт и название базы.
deimosdb=# CREATE SERVER fobos_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '172.31.99.80', port '5432', dbname 'fobosdb');
CREATE SERVER
deimosdb=# ALTER SERVER fobos_server OWNER TO deimos;
ALTER SERVER
deimosdb=# GRANT USAGE ON FOREIGN SERVER fobos_server TO deimos;
GRANT

Список серверов можно посмотреть мета-командой \des
deimosdb=# \des
            List of foreign servers
     Name     |  Owner   | Foreign-data wrapper
--------------+----------+----------------------
 fobos_server |  deimos  | postgres_fdw
(1 row)

Теперь переключаемся в пользователя deimos и создаем отображение пользователя с помощью USER MAPPING, где в качестве реквизитов указываем логин и пароль в удаленной базе fobosdb
deimosdb=# \c - deimos
You are now connected to database "deimosdb" as user "deimos".
deimosdb=> CREATE USER MAPPING FOR CURRENT_USER SERVER fobos_server OPTIONS (user 'fobos', password 'fobospw');
CREATE USER MAPPING

И вот теперь создаем внешнюю таблицу с помощью FOREIGN TABLE. Типы поля в создаваемой внешней таблице должны соответствовать полям в удаленной таблице. Тем не менее имя внешней таблицы и имена полей могут отличаться от имен удаленной таблицы (и имен полей соответственно).
deimosdb=> CREATE FOREIGN TABLE craters_foreign (id int, data text) SERVER fobos_server OPTIONS (table_name 'craters');
CREATE FOREIGN TABLE

Теперь мы можем обращаться к данным во внешней таблице. Также мы можем изменить данные с помощью INSERT/UPDATE/DELETE, при условии что пользователь который был указан при создании пользовательского отображения (CREATE USER MAPPING) имеет соответствующие права на удаленном сервере. Проверяем.
deimosdb=> SELECT count(*) FROM craters_foreign;
 count
-------
  1000
(1 row)
deimosdb=> SELECT * FROM craters_foreign LIMIT 1;
 id |               data              
----+----------------------------------
  1 | fb07325589980c7024047dc00fbbef88
(1 row)

Вот и все. Подобную функциональность можно использовать в случае когда нужно из одной базы получить доступ к данным хранящимся в другой базе.

На главную "Virtualizing Linux"