Closure
Closure
Home » Databases » PostgreSQL » How to Backup a PostgreSQL Database Without Using 3rd Party Tools

How to Backup a PostgreSQL Database Without Using 3rd Party Tools

How to Backup a PostgreSQL Database Without Using 3rd Party Tools
, , , Leave a comment
How to Backup a PostgreSQL Database Without Using 3rd Party Tools
Latest posts by Stratos Matzouranis (see all)

In this article we will see step by step how to do a backup without using 3rd party tools full backup and log backup so that we can do PITR (Point in Time Recovery) if needed in the future. Finally, we will see how we can automate the process and their maintenance through cronjobs.

We should know that the corresponding transaction / archive logs in PostgreSQL are called WAL (Write Ahead Logs).

The footsteps

First we will create the folders that will contain the backups and give them the permissions they need:

sudo mkdir -p /var/lib/postgresql/backup/full
sudo mkdir -p /var/lib/postgresql/backup/archived_wal

sudo chown -R postgres:postgres /var/lib/postgresql/backup
sudo chmod -R 700 /var/lib/postgresql/backup

Then if we want to have the Point in Time Recovery feature we will have to do it with the user postgres to enable archiving and declare their location:

su postgres
vi /var/lib/pgsql/11/data/postgresql.conf
# - Archiving -

archive_mode = on               # enables archiving; off, on, or always

archive_command = 'cp %p /var/lib/postgresql/backup/archived_wal/%f'

As well as the wal_level:

wal_level = replica

At this point, you will need to restart the service:

sudo systemctl restart postgresql-11

We create an executable file which will contain the script:

vi /usr/local/bin/pg_full_backup.sh

In the script we declare the folder we created before for the full backups and the command pg_basebackup runs the process by extracting the files into a compressed archive. As a second step, the maintenance process runs which keeps the files for only 30 days:

#!/bin/bash
BACKUP_DIR="/var/lib/postgresql/backup/full"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
DEST="$BACKUP_DIR/full_$TIMESTAMP"

#full backup
pg_basebackup -D "$DEST" -F t -z -P -X fetch

#30 days retention
find "$BACKUP_DIR" -mindepth 1 -maxdepth 1 -type d -mtime +30 -exec rm -rf {} +

After creating it, we give it the correct permissions:

sudo chown postgres:postgres /usr/local/bin/pg_full_backup.sh
sudo chmod +x /usr/local/bin/pg_full_backup.sh

Correspondingly, we can create executables for WAL maintenance:

vi /usr/local/bin/clean_wal.sh
#!/bin/bash

WAL_DIR="/var/lib/postgresql/archived_wal"

#retention 30 days
find "$WAL_DIR" -type f -mtime +30 -exec rm -f {} \;
sudo chown postgres:postgres /usr/local/bin/clean_wal.sh
sudo chmod +x /usr/local/bin/clean_wal.sh

To add them to the cronjob scheduler, run the following:

sudo -u postgres crontab -e

The following lines will essentially be executed every day at 22:00 and 23:00 respectively:

0 23 * * * /usr/local/bin/pg_full_backup.sh >/dev/null 2>&1
0 22 * * * /usr/local/bin/clean_wal.sh >/dev/null 2>&1

Differential / Incremental backup?

Until PostgreSQL 17 there was no way to do block level backup, so the only native solution was full backup and then the use of WAL, in older versions to be able to do this type of backup you had to use 3rd party tools such as pgBackRest and Bartender.

Sources:

Share it

Leave a reply