CODEWITHSUNDEEP
postgresql
Darren Oakey
Darren Oakey

Reputation: 3654

how do I create a dblink connection in postgres that I can keep using

so - I can create a dblink connection - eg

select * from dblink( 'dbname=whatever host=the_host user=the_user password=my_password', 'select x, y, z from blah')

works fine. I can even make what appears to be a persistent connection

select * from dblink_connect( 'dev', 'dbname=whatever host=the_host user=the_user password=my_password');

select * from dblink( 'dev', 'select x, y, z from blah' );

works fine. For a while. And then after a while - if I try to use dev again - it starts telling me "no open connection". But if I try to run the connect command again, it tells me a connection with that name already exists.

So how do I establish a named connection that I, and others, can just use directly forever afterwards without having to do any sort of connect/disconnect?

Upvotes: 2

Views: 6372

Answers (2)

jjanes
jjanes

Reputation: 44373

You can give dblink() the name of a foreign server, rather than the name of a connection.

create server dev foreign data wrapper dblink_fdw options (host 'thehost', dbname 'whatever');
create user mapping for public server dev options (user 'the_user', password 'my_password');

Then run your dblink query just as you currently are, using 'dev' as the name.

Note that this will increase the number of connections done, it is just that the system manages them so that you don't need to. So it is good for convenience, but not for performance.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 247950

The documentation says:

The connection will persist until closed or until the database session is ended.

So I suspect that you are using a connection pool, and

  1. you may get a different database session for each transaction (but the dblink connection is open in only one of them)

  2. the connection pool may close the backend connections after a while, thereby also closing the dblink connection

If you want to use a feature like dblink, where sessions outlive the duration of a transaction, you need session level pooling.

Upvotes: 2

Related Questions