Самый простой способ сделать всеобщую таблицу таблиц базы данных Postgres?

Рассмотрим ситуацию, когда N машин имеют по одной базе данных postgres, каждая из которых имеет таблицу A с одинаковой схемой и значением. Мне действительно нужно придерживаться этой архитектуры из соображений производительности, но обновление каждой базы данных с объединением всех их коллективных данных является своего рода болью.

Насколько я смог автоматизировать этот сценарий:

mycopy=tableA_`hostname`.pg
pg_dump -t tableA -d $database | sed "s/tableA/$mycopy" > $mycopy
for host in host_x host_y host_z; do
   scp $mycopy host:~/
done

А затем сценарий sql:

BEGIN;
\i tableA_hostx.pg
\i tableA_hosty.pg
\i tableA_hostz.pg
CREATE TABLE new_tableA AS
               (select * from tableA)
UNION DISTINCT (select * from tableA_hostx)
UNION DISTINCT (select * from tableA_hosty)
UNION DISTINCT (select * from tableA_hostz);
DROP TABLE tableA;
DROP TABLE table_hostx;
DROP TABLE table_hosty;
DROP TABLE table_hostz;
ALTER TABLE new_tableA rename to tableA;
COMMIT;

Но это очень четкая и обычная вещь, которую я чувствую, что я делаю, поэтому мне интересно, есть ли какой-нибудь продвинутый высокоуровневый интерфейс для такого общего взаимодействия. В вики postgres описаны некоторые подходы к распределенным базам данных, но может ли кто-нибудь из них сделать именно это, иначе не заставит меня переосмыслить или изменить дизайн моей базы данных?

Источник

2 ответа

Решение

Я бы использовал иностранные таблицы для этого.

Для каждого удаленного хоста создайте одну стороннюю таблицу. Затем создайте материализованное представление, которое содержит union запрос.

Когда вы хотите обновить все, все, что вам нужно сделать, это refresh materialized view tablea_combined, Нет сброса или восстановления не требуется.

Конечно, это предполагает, что сервер, на котором вы объединяете все, может подключаться ко всем другим серверам.

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

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

Источник

Что @a_horse сказал: Используйте MATERIALIZED VIEW на зарубежных столах.

Кроме того, используйте более простой запрос:

CREATE MATERIALIZED VIEW new_tableA AS 
      TABLE tableA
UNION TABLE tableA_hostx  -- being a foreign table
UNION TABLE tableA_hosty
UNION TABLE tableA_hostz;

  • UNION в любом случае возвращает отдельные строки, нет необходимости в избыточности DISTINCT, (Вы бы использовали UNION ALL сохранить дубликаты.)

  • TABLE tableA является сокращением SQL для SELECT * FROM tableA,

Источник
Другие вопросы по тегам