PostgreSQL Backup/Restore or Export/Import or pg_dump-dbname outfile/psql-dbname-infile

1. Introduction

This post shows how to backup/restore (or Export/Import or pg_dump-dbname>outfile/psql-dbname<infile).

The cookbook is:
- Export dump file of source database
- Drop Target database
- Import dump file on target database

2. Step-by-Step

a) Export PostgreSQL dumpfile

$ pg_dumpall > pg_dumpall.psql

or

$ pg_dump -U postgres -d mydb > pg_dump_mydb.psql

b) Transfer file from source server to target server

$ scp ...


c) Drop target database

$ echo "*** Warning: Before execute following command, make sure you are executing this command on the target server ***"
$ dropdb mydb


d) Import PostgreSQL dumpfile 

$ psql -U USERNAME mydb < pg_dumpall.psql

3. References

• https://www.a2hosting.com/kb/developer-corner/postgresql/import-and-export-a-postgresql-database
• http://help.ninefold.com/getstarted/exporting_and_importing_postgresql_data/
• http://www.postgresql.org/docs/9.3/static/backup-dump.html
• http://www.postgresql.org/docs/9.3/static/app-pgrestore.html

How to create database link between PostgreSQL 9.3 to Oracle XE 11 - (SQL/MED Management External Data - Foreign Data Wrapper for Oracle)

1. Introduction

1.1. Summary

This post shows how to create a database link between PostgreSQL vs Oracle using 'oracle_fdw' a PostgreSQL Extension for Foreign Data Wrapper for Oracle.

1.2. Scenario

• Oracle Linux x86_64 server: 

  - DNS Name: oracle.mydomain.com
  - IP: 10.1.1.2
  - Oracle database instance SID=prod

• PostgreSQL Linux x86_64 server

  - DNS Name: postgresql.mydomain.com
  - IP: 10.1.0.21
  - PostgreSQL database = prod

1.3. Cookbook

• Connected to Oracle,  create user for database link and grant appropriated permission
• Connected to PostgreSQL server, install Oracle Instant Client packages and SDK Header files
• Connected to PostgreSQL server, install PostgreSQL headers and PGXS from development packages
• Connected to PostgreSQL server, install 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle
• Connected to PostgreSQL server, cofigure 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle
• Connected to PostgreSQL server, test dblink connection using 'oracle_fdw'

2. Step-by-Step

2.1. Connect to Oracle and create user for database link and grant appropriated permission

a) Create Oracle user used by PostgreSQL dblink connection

$ sqlplus '/ as sysdba'
SQL> CREATE USER dblink_postgres IDENTIFIED BY "dblink_postgres";

User created.


b) Grant connection to dblink-user to appropriated Oracle database instance

SQL> GRANT CONNECT TO dblink_postgres;

Grant succeeded.


c) Grant to dblink-user appropriated database objects permissions

SQL> DECLARE
BEGIN
  FOR rLoop IN
    (
 SELECT CASE 
               WHEN object_type IN ( 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
                 THEN
                   'EXECUTE' 
               ELSE
                   'SELECT' 
             END privilege, 
        owner, 
object_name 
 FROM   all_objects
 WHERE  object_type IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'FUNCTION', 'PACKAGE' )
 AND    owner IN ( 'OWNER_01', 'OWNER_02', 'OWNER_03' )
 ORDER  BY owner, object_name
) LOOP
 dbms_output.put_line( '- ' || rLoop.privilege || ' - ' || rLoop.owner || ' - ' || rLoop.object_name );
 BEGIN
        EXECUTE IMMEDIATE 'GRANT ' || rLoop.privilege || ' ON ' || rLoop.owner || '.' || rLoop.object_name || ' TO dblink_postgres';    
 EXCEPTION
   WHEN OTHERS THEN
 dbms_output.put_line( '- * FAILED!');
 END;
    END LOOP;
END;
/

PL/SQL procedure successfully completed.

SQL> 


d) Disconnect and Connect with dblink-user to test permissions given

