PostgreSQL [Video]: Cross Database Queries using DbLink Extension

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

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:

CREATE DATABASE Database_One;

CREATE TABLE public.tbl_Employee

(

EmpID INT PRIMARY KEY

,EmpName CHARACTER VARYING

,EmpGender CHAR(1)

);

INSERT INTO public.tbl_Employee

VALUES

(1,'Anvesh','M')

,(2,'Neevan','M')

,(3,'Martin','M');

Step 2: Create Second sample table and database:

1	CREATE DATABASE Database_Two;

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:

1	CREATE EXTENSION dblink;

Step 3: Verify the system tables of 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 4: Test the connection for Database_One:

1	SELECT dblink_connect('host=localhost user=dbrnd password=dbrnd dbname=database_one');

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

1 2	CREATE FOREIGN DATA WRAPPER dbrnd VALIDATOR postgresql_fdw_validator; CREATE SERVER demodbrnd FOREIGN DATA WRAPPER dbrnd OPTIONS (hostaddr '127.0.0.1', dbname 'database_one');

Step 6: Mapping of user and server:

1	CREATE USER MAPPING FOR dbrnd SERVER demodbrnd OPTIONS (user 'dbrnd', password 'dbrnd');

Step 7: Test this server:

1	SELECT dblink_connect('demodbrnd');

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.

SELECT * FROM public.dblink

('demodbrnd','SELECT EmpID,EmpName FROM public.tbl_Employee')

AS DATA(EmpID INTEGER,EmpName CHARACTER VARYING);

The Result:

EmpID | EmpName

-------------------------

1 | "Anvesh"

2 | "Neevan"

3 | "Martin"

May 22, 2015Anvesh Patel

Comments: 38

Paszkal Bojti
January 6, 2016 at 9:15 am
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
- Anvesh Patel
  January 6, 2016 at 10:06 am
  Thanks Paszkal for your input, I will also test this.
- TH
  March 3, 2016 at 7:56 am
  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?
  - Anvesh Patel
    March 3, 2016 at 8:43 am
    Have you completed all the step ?
    Yes, I can help you but require some more details from your end.
    - TH
      March 3, 2016 at 12:37 pm
      On this step “SELECT dblink_connect(‘myServerName’);” i have get the error.
      I work with Postgres 9.3 and I have two database Database1 and Database2. In my Database2 i have a table “USER”.I want to get users after execution of a query in Database1.
      On the Database1 i have run succefull different step but on the step to connect server i have get error “******** Erreur **********
      
      ERREUR: could not establish connection
      État SQL :08001
      ”
      Thank for your help
      - Anvesh Patel
        March 3, 2016 at 1:24 pm
        Please check your pg_hba.conf file and make sure that all require port is open.
        Also check the permission of the database user.
        For reference you can visit this article.
        https://www.dbrnd.com/2016/01/postgresql-how-to-allow-remote-connection-to-connect-database/
TH
March 3, 2016 at 1:48 pm
All ports are open and permission are corrects but i get the same error
- Anvesh Patel
  March 3, 2016 at 2:15 pm
  Please add my skype id: anvesh08
  You can contact me any time,
Sampath Kumar Patel Midivelli
March 29, 2016 at 1:49 pm
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
- Anvesh Patel
  March 30, 2016 at 5:02 am
  Hi Sampath,
  Yes, You are getting this error because dblink.sql is not available in proper path or PostgreSQL contrib package is not installed properly.
  Once you installed contrib package, then you can execute CREATE EXTENSION.
  
  You can visit this official document to configure contrib package.
  http://www.postgresql.org/docs/9.1/static/contrib.html
  
  You can also try this command to install a contrib package.
  sudo apt-get install postgresql-contrib
  
  You can also use below command to install a contrib package.
  gmake
  gmake install
  
  You can also try using Yum packages.
  
  I hope this will help you.
  -Anvesh
  - Sampath Kumar Patel Midivelli
    March 30, 2016 at 11:39 am
    Worked like a charm …. Thanks Anvesh
Sampath Kumar Patel Midivelli
April 1, 2016 at 9:02 am
Hi Anvesh,

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

Thanks,
Sampath
- Anvesh Patel
  April 1, 2016 at 9:58 am
  Consider dblink query as virtual subquery or derived table so you can perform insert, update and delete like anyother table join condition.
  
  Insert Example:
  
  INSERT INTO Test_Student
  SELECT * FROM public.dblink
  (‘demodbrnd’,’select RollNo,Name from public.tbl_student’)
  AS DATA(RollNo INTEGER,Name CHARACTER VARYING);
