The PostgreSQL native FDW (foreign data wrapper), postgres_fdw allows you to access tables from remote PostgreSQL servers very transparently, even doing thoughtful things like pushing restrictions to the remote server to reduce the amount of data transferred and ensure work is done close to the data.
Out of the box, the standard PostgreSQL FDW also allows PostGIS geometry to transit from remote to local hosts, which is pretty cool.
However, it will not push spatial restrictions from the local host to the remote host, only restrictions that relate to built-in types. I've done a rough and ready patch to the 9.4 branch to allow spatial restrictions to pass over FDW, and it is available here:
- https://github.com/pramsey/postgres/tree/9.3-postgres-fdw-postgis
- https://github.com/pramsey/postgres/tree/9.4-postgres-fdw-postgis
It probably makes sense to extend this a little and have it apply to any extension that is available on both the local and remote hosts, not just PostGIS.
To use it, just do the standard FDW stuff (here's a "wraparound" example, where a PostgreSQL attached to itself, so you can do a "remote" read on a table that is actually local). When you define your remote table, add the use_postgis option to the table definition to get spatial restrictions passed through.
CREATE EXTENSION postgis;
CREATE EXTENSION postgres_fdw;
CREATE TABLE test (
id SERIAL PRIMARY KEY,
name TEXT,
g GEOMETRY
);
CREATE SERVER loopback
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'localhost', dbname 'mydb', port '5432');
CREATE USER MAPPING
FOR bob
SERVER loopback
OPTIONS (user 'bob', password 'secret');
CREATE FOREIGN TABLE test_fdw (
id INTEGER,
name TEXT,
g GEOMETRY)
SERVER loopback
OPTIONS (
table_name 'test',
use_postgis 'true'
);
INSERT INTO test (name, g) VALUES ('Bob', 'POINT(0 0)');
SELECT *
FROM test_fdw
WHERE ST_Intersects(g, ST_MakeEnvelope(-1, -1, 1, 1));