Replicating Data Using couch2pg to Postgres Local Db

Technical Support
, ,
21

@cliff, did the replication procedure using cht-couch2pg work for you? Did you manage to pull the data from CouchDb, either remote or local to the local Postgres, please advise

We managed to replicate CouchDb from the server to a local instance, but moving the data to Postgres has been a challenge, still working on it

22

we did the replication but from a remote instnace, havent yet been successful with a local instance from the medic-os container to a postgres container

which instance , i though we are pulling from Couch DB instance for the local CHT to postgres, correct me if am wrong

23

Hi @oyierphil & @cliff,

Efforts are underway to streamline the docker compose file used for cht-couch2pg in this issue and it should be ready for use in the near future.

1 Like
24

@cliff, we have two scenarios, either from the local CouchDb to local Postgres, or remote CouchDb to local Postgres, which one worked for you

25

@oyierphil we have worked from remote couch DB to a local postgres container

26

@cliff, thank you, please share the experience, especially the configuration

27

thanks @oyierphil
most of the work is done with configuring the environment variables in the couch2pg docker-compose file and all in the same network environment

28

@Cliff, this is noted, we are following the same guide with the challenges highlighted earlier, thank you

1 Like
29

Today we (@oyierphil @kitsao @mrjones & Elias) spent some time debugging directly running couch2pg from a repository clone and these are our findings:

1. NodeJS version

Couch2pg has an implicit dependency on the NodeJS version in use. We discovered that versions 10 and 12 work well, while 14 and 16 fail silently. To conveniently switch between node versions, nvm is highly recommended. Installation instructions here.

2. Postgres authentication

Running couch2pg with the right node version may also lead to the error below being thrown:

[2022-02-28T08:11:31.910Z DEBUG]:  { debug: true,
  v4Mode: false,
  couchdbUrl: '<cht-instance>/medic',
  postgresqlUrl: '<pg-instance>/monday',
  docLimit: 1000,
  changesLimit: undefined,
  retryCount: 5,
  sleepMins: 120,
  couchdbUsersMetaDocLimit: 50 }
<couch2pg path>/node_modules/couch2pg/node_modules/pg/lib/connection.js:426
  throw new Error("Unknown authenticationOk message type" + util.inspect(msg));
  ^

Error: Unknown authenticationOk message typeMessage { name: 'authenticationOk', length: 23 }
    at Connection.parseR (<couch2pg path>/node_modules/couch2pg/node_modules/pg/lib/connection.js:426:9)
    at Connection.parseMessage (<couch2pg path>/node_modules/couch2pg/node_modules/pg/lib/connection.js:345:17)
    at Socket.<anonymous> (<couch2pg path>/node_modules/couch2pg/node_modules/pg/lib/connection.js:105:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)

Observe the message:

Unknown authenticationOk message

This means that postgres is setup to use a different password encryption algorithm compared to what couch2pg uses. Couch2pg was made to work with md5 which is the default method in postgres v10-13. However, on postgres v14 the default method is scram-sha-256 detailed here.

The setting can be updated in the postgres configuration file which is in /etc/postgresql/14/main/postgres.conf in Ubuntu 20.04. The key password_encryption should be set to md5. After updating the setting, the postgres service needs to be restarted for it to take effect using:

systemctl restart postgresql

To confirm that the role used with couch2pg has an md5 encrypted password use this query:

SELECT rolname, rolpassword FROM pg_authid

The role password should start with md5.

A successful run will produce logs like this:

[11:13:17 AM] table couch2pg_migrations does not exist - creating it.
[11:13:17 AM] version of database is: 0
[11:13:17 AM] migrating up to 201803202020
[11:13:17 AM] running 201606091247.do.2318-standardise-schema.sql
[11:13:17 AM] running 201606091248.do.2124-support-edit-and-delete-of-documents.sql
[11:13:17 AM] running 201607132040.do.2506-unique-id-constraint.sql
[11:13:17 AM] running 201607210832.do.2523-remove-unused-index.sql
[11:13:17 AM] running 201611271809.do.2923-couch2-support.sql
[11:13:17 AM] running 201803202020.do.3423-multiple-db-support.sql

and will continue to pull documents from CouchDB.

Following this up in #35.

Let us know if this works. Thank you for your patience.

2 Likes
30

Such great debugging work here @kitsao and @elijah! Great to see this working.

31

@mrjones, @elijah, @kitsao, I did manage to pull the data after downgrading Node to version 10 and not 12, it doesnā€™t work for Node version 12. Working from my laptop, Win 11 and WSL, I was forced to install Postgres in Ubuntu since I couldnā€™t see the Postgres Db in Windows.

I now the couchDb replicated to my local instance, my laptop, accessed the data and converted it to CSV from Postgres. Still figuring about the format, it is not well organized as the report downloaded from the medic portal (With case investigation data), thus not suitable for analysis. I see mostly columns, mixed up and cant tell which is which, cant see any data values, will think about it tomorrow morning, we have a meeting tomorrow at 12 noon

32

Thanks for the feedback @oyierphil! If thereā€™s specific questions about how to use the data in postgres that is populated from couch2pg, please open a new thread in the forum - weā€™d love to help on your reporting!

1 Like
33

hello @elijah @mrjones
I am populating the populating the postgres container with remote CHT data using cht-couch2pg when in docker-compose up i get

  Connection matched pg_hba.conf line 99: "host all all all md5"
cht-couch2pg_1  | [2022-03-15T07:23:53.547Z ERROR]:  An unrecoverable error occurred
cht-couch2pg_1  | [2022-03-15T07:23:53.548Z ERROR]:  { error: password authentication failed for user "cht_couch2pg"
cht-couch2pg_1  |     at Connection.parseE (/app/node_modules/couch2pg/node_modules/postgrator/node_modules/pg/lib/connection.js:539:11)
cht-couch2pg_1  |     at Connection.parseMessage (/app/node_modules/couch2pg/node_modules/postgrator/node_modules/pg/lib/connection.js:366:17)
cht-couch2pg_1  |     at Socket.<anonymous> (/app/node_modules/couch2pg/node_modules/postgrator/node_modules/pg/lib/connection.js:105:22)
cht-couch2pg_1  |     at Socket.emit (events.js:198:13)
cht-couch2pg_1  |     at addChunk (_stream_readable.js:288:12)
cht-couch2pg_1  |     at readableAddChunk (_stream_readable.js:269:11)
cht-couch2pg_1  |     at Socket.Readable.push (_stream_readable.js:224:10)
cht-couch2pg_1  |     at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
cht-couch2pg_1  |   name: 'error',
cht-couch2pg_1  |   length: 108,
cht-couch2pg_1  |   severity: 'FATAL',

Not sure why the password is not being authenticated for cht_couch2pg user

34

Hello @cliff, you probably have the port 5432 already in use. If so, stop all processes using it and try again.

35

Hello @kitsao
yeah i did fix the issue and i had stop all the processes and tried it again

Thanks

1 Like