Statspack alternative for performance monitoring Oracle XE because AWR is not licenced

1. Introduction

Unfortunatly, Oracle Express does not license "AWR - Automatic Workload Management" (see references). But performance issues  exists in any system! You can install and use an old Oracle tool called StatsPac to monitor and capture statistical data from your Oracle instance.

2. Step by Step

2.1. Install StatsPack Binary

2.2. Test StatsPack

3. References

• http://www.oracle.com/us/products/database/enterprise-edition/comparisons/index.html
• http://www.oracledistilled.com/oracle-database/performance/installing-and-configuring-statspack/
• http://docs.oracle.com/cd/B10501_01/server.920/a96533/statspac.htm
• http://www.dba-oracle.com/tips_install_oracle_statspack.htm

Powershell script to read .csv file and processing each row

1. Introduction

This script shows how to process each line of a .csv file.  Put fields headers in first line.

2. Script

2.1. Sample 'services.csv' input file

servico,login,senha

Spooler,lp0795\tmp,tmp

Themes,lp0795\tmp,tmp

Spooler,lp0795\tmp,tmp

2.2. Script sample

# ##############################################################################

# filename: csv-process.ps1

# author  : josemarsilva@yahoo.com.br

# date    : 2015-10-09

# purpose : Sample script processing each row from a .csv file. First row has

#           fields titles

# pre-reqs: 

#           - Set-ExecutionPolicy Unrestricted

# remarks :

# ############################################

#

$csvFile = "C:\Users\Josemarsilva\Downloads\services.csv"

$bufferRead = Import-Csv $csvFile

foreach ( $line in $bufferRead ) {

    # println fields ...

    "servico = " + $line.servico + "; login = " + $line.login + "; senha=" + $line.senha

    # do whatever you want here! you have each field in $line.<field>

}

2.3. Script output sample

servico = Spooler; login = lp0795\tmp; senha=tmp

servico = Themes; login = lp0795\tmp; senha=tmp

servico = Spooler; login = lp0795\tmp; senha=tmp

Usefull tool for your end user query database and export result to Excel

1. Introduction

dbQueryExport is usefull tool to your final end user that does not know SQL, but frequently issues you need of information from your systems. Usually final end user needs the result of a query execution exported in a Excel file. Using dbQueryExport, you can customize command line arguments to pre-setting some options for your end user, like database connection, path-to-sqlfile, etc. And finally, you can create shortcut icons with these command line arguments.

dbQueryExport connect to databases ( Oracle, MySQL, PostgreSQL or any other database throught Jdbc Driver ).

2. Demonstration 

• Demo #1: Using dbQueryExport to export an Oracle database Query

• Demo #2: Using dbQueryExport to export a Mysql e database Query

• Demo #3: Using dbQueryExport to export a PostgreSQL database Query

3. Command line arguments

Usage: dbQueryExport [options]

    -c     Class name for invocation
    -d     Database Url location for Jdbc Driver
    -u     Username Jdbc connection
    -p     Password Jdbc connection
    -f     SQL Query Filename complete path
    -o     Export Filename complete path


Examples:
    dbQueryExport.jar -c oracle.jdbc.driver.OracleDriver -d jdbc:oracle:thin:@localhost:1521:dbname -u username -p password -f "C:\TEMP\sqlquery.sql" -o "C:\TEMP\sqlquery.xls"
    dbQueryExport.jar -c org.postgresql.Driver -d jdbc:postgresql://localhost/dbname -u username -p password -f "C:\TEMP\sqlquery.sql" -o "C:\TEMP\sqlquery.xls"
    dbQueryExport.jar -c com.mysql.jdbc.Driver -d jdbc:mysql://localhost:3306/dbname -u username -p password -f "C:\TEMP\sqlquery.sql" -o "C:\TEMP\sqlquery.xls"

See also:
    http://github.com/josemarsilva/dbQueryExport

4. References

• https://github.com/josemarsilva/dbQueryExport

Install OpenLDAP and phpLDAPadmin into Ubuntu 14.04

1. Introduction

This post only gathers information about how to install OpenLdap and phpLdapAdmin into Ubuntu 14.04

2. References

• https://www.digitalocean.com/community/tutorials/how-to-install-and-configure-openldap-and-phpldapadmin-on-an-ubuntu-14-04-server
• https://help.ubuntu.com/14.04/serverguide/openldap-server.html

Installing DBDesigner 4.0.5 on Ubuntu (32bits) 14.04 and solving libborqt error

1 Introduction

This post show step-by-step how to install DBDesigner tool into Ubuntu 14.04 32bits

2 Step-by-Step

2.1 Download DBDesigner from fabForce site or download 

Enter site http://fabforce.net/dbdesigner4/ and look for Linux 32bits i386 file. I used this file http://fabforce.net/downloadfile.php?iddownloadfile=2

