Replicating Data Using couch2pg to Postgres Local Db

Technical Support
, ,
1

Good afternoon
Trying to pull data from medic couchDb to my local Postgres Db (Relational) so that I can access contact registration data, which is currently not available on the medic portal. We are using the codes below on the test environment using medic-couch2pg:

     export POSTGRESQL_URL=jdbc:postgresql://postgres:admin@localhost:5432/bodadb
 export COUCHDB_URL=http://dbName:dbPwd@domain:5984/medic
 export COUCH2PG_SLEEP_MINS=120
     export COUCH2PG_DOC_LIMIT=1000
     export COUCH2PG_RETRY_COUNT=5
     node index.js

Running the codes brings errors, will appreciate any insights, especially if I missed something

2

Good afternoon,
Please share the errors encountered and steps followed to facilitate investigation into where the problem could be.

3

@elias
In summary, we already CHT medic tool configured and working, we can access case investigation report, our interest is to have both the contact registration and case investigation data. Had sorted out the error with the local staging db, called bodadb, re-running the code and it recurs
Steps to replicate from CouchDb to Postres (GitHub - medic/cht-couch2pg: Software for creating read-only replicas of data from CHT applications, using PostgreSQL v9.4)

couch2pg errors
couch2pg errors973×223 8.86 KB

couch2pg errors_2
couch2pg errors_21572×601 19.7 KB

4

The first screenshot indicates that host that’s running couch2pg is unable to reach the postgres server because DNS lookup timed out. This is most commonly due to a typo in the url but could also be caused by faulty DNS settings.

For the second screenshot, see

database “bodadb” does not exist

Confirm that the database exists on the postgres server and access rights have been granted to the couch2pg user.

5

@elijah
I can connect to the local staging Postgres db called bodadb on my local machine both using pgAdmin and CLI, no tables created so far, owner is postgres. I granted all privileges to the user postgres to bodadb

Thinking on how to grant access rights to the couch2pg user, I no user exist called couch2pg, please clarify.

6

The couch2pg user can be created using this query:

CREATE USER couch2pg WITH ENCRYPTED PASSWORD 'mypassword';

Then access privileges granted using:

GRANT ALL PRIVILEGES ON DATABASE bodadb TO couch2pg;

Finally, configuration for postgres URL should be

export POSTGRESQL_URL=postgres://couch2pg:mypassword@localhost:5432/bodadb
7

@elijah
Created another user as above, couch2pg and granted access to my staging Db, still errors when I ran the code, cant access the local Postgres database

8

Sorry to hear @oyierphil.

Are the errors the same or new ones have been raised?

9

@elijah
I get the same error, despite creating another user on my local Posgres and exporting the following variables:
error: password authentication failed for user “couch2pg”

export POSTGRESQL_URL=postgres://couch2pg:bodapg@localhost:5432/bodadb

10

This means the password used in the configuration file is not the one in use by postgres so authentication fails. The password for the couch2pg user can be updated using this command

ALTER USER couch2pg WITH PASSWORD 'new_password';
11

I have done this many times, even used the postgres user and same error, either error: password authentication failed for user “couch2pg” or error: database “bodadb” does not exist

I have seen online similar challenge, where the local Postgres Db not been seen, yet one can access it both using CL and pgAdmin. Researching on why a local DB, on localhost can’t be accessed in Ubuntu server

12

Please elaborate on what the architecture looks like.

Is localhost your local machine and ubuntu server is in the cloud? Is there a firewall or other form of access control implemented on the server?

13

Localhost is my machine, my laptop. I have a virtualized server, physically located within the data centre at the my workplace with a test VM, which has a public IP address and a subdomain name mapped to the IP

I replicated CouchDb instance from the CHT application on the server to my localhost instance, and this is successful.

14

If both postgres and couchDB are local and both services are running, they should be able to communicate via couch2pg. I’ll share recommendations for the error messages encountered:

error: password authentication failed for user “couch2pg”
Updating the couch2pg user password using the query below should help with this error:

ALTER USER couch2pg WITH PASSWORD 'new_password';

error: database “bodadb” does not exist
Case sensitivity of database names in postgres could be causing this i.e. bodadb is different from bodaDB and Bodadb. The database can be created using this query:

CREATE DATABASE bodadb;
15

@elijah
I think we are not communicating…
I indicated that despite creating the local Postgres Db and accessing it both using CLI and PgAdmin, running the environment variable codes brings the error above on the remote Ubuntu server

16

Thank you for the clarification @oyierphil.

With couch2pg running on the server and the connection configuration set to

export POSTGRESQL_URL=postgres://couch2pg:mypassword@localhost:5432/bodadb

Couch2pg will attempt to connect to ‘bodadb’ on the server postgres rather than on your local machine. This is why the error ‘bodadb does not exist’ is thrown. If your local machine has a public IP or domain reachable from the internet, you can substitute it for localhost in the configuration. However, note that couch2pg will not be able to connect when your laptop isn’t online.

I would recommend an alternative setup where couch2pg runs on your laptop with the settings staying as they are now, pulling data from the couchdb instance which already has a publicly accessible subdomain.

17

@elijah
Couch2pg connects to ‘bodadb’ on the local postgres server, once the local postgres server has been started on my local laptop.
Thus the connection configuration variable should be fine:
export POSTGRESQL_URL=postgres://couch2pg:mypassword@localhost:5432/bodadb

We are pulling data from the couchdb instance on the publicly accessible subdomain, already registered and available

18

Hello @elijah @oyierphil

What’s the url of the CouchDB instance running in your medic-os container?
Am attempting to connect to the couch DB url in the medic-os container using couch2pg but i keep on getting No couchdb end point Found

19

Hi @cliff,

Confirm couchdb is running and the connection configuration for couch2pg which should be:

export COUCHDB_URL=https://admin:pass@localhost:5984/medic

where ‘medic’ is the name of the database.

20

Thanks @elijah
i have export COUCHDB_URL=https://admin:pass@localhost:5984/medic in the terminal
but when i curl http://admin:pass@localhost:5984/medic i get {"error":"unauthorized","reason":"Name or password is incorrect."}

i tried curl http://myadminuser:myadminpass@localhost:5984/medic and it gives `

> {"db_name":"medic","purge_seq":"0-g1AAAAEzeJzLYWBg4MhgTmHgzcvPy09JdcjLz8gvLskBCjPlsQBJhgNA6v____ezEhnwqnsAUfefkLoFEHX7catLSgCSSfV4zUpyAKmJx69GAaTGngg3NUDcNB-3ukSGJHmIQVkAHvNi9g","update_seq":"115-g1AAAAEzeJzLYWBg4MhgTmHgzcvPy09JdcjLz8gvLskBCjMlMiTJ____PyuRFYeCJAUgmWQPVoPLkCQHkJp4sBpmXGoSQGrqwWq4cKjJYwGSDA1ACqhsflaiBF51CyDq9mclsuFVdwCi7n5WIjdedQ8g6oDuM8gCAK1YY2k","sizes":{"file":151119442,"external":151217072,"active":150468468},"other":{"data_size":151217072},"doc_del_count":0,"doc_count":39,"disk_size":151119442,"disk_format_version":7,"data_size":150468468,"compact_running":false,"cluster":{"q":8,"n":1,"w":1,"r":1},"instance_start_time":"0"}

Am suspecting http://myadminuser:myadminpass@localhost:5984/medic` to be the end point for the local couchDB and not the one running in medic-os container