Leeladharan Achar - alias - leelu ~ blogging...hola mi amigos..'s Blog

Posted May 10, 2016   21087 views

PostgreSQL Cross Database Queries using DbLink

In this post I am going to show you that how we perform cross database query in PostgreSQL.

Like MSSQLServer, MySQL database, you can select data from one database to another database. What we are doing is we simply select data using database_name.schema.table. In PostgreSQL you cannot perform the cross database query like other database technology.

In this post I demonstrate DbLink extension of PostgreSQL which is used to connect one database to another database.

Step 1:

Install DbLink extension.

    CREATE EXTENSION dblink;

Step 2:

Verify DbLink:

    SELECT pg_namespace.nspname, pg_proc.proname 
    FROM pg_proc, pg_namespace 
    WHERE pg_proc.pronamespace=pg_namespace.oid 
       AND pg_proc.proname LIKE '%dblink%';

Step 3:

Test connection of database:

In this demo, I have two database one is Postgres and second is Employee and we execute query in Employee database to select data from Postgres database.

    SELECT dblink_connect('host=localhost user=postgres password=enjoy dbname=postgres');

If return result is OK only then you can connect your database using DbLink.

Step 4:

Create foreign data wrapper for global authentication. Once this connection wrapper is created and configure then you can use this name for cross database query.

    CREATE FOREIGN DATA WRAPPER dbrnd VALIDATOR postgresql_fdw_validator;
    CREATE SERVER demodbrnd FOREIGN DATA WRAPPER dbrnd OPTIONS (hostaddr '127.0.0.1', dbname 'postgres');
    CREATE USER MAPPING FOR postgres SERVER demodbrnd OPTIONS (user 'postgres', password 'enjoy');

Like above code first create Foreign Data Wrapper, create server with connection details and map server with user.

Step 5:

Now test created server:

    SELECT dblink_connect('demodbrnd');

Result is only OK.

Step 6:

Give require permission to map user:

    GRANT USAGE ON FOREIGN SERVER demodbrnd TO postgres;

Step 7:

Now all configuration is done, now you can select data from postgres database into employee database.

    SELECT * FROM public.dblink
    ('demodbrnd','select RollNo,Name from public.tbl_student') 
    AS DATA(RollNo INTEGER,Name CHARACTER VARYING);

Result is:

    DBlinkResult

This data is from postgres database and I executed above query into my employee database. As you can see in above query, you have to give dblink server name to execute cross database query and also its require to give a definition of returning data.

Copy of the content from http://www.dbrnd.com/2015/05/postgresql-cross-database-queries-using/

This is basic about DbLink and you can learn more on this at url:

http://www.postgresql.org/docs/current/interactive/dblink.html

Corrections from a comment from Paszkal Bojti
    CREATE EXTENSION postgres_fdw;
    CREATE EXTENSION dblink;
    SELECT dblink_connect(‘host=localhost user=USER password=PW dbname=DB’);
    CREATE FOREIGN DATA WRAPPER FDW VALIDATOR postgresql_fdw_validator;
    CREATE SERVER myServerName FOREIGN DATA WRAPPER FDW OPTIONS (hostaddr ‘127.0.0.1’, dbname ‘DB’);
    CREATE USER MAPPING FOR postgres SERVER myServerName OPTIONS (user ‘USER’, password ‘PW’);
    SELECT dblink_connect(‘myServerName’);
    GRANT USAGE ON FOREIGN SERVER myServerName TO postgres;
    SELECT * FROM dblink
    (‘myServerName’,’select id from DB.public.TABLE’)
    AS DATA(id INTEGER);