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?
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.