postgresql - Easiest Way To Do All-To-All Union Of Postgres Database Tables? -

consider situation n machines have 1 postgres database each, having table of same schema , meaning. need stick architecture performance reasons, refreshing each database union of collective data kind of pain.

the extent i've been able automate shell script doing:

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

and sql script:

begin; \i tablea_hostx.pg \i tablea_hosty.pg \i tablea_hostz.pg create table new_tablea                (select * tablea) union distinct (select * tablea_hostx) union distinct (select * tablea_hosty) union distinct (select * tablea_hostz); drop table tablea; drop table table_hostx; drop table table_hosty; drop table table_hostz; alter table new_tablea rename tablea; commit; 

but well-defined , ordinary thing feel doing, wonder if there advanced higher-level interface such all-to-all communication. there distributed database approaches described in the postgres wiki, can of them , otherwise not force me rethink or redesign database?

i use foreign tables that.

for each remote host create 1 foreign table. create materialized view contains union query.

when want refresh everything, need refresh materialized view tablea_combined. no dumping or restoring required.

of course assumes server combine can connect other servers.

if want convenient way select tables, simple view might enough - depends on if performance enough.

if use 9.5 or later can create partitioned table uses inheritance combining foreign tables 1 table on "master" server. make efficient, requires have column identifies "source" server , can used partitioning key.