How to query a table in one PostgreSQL database from another PostgreSQL database using a foreign data wrapper

You can query a table from a different PostgreSQL database using the PostgreSQL Foreign data wrapper (postgres_fdw).

The steps are as follows:

1. Install the postgres_fdw extension
2. Create the foreign server connection
3. Create a user mapping
4. Create a foreign table

You can use SELECT, UPDATE, INSERT and DELETE on the foreign table.

Install the postgres_fdw extension

The query to install the foreign data wrapper extension is:

CREATE EXTENSION postgres_fdw;

 Create the foreign server connection

The query to create the foreign server connection is:

CREATE SERVER tloustore_srv   

FOREIGN DATA WRAPPER postgres_fdw   

OPTIONS (host '127.0.0.1', dbname 'tloustore', port '5432');  

The arguments:

• tloustore_srv is the name I am giving to the foreign server
• host '127.0.0.1' is the ip address of the foreign server
• dbname 'tloustore' is the database on the foreign in the foreign server
• port '5432' is the port of the foreign server

Create the user mapping

The query to create the user mapping is:

CREATE USER MAPPING FOR admin 

SERVER tloustore_srv   

OPTIONS (user 'elephant', password 'secret'); 

 The arguments:

• admin is the user on the database I am connected to
• tloustore_srv is the foreign server we created
• user 'elephant' is the user on the foreign database
• password 'secret' is the password on the foreign database

Create a foreign table

You need to create a foreign table in you current database before you can query the table. You can add all the table columns or only a few. The query is as follows:

CREATE FOREIGN TABLE foreign_inventory 

( 

    itmcode integer NOT NULL, 

    description character varying, 

    price numeric(15,2)

) 

SERVER tloustore_srv  

OPTIONS (schema_name 'public', table_name 'inventory'); 

Query the data

You can now query the foreign table just like any local table. 

SELECT * FROM foreign_inventory;

Example: 

Notes:

1) primary key constraints are not supported on foreign tables

2) --If you don't want to import individual tables one-by-one, then import the schema instead using IMPORT FOREIGN SCHEMA:

IMPORT FOREIGN SCHEMA production  

LIMIT TO (tracks, pages)  

FROM SERVER segment INTO public;