postgres_fdw

postgres_fdw gives ability to connect to a Postgres cluster, then query a table on a remote cluster. For instance, if you had two clusters: one called "metrics" and another called "application-production". You can use postgres_fdw to on "application-production" to query a table on the metrics cluster.

To keep things logical, we will refer to two different Postgres clusters as connecting and remote:

  • connecting cluster: the machine where the queries will run first, and it connects to the remote cluster
  • remote cluster: the cluster that has the data that will be queried

All configuration of postgres_fdw will happen from the connecting cluster.

To configure postgres_fdw, go to the connecting host, and perform the following steps. Load the extension on your database:

CREATE EXTENSION postgres_fdw;

Once loaded, set up the server:

CREATE SERVER metrics_warehouse
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
    host 'otherclusterhostname.db.postgresbridge.com',
    dbname 'database-name-on-other-cluster',
    port '5432',
    sslmode 'require'
);

Create a user mapping which maps the user on the connecting cluster with a username / password on the remote cluster:

CREATE USER MAPPING FOR POSTGRES SERVER metrics_warehouse
OPTIONS (
    user 'postgres',
    password 'password-to-remote-cluster'
);

Then, create a table to represent map the data:

CREATE FOREIGN TABLE metrics_table_fdw (
  id BIGINT,
  metric_name TEXT,
  value FLOAT,
  created_at TIMESTAMP
)
SERVER metrics_warehouse;

Note that if you see the following error, and you've confirmed that all username / password / post settings are correct, you have probably missed the SSL values on the server configuration above:

ERROR:  could not connect to server "warehouse"
DETAIL:  connection to server at "otherclusterhostname.db.postgresbridge.com" (8.8.8.8), port 5432 failed:
FATAL:  password authentication failed for user "postgres"
connection to server at "otherclusterhostname.db.postgresbridge.com" (8.8.8.8), port 5432 failed: 
FATAL:  no pg_hba.conf entry for host "8.8.8.9", user "postgres", database "database-name-on-other-cluster", no encryption

You can run something like the following to alter the server and add SSL mode values:

ALTER SERVER metrics_warehouse OPTIONS (sslmode 'require');

We've created a few tutorials for using postgres_fdw:

postgres_fdw documentation: