bGRw5uJu

1/* =================================================
2* 
3* Linking open and remote (PostgreSQL) read-only 
4* databases easily! Uses postgres_fdw the SQL/MED 
5* implementation (Foreign Data Wrapper). 
6* 
7* Caveats:
8* - Must be superuser to create extension postgres_fdw.
9* - Joins are slow or even never returning.
10*
11* Future Work: 
12* - Create a stored procedure link_pg_server(...). 
13* - Promote sharing of endpoints like SPARQL. 
14* 
15* Tested with PostgreSQL 11. Credits: 
16* https://robots.thoughtbot.com/postgres-foreign-data-wrapper
17* 
18================================================= */ 
19
20-- Step 1: Preparation
21CREATE EXTENSION IF NOT EXISTS postgres_fdw;
22
23-- Connect to remote DB server:
24DROP SERVER IF EXISTS gis_db_server CASCADE;
25CREATE SERVER gis_db_server
26  FOREIGN DATA WRAPPER postgres_fdw
27  OPTIONS (host 'xxx', port 'xxx', dbname 'xxx');
28CREATE USER MAPPING FOR CURRENT_USER
29  SERVER gis_db_server
30  OPTIONS (user 'xxx', password 'xxx');
31
32-- Step 2: Import Schema
33DROP SCHEMA IF EXISTS gis_db_public CASCADE;
34CREATE SCHEMA gis_db_public;
35CREATE EXTENSION IF NOT EXISTS postgis;
36CREATE EXTENSION IF NOT EXISTS hstore; -- needed by gis_db schema
37IMPORT FOREIGN SCHEMA public
38  FROM SERVER gis_db_server
39  INTO gis_db_public;
40
41-- Step 3: Go!
42SELECT COUNT(*) FROM gis_db_public.osm_point;
43
44-- Slow - or even never returning! No operator pushdown ??? 
45-- (no "join op nor tmp table sent to linked db) 
46-- See EXPLAIN:  https://explain.depesz.com/s/PStr 
47with tmp(osm_id, name) as (
48 values (4066310784, 'Tierpark Chur'), (1398864548, 'Tierpark Aletsch')
49)
50select point.* 
51from gis_db_public.osm_point as point
52join tmp on tmp.osm_id=point.osm_id;