How-to PostgreSQL query from multiples databases or separate databases or remote dblink databases

1. Introduction

1.1. Summary

This post shows how to create a database link to another PostgreSQL database ( local or remote ) to query multiples database tables, using 'posgres_fwd'  PostgreSQL Extension

1.2. Scenario

• PostgreSQL Linux x86_64 server

  - DNS Name: postgresql.mydomain.com
  - IP: 127.0.0.1 (loopback)
  + PostgreSQL database list: 
    - 'prod'
    - 'homolog'
    - 'develop'

1.3. Cookbook

• Install PostgreSQL and check pre-requisite packages
• Create databases 'prod', 'homolog' and 'develop' each one with a sample table with some data
• Create Extension for PostgreSQL Foreign Database 
• Create Foreign Servers for each database
• Create User, configure user access and User Mapping
• Grant privileges for database link user
• Create foreign table for each database
• Connect each database and test SELECT on local table and FOREIGN tables

2. Step-by-Step

2.1. Install PostgreSQL and check pre-requisite packages

a) To Install PostgreSQL on CentOS environment follow these steps

# yum list installed 'postgres*'
# yum install postgresql94-server
# service --status-all
# service postgresql-9.4 initdb
# chkconfig postgresql-9.4 on
# service postgresql-9.4 start


b) check pre-requisite for database link: i) postgresql*libs*; ii) postgre*contrib*

# yum list installed 'postgres*'
Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
 * base: mirror.globo.com
 * epel: mirror.globo.com
 * extras: mirror.globo.com
 * updates: mirror.globo.com
Installed Packages
postgresql.x86_64           8.4.20-1.el6_5    @updates
postgresql-libs.x86_64      8.4.20-1.el6_5    @updates
postgresql-odbc.x86_64      08.04.0200-1.el6  @anaconda-CentOS-201303020151.x86_64/6.4
postgresql94.x86_64         9.4.1-1PGDG.rhel6 @pgdg94
postgresql94-contrib.x86_64 9.4.1-1PGDG.rhel6 @pgdg94
postgresql94-libs.x86_64    9.4.1-1PGDG.rhel6 @pgdg94
postgresql94-server.x86_64  9.4.1-1PGDG.rhel6 @pgdg94

2.2. Create databases 'prod', 'homolog' and 'develop' each one with a sample table with some data

a) Create PostgreSQL databases

# su - postgres
$ psql
postgres=# CREATE DATABASE prod;
CREATE DATABASE
postgres=# CREATE DATABASE homolog;
CREATE DATABASE
postgres=# CREATE DATABASE develop;
CREATE DATABASE


b) Create Sample Tables on each PostgreSQL databases

postgres=# -- Connect to 'prod' database
postgres=# \c prod
You are now connected to database "prod".
prod=# CREATE TABLE sample_table_prod( id INT NOT NULL, text_value varchar(100) NOT NULL,  CONSTRAINT PK_SAMPLETABLEPROD PRIMARY KEY ( id ) );
CREATE TABLE
prod=# INSERT INTO sample_table_prod VALUES ( 1, 'database: prod' );
INSERT 0 1
prod=#
prod=# -- Connect to 'homolog' database
prod=# \c homolog
You are now connected to database "homolog".
homolog=# CREATE TABLE sample_table_homolog( id INT NOT NULL, text_value varchar(100) NOT NULL,  CONSTRAINT PK_SAMPLETABLEHOMOLOG PRIMARY KEY ( id ) );
CREATE TABLE
homolog=# INSERT INTO sample_table_homolog VALUES ( 1, 'database: homolog' );
INSERT 0 1
homolog=#
homolog=# -- Connect to 'develop' database
homolog=# \c develop
You are now connected to database "develop".
develop=# CREATE TABLE sample_table_develop( id INT NOT NULL, text_value varchar(100) NOT NULL,  CONSTRAINT PK_SAMPLETABLEDEVELOP PRIMARY KEY ( id ) );
CREATE TABLE
develop=# INSERT INTO sample_table_develop VALUES ( 1, 'database: develop' );
INSERT 0 1
postgres=# \q

2.3. Create Extension for PostgreSQL Foreign Database 

$ psql prod
prod=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
prod=# \q
$ psql homolog
homolog=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
homolog=# \q
$ psql develop
develop=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
develop=# \q

2.4. Create Foreign Servers for each database

• Connect each database and create server: prod(homolog,develop), homolog(prod,develop), develop(prod,homolog)

a) 'prod' database

$ psql prod
prod=# CREATE SERVER homolog_foreign_server
       FOREIGN DATA WRAPPER postgres_fdw
       OPTIONS (host '127.0.0.1', port '5432', dbname 'homolog');
CREATE SERVER
prod=# CREATE SERVER develop_foreign_server
       FOREIGN DATA WRAPPER postgres_fdw
       OPTIONS (host '127.0.0.1', port '5432', dbname 'develop');
CREATE SERVER
prod=# \des
                 List of foreign servers
          Name          |  Owner   | Foreign-data wrapper
------------------------+----------+----------------------
 develop_foreign_server | postgres | postgres_fdw
 homolog_foreign_server | postgres | postgres_fdw
(2 rows)

prod=# \q


b) 'homolog' database

$ psql homolog
homolog=# CREATE SERVER prod_foreign_server
          FOREIGN DATA WRAPPER postgres_fdw
          OPTIONS (host '127.0.0.1', port '5432', dbname 'prod');
CREATE SERVER
homolog=# CREATE SERVER develop_foreign_server
          FOREIGN DATA WRAPPER postgres_fdw
          OPTIONS (host '127.0.0.1', port '5432', dbname 'develop');
CREATE SERVER
homolog=# \des
                 List of foreign servers
          Name          |  Owner   | Foreign-data wrapper
------------------------+----------+----------------------
 develop_foreign_server | postgres | postgres_fdw
 prod_foreign_server    | postgres | postgres_fdw
(2 rows)

homolog=# \q


c) 'develop' database

$ psql develop
develop=# CREATE SERVER prod_foreign_server
          FOREIGN DATA WRAPPER postgres_fdw
          OPTIONS (host '127.0.0.1', port '5432', dbname 'prod');
CREATE SERVER
develop=# CREATE SERVER homolog_foreign_server
          FOREIGN DATA WRAPPER postgres_fdw
          OPTIONS (host '127.0.0.1', port '5432', dbname 'homolog');
CREATE SERVER
develop=# \des
                 List of foreign servers
          Name          |  Owner   | Foreign-data wrapper
------------------------+----------+----------------------
 homolog_foreign_server | postgres | postgres_fdw
 prod_foreign_server    | postgres | postgres_fdw
(2 rows)

develop=# \q

2.5. Create User, configure user access and User Mapping

a) Create the same user for all databases on this server

$ psql
postgres=# CREATE USER dblink_postgresql WITH PASSWORD 'dblink_postgresql';
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE prod    TO dblink_postgresql;
postgres=# GRANT CONNECT ON DATABASE homolog TO dblink_postgresql;
postgres=# GRANT CONNECT ON DATABASE develop TO dblink_postgresql;
postgres=# \q

b) User Mapping for 'prod' database

$ psql prod
prod=# CREATE USER MAPPING FOR postgres
       SERVER homolog_foreign_server
       OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
prod=# CREATE USER MAPPING FOR postgres
       SERVER develop_foreign_server
       OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
prod=# CREATE USER MAPPING FOR dblink_postgresql
       SERVER homolog_foreign_server
       OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
prod=# CREATE USER MAPPING FOR dblink_postgresql
       SERVER develop_foreign_server
       OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
prod=# \q


c) User Mapping for 'homolog' database

$ psql homolog
homolog=# CREATE USER MAPPING FOR postgres
          SERVER prod_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
homolog=# CREATE USER MAPPING FOR postgres
          SERVER develop_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
