Create db_link to the same Database in Postgres

Question

We do have a requirement to create a db_link(Database link) have to refer to the same database.

When I am looking at the code in the function. It is some how like this.

perform dblink_connect(cn,'loopback'::text);
   cn is the connection name;

I verified if there are any foreign servers with the name loopback by using this query. But, no luck

select 
    srvname as name, 
    srvowner::regrole as owner, 
    fdwname as wrapper, 
    srvoptions as options
from pg_foreign_server
join pg_foreign_data_wrapper w on w.oid = srvfdw;

I suspect that loopback refers to the same database.

When I execute the Function, I am receiving the below error

**Error:password is required**

I verified the db_link extension it's there and dblink_fdw is also there

Environment:Postgres RDS

Laurenz Albe · Accepted Answer · 2024-01-17 10:32:53Z

2

The function call should look like this:

PERFORM dblink_connect(cn, 'host=loopback user=...');

This is a normal libpq connect string; see the documentation for details.

It would be smarter to use the IP addresses in the connection string, like

host=127.0.0.1 user=...

or to use domain socket connections, like this

host=/var/run/postgresql user=...

(that is, if your unix_socket_directories contains this directory).

pg_hba.conf should contain lines like

# lockal socket connections
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

If you want password authentications, use md5 instead of trust and supply a password in the connection string.

I have never heard of dblink_fdw …

edited Jan 17, 2024 at 10:32

answered Jan 17, 2018 at 16:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ranganath Over a year ago

Thanks Laurenz Albe, I am not aware of loopback connections. Can you please post a link that explains the dblink loopback. How to supply password to the loopback connection?

Ranganath Over a year ago

So, Do I need to create foreign Server with the name of loopback to refer to the same database? Notes: -dblink_fdw Foreign Data Wrapper is created automatically when we CREATE EXTESION DBLINK; -To avoid hardcoding the password we will create foreign server reference: stackoverflow.com/questions/44663478/…

Laurenz Albe Over a year ago

You don't need a FDW. loopback is a DNS hostname for the machine itself. The PostgreSQL documentation says nothing about a dblink_fdw.

John Demetriou Over a year ago

I'm getting error that hostname cannot be found to be honest. Any other ideas?

Laurenz Albe Over a year ago

@JohnDemetriou Then use the IP address 127.0.0.1

Collectives™ on Stack Overflow

Create db_link to the same Database in Postgres

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related