Получить профессию в SkyproПеренос данных между таблицами в PostgreSQL: решение
Быстрый ответ
Для обмена данными между базами PostgreSQL используйте команду COPY, если вы хотите работать с файлами локально, или FDW для практически мгновенной передачи данных:
-- Экспорт данных в формате CSV:
COPY your_table TO '/tmp/data.csv' WITH CSV;
-- Импорт данных из файла CSV:
COPY your_table FROM '/tmp/data.csv' WITH CSV;Для установления прямой связи между базами данных примените FDW:
-- Инициализация связи для межбаз данных
CREATE EXTENSION postgres_fdw;
CREATE SERVER src_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'source_host', dbname 'source_db');
CREATE USER MAPPING FOR CURRENT_USER SERVER src_srv OPTIONS (user 'source_user', passwd 'source_pass');
CREATE FOREIGN TABLE foreign_your_table (LIKE your_table) SERVER src_srv OPTIONS (table_name 'your_table');
-- Синхронизация данных
INSERT INTO your_table SELECT * FROM foreign_your_table;COPY подходит для однократного переноса больших объемов данных, а FDW – для постоянного обмена данными между базами.
Золотой дуэт: pg_dump & psql
Воспользуйтесь комбинацией pg_dump и psql для миграции данных между базами:
-- Экспорт и импорт данных через pipeline
pg_dump -U user_name -t table_name source_db | psql -U user_name target_dbНе забывайте настроить права доступа и при необходимости назначить их в целевой базе данных:
-- Назначение прав доступа новому пользователю
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO target_user;В некоторых случаях потребуется сменить владельца таблицы:
-- Смена владельца таблицы
ALTER TABLE table_name OWNER TO new_owner;Убедитесь, что соблюдены соответствия первичных ключей и типов данных столбцов — это важное условие.
Телепортация данных с использованием SQL
Для однократной передачи данных или выполнения сложных операций по переносу данных между базами используйте команду INSERT INTO ... SELECT:
-- Мгновенный перенос данных
INSERT INTO new_db.table_name
SELECT * FROM old_db.table_name;Визуализация
Если вам непривычно использовать метафоры, представьте себе следующую ситуацию:
База данных A (🏦): [Ценность1💎, Ценность2💎, Ценность3💎]
База данных B (🚐): [Пусто1, Пусто2]Операция "Перевод":
🏦 -> 🚚 -> 🚐
# Изымаем ценности из Банка A (🏦), SWAT команда обеспечивает прикрытие 🚚, затем мы перегружаем ценности в фургон-убегайку (🚐)После ограбления:
База данных B (🚐): [Пусто1, Пусто2, *Ценность1💎*, *Ценность2💎*, *Ценность3💎*]
# Прикол в том, что ценности с символом 💎 из Банка A теперь в нашем укрытии (🚐)Продвинутое и согласованное перемещение: используйте FDW и dblink
Для регулярной синхронизации данных между базами используйте связку FDW и dblink:
-- DBlink поддерживает бесперебойное подключение:
CREATE EXTENSION dblink;
SELECT dblink_connect('connection_name', 'hostaddr=127.0.0.1 port=5432 dbname=target_db user=target_user password=target_pass');
SELECT * FROM dblink('connection_name', '<запрос>') AS data(column1 datatype1, column2 datatype2, ...);
-- Для обработки данных используйте временные таблицы:
BEGIN;
CREATE TEMP TABLE tmp_table AS
SELECT * FROM dblink('connection_name', 'SELECT * FROM foreign_table') AS data(column1 datatype1, column2 datatype2, ...);
DROP TABLE tmp_table;
COMMIT;Не забывайте проверять соответствие первичных ключей и типов данных столбцов. Бдительность — залог успешной работы.
Будьте настороже: избегайте препятствий, планируйте на непредвиденные ситуации
- Будьте внимательны к вопросам, связанным с правами доступа в целевой базе данных. Права доступа критически важны для успешной миграции.
- Иногда изменение владельца неминуемо — сделайте это правильно.
- Не забывайте о проверке сетевых настроек для обеспечения стабильности соединений.