[PostgreSQL] Cross database query with dblink

 PostgreSQL   dblink

▌Introduction

---

dblink is a module that supports connections to other PostgreSQL databases from within a database session. (Reference)

In this article, we will learn how to use psql to create a View with records located in another PostgreSQL database.

▋Related articles

1. [PostgreSQL] pgcrypto - encryption and decryption

2. [PostgreSQL] pgAgent - Scheduling agent

 

▌Environment

---

▋Docker Engine Community 18.09.2

▋postgres 10

▋pgAdmin 4

▌Implement

---

▋Create dblink

▋Install dblink extension

CREATE EXTENSION dblink;

▋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%';

▋Test connection of the target database

SELECT dblink_connect('host=<server_name_or_ip> user=<user> password=<pwd> dbname=<target_database>');

For example, we want to connect to the database: MyDatabase, in db-server-2, we can first test the connection like this,

SELECT dblink_connect('host=db-server-2 user=postgres password=xxxxxxx dbname=MyDatabase');

▋Create foreign data wrapper for global authentication

Create a connection wrapper that we can use this name for cross database query.

CREATE FOREIGN DATA WRAPPER <wrapper_name> VALIDATOR postgresql_fdw_validator;

CREATE SERVER <custom_server_name> FOREIGN DATA WRAPPER <wrapper_name> OPTIONS (hostaddr '<IP>', dbname '<db_name>' port '<db_port>');

CREATE USER MAPPING FOR <custom_user_mapping_name> SERVER <custom_server_name> OPTIONS (user '<user>', password '<pwd>');

Ex.

CREATE FOREIGN DATA WRAPPER data_wrapper VALIDATOR postgresql_fdw_validator;

CREATE SERVER db2 FOREIGN DATA WRAPPER data_wrapper OPTIONS (host 'db-server-2', dbname 'MyDatabase' );

CREATE USER MAPPING FOR postgres SERVER db2 OPTIONS (user 'postgres', password 'xxxxxx');

Notice that if you had already created the Password File (.pgpass), we can skip the password parameter on creating USER MAPPING.  CREATE USER MAPPING FOR postgres SERVER db2 OPTIONS (user 'postgres');

 

▋Test created server

SELECT dblink_connect('db2');

▋Give require permission to map user

GRANT USAGE ON FOREIGN SERVER <custom_server_name> TO <custom_user_mapping_name >;

In this example,

GRANT USAGE ON FOREIGN SERVER db2 TO postgres;

▋Cross database query

Now we can use the dblink to do cross database query.

For example, I created a View which query data from the other database: db-server-2.

CREATE VIEW "VmCustomers" AS

SELECT * FROM public.dblink('db2','SELECT Id, Type, Name, Address FROM public.message')

AS DATA("Id" integer, "Type" text,"Name" text, "Address" text)

WHERE "Type"='VIP'

▌Reference

---

▋PostgreSQL Cross Database Queries using DbLink