Cannot Resync anymore the data from the couchdb to AWS

Marcelo_De_Guzman · March 12, 2024, 2:06am

Hi,

I am having a hard time to know what is the issue here. But, we cannot sync the data anymore.

How can I fix this one?

Thank you for helping. I really appreciate all of it.

@binod @diana @mrjones

Marcelo_De_Guzman · March 12, 2024, 2:15am

I have an updated docker-compose.yml

version: ‘3.7’
services:

postgres:
image: medicmobile/cht-postgres:release-postgres13-rc.1
restart: always
environment:

  POSTGRES_DB: ${POSTGRES_DB_NAME:-cht}
  POSTGRES_USER: ${POSTGRES_USER_NAME:-postgres_root}
  POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres_root_password}

  COUCH2PG_USER: ${COUCH2PG_USER:-cht_couch2pg}
  COUCH2PG_USER_PASSWORD: ${COUCH2PG_USER_PASSWORD:-cht_couch2pg_password}
  DB_OWNER_GROUP: ${DB_OWNER_GROUP:-cht_analytics}
volumes:
  - cht-postgres-data:/var/lib/postgresql/data
ports:
  - ${POSTGRES_PORT:-5432}:5432

cht-couch2pg:
image: medicmobile/cht-couch2pg:main-node-12
environment:
COUCHDB_URL: ${COUCHDB_URL:-https://medic:password@localhost:5988/medic}

  POSTGRES_USER_NAME: ${COUCH2PG_USER:-cht_couch2pg}
  POSTGRES_PASSWORD: ${COUCH2PG_USER_PASSWORD:-cht_couch2pg_password}
  POSTGRES_SERVER_NAME: ${POSTGRES_SERVER_NAME:-postgres}
  POSTGRES_DB_NAME: ${POSTGRES_DB_NAME:-cht}

  COUCH2PG_CHANGES_LIMIT: ${COUCH2PG_CHANGES_LIMIT:-100}
  COUCH2PG_SLEEP_MINS: ${COUCH2PG_SLEEP_MINS:-60}
  COUCH2PG_DOC_LIMIT: ${COUCH2PG_DOC_LIMIT:-1000}
  COUCH2PG_RETRY_COUNT: ${COUCH2PG_RETRY_COUNT:-5}
depends_on:
  - postgres

volumes:
cht-postgres-data:
name: cht-postgres-data

And when there is an issue on the syncing on the backend side, I will just do a force sync using this command.

docker-compose --env-file couch2pg.env up -d

So, what do you think the problem is?

Marcelo_De_Guzman · March 12, 2024, 3:33am

I have seen this post, Read-only replica of couchDB docs into postgres for data-visualization - #18 by cliff

And, the thing is, it needs to delete the container,.

Is there a possible solution that is not requred to delete or remove the containers ?

Thanks and God Bless.

@binod @diana @mrjones

diana · March 12, 2024, 4:02am

Hi @Marcelo_De_Guzman

This error that you’ve shared seems to be about mapping authentication mechanisms to hosts.
I’m not really familiar with how Postgres does this, but I found this thread on stackoverflow about a very similar error to yours: pg hba.conf - connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host - Database Administrators Stack Exchange

My guess is that containers restarted (?) or something else caused the IP of your postgress host to change.
Can you check the contents of this pg_hba.conf file? Does it indeed not have an entry for this IP?
Can you try adding it, just like the stackoverflow thread says?

Marcelo_De_Guzman · March 12, 2024, 4:06am

Hi @diana ,

Thank you for responding.

How do I get to this pg_hba.conf ?

from the git bash
then login using production credential
then sudo su
then go to /srv/cht-couch2pg
the ls, to look the file?

is this the right path?

Thanks and God Bless

Marcelo_De_Guzman · March 12, 2024, 7:05am

When I looked at this location /var/lib/pgsql/data/pg_hba.conf,

there is no file or directory.

Marcelo_De_Guzman · March 12, 2024, 7:55am

Hi @mrjones , I saw your post about Syncing data on the couchdb,

When removing the container for cht-couch2pg-cht-couch2pg-1 and cht-couch2pg-postgres-1, is the data will not be affected on medic-os or haproxy?

Because we have important data there from the production app.

I am sorry, it was sounds like desperate messages here, we need this to be fix that our team can know what now is the status of the data.

Thank you and God Bless to you and all who helps me fixing this.

Marcelo_De_Guzman · March 12, 2024, 8:19am

existing pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
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
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all md5

Hi @diana, I followed your recommendation and still I have the error of no pg_hba.conf entry for host “10.2.3.212”, user “couch2pg”, database “postgres”, no encryption.

Hi @mrjones , If I will try your suggestion here, Read-only replica of couchDB docs into postgres for data-visualization - #19 by mrjones, and I have 2M rows on couchdb, what time will I finish?

@binod , thank you very much on your help, and sorry I am like desperate to resolve my issue.

Thank you and God Bless to all. Will wait for your answers

diana · March 12, 2024, 10:19am

Hi @Marcelo_De_Guzman

Are you using AWS RDS? I’m seeing a lot of posts about people having the same issue with RDS (example: no pg_hba.conf entry for host "my-ip", user "postgres", database "db-name", no encryption - #8 by Juan_Manuel_Truffa - postgresql - Posit Community)

Marcelo_De_Guzman · March 12, 2024, 12:42pm

Hi @diana ,

Yes, I am using AWS RDS to store data from couchdb to AWS. And from AWS, we can manipulate the data to create a dashboard for reporting.

diana · March 12, 2024, 1:25pm

Can you check the thread that I gave you to see if you can find any useful information?

Marcelo_De_Guzman · March 12, 2024, 11:35pm

Hi @diana,

I already explore the thread and there is no useful information there. It was on the R connection from AWS.

On my end, it was from couchdb to AWS.

There is an issue from syncing (mirroring) data from couchdb to AWS RDS.

diana · March 13, 2024, 12:56am

It looks like cht-couch2pg is having trouble uploading to postgres. The error “no encryption” points to AWS RDS only accepting https connections, just like the thread I linked said.
Are you using https to connect to RDS?

Not sure how you have your RDS set up. The problem seems to be there rather than anywhere else.
Here’s another example of someone encountering the same issue: Error connecting to postgres db: no pg_hba.conf entry for host "MyIPadres", user admin, database postgres, no encryption - How To - Wappler Community (except hosted on Azure).

diana · March 13, 2024, 1:01am

Maybe this will help: how to fix "FATAL: no pg_hba.conf entry for host" for aws rds postgres · Issue #21616 · dbeaver/dbeaver · GitHub

yuv · March 13, 2024, 1:03am

Hi @Marcelo_De_Guzman , to ensure that the Postgres server is configured properly, can you do following:

With any Database tool, for example like Dbeaver, try connecting the postgres server you’ve specified in couch2pg setting with the values you’ve specified.

In the process of debugging, did you modify pga_hba.conf from it’s original value?

Marcelo_De_Guzman · March 13, 2024, 1:29am

Hi @diana,

I fixed it by using this forum.

It was when it was upgraded the PostgreSQL from older to 14.X and up.

What I’ve done is something like this;

Go to AWS console, and go to RDS,
Check the Parameter for the RDS ( I checked it and it was default.postgres15), So,
I create another parameter for this one and update force.ssl value from 1 to 0,
then go back to the rds and modify it, change the rds and reflect immediately.
then their is some time for modifying the rds, after it is finished, just reboot the rds, and once it was finished,
I go back to the bash shell, and try again to do a docker-compose, and it was now syncing perfectly.

Thank you very much for the help.

bssennoga · March 16, 2024, 1:27pm

Tracking this issue with our couch2pg as well. Worked backwards from AWS, restarted the containers, but didnt think to check RDS. Will share here when we know more.