PostgreSQL doesn’t provide a built-in job scheduler like MS SQL, MySQL, and Oracle do. So if you need to run recurring tasks (like database backup), you have to use some other external tools like pgAgent, corn, or pg_cron (on Linux), and Task Scheduler (on Windows). Let’s look at these options closer.

By the way, if you are running Windows and need to run scheduled PostgreSQL backups, then probably the most reasonable solution is to use the SQLBackupAndFTP tool.

How to Schedule PostgreSQL tasks on Linux using Cron

Cron is a Linux scheduler that can run scripts or command on a regular basis. Using it for scheduling PostgreSQL tasks is a simple and straightforward solution, but some people prefer pg_cron instead (see the next section) that integrates directly with the database engine. It supports PostgreSQL 9.5 or higher.

To schedule a task with a Linux cron, you need to edit the cron table containing commands and times when they have to be executed. To edit the cron table type

crontab -e

and in the opened text file, add a line with the following format:

<minute> <hour> <day of month> <month> <day of week> <command to run>

Any of these time fields can be set to an asterisk, which will te the daemon to use any value for this field. For instance, setting the month value to an asterisk will run the job every month.

For example, the following line will execute my.sql script every day at 00:00:

0 0 * * * psql -U username -d database < my.sql

The following line will make PostgreSQL backup each Monday at 01:00:

0 2 * * 1 pg_dump --no-password -U user database > backup.sql

Note that you don’t specify a password here. For security reasons the password should be added to .pgpass file in the following format:

hostname:port:database:username:password

Also, don’t forget to set proper permission to the file:

chmod 600 .pgpass

How to Schedule PostgreSQL tasks using pg_cron (Linux only)

pg_cron is a database extension by citusdata that runs periodic jobs in PostgreSQL. It is cron-based and shares the same syntax with cron, but allows to schedule commands directly in SQL. For example:

-- Delete old log records on Monday at 1:00am (GMT)

SELECT cron.schedule('0 1 * * 1', $$DELETE FROM log WHERE created_time < now() - interval '1 week'$$);

-- Vacuum every day at 10:00am (GMT)

SELECT cron.schedule('0 10 * * *', 'VACUUM');

-- Unschedule all tasks

SELECT cron.unschedule(jobid) FROM cron.job;

If necessary, pg_cron runs several jobs in parallel, but only one instance of a job can be run at a time. If a second run should be started before the first one finishes, then it is queued and will be started as soon as the first run completes.

Note that pg_cron is available on Linux only, Windows users are suggested to use pgAdmin instead.

For PostgreSQL 11 on Red Hat, CentOS, Fedora, Amazon Linux, the installation process will be the following:

# Add Citus Data package repository

curl https://install.citusdata.com/community/rpm.sh | sudo bash

# Install the pg_cron extension

sudo yum install -y pg_cron_11

For PostgreSQL 11 on Debian or Ubuntu use the following command:

# Install the pg_cron extension

sudo apt-get -y install postgresql-11-cron

Then you need to start the background worker by adding pg_con to shared_preload_libraries in postgresql.conf and configure the database name:

# add to postgresql.conf:

shared_preload_libraries = 'pg_cron'

cron.database_name = 'postgres'

After these settings are done, restart the PostgreSQL

sudo /bin/systemctl restart postgresql-11

#or

sudo service postgresql-11 restart

And create the pg_cron functions and metadata tables

-- run as superuser:

CREATE EXTENSION pg_cron;

-- optionally, grant usage to regular users:

GRANT USAGE ON SCHEMA cron TO username;

How to run recurring PosgtgreSQL tasks using pgAgent

pgAgent is a scheduling agent for PostgreSQL databases. It can run shell scripts or SQL commands according to complicated schedules. The agent runs as a daemon on Unix systems, and a service on Windows.

pgAgent is not included in pgAdmin since v1.9 and shipped separately. You need to download and install it on your own. On Windows, you can download and install pgAgent using the “Application Stack Builder” app in Windows Start Menu.

After pgAgent is installed, you will see the “pgAgent Jobs” item in pgAdmin’s tree. Right-click it and then select Create > pgAgent Job. You will see a job configuration pop up where you can set what and when this job has to do. You can specify one or more batch files or SQL scripts to be executed according to your schedule.

How to Schedule PostgreSQL backup with SQLBackupAndFTP

SQLBackupAndFTP created regular backups of PostgreSQL, MySQL, and MS SQL databases on Windows. It uses its own Windows Service to run the backups and notifies a user if anything went wrong.

After you download and install the SQLBackupAndFTP tool you need to connect to your PostgreSQL Server by clicking the “gear” button at the “Connect to Database Server” section. Then choose “PostgreSQL Server” as a server type and specify your credentials. By clicking the “Test Connection” button you can ensure that the program is able to connect your server and if everything is fine click the “Save” & Close” button.

After that select all PostgreSQL databases you are going to backup

In the next step, you need to select a destination for your database backups. Simply click the ”plus” icon at the “Store backups in selected destinations” section and choose a destination from the list. You can send your backups to a local folder as long as to FTP and several cloud storage like Amazon S3, Dropbox, Google Drive, OneDrive, Box, or Azure Storage.

Now it’s time to specify when the backups will take place. Turn a switch next to the “Schedule backups” title on and click the “gear” button to open the advanced backup schedule settings.

That is all. Your SQLBackupAndFTP backup job is ready. Your backups will be run automatically according to your schedule. But if you want to create a PostgreSQL backup immediately – click the “Run Now” button on the top of the window.

Using Windows Scheduler to run recurring PostgreSQL tasks

If you run Postgres on Windows and don’t want to use pgAgent or SQLBackupAndFTP, you can use Windows Task Scheduler, which is an equivalent of cron in Linux.

To configure the Task Scheduler press the Windows key, type “Task Scheduler”, and click it in the list. When a window opens right-click Task Scheduler (Local) node and select “Create Basic task…” or “Create Task…” menu item.

In the opened dialog you will be able to set up the task’s name, when it should trigger and what it should do (Action). In full (not basic)  mode you also will be able to specify under what user the task should be executed.

To execute any PostgreSQL command you can set Action to something like

psql -U username -d databasename -w -c 'SELECT my_procedure()'

Don’t forget to put the password in the .pgpass file, or set a PGPASS environment variable in the task otherwise, the task will fail.