SQL> CONNECT dblink_postgres/dblink_postgres
Connected.


e) Describe an existing table and select some data only for test

SQL> desc sys.dual

Name  Type        Nullable Default Comments 
----- ----------- -------- ------- -------- 
DUMMY VARCHAR2(1) Y                         

2.2. Connect to PostgreSQL server, install and configure "Oracle Instant Client" and "Instant Client Package - SDK Additional header files" and "Instant Client Package SQL*Plus"

a) Download appropriated Instant Client package for Linux. See http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html for details. In my case, I will download and install Oracle Version "11.2.0.4.0" for: i) "Instant Client for Linux x86-64"; ii) "Instant Client Package - SDK: Additional header files"; iii) "Instant Client Package - SQL*Plus".

# mkdir /opt
# cd    /opt
#
# echo "Download Oracle products here ..."
#


b) Unzip Oracle Instant Client and Oracle ODBC driver and execute install/update script

# cd    /opt
# unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
# unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip
# unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip


c) Configure environment variables for Oracle

#
# vim ~/.bash_profile
ORACLE_HOME=/opt/instantclient_11_2
LD_LIBRARY_PATH=/opt/instantclient_11_2
PATH=$PATH:$HOME/bin:$ORACLE_HOME
export PATH
export ORACLE_HOME
export LD_LIBRARY_PATH
#
# echo "Logout/Re-Login ..."
# logout


d) Test Oracle Client Connection

# sqlplus dblink_postgres/dblink_postgres@oracle-homolog:1521/XE

2.3. Install PostgreSQL headers and PGXS from development packages

a) Check if packages 'postgresql*-devel*' is installed

# yum list installed 'postgres*'
Installed Packages
postgresql93.x86_64                      9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-contrib.x86_64              9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-devel.x86_64                9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-libs.x86_64                 9.3.5-2PGDG.rhel7               @pgdg93
postgresql93-server.x86_64               9.3.5-2PGDG.rhel7               @pgdg93


b) Check if PostgreSQL Extension Development Environment Variables 'pg_config' is configured on PATH if not configure it

# echo "Check ..."
# pg_config
-bash: pg_config: command not found
#
# echo 'Not configured! So where is "pg_config" binary ?'
# find / -name pg_config
/usr/pgsql-9.3/bin/pg_config
#
# echo 'Configure PATH on .bash_profile ...'
# vim ~/.bash_profile
ORACLE_HOME=/opt/instantclient_11_2
LD_LIBRARY_PATH=/opt/instantclient_11_2
PG_CONFIG=/usr/pgsql-9.3/bin
PATH=$PATH:$HOME/bin:$ORACLE_HOME:$PG_CONFIG
export PATH
export ORACLE_HOME
export LD_LIBRARY_PATH

2.4. Install 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle.

The 'oracle_fdw' is a PostgreSQL extension and uses the Extension Building Infrastructure "PGXS". You can download 'oracle_fdw' from http://pgxn.org/dist/oracle_fdw/

a) Check pre-requisites: i) PostgreSQL Development Package; ii) PATH environment variable point to 'pg_config'; iii) Oracle Client or Instant Client software installed; iv) Oracle ORACLE_HOME environment variable is set; v) Oracle LD_LIBRARY_PATH is set; vi) GNU Compiler Collection C/C++

# yum list installed 'postgres*' | grep 'postgresql' | grep 'devel'
postgresql93-devel.x86_64                9.3.5-2PGDG.rhel7               @pgdg93
# pg_config | head -n 1
BINDIR = /usr/pgsql-9.3/bin
# pg_config --pgxs
# echo $ORACLE_HOME
/opt/instantclient_11_2
# echo $LD_LIBRARY_PATH
/opt/instantclient_11_2
# yum list installed 'gcc*'
Error: No matching Packages to list
# which gcc
/usr/bin/which: no gcc in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/opt/instantclient_11_2:/usr/pgsql-9.3/bin)
# echo 'Install gcc'
# yum install gcc
Installed:
  gcc.x86_64 0:4.8.2-16.2.el7_0

