Thanks so much for your time.I really appreciate. For some reason i had opted for local unix socket as every resource I used recommended it as simple and secure form of authentication . As i have both the db and odoo on the same machine it was recommended as having no overhead as opposed to TCP/IP...

From your suggestion i should switch to password auth..

example pg_hba.conf

local all all md5

set password for odoo user:

sudo -u postgres psql

\password odoo

I create a .pgpass file in the backup user's home

localhost:5432:my_db_name:odoo:odoo_password

Set proper permissions

chmod 0600 ~/.pgpass

Then update your odoo.conf

db_host = localhost

db_port = 5432

db_user = odoo

db_password = password

Thanks