Backing up PostgreSQL databases using cron jobs

Automated backups are essential for protecting your data. On ruachost.com, you can schedule PostgreSQL backups using cron jobs, ensuring your databases are regularly saved without manual intervention.

Why Use Cron Jobs for Backups?

• Automates routine backups.

• Reduces risk of data loss.

• Ensures recovery points are always available.

• Saves time compared to manual exports.

Steps to Set Up Automated Backups

Step 1: Configure .pgpass File

Cron jobs run without user interaction, so you must store database credentials securely in a .pgpass file.

1. Log in via SSH.

2. Create a file named .pgpass in your home directory:

   Bash
   
   nano ~/.pgpass
   

3. Add the following line (replace placeholders with actual values):

   hostname:port:database:username:password
   

   Example:

   localhost:5432:mydb:dbuser:dbpassword
   

4. Set file permissions:

   Bash
   
   chmod 600 ~/.pgpass
   

Step 2: Create Backup Command

Use pg_dump to export the database.

Bash

/usr/bin/pg_dump --no-password -U dbuser dbname > /home/username/backups/mydb_backup.pgsql

• Replace dbuser, dbname, and username with your actual values.

• Ensure the backup path exists (/home/username/backups/).

Step 3: Schedule Cron Job

1. Open the crontab editor:

    

   Bash
   
   crontab -e
   

2. Add a line to schedule the backup. Example: run daily at 2 AM:

   0 2 * * * /usr/bin/pg_dump --no-password -U dbuser dbname > /home/username/backups/mydb_backup.pgsql
   

    

3. Save and exit.

Example Cron Expressions

• Daily at 2 AM:

  0 2 * * * ...
  

• Every Sunday at midnight:

  0 0 * * 0 ...
  

• Every 6 hours:

   

0 */6 * * * ...