Local Couch2pg Setup

Setting up a Couch2pg service to download data from CouchDB to Postgres Database

This tutorial will take you through setting up a Couch2pg service.

By the end of the tutorial you should be able to:

  • Set up a Couch2pg service
  • Run the Couch2pg service

CHT Couch2pg is a background process that moves data from Couchdb to Postgres through one way replication. It therefore, needs to have full read and write access to both the Postgres Database and Couchdb upstream. It is built in nodejs and can be set up as a background process using systemd. Review this architecture diagram to get a conceptual understanding of how couch2pg works.

Brief Overview of key environmental variables

COUCHDB_URL is the CouchDB instance URL with no trailing slash after /medic, format: https://[user]:[password]@localhost:[port]/medic

POSTGRESQL_URL is the PostgresSQL instance URL, format: postgres://[user]:[password]@localhost:[port]/[database name]

COUCH2PG_SLEEP_MINS is the interval size in minutes Couch2pg will use to poll Couchdb.

COUCH2PG_DOC_LIMIT is the number of documents Couch2pg will fetch in each query.

COUCH2PG_RETRY_COUNT is the amount of times couch2pg should retry a failed connection before it fails.

COUCH2PG_CHANGES_LIMIT is the number of changes to query since the last sync operation.

To read more about environmental variables, see the CHT Couch2pg readme.

Required Resources

Before you begin, you need to have some useful software and tools that are required for things to work:

Prerequisites

You should have a functioning CHT instance installed locally

You should have a working database with a user that has full creation rights on the database. A database POSTGRES_DB_NAME and couch2pg user can be created and access granted using the following query:

CREATE DATABASE POSTGRES_DB_NAME;
CREATE USER couch2pg WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE POSTGRES_DB_NAME TO couch2pg;

All steps below require you to have a local clone of the repo.

git clone https://github.com/medic/cht-couch2pg.git`

Setting up with environment variables

  1. Change directory into the repo’s directory where you cloned it: cd /path/to/cht-couch2pg

  2. Install dependencies: npm ci

  3. Export the four variables with the correct values:

export POSTGRESQL_URL=postgres://[user]:[password]@localhost:[port]/[database name]
export COUCHDB_URL=https://[user]:[password]@localhost:[port]/medic
export COUCH2PG_DOC_LIMIT=1000
export COUCH2PG_RETRY_COUNT=5
export COUCH2PG_SLEEP_MINS=120
export COUCH2PG_CHANGES_LIMIT=1000
  1. Run: node .

If you want to set and save all possible variables:

  1. Copy sample.env to couch2pg.env: cp sample.env couch2pg.env

  2. Edit couch2pg.env to have all the variables you need.

  1. Run: . ./couch2pg.env && node .


Using docker-compose

The simplest way to run couch2pg is with docker-compose which only needs configuration of the CouchDB instance URL. The compose file will then create a PostgresSQL container, connect to the CouchDB server and proceed to download couchDB documents to the PostgreSQL container:

  1. Change directory into the repo’s directory where you cloned it: cd /path/to/cht-couch2pg

  2. Set the URL for CouchDB in the COUCHDB_URL env variable. e.g.

export COUCHDB_URL=https://medic:password@192-168-68-26.my.local-ip.co:8442/medic
  1. Run: docker-compose up

  2. Connect to the PostgreSQL instance with login cht_couch2pg, password cht_couch2pg_password and database cht.



Known issues

  1. Node version compatibility
  • Version 14 and 16 have been known to fail silently, and you can conveniently switch between node versions using nvm.
  1. Postgres authentication
  • If the error Error: Unknown authenticationOk message typeMessage { name: 'authenticationOk', length: 23 } is observed, it is because 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 in the notes.

  • 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, restart the Postgres service.

  • To confirm that the role used with couch2pg has an md5 encrypted password use the query SELECT rolname, rolpassword FROM pg_authid. The role password should start with md5.




CHT Applications > Tutorials > Local Setup

Setting up a local environment to build and test CHT applications


Last modified 04.07.2022: Adds couch2pg tutorial (66676e7)