Perl: Create schema backups in PostgreSQL

At my recent job, I was asked to create a backup procedure, which would dump a PostgreSQL schema to a compressed file and which was able to create weekly and daily backups.
The backups had to be full backups each time a backup was made and the amount of daily and weekly backups should be defined through thresholds.

The PostgreSQL tool used for those backups is ‘pg_dump’ and I have used Perl to script all the interesting stuff together.

The script will basically go through the following steps:

– Check the backup path for the required directories (and if not, create them)
– Rotate old backups based on thresholds
– Create a new backup

The script shown below is just an example and probably needs to be adopted for your own needs. The script works for me and the environment it was created in.

First things first.
The script uses the following Perl modules:

A YAML configuration file is used to provide the script with essential information. An example configuration file looks like the following:

Remember: YAML is sensitive about tabs!

Command line arguments are set up in the script by using Getopt::Long.

The script needs to run as the ‘postgres’ user. Should it be executed by another user (for instance root), then script will try to switch to the ‘postgres’ user.

Next we will load the configuration file and check if a schema name was supplied on the command line. If one was defined, then we will override the schema names which were set in the configuration, and only create a backup of that one schema name.

And now we are ready for the mainloop of the script:

For each schema, we will first check if the required directories are in place and otherwise create them. Afterwards we will check those directories for older backups.

The rotation of the backups works like follows:
– If the day threshold has been reached (for instance 7 daily backups), then those files will be nominated for rotation or deletion

The rotation itself is custom designed for my current job. Each backup filename is appended the day name (Monday, Tuesday, …). Backup files matching a certain pattern (in my situation ‘sunday’) will be moved into the ‘weekly’ backup path, other old files will be deleted.

Since rotation is done before a backup is created, we will delete one more as required file (since a new backup file is going to be created in a few lines further).

At this point now, the required directory structure has been checked and is present and older backup files have been rotated or deleted.
Finally we can create the actual backup:

The backup is created by issuing pg_dump for that schema and it will produce a normal text SQL file. This file will be compressed with gzip and afterwards the file permissions will be changed to 0660. This means that, since the backup file is created by the postgres user, only the postgres user will have access to this file.

The full script and configuration file can be found at

Leave a Reply

Your email address will not be published. Required fields are marked *