[PostgreSQL/PPAS] 利用Postgres_FDW + Materialized View來Stream及封裝Data

DB1--> testdb -> tb1 (實體Table)
# create database testdb;
# create table tb1 (a int, b text);
# insert into tb1 values (1,'a'),(2,'b');

DB2 -> testdb1 ->
// 先開啟postgres_fdw extension.
# create extension postgres_fdw;

// create remote server, remote那台的pg_hba.conf要開
# CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'DB1', dbname 'testdb', port '5432');

// create user mapping for remote server
# CREATE USER MAPPING for postgres SERVER myserver;

// create MView
# CREATE MATERIALIZED VIEW remote_tb1 AS SELECT * FROM tb1;

// refresh MView
# REFRESH MATERIALIZED VIEW remote_tb1;

**以目前PostgreSQL 9.3.2版的Materialized View並不支援Refresh Concurrently所以在refresh時原有的MView會被Lock!!