How to back up PostgreSQL databases using cron jobs

This article describes how to set up a cron job that automatically backs up a PostgreSQL database to a file at specific intervals.

Automated database backups using cron jobs

A cron job enables you to automatically back up a PostgreSQL database at an interval you define. A cron job runs without any user interaction, though, so you must do some extra configuration steps that enable cron to access a database without being prompted to enter a password.

To do this, follow these steps:

1. Log in to your account using SSH.
2. Using your preferred text editor, create a file named .pgpass in the /home/username directory, where username represents your A2 Hosting account username.
   Make sure you include the leading period (.) in the filename.
3. Copy and paste the following text into the .pgpass file. Replace dbname with the name of the database you want to back up, dbusername with a user who has access to the database, and password with the database user's password:

   #hostname:port:database:username:password
   localhost:5432:dbname:dbusername:password

4. Save the changes to the .pgpass file and exit the text editor.

5. At the command prompt, type the following command:
   

   chmod 600 .pgpass

   For the pg_dump program to work correctly, the correct permissions must be set on the .pgpass file.

6. Create a cron job that runs the following command. Replace dbusername with the user who has access to the database, replace dbname with the name of the database you want to back up, and replace path with the path where you want to store the database backup file. This example uses backup.pgsql for the backup's filename, but you can use any filename you want:
   

   /usr/bin/pg_dump --no-password -U dbusername dbname > /path/backup.pgsql

   • If your account is on a shared hosting server or reseller hosting server, path must begin with ${HOME} or /home/username, where username represents your A2 Hosting account username.
   • For information about how to use cPanel to create a cron job, please see this article.

More Information

• For more information about the pg_dump program, please visit http://www.postgresql.org/docs/9.1/static/app-pgdump.html.
• For more information about the .pgpass file, please visit http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html.