PostgreSQL выполнение запросов к нескольким базам

В PostgreSQL не существует способа создать запрос к базам данных отличным от текущей (https://wiki.postgresql.org/), но выполнять запросы между базами возможно при помощи расширения dblink (https://www.postgresql.org/docs/9.6/static/dblink.html).

Допустим, есть машина с CentOS 7, установленным PostgreSQL 9.6 и ip адресом 192.168.1.10.
Для начала необходимо чтобы был пользователь с правами подключения к нашему серверу PostgreSQL по паролю (метод md5). Для примера используем пользователя postgres и настроим права подключения в конфиге pg_hba.conf:
host     all     postgres     192.168.1.0/24     md5
После этого применим изменения:
sudo su - postgres
/usr/pgsql-9.6/bin/pg_ctl reload

Либо при помощи SQL запроса:
SELECT pg_reload_conf();
Теперь установим пакет contrib (если он еще не установлен) нужной нам версии, содержащий в себе расширения для PostgreSQL:
yum install postgresql96-contrib
После этого в директории (в зависимости от ОС директория может отличаться) /usr/pgsql-9.6/share/extension/ можно будет увидеть файлы расширений PostgreSQL.
Для установки dblink достаточно выполнить SQL запрос:
CREATE EXTENSION dblink;
После этого расширение установлено, можно его использовать.
Важно: команда CREATE EXTENSION dblink; установит расширение только для текущей бд (в моем случае в бд postgres), выполнять запросы с использованием dblink нужно подключившись к бд, в которой оно установлено. Если вы подключаетесь к другой бд и выполняете запросы из нее — необходимо сначала установить расширение в этой бд. В данном случае dblink установлен в бд postgres.
Допустим, есть две базы db_one и db_two с идентичными по структуре таблицами tbl_employee (db_one) и tbl_employee2 (db_two), необходимо скопировать данные из таблицы первой бд во вторую.
Создаем первую бд и заполним таблицу данными:
CREATE DATABASE db_one;

\c db_one

CREATE TABLE tbl_employee
(
EmpID INT PRIMARY KEY
,EmpName VARCHAR
,EmpGender CHAR(1)
);

INSERT INTO tbl_employee
VALUES
(1,'Anvesh','M')
,(2,'Neevan','M')
,(3,'Martin','M');

Создаем вторую бд с пустой таблицей:
CREATE DATABASE db_two;

\c db_two

CREATE TABLE tbl_employee2
(
EmpID INT PRIMARY KEY
,EmpName VARCHAR
,EmpGender CHAR(1)
);

Переключаемся на бд postgres и проверяем возможность подключения к бд db_one при помощи dblink:
\c postgres
SELECT dblink_connect('host=192.168.1.10 port=5432 user=postgres password=pass dbname=db_one');

При успешном подключении в ответ получим:
dblink_connect
----------------
OK
(1 row)

Подключимся обратно к БД postgres и пробуем сделать выборку данных из таблицы первой бд:
\c postgres
SELECT * FROM dblink('host=192.168.1.10 port=5432 user=postgres password=pass dbname=db_one', 'SELECT * FROM tbl_employee') AS tbl(id int, name varchar, gender char(1));

В ответ получим:
id | name | gender
----+--------+--------
1 | Anvesh | M
2 | Neevan | M
3 | Martin | M
(3 rows)

Подключаемся к бд db_two, устанавливаем расширение dblink и копируем данные из таблицы tbl_employee бд db_one:
\c db_two
CREATE EXTENSION dblink;
INSERT INTO tbl_employee2 SELECT * FROM dblink('host=192.168.1.10 port=5432 user=postgres password=pass dbname=db_one', 'SELECT * FROM tbl_employee') AS tbl(id int, name varchar, gender char(1));

Проверяем что данные скопированы из таблицы tbl_employee (бд db_one) в таблицу tbl_employee2 (бд db_two):
SELECT * FROM tbl_employee2;
В ответ получаем:
empid | empname | empgender
-------+---------+-----------
1 | Anvesh | M
2 | Neevan | M
3 | Martin | M
(3 rows)

0

Добавить комментарий

Ваш e-mail не будет опубликован.