PostgreSQL 11 Administration Cookbook

How to do it…

We will first describe the variant that uses dblink:

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 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

3. You must create FOREIGN DATA WRAPPER only once, though you ...