Dependency Installed:
  cpp.x86_64 0:4.8.2-16.2.el7_0  glibc-devel.x86_64 0:2.17-55.el7_0.5  glibc-hea

Complete!


b) Download 'oracle_fdw'

# mkdir /opt
# cd    /opt
#
# echo "Download oracle_fdw here ..."


c) Unzip 'oracle_fdw'

# cd    /opt
# unzip oracle_fdw-1.2.0.zip


d) Create symbolic link for Oracle library referenced by 'oracle_fdw' and add Oracle to Linux Shared Lib

$ cd /opt/instantclient_11_2/
$ ln -s libclntsh.so.11.1 libclntsh.so
$ ln -s libocci.so.11.1   libocci.so
$ ln -s libsqora.so.11.1  libsqora.so
$
$ echo /opt/instantclient_11_2 > /etc/ld.so.conf.d/oracle.conf
$ ldconfig 


e) Change directory to 'oracle_fdw' and Make and Make install

# cd /opt/oracle_fdw-1.2.0/
# pg_config --pgxs # Last check if pg_config is correctly configured
# make
# make install


f) Check Shared Library Dependency

# pg_config | grep PKGLIBDIR
PKGLIBDIR = /usr/pgsql-9.3/lib
# ls -l /usr/pgsql-9.3/lib/oracle_fdw*
-rwxr-xr-x 1 root root 433239 Mar  4 10:08 /usr/pgsql-9.3/lib/oracle_fdw.so
# ldd /usr/pgsql-9.3/lib/oracle_fdw.so
        linux-vdso.so.1 =>  (0x00007fff3f3fe000)
        libclntsh.so.11.1 => /opt/instantclient_11_2/libclntsh.so.11.1 (0x00007fb3a5e9a000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fb3a5ad1000)
        libnnz11.so => /opt/instantclient_11_2/libnnz11.so (0x00007fb3a5704000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fb3a5500000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fb3a51fd000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fb3a4fe1000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fb3a4dc8000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fb3a4bc5000)

        /lib64/ld-linux-x86-64.so.2 (0x00007fb3a8a27000)

2.5. Cofigure 'oracle_fdw' PostgreSQL Foreign Data Wrapper for Oracle

a) Restart PostgreSQL service

# systemctl stop postgresql-9.3.service ; systemctl start postgresql-9.3.service


b) Create PostgreSQL Extension, Server and Mapping

$ psql db_pgsql
db_pgsql=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
db_pgsql=# CREATE SERVER oraprod FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//oracle-homolog.inmetrics.com.br/prod.inmetrics.com.br');
CREATE SERVER
db_pgsql=# GRANT USAGE ON FOREIGN SERVER oraprod TO postgres;
GRANT
db_pgsql=# CREATE USER MAPPING FOR postgres SERVER oraprod OPTIONS (user 'dblink_postgres', password 'dblink_postgres');
CREATE USER MAPPING

2.6. Test dblink connection using 'oracle_fdw'

db_pgsql=# select oracle_diag();

                                      oracle_diag

--------------------------------------------------------------------------------
--------
 oracle_fdw 1.2.0, PostgreSQL 9.3.5, Oracle client 11.2.0.4.0, Oracle server 11.
2.0.3.0
(1 row)

db_pgsql=# CREATE FOREIGN TABLE ft_dual (
           dummy character varying(1) NOT NULL
           ) SERVER oraprod OPTIONS (schema 'SYS', table 'DUAL');
db_pgsql=# 
db_pgsql=# SELECT * FROM ft_dual;
 dummy
-------
 X
(1 row)
db_pgsql=# \q

3. References

• http://pgxn.org/dist/oracle_fdw/
• https://www.youtube.com/watch?v=Sz69kdItkPw
• http://stackoverflow.com/questions/2708317/libclntsh-so-11-1-cannot-open-shared-object-file

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/