Setting up a foreign PostgreSQL server

The first requirement of data federation is the ability to connect to remote databases. With this capability, we can read or write to a remote PostgreSQL database table as if it were local. By doing so, certain query elements can be offloaded to the other server. We can also access metadata that is stored in some central location that acts as a shared resource for all database servers.

This recipe will describe how to create a foreign PostgreSQL server and will be the basis for several upcoming segments.

Getting ready

Before we can use the PostgreSQL foreign data wrapper functionality, we need to add the postgres_fdw extension to the database that will use it. Execute this SQL statement as the postgres user in the database that will be contacting foreign servers (pgbench, for example):

CREATE EXTENSION postgres_fdw;

How to do it...

For this recipe, we have two servers: pg-primary as our main data source and pg-report as a reporting server. As with the previous...