Issues with connecting postgres (localhost) with latest odk central (v 2023.2)

I tried to install ODK latest version in my local Ubuntu machine but it has some issues to connect with my local PostgreSQL db. it is running on port 5432 and i have turned off firewall.

central-service-1 Error: connect ECONNREFUSED 127.0.0.1:5432

My env

DOMAIN=local
SYSADMIN_EMAIL=administrator@email.com
SSL_TYPE=selfsign
HTTP_PORT=81
HTTPS_PORT=443

Optional: connect to a custom database server
DB_HOST=localhost
DB_USER=postgres
DB_PASSWORD=****
DB_NAME=mydbname

I followed all the steps given in the docs .will it connect with local PostgreSQL db , what am i doing wrong?

Dockered version of postgres has this error

central-service-1 exited with code 1
central-postgres14-1 | 2023-05-17 05:46:14.379 UTC [55] LOG: incomplete startup packet
central-postgres14-1 | 2023-05-17 05:46:14.649 UTC [56] FATAL: password authentication failed for user "odk"
central-postgres14-1 | 2023-05-17 05:46:14.649 UTC [56] DETAIL: Connection matched pg_hba.conf line 100: "host all all all scram-sha-256"
central-service-1 | Error: password authentication failed for user "odk"
central-service-1 | checking migration success..

What is the local Ubuntu version?

DB_HOST needs to be the IP or hostname of the database from the perspective of the service container. When you put localhost, the service container is trying to connect to itself.

I haven't tried this myself, but according to https://stackoverflow.com/questions/24319662, if you use host.docker.internal as the DB_HOST and the following to your compose file, it should work.

extra_hosts:
    - "host.docker.internal:host-gateway"

I am using Ubuntu 22.04.2 LTS.

Hi @yanokwa , thanks it worked , but i have to add changes in my pg_hba conf
host all biodiv 0.0.0.0/0 md5 because i got an error

central-service-1 exited with code 1
central-service-1             | wait-for-it.sh: host.docker.internal:5432 is available after 0 seconds
central-service-1             | generating local service configuration..
central-service-1             | running migrations..
central-service-1             | Error: no pg_hba.conf entry for host "172.24.0.5", user "biodiv", database "mydbname", no encryption
central-service-1             | checking migration success..
central-service-1             | Error: no pg_hba.conf entry for host "172.24.0.5", user "biodiv", database "mydbname", no encryption
central-service-1             | *** Error starting ODK! ***
central-service-1             | After attempting to automatically migrate the database, we have detected unapplied migrations, which suggests a problem with the database migration step. Please look in the console above this message for any errors and post what you find in the forum: https://forum.getodk.org/

and this change listen_addresses ='*' in postgresql.conf without it i have this error

central-service-1 exited with code 0
central-service-1             | wait-for-it.sh: timeout occurred after waiting 15 seconds for host.docker.internal:5432
central-service-1             | generating local service configuration..
central-service-1             | running migrations..
central-service-1             | Error: connect ECONNREFUSED 172.17.0.1:5432
central-service-1             | checking migration success..
central-service-1             | Error: connect ECONNREFUSED 172.17.0.1:5432
central-service-1             | *** Error starting ODK! ***
central-service-1             | After attempting to automatically migrate the database, we have detected unapplied migrations, which suggests a problem with the database migration step. Please look in the console above this message for any errors and post what you find in the forum: https://forum.getodk.org/
central-service-1 exited with code 0

its IP (172.24.0.5) keeps on changing when i stopped and ran it again. is this a concern , any other way can i overcome these changes?

The changes you've made so far seem expected.

I'd be cautious about listen_addresses. https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-against-automated-attacks has more guidance on that. The most important thing here is to make sure your database isn't accessible outside of your local machine.

The 172.x.x.x IP range is usually from Docker. Not sure how to work around that. Either way, it's outside the scope of the support we can provide on ODK.

Hi @yanokwa, I have discovered an alternative method for connecting ODK Central to localhost Postgres. Here are the modifications I made:

Environment variable changes:

DB_HOST=192.X.X.X
DB_USER=odk
DB_PASSWORD=XXXX
DB_NAME=odk

Modifications in postgresql.conf:
listen_addresses ='localhost,192.X.X.X'

Modifications in pg_hba.conf:
host all all 172.0.0.0/8 md5

These adjustments have proven to be effective, and thanks for the support.

1 Like