Skip to content
Ruachost Help Center

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?

Section titled “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

Section titled “Steps to Set Up Automated Backups”

Step 1: Configure .pgpass File

Section titled “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:

    Terminal window
    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:

    Terminal window
    chmod 600 ~/.pgpass

Step 2: Create Backup Command

Section titled “Step 2: Create Backup Command”

Use pg_dump to export the database.

Terminal window
/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

Section titled “Step 3: Schedule Cron Job”

  1. Open the crontab editor:

    Terminal window
    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

Section titled “Example Cron Expressions”
0 */6 * * * ...