homolog=# CREATE USER MAPPING FOR dblink_postgresql
          SERVER prod_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
homolog=# CREATE USER MAPPING FOR dblink_postgresql
          SERVER develop_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
homolog=# \q


d) User Mapping for 'develop' database

$ psql develop
develop=# CREATE USER MAPPING FOR postgres
          SERVER prod_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
develop=# CREATE USER MAPPING FOR postgres
          SERVER homolog_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
develop=# CREATE USER MAPPING FOR dblink_postgresql
          SERVER prod_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
develop=# CREATE USER MAPPING FOR dblink_postgresql
          SERVER homolog_foreign_server
          OPTIONS (user 'dblink_postgresql', password 'dblink_postgresql');
CREATE USER MAPPING
develop=# \q

2.6. Grant privileges for database link user

a) Grant privileges on 'prod' database to database link user

$ psql prod
prod=# GRANT USAGE ON SCHEMA public TO dblink_postgresql;
GRANT
prod=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblink_postgresql;
GRANT
prod=# GRANT USAGE ON FOREIGN SERVER homolog_foreign_server TO dblink_postgresql;
GRANT
prod=# GRANT USAGE ON FOREIGN SERVER develop_foreign_server TO dblink_postgresql;
GRANT
prod=# GRANT USAGE ON FOREIGN SERVER homolog_foreign_server TO postgres;
GRANT
prod=# GRANT USAGE ON FOREIGN SERVER develop_foreign_server TO postgres;
GRANT
postgres=# \q


b) Grant privileges on 'homolog' database to database link user

$ psql homolog
homolog=# GRANT USAGE ON SCHEMA public TO dblink_postgresql;
GRANT
homolog=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblink_postgresql;
GRANT
homolog=# GRANT USAGE ON FOREIGN SERVER prod_foreign_server TO dblink_postgresql;
GRANT
homolog=# GRANT USAGE ON FOREIGN SERVER develop_foreign_server TO dblink_postgresql;
GRANT
homolog=# GRANT USAGE ON FOREIGN SERVER prod_foreign_server TO postgres;
GRANT
homolog=# GRANT USAGE ON FOREIGN SERVER develop_foreign_server TO postgres;
GRANT
homolog=# \q


c) Grant privileges on 'develop' database to database link user

$ psql develop
develop=# GRANT USAGE ON SCHEMA public TO dblink_postgresql;
GRANT
develop=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO dblink_postgresql;
GRANT
develop=# GRANT USAGE ON FOREIGN SERVER prod_foreign_server TO dblink_postgresql;
GRANT
develop=# GRANT USAGE ON FOREIGN SERVER homolog_foreign_server TO dblink_postgresql;
GRANT
develop=# GRANT USAGE ON FOREIGN SERVER prod_foreign_server TO postgres;
GRANT
develop=# GRANT USAGE ON FOREIGN SERVER homolog_foreign_server TO postgres;
GRANT
develop=# \q

2.7. Create foreign table for each database

a) Database 'prod' foreign tables

$ psql prod
prod=# CREATE FOREIGN TABLE sample_table_homolog( id integer, text_value character varying(100) )
       SERVER homolog_foreign_server OPTIONS (table_name 'sample_table_homolog');
CREATE FOREIGN TABLE
prod=# CREATE FOREIGN TABLE sample_table_develop( id integer, text_value character varying(100) )
       SERVER develop_foreign_server OPTIONS (table_name 'sample_table_develop');
CREATE FOREIGN TABLE
prod=# \q


b) Database 'homolog' foreign tables

$ psql homolog
homolog=# CREATE FOREIGN TABLE sample_table_prod( id integer, text_value character varying(100) )
          SERVER prod_foreign_server OPTIONS (table_name 'sample_table_prod');
CREATE FOREIGN TABLE
homolog=# CREATE FOREIGN TABLE sample_table_develop( id integer, text_value character varying(100) )
          SERVER develop_foreign_server OPTIONS (table_name 'sample_table_develop');
