fix: db container healthchecks that was earlier broken

mahesh-naxa commented 2 months ago

What type of PR is this? (check all applicable)

[x] 🐛 Bug Fix
[ ] 🍕 Feature
[ ] 📝 Documentation
[ ] 🧑‍💻 Refactor
[ ] ✅ Test
[x] 🤖 Build or CI
[ ] ❓ Other (please specify)

Issue

Many users were experiencing failed tm-migration container that resulted in tm-backend service left in Created state and never to Started.

This is mainly caused due to healthcheck in tm-db being too light, and only checks if the postgresql server is started or not.

This was one of the reason deployment attempts by Bash from Technology without borders was not successful. Errors he received:

tasking-manager-main-tm-migration-1  |     return self.loaded_dbapi.connect(*cargs, **cparams)
tasking-manager-main-tm-migration-1  |   File "/home/appuser/.local/lib/python3.10/site-packages/psycopg2/__init__.py", line 122, in connect
tasking-manager-main-tm-migration-1  |     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
tasking-manager-main-tm-migration-1  | sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) connection to server at "tm-db" (172.31.0.5), port 5432 failed: Connection refused
tasking-manager-main-tm-migration-1  |  Is the server running on that host and accepting TCP/IP connections?

This second error also occurs after running the 'docker-compose up --detach' command:

    service "tm-migration" didn't complete successfully: exit 1

Describe this PR

I have updated healthcheck defination from

pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}

to

psql -h 0.0.0.0 -U ${POSTGRES_USER:-tm} -d ${POSTGRES_DB:-tasking-manager} -c 'SELECT 1;'

This verifies that the defined database & database role is created, and also performs a SELECT 1 that validates the initialization of the tm-db service.

Also to mention pg_isready doesn't do that, you could have pg_isready -U this_user_doesnt_exist -d this_db_doesnt_exist and it would still exit 0, which is unwanted.

Also, -h 0.0.0.0 verifies reachibility from other services, Ensuring the db now can be finally consumed.

Review Guide

Notes for the reviewer. How to test this change?

Checklist before requesting a review

📖 Read the Tasking Manager Contributing Guide: https://github.com/hotosm/tasking-manager/blob/develop/docs/developers/contributing.md
📖 Read the HOT Code of Conduct: https://docs.hotosm.org/code-of-conduct
👷‍♀️ Create small PRs. In most cases, this will be possible.
✅ Provide tests for your changes.
📝 Use descriptive commit messages.
📗 Update any related documentation and include any relevant screenshots.
🔠 Does this PR introduce or change any environment variables? If so, make sure to specify this change in the description.