If you want to query a table in one database server from another you need a way to connect these two servers with each other. For PostgreSQL databases the feature that makes this possible is called Foreign Data Wrapper.
To use this feature you have to load its extension into the current database:
CREATE EXTENSION postgres_fdw;
The postgres_fdw extension ships with a default PostgreSQL installation. It allows you to connect a PosgreSQL database with another PostgreSQL database. You can connect a PostgreSQL database with databases by other vendors, too, but then you need external extensions like oracle_fdw for Oracle databases or mysql_fdw for MySQL databases. In this article we will only use postgres_fdw.
You can check if the extension was loaded successfully. The following query should return a row for the extension:
SELECT * FROM pg_extension WHERE extname='postgres_fdw';
The next step is to set up the remote server instance:
CREATE SERVER remotesrv FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host '127.0.0.1', port '5432', dbname 'remotedb' );
This statement registers a remote server under the name remotesrv, a name you can choose freely. You have to specify which Foreign Data Wrapper to use (postgres_fdw in this case) as well the target host, port and database name.
CREATE SERVER statement didn’t contain any user login information, you have to provide it via a user mapping:
CREATE USER MAPPING FOR CURRENT_USER SERVER remotesrv OPTIONS ( user 'joe', password 'secret' );
In this case we map the remote user joe to the current user (
CURRENT_USER) of the local server. It doesn’t have to be the current user, you could specify any user name.
Now you have to import tables from the remote database. You can either explicitly import individual tables or a whole schema. Here’s how to import the public schema of the remote (“foreign”) database into the public schema of the local database:
IMPORT FOREIGN SCHEMA public FROM SERVER remotesrv INTO public;
You can restrict which tables to import with the
LIMIT TO or
EXCEPT clauses. The following statement will only import the tables books and students:
IMPORT FOREIGN SCHEMA public LIMIT TO (students, books) FROM SERVER remotesrv INTO public;
Now you can access these tables as if they were in the local database.