How To Create Dblink In Postgresql

Setting up DB link in PostgreSQL

Please find the instructions in normal font and the commands italicized.

Purpose:

'DB Link' in PostgreSQL enables a database user to access a table present on a different postgres instance. It provides a functionality in PostgreSQL similar to that of 'DB Link' in Oracle, 'Linked Server' in SQL Server and 'Federated tables' in MySQL.

Environment:

OS Version: I worked on CentOS 7. The OS vers i ons were same on both the hosts.
DB Version: I worked on PostgreSQL 10.6. PostgreSQL versions were same on both the hosts.
Plugin: PostgreSQL package 'postgresql-contrib' needs to be installed on host 'goku'.
Accessibility: User should be able to connect to the remote database from the host he is working on.
Credentials: User credentials (user, password, port and hostname) of the user which accesses the table on remote database are needed.

Setup:

Here, I work on 2 hosts, viz: 'goku' and 'vegeta'.
A table by name 'emp' is created on the 'vegeta' database which resides on 'vegeta' host.
I access this table using the db link on the 'goku' database which resides on 'goku' host.

Current Host/DB details:
Hostname: goku
DB name: goku (Created in steps ahead)

Remote Host/User details:
Hostname: vegeta
Port on which MySQL runs: 5432
DB name: vegeta (Created in steps ahead)
User: veguser (Created in steps ahead)
Password: veguser (Set for user 'veguser' in steps ahead)

Steps to create DB link:

Create a user 'gokuser' on the host 'goku' on PostgreSQL prompt. Note that 'gokuser' should have superuser privilege. After this, create a database 'goku' whose owner is the user 'gokuser'.
create user gokuser PASSWORD 'gokuser';
ALTER USER gokuser WITH SUPERUSER;
create database goku OWNER gokuser;

Create a user 'veguser' on the host 'vegeta' on PostgreSQL prompt. After this, create a database 'vegeta' whose owner is the user 'veguser.
create user veguser PASSWORD 'veguser';
create database vegeta OWNER veguser;

Connect using the user 'gokuser' on the database 'goku'. Load the 'dblink' extension using 'CREATE EXTENSION' command.
CREATE EXTENSION dblink;

On database 'goku', create a foreign server object using the 'CREATE SERVER' command. Here, you need to enter the details of the host, the remote database you intend to connect and the port on which PostgreSQL is running. In our case, the remote database is 'vegeta'.
CREATE SERVER server_vegeta_remote FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'vegeta', dbname 'vegeta', port '5432');

Create a server mapping for 'gokuser' user on the foreign server. Here, we also need to provide the remote user name and its password so that 'gokuser' can connect remotely using the credentials of remote user (In our case, 'veguser').
CREATE USER MAPPING FOR gokuser SERVER server_vegeta_remote OPTIONS (user 'veguser',password 'veguser');

Provide the permission to 'gokuser' to use the foreign server.
GRANT USAGE ON FOREIGN SERVER server_vegeta_remote TO gokuser;

Now, we create a table 'emp' on the database 'vegeta' and insert some records in it.
CREATE TABLE emp (empid int,empname text);
INSERT INTO emp (empid,empname) VALUES (1,'Gohan');
INSERT INTO emp (empid,empname) VALUES (1,'Piccolo');
INSERT INTO emp (empid,empname) VALUES (1,'Tien');
select * from emp;

Establish a connection to the foreign server using the dblink_connect() function.
SELECT dblink_connect('conn_db_link','server_vegeta_remote');

Execute a select query using dblink() function on 'goku' database to access the data present in 'vegeta' database in the 'emp' table.
SELECT * from dblink('conn_db_link','select * from emp') AS x(a int,b text);

Think. Tinker. Test.

How To Create Dblink In Postgresql

Source: https://medium.com/@techrandomthoughts/setting-up-db-link-in-postgresql-d196468b43f8#:~:text=Load%20the%20'dblink'%20extension%20using,on%20which%20PostgreSQL%20is%20running.

Posted by: vangentler63.blogspot.com