dblink

Introduction

dblink is an extension module that supports connections to other RDS for PostgreSQL databases. dblink is mainly used for distributed query. It allows you to query data between different databases. It can consider different databases as a whole, so that you can query data or perform other operations across databases.

It can also be used for data backup and restoration, and data synchronization. It greatly improves the flexibility and scalability of your databases, helping you use databases more efficiently and reliably. For more information, see the official dblink documentation.

Supported Versions

This extension is available to RDS for PostgreSQL 9.5 to 15.

You can run the following SQL statement to check whether your DB instance supports this extension:

SELECT * FROM pg_available_extension_versions WHERE name = 'dblink';

If this extension is not supported, upgrade the minor version of your DB instance.

To see more extensions supported by RDS for PostgreSQL, go to Supported Extensions.

Extension Installation and Uninstallation

• Installing the extension

  SELECT control_extension ('create', 'dblink');

• Uninstalling the extension

  SELECT control_extension ('drop', 'dblink');

For more information, see Installing and Uninstalling an Extension on the RDS Console and Installing and Uninstalling an Extension Using SQL Commands.

When dblink is used to perform cross-database operations, the server IP addresses of the two DB instances involved must be in the same VPC.

How to Use

Scenario 1:

If certain operations need to be performed in multiple databases in an instance, you can use dblink to perform cross-database operations.

-- 1. Create databases.
create database homedb;
create database db1;

-- 2. Switch to homedb and create a dblink connection.
select dblink_connect('connect_db1', 'dbname=db1 port=5432 user=root password=******** host=127.0.0.1');
dblink_connect
----------------
OK
(1 row)

-- 3. Run SQL statements.
-- Query data.
SELECT * FROM dblink('connect_db1', 'select * from test') as test(id integer, info varchar(8));
id | info
----+------
1 | a
2 | b
(2 rows)

-- Insert data.
SELECT dblink_exec('connect_db1', 'insert into test values(3,'c')');
dblink_exec
-------------
INSERT 0 1
(1 row)

-- 3. Switch to db1 and view the result.
select * from test;
id | info
----+------
1 | a
2 | b
3 | c
(3 rows)

-- 4. Close the remote connection.
SELECT dblink_disconnect('connect_db1');
dblink_disconnect
----------------
OK
(1 row)

Scenario 2:

If there are two RDS for PostgreSQL DB instances in the same VPC, one production instance (db1) and one test instance (db2), and data in the test instance needs to be synchronized to the production instance, you can use the dblink extension to synchronize the data.

-- 1. Log in to the production instance and create a database.
create database db1;

-- 2. Switch to the production database db1 and connect to the test database db2.
select dblink_connect('connect_db2', 'dbname=db2 port=5432 user=root password=******** host=10.29.182.247');
dblink_connect
----------------
OK
(1 row)

-- 3. Query data of table test1 in test database db2.
SELECT * FROM dblink('host=10.29.182.247 port=5432 user=root password=******** dbname=db2', 'select * from test1') as test1(id int, name text);
id | name
----+------
1 | a
2 | b
(2 rows)

-- 4. Synchronize data of table test1 in test database db2 to table backup1 in production database db1.
insert into backup1 SELECT * FROM dblink('dbname=db2 port=5432 user=root password=******** host=10.29.182.247', 'select * from test1') as backup1(id int, name text);
dblink
-------------
INSERT 0 2
(2 row)

--5. Query data of table backup1 in production database db1.
select * from backup1;
id | name
----+------
1 | a
2 | b
(2 rows)

--6. Close the connection.
SELECT dblink_disconnect('connect_db2');
dblink_disconnect
----------------
OK
(1 row)