CREATE FOREIGN TABLE
homolog=# \q


c) Database 'develop' foreign tables

$ psql develop
develop=# CREATE FOREIGN TABLE sample_table_prod( id integer, text_value character varying(100) )
          SERVER prod_foreign_server OPTIONS (table_name 'sample_table_prod');
CREATE FOREIGN TABLE
develop=# CREATE FOREIGN TABLE sample_table_homolog( id integer, text_value character varying(100) )
          SERVER homolog_foreign_server OPTIONS (table_name 'sample_table_homolog');
CREATE FOREIGN TABLE
develop=# \q

2.8. Connect each database and test SELECT on local table and FOREIGN tables

a) Database 'prod'

$ psql -h 127.0.0.1 -d prod -U dblink_postgresql
prod=# SELECT id, text_value FROM sample_table_prod;
 id |   text_value
----+----------------
  1 | database: prod
(1 row)
prod=# SELECT id, text_value FROM sample_table_homolog;
 id |    text_value
----+-------------------
  1 | database: homolog
(1 row)
prod=# SELECT id, text_value FROM sample_table_develop;
 id |    text_value
----+-------------------
  1 | database: develop
(1 row)
prod=# SELECT prod.id, prod.text_value, homolog.text_value, develop.text_value
       FROM   sample_table_prod prod
  INNER  JOIN sample_table_homolog homolog
  ON     prod.id = homolog.id
  INNER  JOIN sample_table_develop develop
  ON     prod.id = develop.id;
 id |   text_value   |    text_value     |    text_value
----+----------------+-------------------+-------------------
  1 | database: prod | database: homolog | database: develop
(1 row)
prod=# \q


b) Database 'homolog'

$ psql -h 127.0.0.1 -d homolog -U dblink_postgresql
homolog=# SELECT id, text_value FROM sample_table_homolog;
 id |    text_value
----+-------------------
  1 | database: homolog
(1 row)
homolog=# SELECT id, text_value FROM sample_table_prod;
 id |   text_value
----+----------------
  1 | database: prod
(1 row)
homolog=# SELECT id, text_value FROM sample_table_develop;
 id |    text_value
----+-------------------
  1 | database: develop
(1 row)
homolog=# SELECT homolog.id, homolog.text_value, prod.text_value, develop.text_value
          FROM   sample_table_homolog homolog
     INNER  JOIN sample_table_prod prod
     ON     homolog.id = prod.id
     INNER  JOIN sample_table_develop develop
     ON     homolog.id = develop.id;
 id |    text_value     |   text_value   |    text_value
----+-------------------+----------------+-------------------
  1 | database: homolog | database: prod | database: develop
(1 row)
homolog=# \q


c) Database 'develop'

$ psql -h 127.0.0.1 -d develop -U dblink_postgresql
develop=# SELECT id, text_value FROM sample_table_develop;
 id |    text_value
----+-------------------
  1 | database: develop
(1 row)
develop=# SELECT id, text_value FROM sample_table_prod;
 id |   text_value
----+----------------
  1 | database: prod
(1 row)
develop=# SELECT id, text_value FROM sample_table_homolog;
 id |    text_value
----+-------------------
  1 | database: homolog
(1 row)
develop=# SELECT develop.id, develop.text_value, prod.text_value, homolog.text_value
          FROM   sample_table_homolog develop
     INNER  JOIN sample_table_prod prod
     ON     develop.id = prod.id
     INNER  JOIN sample_table_homolog homolog
     ON     develop.id = homolog.id;
 id |    text_value     |   text_value   |    text_value
----+-------------------+----------------+-------------------
  1 | database: homolog | database: prod | database: homolog
(1 row)
develop=# \q

3. References

• http://stackoverflow.com/questions/4678862/joining-results-from-two-separate-databases
• http://www.postgresql.org/docs/9.3/static/postgres-fdw.html
• http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-postgres_fdw/