[SOLVED] Why pg_dump was not getting the password in cron job?

Issue

We are using cron job to backup Postgres database on Ubuntu 12.04. The pg_dump command was added to a bash script which has been running for backing up SQLite. The command is:

pg_dump -U postgres -Fc mydbname > /home/myback_$now   #$now append last 2 digits of date to file name

A .pgpass file was created in the same home dir as it is for the bash script:

localhost:5432:mydbname:postgres:mypassword

Also did chmod 600 .pgpass.

The problem is that there are backup files generated but the size of them are all 0. In backup log, there is an error:

pg_dump: [archiver (db)] connection to database "mydbname" failed: fe_sendauth: no password supplied

It seems that the password in .pgpass was not referred correctly by pg_dump. What could be missing here in the backup config?

Solution

If, as you stated in the comments, you did

sudo crontab -e

then you are editing the crontab of root, not the crontab of your own user.

Therefore the home directory that will be used will be root's home directory (normally /root, but I am not certain for Ubuntu).

Although you did not explicitly say so I am guessing you put the .pgpass file in the home directory of your user.

It is not necessary, or even a good idea, to run pg_backup as root, so I recommend putting your backup job in the crontab of your regular user.

Answered By - harmic

Answer Checked By - Mary Flores (WPSolving Volunteer)