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.
  1. 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 WRAPPERpostgres=# CREATE SERVER otherdb FOREIGN DATA WRAPPER postgresql OPTIONS (host 'foo', dbname 'otherdb', port '5432');CREATE SERVERpostgres=# CREATE USER MAPPING FOR PUBLICSERVER otherdb;CREATE USER MAPPING
  1. You must ...

Get PostgreSQL 10 Administration Cookbook now with O’Reilly online learning.

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