2.2 Uncompress tar.gz file download

$ cd ~/Downloads/
$ tar -zxvf DBDesigner4.0.5.4.tar.gz 

2.3 Enter DBDesigner subdirectory and try to run it. If the result is an error trying to open sared object "libborqt*.so" then follow the next steps

$ cd DBDesigner4/
$ ./DBDesigner4
libborqt-6.9-qt2.3.so: cannot open shared object file: No such file or directory

2.4 Download library from Kylix and uncompress 

$ cd ~/Downloads/
$ wget http://ufpr.dl.sourceforge.net/sourceforge/kylixlibs/kylixlibs3-borqt-3.0-2.tar.gz
$ tar zxvf kylixlibs3-borqt-3.0-2.tar.gz

2.5 Move correct version of library 'libborqt' to '/usr/lib' and adjust permissions

$ cd kylixlibs3-borqt/
$ sudo mv libborqt-6.9.0-qt2.3.so /usr/lib
$ sudo chown root:root /usr/lib/libborqt-6.9.0-qt2.3.so
$ sudo chmod ugo+r /usr/lib/libborqt-6.9.0-qt2.3.so

2.6 Create a symbolic link from expected file name of library to correct version of library

$ sudo ln -s /usr/lib/libborqt-6.9.0-qt2.3.so /usr/lib/libborqt-6.9-qt2.3.so

2.7 Install another required libraries 'libjpeg62'

$ sudo apt-get install libjpeg62:i386

2.8 Run DBDesigner successfully

$ cd DBDesigner4/
$ ./DBDesigner4

3. References

• http://www.vivaolinux.com.br/artigo/Instalacao-e-correcao-de-erros-do-DBDesigner-4-no-Linux
• http://askubuntu.com/questions/313691/missing-libjpeg-so-62-from-ia32-shared-library

Installing Oracle XE 10.2 on Linux Ubuntu 32bits 14.04

1. Introduction

This post only gathers information about how to install Oracle XE 10.2 on Linux Ubuntu 32 bits 14.04.

• Oracle XE database versions 11 and 12 are not available on plataform x86 32bits Linux. You have to install version 10g.

2. References

• http://meandmyubuntulinux.blogspot.com.br/2013/03/installing-oracle-xe-on-ubuntu-32-bit.html

How to install Rails ( Ruby on Rails ) on Ubuntu 14.04

1. Introduction

This post only gathers information about how to install RAILS ( Ruby on Rails ) on Ubuntu 14.04.

2. Steps

Follow instructions on reference document from GoRails:

• Install/update dependencies
• Install Ruby 2.2.1  (recommended)
• Install RbEnv, RubyBuild, 
• Configure a SSH Key for Git connnect to GitHub 
• Install Rails 4.2.0 (recommended)
• Setting Up MySQL
• Setting Up PostgreSQL

3. References

• https://gorails.com/setup/ubuntu/14.04

How to install PostgreSql 9.4 on Ubuntu Desktop 14.04 (Trusty)

1. Introduction

This post gathers information about installing PostgreSql 9.4 on Linux Ubuntu Desktop 14.04 (Trusty)

2. Step-by-Step

• Add apt repository

$ sudo vim /etc/apt/sources.list.d/pgdg.list 
# PostgreSQL 9.4 
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main

• Import the repository signing key, and update the package lists

$ wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \
  sudo apt-key add -
sudo apt-get update

• Install PostgreSQL

$ sudo apt-get install postgresql-9.4
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following package was automatically installed and is no longer required:
  dkms
Use 'apt-get autoremove' to remove it.
The following extra packages will be installed:
  libpq5 pgdg-keyring postgresql-client-9.4 postgresql-client-common
  postgresql-common
Suggested packages:
  oidentd ident-server locales-all postgresql-doc-9.4
The following NEW packages will be installed:
  libpq5 pgdg-keyring postgresql-9.4 postgresql-client-9.4
  postgresql-client-common postgresql-common
0 upgraded, 6 newly installed, 0 to remove and 382 not upgraded.
Need to get 4.921 kB of archives.
After this operation, 23,6 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
  :
* Starting PostgreSQL 9.4 database server                               [ OK ] 

• Connect to PostgreSQL 9.4 and check basic

$ sudo su - postgres
$ psql 
psql (9.4.1)
Type "help" for help.

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   

-----------+----------+----------+-------------+-------------+---------------------

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres         

           |          |          |             |             | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres         

           |          |          |             |             | postgres=CTc/postgres

(3 rows)

postgres=# select schemaname, count(*) from pg_tables group by schemaname;

     schemaname     | count 

--------------------+-------

 information_schema |     7

 pg_catalog         |    51

(2 rows)

3. References

• http://www.postgresql.org/download/linux/ubuntu/

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/