Cron access to PostgreSQL in Docker

Question

I've created a PostgreSQL instance via Docker and I can create tables and insert data etc fine. The issue is when I use crontab to do this. I'm using a Python script that uses a custom module called PostgreSQL which basically uses psycopg2.

Python script:

#!/usr/bin/env python3
import datetime
import csv
import os
from postgresql.postgresql import PostgreSQL

pg = PostgreSQL(**{
    "host": os.environ.get("HOST"),
    "database": os.environ.get("CREATE_DATABASE"),
    "user": os.environ.get("WRITE_USER"),
    "password": os.environ.get("WRITE_PASSWORD"),
})
now = datetime.datetime.now()
utc = datetime.datetime.utcnow()

with open("test.csv", "w") as f:
    writer = csv.writer(f)
    writer.writerow(["now", "utc", "pg"])
    writer.writerow([now, utc, pg])


pg.run_sql("create table if not exists team.test (now text, utc text, pg text")

Note: Everything else worked on cron except when I added the last line. The whole script works as expected locally.

Cron job (ran in host EC2 - not in container):

$ crontab -l
* * * * * /home/centos/venv/bin/python /home/centos/my_s3_dir/test.py

Error Message:

$ tail /var/spool/mail/centos
  File "/home/centos/venv/lib/python3.9/site-packages/postgresql/postgresql.py", line 42, in open_connection
    raise e
  File "/home/centos/venv/lib/python3.9/site-packages/postgresql/postgresql.py", line 38, in open_connection
    self.conn = psycopg2.connect(host=self.host, user=self.user, password=self.password, dbname=self.database)
  File "/home/centos/venv/lib/python3.9/site-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
    Is the server running locally and accepting connections on that socket?

This is how I set up my Docker container:

sudo docker run -d \
--name postgres_container \
-p 5432:5432 \
-e POSTGRES_PASSWORD \
-v /var/lib/postgresql/data:/var/lib/postgresql/data:Z \
-v /tmp:/tmp:Z \
postgres

How do I get my cron job to run without this error?

• Do I need to add an additional volume to allow cron/postgres access?
• Should I be adding a chmod to one /var/lib/postgresql/data? If so, which one?
• Edit: I added the os.environ.get(...) values to the output file and they were empty (Thanks @AdelinoSilva for pointing me to that)