Скопируйте результаты из представления PostgreSQL в одной БД в таблицу в другой

Полный курс PostgreSQL newb.

У меня есть 7-8 представлений данных в db1, которые мне нужно скопировать в таблицы с соответствующими схемами (схемами?) в другой базе данных, db2. База данных назначения может быть тем же самым экземпляром PostgreSQL или полностью на другом компьютере.

Я знаю 2-3 разных способа сделать это с базами данных, с которыми я знаком, но я беспомощен в этом. Может кто-нибудь предложить некоторые основные стратегии для меня?

В идеальном мире я бы предпочел не делать ничего, что кажется слишком ETL-подобным — я бы предпочел сделать что-то вроде

SELECT FROM instance1.db1.viewname INTO instance2.db5.tablename

затем выгрузите данные из представления в виде текстового файла и перезагрузите в целевую таблицу.

Поскольку я не знаю PostgreSQL, я действительно не знаю, что находится в пределах возможного.


postgresql etl replication
person Russell Christopher    schedule 05.11.2011    source источник
comment
Спасибо вам обоим. dblink_connect кажется именно тем, что мне нужно. Однако похоже, что эта функция не установлена ​​автоматически в дистрибутиве, который я только что закинул на свою коробку. Есть ли какая-то команда, которую мне нужно запустить изнутри pgAdmin, чтобы установить ее?   -  person Russell Christopher    schedule 06.11.2011
comment
Не бери в голову. Обнаружил, что это расширение, которое я могу установить для рассматриваемой базы данных с помощью pgAdmin. Я в порядке.   -  person Russell Christopher    schedule 06.11.2011

Ответы (2)


arrow_upward
5
arrow_downward

Вам не нужно создавать временную таблицу для COPY TO. Источником может быть любой запрос, начиная с PostgreSQL 8.2.

COPY (SELECT * FROM view1) TO '/var/lib/postgres/myfile1.csv';

Прочтите руководство по COPY. Создайте необходимые таблицы локально с помощью

CREATE table tbl1 AS
SELECT * FROM view1
LIMIT 0;   -- no data, just the schema.

Скопируйте инструкции DDL и создайте все таблицы в целевой базе данных. pgAdmin — удобный графический интерфейс для этого. Снова удалите пустые таблицы в исходной базе данных. Загрузить данные с помощью

COPY tbl1 FROM '/var/lib/postgres/myfile1.csv';

Дамп / восстановление, как описывает это @wildplasser, - это еще один способ.

Для однократного перевода рекомендуется использовать один из этих методов. Для повторного применения используйте dblink или SQL/MED (Управление внешними данными) может быть более подходящим .

person Erwin Brandstetter    schedule 06.11.2011
comment
Упс. моя вина. Но скопировать представление ИЗ файла по-прежнему невозможно :-) [что на самом деле жаль, так как система правил позволяет создавать обновляемые представления] - person wildplasser; 06.11.2011

arrow_upward
3
arrow_downward 

CREATE TEMPORARY TABLE mytmp
AS SELECT * from myview
WHERE 1=1
;


COPY mytmp TO '/tmp/test.csv'
;

Еще лучший способ:

  • скопируйте представления в таблицы (создайте table1 как select * from view1; ...)
  • используйте pg_dump -t table1 -t table2 ... mydbname>myfile.out
  • используйте myfile.out для воссоздания и заполнения таблиц.
person wildplasser    schedule 05.11.2011
comment
Причина этого дополнительного копирования заключается в том, что представления нельзя использовать в качестве источника или цели для операторов COPY. Таблица создания AS заботится о (большинстве) типов данных. Ограничения не будут скопированы в новую таблицу. - person wildplasser; 05.11.2011