How to do it...

We will first describe the variant that uses dblink, which applies to all supported PostgreSQL versions:

  1. First, we need to install the dblink contrib module. The general procedure is explained in the Adding an external module to PostgreSQL recipe of Chapter 3, Configuration.
  2. Then, we create some access definitions. The preferred way is to use the following commands, which are SQL standard (SQL/MED):
        postgres=# CREATE FOREIGN DATA WRAPPER postgresql          VALIDATOR postgresql_fdw_validator;         CREATE FOREIGN DATA WRAPPER         postgres=# CREATE SERVER otherdb         FOREIGN DATA WRAPPER postgresql         OPTIONS (host 'foo', dbname 'otherdb', port '5432');         CREATE SERVER         postgres=# CREATE USER MAPPING FOR PUBLIC         SERVER otherdb;  CREATE USER MAPPING ...

Get PostgreSQL Administration Cookbook, 9.5/9.6 Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.