Question 81659 in Docker Question posted in
The official documentation can be found here.

SQL Error [08001] when trying to connect to local postgresql with dblink_connect. psql connects fine with the same url – Docker

I got strange behavior from dblink_connect.

I run my postgresql’s servers using docker, mapped 5432 to localhost:5433.

When I am connecting to it using psql everything is fine

$ psql 'postgresql://pguser:pgpass@localhost:5433/mt5?sslmode=disable'
psql (14.4, server 10.22)

But when I connect to it from another postgresql’s instance using dblink_connect it fails

psql 'postgresql://pguser:pgpass@localhost:5431/pgdb?sslmode=disable'
psql (14.4, server 14.3)
Type "help" for help.

pgdb=# select dblink_connect('postgresql://pguser:pgpass@localhost:5433/mt5?sslmode=disable');
ERROR:  could not establish connection
DETAIL:  connection to server at "localhost" (127.0.0.1), port 5433 failed: Connection refused
        Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5433 failed: Address not available
        Is the server running on that host and accepting TCP/IP connections?
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the cause of error: as both instances are docker's they can't see each other as localhost: you have to use IP address of you internal network to which they exposed, like 192.168.88.22:

    pgdb=# select dblink_connect('postgresql://pguser:[email protected]:5433/mt5?sslmode=disable');
 dblink_connect
----------------
 OK
(1 row)

    I figured this out while asking question! Suppose it will help to somebody else.


  2. 0

    If your docker-compose.yaml file contains something like:

    services:
  postgres:
    image: postgres:15
    environment:
      POSTGRES_USER: pguser
      POSTGRES_PASSWORD: pgpass
      POSTGRES_DB: mydb

    Then you’d better use the host:DockerServiceName syntax, as it will not change (while your 192.168.88.22 IP may vary). You would do:

    SELECT dblink_connect('host=postgres port=5432 dbname=mydb user=pguser password=pgpass');

    If you prefer not to use service name, the parameter is hostaddr, like in:

    SELECT dblink_connect('hostaddr=192.168.88.22 port=5432 dbname=mydb user=pguser password=pgpass');

    (Be aware that there are risks associated with passing the password this way).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search