Unable to Sync data to PostgresDB using cht-sync

Hi,
I’m trying to sync my data to postgres and I’m following this cht-sync.
However looking at my logs I see both Logstash (logstash_1) and postgrest (postgrest_1) are not able to connect, see sample log error
ostgrest_1 | 19/Jan/2023:09:38:35 +0000: {“code”:“PGRST000”,“details”:“could not translate host name "postgres" to address: Try again\n”,“hint”:null,“message”:“Database connection error. Retrying the connection.”}
postgrest_1 | 19/Jan/2023:09:38:35 +0000: Attempting to reconnect to the database in 32 seconds…
logstash_1 | [2023-01-19T09:38:39,382][ERROR][logstash.inputs.couchdbchanges][main][6d96458f146fbed4fe572e56aa21cbb99a0bcb04637cfde4022a694c32493513] Connection problem encountered: Retrying connection in 10 seconds… {:error=>“Connection refused - Failed to open TCP connection to 192.168.8.170:5984 (Connection refused - connect(2) for "192.168.8.170" port 5984)”, :host=>“192.168.8.170”, :port=>“5984”, :db=>“medic”}
superset_1 | 127.0.0.1 - - [19/Jan/2023:09:38:45 +0000] “GET /health HTTP/1.1” 200 2 “-” “curl/7.74.0”
logstash_1 | [2023-01-19T09:38:49,388][ERROR][logstash.inputs.couchdbchanges][main][6d96458f146fbed4fe572e56aa21cbb99a0bcb04637cfde4022a694c32493513] Connection problem encountered: Retrying connection in 10 seconds… {:error=>“Connection refused - Failed to open TCP connection to 192.168.8.170:5984 (Connection refused - connect(2) for "192.168.8.170" port 5984)”, :host=>“192.168.8.170”, :port=>“5984”, :db=>“medic”}

Any guidance on how to resolve this issue?
Thank you.
Erick

Hello Erick,

From the logs it seems that postgrest is unable to make a connection to your postgres instance. Is your postgres database running in a docker container or are you running it as a standalone service?

Thanks @njuguna,
My postgres is running as a standalone on the local host.

Erick

@ewafula you will need to set the POSTGRES_HOST environment variable to localhost or the URL of your postgres instance. It defaults to postgres if it is not set as seen here and I believe that might be the issue. If you are using MacOS you will have to create an IP address alias to the host. Details on how to do this and an explanation of why it’s needed can be found here.

Thank you @njuguna, I’ve added POSTGRES_HOST but unfortunately the connection is not successful

ogstash_1 | [2023-01-20T18:15:18,609][ERROR][logstash.inputs.couchdbchanges][main][6d96458f146fbed4fe572e56aa21cbb99a0bcb04637cfde4022a694c32493513] Connection problem encountered: Retrying connection in 10 seconds… {:error=>“No route to host - Failed to open TCP connection to 192.168.8.170:5984 (No route to host - SocketChannel.connect)”, :host=>“192.168.8.170”, :port=>“5984”, :db=>“medic”}
postgrest_1 | 20/Jan/2023:18:15:19 +0000: {“code”:“PGRST000”,“details”:“connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused\n\tIs the server running on that host and accepting TCP/IP connections?\nconnection to server at "localhost" (::1), port 5432 failed: Address not available\n\tIs the server running on that host and accepting TCP/IP connections?\n”,“hint”:null,“message”:“Database connection error. Retrying the connection.”}
postgrest_1 | 20/Jan/2023:18:15:19 +0000: Attempting to reconnect to the database in 32 seconds…
logstash_1 | [2023-01-20T18:15:31,697][ERROR][logstash.inputs.couchdbchanges][main][6d96458f146fbed4fe572e56aa21cbb99a0bcb04637cfde4022a694c32493513] Connection problem encountered: Retrying connection in 10 seconds… {:error=>“No route to host - Failed to open TCP connection to 192.168.8.170:5984 (No route to host - SocketChannel.connect)”, :host=>“192.168.8.170”, :port=>“5984”, :db=>“medic”}

Updates:
I’ve managed to fix the connection to my local db and that is working fine now.
However for some reason logstash is not able to connect to the couchdb. I noticed that you can connect to couchdb successfully without the port number when using the curl command. It also connects fine when inside the couchdb container.
Any insights on how to allow connection to the couchdb container when the port is specified. For the command curl https://username:password@localhost:5984 returns Failed to connect to localhost port 5984 while curl https://username:password@localhost returns a successful connection.
My cht app version is 4.x
Thank you.
Erick