How to Use Postgresql pg_dump in Crontab Without Password

Postgresql pg_dump is a usefull tool to backup Postgresql database. By default, pg_dump will ask for a password of the user that runs the command from command line. It is pretty easy if we manually type it from command line. But if we want to use it in automatic regular backup using crontab, it can be a problem because we can not input the password.

For example, the pg_dump command in following cron job will not work:

5 0 * * * pg_dump -U username -Fc dbname > ~/backup/database/mydb_backup.gz

Fortunately, Postgresql has a Password File.pgpass‘ feature that we can use to overcome this problem. Create a ~/.pgpass file in user’s home directory with the following format:

Ex: localhost:5432:mydatabase:lorenz:lorensxyz

Then don’t forget to change its’ permission to 0600 (chmod 0600 ~/.pgpass). pg_dump will use the password from .pgpass file and the cronjob will work successfully.

facebooktwittergoogle_plusredditpinterestlinkedinmailby feather


Leave a Reply

Your email address will not be published. Required fields are marked *