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:
- Log in to your account using SSH.
- 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.
- 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:
- Save the changes to the .pgpass file and exit the text editor.
At the command prompt, type the following command:
chmod 600 .pgpass
The pg_dump program only runs if the correct permissions are set for the .pgpass file.
Create a cron job that runs the following command. 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:
pg_dump --no-password DBNAME > /PATH/backup.pgsql
- If your account is on a shared hosting server or reseller hosting server, PATH must begin with /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.