Use systemd timers to make PostgreSQL backups#
Systemd timers are a great way to replace cron jobs. They are more flexible and can have dependencies that can defined in easy statements instead of creating a script to check for the dependency. One of the use-cases is to use systemd timers to create PostgreSQL backups and remove old backups. In this example, we will create a backup of two databases, db1 and db2, and remove backups older than 10 days.
Install PostgreSQL#
First step is to install PostgreSQL on the system. This example is for Debian or Ubuntu and uses the root user to connect to the database. It is recommended to create a user with limited privileges to connect to the database.
$ sudo apt-get install postgresql
The second step is to create the databases and users. This example creates two databases, db1 and db2, and two users, db1 and db2. The passwords are set to cangetin. It is recommended to use a more secure password.
$ sudo -u postgres psql -c "CREATE DATABASE db1"
$ sudo -u postgres psql -c "CREATE USER db1 WITH PASSWORD 'cangetin'"
$ sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE db1 TO db1"
$ sudo -u postgres psql -c "CREATE DATABASE db2"
$ sudo -u postgres psql -c "CREATE USER db2 WITH PASSWORD 'cangetin'"
$ sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE db2 TO db2"
Create the systemd unit files for PostgreSQL backups#
The next step is to create the systemd unit files. The first file is the target file. The target file is used to group the services together. The second file is the service file. The service file is used to create the mysqldump command. The third file is the timer file. The timer file is used to define when the service should run.
/etc/systemd/system/postgresql-dump.target#[Unit]
Description=PostgreSQL dump target
After the target unit file is created the service file can be created. The service file is used to check if the backup directory exists and then create the backup with the pgsql_dump command. The pgsql)dump command is run as the postgres user. The command to dump the database may require additional parameters and those are described in PostgreSQL documentation This service file is similar to the unit files in post Manage multiple systemd service instances where the %i is used to define the instance name. In this example, the instance name is the database name.
/etc/systemd/system/postgresql-dump@.service#[Unit]
Description=PostgreSQL dump for %i
Requires=postgresql.service
[Service]
Type=simple
User=postgres
Group=postgres
ExecStartPre=/usr/bin/test -d /srv/backups/postgresql/daily
ExecStart=/bin/sh -c "/usr/bin/pgsql_dump -f /srv/backups/postgresql/daily/%i.sql %i"
[Install]
WantedBy=postgresql-dump.target
Warning
The service run as the postgres user and group as under Debian and Ubuntu the postgres user and group are used to run the database and is allowed to dump the database. If you are using another distribution or another user and group you need to change the user and group in the service file.
The last file is the timer file. The timer file is used to define when the service should run. In this example, the service is run daily, but it can also run on other intervals.
/etc/systemd/system/postgresql-dump.timer#[Unit]
Description=Execute PostgreSQL dumps
[Timer]
OnCalendar=daily
Unit=postgresql-dump.target
[Install]
WantedBy=multi-user.target
The final step is to reload the systemd daemon to load the new unit files.
$ sudo systemctl daemon-reload
Now that the systemd unit files are created the timer can be enabled. The timer is enabled to run daily.
$ sudo systemctl enable postgresql-dump.timer
Enabling the systemd units for PostgreSQL backups#
Now that everything is in place the instances can be enabled. In this example, the instances are db1 and db2.
$ sudo systemctl enable postgresql-dump@db1
$ sudo systemctl enable postgresql-dump@db2
Note
Disabling a systemd unit can be done with the disable command.
$ sudo systemctl disable postgresql-dump@db1
The new systemd units will be started by the timer, but the timer can also be started manually.
$ sudo systemctl start postgresql-dump.timer
Verify the systemd timers for PostgreSQL backups#
By using systemd you can easily list the timers and see when they will run next or when they last ran.
$ sudo systemctl list-timers
Removing old PostgreSQL backups#
Removing old backups can be done with the tmpfiles.d configuration. The tmpfiles.d configuration is used to remove old files and directories. In this example, the tmpfiles.d configuration is used to remove backups older than 10 days.
/etc/tmpfiles.d/postgresql-dump.conf for tmpfiles#d /srv/backups/postgresql/daily 0750 postgres postgres 10d -
The clean-up can be done manually or by using the systemd-tmpfiles-clean command, but is also done automatically by systemd on an interval.