Pratika
May 13, 2016 at 7:30 am
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
- Anvesh Patel
  May 13, 2016 at 9:09 am
  As per my understanding, you require to perform cross insert statement. You can refer last comment or you can use this kind of query structure.
  INSERT INTO Test_Student
  SELECT * FROM public.dblink
  (‘demodbrnd’,’select RollNo,Name from public.tbl_student’)
  AS DATA(RollNo INTEGER,Name CHARACTER VARYING);
Khoa
September 26, 2016 at 8:12 am
Great! It’s very helpful. Thank you!
Bindu
September 27, 2016 at 5:58 am
Can we use dblink in Teradata?If yes, please help me with the versions and the procedure
- Anvesh Patel
  September 27, 2016 at 7:08 pm
  @Bindu, don’t have idea about teradata, will check and let you know.
  If anyone knows, please help her.
Avadoot Nachankar
November 4, 2016 at 6:31 am
Thank you Anvesh Patel 🙂
- Anvesh Patel
  November 6, 2016 at 7:34 am
  Your Welcome ! 🙂
Piyush
January 31, 2018 at 7:11 am
Hello Anvesh ,

I am using postgres database , I want to copy table from one database to another database, but I want different table name for target database.
- Anvesh Patel
  January 31, 2018 at 6:22 pm
  Try this,
  https://www.dbrnd.com/2017/02/postgresql-how-to-take-a-backup-of-your-table-pg_dump-pg_admin/
Jayesh
April 10, 2018 at 5:57 am
hi,
avnesh
in
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.
SELECT * FROM public.dblink
(‘demodbrnd’,’SELECT EmpID,EmpName FROM public.tbl_Employee’)
AS DATA(EmpID INTEGER,EmpName CHARACTER VARYING);

can we provide where condition ?
- Jayesh
  April 10, 2018 at 6:46 am
  SELECT * FROM public.dblink
  (‘demodbrnd’,’SELECT EmpID,EmpName FROM public.tbl_Employee’ where EmpName = ”Anvesh”’)
  AS DATA(EmpID INTEGER,EmpName CHARACTER VARYING);
Jayesh
April 10, 2018 at 7:03 am
hey avnesh,
JAYESH here again

SELECT * FROM public.dblink
(‘demodbrnd’,’SELECT EmpID,EmpName FROM public.tbl_Employee’ where EmpName = ”Anvesh”’)

AS DATA(EmpID INTEGER,EmpName CHARACTER VARYING);

can we have alternate to AS DATA(col1,col2,…)
such we can execute the select query without knowing the columns name
samantha
June 27, 2018 at 7:55 am
How about updating the fields?
- Anvesh Patel
  June 28, 2018 at 5:45 pm
  Do you want to update between databases?
JovannyRch
October 17, 2018 at 4:47 am
Thanks from Mexico
Snigdha Singh
December 17, 2018 at 8:06 am
Thanks a ton for making it so simple and easy.I was struggling with this problem for a week.
Kiran
February 13, 2019 at 10:37 am
Hai Anvesh
Need help in replicating the data between two tables in PostgreSQL which are present in two different Computers
- Anvesh Patel
  February 17, 2019 at 6:10 pm
  Need to setup replication between server. Do you want real time sync between tables?
  - Sujith M
    May 8, 2020 at 9:43 pm
    Can you please advise for real time sync up between two different DB’s
Antonio Pereira
March 27, 2019 at 5:27 pm
Obrigado Sr. Patel. Serviu de base para uma melhor compreensão.
Raj
January 11, 2020 at 10:41 pm
Thanks for the article Anvesh…Can we implement the same with out super user.. for ex I want to do the same with the user just having read write permissions..
Thank you
Raj
Savke
January 28, 2020 at 3:11 pm
Man, this is awesome… You can do UNION of multiple tables of multiple servers and insert into one table. And than monitor only that one table
yamini
March 17, 2020 at 10:28 am
please db link installation
troy
April 9, 2020 at 6:52 pm
Hi, I followed the steps and all is successful except the dblink query. I get this error:

ERROR: could not establish connection
DETAIL: fe_sendauth: no password supplied

The dblink_connect works fine the user mapping has the correct password. I googled but couldn’t find a solution. I have postgres version “PostgreSQL 12.2, compiled by Visual C++ build 1914, 64-bit”
KC Viswanathan
May 9, 2020 at 1:58 am
Excellent post!

PostgreSQL [Video]: Cross Database Queries using DbLink Extension

Anvesh Patel

About Me!

About DBRND !

Recent Comments !

Follow Me !