Копирование нескольких строк между базами данных с помощью dblink в PostgreSQL

На чтение 3 мин Опубликовано 11.10.2024

Вопрос или проблема

Я хотел бы скопировать несколько строк между двумя базами данных PostgreSQL (A и B), которые находятся на одном локальном сервере.

Я знаю, что это, вероятно, возможно через расширение dblink, но я не нашел ответа на то, как создать запрос SELECT из базы данных A и INSERT через dblink в базу данных B?

Простой поиск в Google был бы достаточно.

Документация PostgreSQL по dblinks

Ответ на Stackoverflow на ваш вопрос

ИЗМЕНЕНИЕ: Чтобы вставить строки / данные из локальной базы данных A в удаленную базу данных B, вам понадобится Postgres-FDW, потому что простой INSERT INTO remotetable SELECT * FROM localtable не сработает с PostgreSQL.

Ответ или решение

Для копирования нескольких строк между двумя базами данных PostgreSQL на одном локальном сервере с использованием расширения dblink, вы можете выполнить следующие шаги.

Сначала убедитесь, что расширение dblink установлено в вашей базе данных. Для этого выполните команду:

CREATE EXTENSION dblink;

После этого вы можете использовать следующую конструкцию SQL для выборки данных из базы данных A и вставки их в базу данных B через dblink. Предположим, у вас есть таблица my_table в базе данных A и вы хотите вставить данные из этой таблицы в таблицу my_table_in_B в базе данных B.

Настройте соединение: Вам нужно будет указать параметры подключения к базе данных B с использованием dblink. Эти параметры включают имя базы данных, пользователя и пароль.
Сформируйте запрос: Используйте следующий пример запроса для выборки данных из базы данных A и вставки их в базу данных B.

Пример SQL-запроса:

INSERT INTO dblink('dbname=database_B user=your_user password=your_password',
                   'SELECT column1, column2, column3 FROM my_table WHERE condition')
                   AS t(column1 type1, column2 type2, column3 type3);

Замените database_B, your_user и your_password на соответствующие значения вашей конфигурации. column1, column2, column3 — это названия колонок, которые вы хотите скопировать, а condition — это условие для фильтрации строк, которые вы хотите скопировать.

Убедитесь, что типы данных совпадают: Убедитесь, что типы данных колонок, которые вы вставляете, совпадают с типами данных колонок в таблице my_table_in_B, или преобразуйте их по необходимости в запросе.

Этот подход позволит вам сделать выборку нужных строк из одной базы данных и вставить их в другую с помощью dblink. Однако, как было упомянуто в вашем вопросе, также можно использовать postgres_fdw (Foreign Data Wrapper) для более сложных операций с удаленными базами данных, если это необходимо для вашего применения.

Если вам требуется больше гибкости или более сложные операции с данными, то использование postgres_fdw может быть лучшим решением, так как оно позволяет работать с удаленными таблицами как с локальными, что делает синхронизацию данных более простой и интуитивно понятной.

Для установки и работы с postgres_fdw, вы можете использовать:

CREATE EXTENSION postgres_fdw;

Затем создайте серверное соединение и пользовательское маппирование:

CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw 
  OPTIONS (host 'localhost', dbname 'database_B', user 'your_user');

CREATE USER MAPPING FOR your_user SERVER foreign_server 
  OPTIONS (password 'your_password');

Теперь вы можете использовать запросы к удаленной таблице:

INSERT INTO foreign_table (column1, column2, column3)
SELECT column1, column2, column3 FROM local_table WHERE condition;

Таким образом, вы можете выбрать наиболее подходящий метод для вашего случая.