foreign data wrappers POC

fdw.md

foreign data wrappers POC

local test setup

docker run \
    --name pg-playground \
    -p 5432:5432 \
    -e POSTGRES_DB=manifest \
    -e POSTGRES_USER=mmuser \
    -e POSTGRES_PASSWORD=test123 \
    -d postgres

psql -h localhost -u mmuser -d manifest

simulate cloud instances

create database myforeign1;
\c myforeign1
create table users (id serial primary key, email varchar);
insert into users (email) values ('agnes.larson@test.com'),('aayan.garrison@test3.com');

create database myforeign2;
\c myforeign2
create table users (id serial primary key, email varchar);
insert into users (email) values ('zaynab.ray@test2.com'),('hajra.escobar@test.com');

install the extension one time

\c manifest

create extension postgres_fdw;

run on manifest db upon each new cloud instance creation

create server foreign_server1
        foreign data wrapper postgres_fdw
        options (host 'localhost', port '5432', dbname 'myforeign1');

create server foreign_server2
        foreign data wrapper postgres_fdw
        options (host 'localhost', port '5432', dbname 'myforeign2');

create user mapping for mmuser
        server foreign_server1
        options (user 'mmuser', password 'test123');

create user mapping for mmuser
        server foreign_server2
        options (user 'mmuser', password 'test123');

create foreign table foreign_users_table1 (
    id serial,
    email varchar
)
server foreign_server1
options (schema_name 'public', table_name 'users');

create foreign table foreign_users_table2 (
    id serial,
    email varchar
)
server foreign_server2
options (schema_name 'public', table_name 'users');

create materialized view mat_view as 
select 'instance1' as instance, email from foreign_users_table1
union all select 'instance2' as instance, email from foreign_users_table2;

run on each new user and invite created, updated, deleted on all instances

refresh materialized view mat_view;

lookup instances associated to email

select instance from mat_view where email = 'agnes.larson@test.com';