Setting Up Automated Backups with pg_cron and cron on Linux

Automating backups is like having an electric toothbrush for your database. Regular backup routines save the world (well, your data) from disaster. Nobody wants to get a 3AM message that the server crashed, only to realize the last backup was two weeks ago. Automation fixes this: no more risking your data because of human error.

There are tons of tools for automation, but today we’ll talk about the two most popular ones:

1. cron — the classic, universal task scheduler in Linux.
2. pg_cron — a PostgreSQL extension that lets you run jobs right from the database server itself.

The pg_cron extension actually calls the cron service under the hood. This service is only available on Linux!

Let’s get started!

Installing pg_cron

pg_cron is a PostgreSQL extension that lets you schedule automated jobs right inside your database. It’s slick, convenient, and turns your PostgreSQL into a mini operations center.

Installation steps:

1. Make sure you’re using PostgreSQL 10+, because pg_cron only works on modern versions.

2. Install the extension using your system’s package manager. For Ubuntu, for example:

   sudo apt install postgresql-<version>-cron
   

   Replace <version> with the version of PostgreSQL you have installed.

3. Enable the extension in your PostgreSQL config. Open postgresql.conf and add:

   shared_preload_libraries = 'pg_cron'
   

   Why do you need this? pg_cron needs the library loaded when PostgreSQL starts — it’s a must.

4. Restart your PostgreSQL server:

   sudo systemctl restart postgresql
   

5. Activate the extension in your database:

   CREATE EXTENSION pg_cron;
   

At this point, pg_cron is ready to roll!

Creating a Job for Automatic Backups

The main trick with pg_cron is that you can run commands on a schedule. Let’s create a job to automatically run pg_dump.

Example:

SELECT cron.schedule(
    'nightly_backup', -- job name
    '0 2 * * *', -- schedule (every night at 2am)
    $$pg_dump -U username -F c -f /backups/university_backup.dump university$$ -- command
);

Let’s break down the magic:

• 'nightly_backup' — this is your custom job name so you can easily spot it.
• '0 2 * * *' — cron format: every day at 2:00 AM.
• The command inside $$ $$: we’re running pg_dump to back up the university database.

Once you create the job, it’ll start running on schedule!

Viewing and Removing pg_cron Jobs

To see all existing jobs:

SELECT * FROM cron.job;

If a job is no longer needed, you can remove it like this:

SELECT cron.unschedule(job_id);

Replace job_id with the job’s ID from the query above.

Using cron for Automation

If you prefer the classic Linux way of automating things, or if pg_cron isn’t available for some reason, the almighty cron has your back.

Setting Up a Job with cron

First, make sure cron is installed and running:

sudo systemctl enable cron
sudo systemctl start cron

Now let’s add a job to create a backup. Open the editor to set up cron jobs:

crontab -e

Add this line:

0 2 * * * pg_dump -U username -F c -f /backups/university_backup.dump university

This magic line will do the following:

• At 2AM every day (0 2 * * *), it’ll create a backup of the university database.
• All backups will be saved to /backups/university_backup.dump.

Once you save the crontab file, the scheduled job will start running.

cron Execution Logs

Sometimes it’s handy to know what went wrong. Make sure your command output is logged for troubleshooting. To do this, add output redirection to your command:

0 2 * * * pg_dump -U username -F c -f /backups/university_backup.dump university >> /var/log/backup.log 2>&1

Now, everything that happens during the job will be saved to /var/log/backup.log.

Comparing pg_cron and cron

So, we’ve got two powerful tools. How do you pick the right one?

• pg_cron is a great choice if you want to schedule and manage jobs right from PostgreSQL. It’s convenient, you don’t have to leave the database, and it lets you scale your jobs easily.

• cron is more universal. You can use it not just for PostgreSQL, but for automating anything else too.

Checking Job Execution

To check that your backup is working, you can run the cron command manually:

pg_dump -U username -F c -f /backups/university_backup.dump university

It’s also a good idea to check for backup files in the target directory, their size, and the last modified time. For example:

ls -lh /backups/

Check your logs regularly and make sure your jobs are running as expected.

Simple Protection from Common Mistakes

Mistake #1: "Forgot to set permissions!"

If the user running cron doesn’t have rights to run pg_dump, your jobs won’t work. Make sure the user has access to the database.

Mistake #2: "File went to the wrong place!"

Always use full paths for files and commands. cron doesn’t know about your environment — full paths are a must: pg_dump -> /usr/bin/pg_dump.

Mistake #3: "Where are my logs?"

Don’t forget to redirect command output to a log file. Without this, you won’t get any info about problems.

At this point, you’re ready to automate your database backups. Now, even if your server suddenly goes on vacation, your data will be safe!