PostgreSQL [Video]: Cross Database Queries using DbLink Extension

How we can perform cross database queries in PostgreSQL?

The different RDBMS systems like Microsoft SQL Server, MySQL, 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 the PostgreSQL, you cannot perform the cross database query like other database technology.

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

Using DbLink extension, you can perform cross database queries in the PostgreSQL.

In this demonstration, I have created two different databases. Respective names “Database_One” and “Database_Two”.

I created one sample table into “Database_One” and using DbLink, going to SELECT this table from “Database_Two”.
Now, We have to configure DbLink extension in “Database_Two”.

Below is a full demonstration of this:
Step 1: Create First sample table and database:

Step 2: Create Second sample table and database:

Now, I am going to SELECT Databasee_One data from Database_Two so we have to execute all below scripts on Database_Two to configure DbLink extension for cross database queries.


Step 1: Connect to Database_Two:

Step 2: Install / Create DBLink Extenstion:

Step 3: Verify the system tables of DBLink:

Step 4: Test the connection for Database_One:

Step 5: Create foreign data wrapper and server for global authentication.
You can use this server object for cross database queries:

Step 6: Mapping of user and server:

Step 7: Test this server:

Step 8: Now, you can SELECT the data of Database_One from Database_Two:
It is creating a virtual table so we have to specify return data type.

The Result:


Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

20 Comments on "PostgreSQL [Video]: Cross Database Queries using DbLink Extension"

Notify of
avatar
Sort by:   newest | oldest | most voted
Paszkal Bojti
Guest

Hi,
first of all, thank for this post. But to run this tutorial successfully, I had to make some modifications, and add some new lines. A fully working sequence:

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);

Cheers

TH
Guest

Hi,
thank for this post.But i get error “ERREUR: could not establish connection
État SQL :08001” when i try to execute query in my second database. can you help me?

TH
Guest

All ports are open and permission are corrects but i get the same error

Sampath Kumar Patel Midivelli
Guest
Sampath Kumar Patel Midivelli

Hi Anvesh,

I am new to Postgres and trying to work on cross databases queries. I am getting the below error when I was trying to create extension. MY postgres 9.5 is on CentOS7.

postgres=# CREATE EXTENSION dblink;
ERROR: could not open extension control file “/usr/pgsql-9.5/share/extension/dblink.control”: No such file or directory

I have searched for dblink.control in all folders but no luck.

It’s an immediate requirement for me. Please Help

Thanks,
Sampath

Sampath Kumar Patel Midivelli
Guest
Sampath Kumar Patel Midivelli

Hi Anvesh,

How to perform cross database table insert, update and delete operations?

Thanks,
Sampath

Pratika
Guest

Hi Avnesh,

I am having problem in the last step.I want to insert values in table named User present in database Test from User table present in backup table.Can u please help me with the query.

Thanks ,
Pratika

Khoa
Guest

Great! It’s very helpful. Thank you!

Bindu
Guest

Can we use dblink in Teradata?If yes, please help me with the versions and the procedure

Avadoot Nachankar
Guest

Thank you Anvesh Patel 🙂

wpDiscuz