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.oidAND 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=192.168.1.158 port=5444 user=postgres password=post 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 test_server FOREIGN DATA WRAPPER dbrnd OPTIONS (hostaddr '192.168.1.158', dbname 'postgres');CREATE USER MAPPING FOR postgres SERVER test_server OPTIONS (user 'postgres', password 'post');

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('test_server');

Result is only OK.
Step 6:Give require permission to map user:

GRANT USAGE ON FOREIGN SERVER test_server TO postgres; 

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

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

Please mention the abover query what you want fetch the column na please mention the fields after as