In this tutorial, we're streamlining PostgreSQL backups with pgBackRest.
Introduction
pgBackRest is a powerful, reliable backup and restore tool for PostgreSQL databases. It offers features like full, differential, and incremental backups, along with support for parallel processing, compression, encryption, and more. This tutorial will guide you through setting up pgBackRest for automating backups of your PostgreSQL databases.
Prerequisites
Before you begin, ensure you have the following:
- A PostgreSQL database server running on a Linux dedicated server or KVM VPS.
- Root or sudo access to the server.
- Basic knowledge of PostgreSQL and Linux command-line operations.
Step 1: Install pgBackRest
First, you need to install pgBackRest on your PostgreSQL server. Use the following commands to add the pgBackRest repository and install the package:
# Import the repository signing key:
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# Update the package lists:
sudo apt update
# Install pgBackRest
sudo apt-get install pgbackrest -y
Step 2: Configure pgBackRest
Next, you'll need to configure pgBackRest. The configuration file for pgBackRest is typically located at /etc/pgbackrest.conf. If the file doesn't exist, create it:
sudo nano /etc/pgbackrest.conf
Add the following configuration:
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
repo1-retention-diff=2
repo1-retention-archive=2
start-fast=y
[global:archive-push]
compress-level=3
[demo]
pg1-path=/var/lib/postgresql/12/main
pg1-port=5432
pg1-socket-path=/var/run/postgresql
[global:archive-get]
process-max=2
Replace [demo] with your PostgreSQL cluster name. Adjust pg1-path, pg1-port, and other parameters as necessary for your environment.
Step 3: Create Backup Directories
Create the directories where backups and archives will be stored. These should match the paths specified in the repo1-path in the configuration file.
sudo mkdir -p /var/lib/pgbackrest
sudo chown postgres:postgres /var/lib/pgbackrest
Step 4: Set Up PostgreSQL for Archiving
pgBackRest relies on PostgreSQL's Write-Ahead Logging (WAL) to perform backups. You'll need to configure PostgreSQL to archive WAL logs by editing the postgresql.conf file:
sudo nano /etc/postgresql/16/main/postgresql.conf
Note: Replace 16 with your PostgreSQL version.
Uncomment and set the following parameters:
archive_mode = on
archive_command = 'pgbackrest --stanza=demo archive-push %p'
Again, replace demo with your actual stanza name that you have mentioned in pgbackrest.conf.
Restart PostgreSQL service:
sudo systemctl restart postgresql
Step 5: Initialize the pgBackRest Stanza
A "stanza" in pgBackRest represents a PostgreSQL cluster configuration. You'll need to initialize it:
sudo -u postgres pgbackrest --stanza=demo --log-level-console=info stanza-create
Again, replace demo with your actual stanza name that you have mentioned in pgbackrest.conf.
If successful, you should see a message confirming the stanza creation.
Step 6: Perform Your First Backup
Now that everything is configured, you can perform your first backup:
sudo -u postgres pgbackrest --stanza=demo --type=full --log-level-console=info backup
Again, replace demo with your actual stanza name that you have mentioned in pgbackrest.conf.
This command performs a full backup of the PostgreSQL database. Subsequent backups can be incremental or differential.
Step 7: Automate Backups with Cron
To automate the backup process, you can set up a cron job:
sudo crontab -e -u postgres
Add the following line to perform a daily full backup at midnight:
0 0 * * * /usr/bin/pgbackrest --stanza=demo --type=full --log-level-console=info backup
Again, replace demo with your actual stanza name that you have mentioned in pgbackrest.conf.
You can adjust the schedule and backup type (full, differential, or incremental) as needed.
Step 8: Restore a Backup (Optional)
In case you need to restore a backup, use the following command:
sudo -u postgres pgbackrest --stanza=demo --delta --log-level-console=info restore
Again, replace demo with your actual stanza name that you have mentioned in pgbackrest.conf.
This command will restore the latest backup. Make sure to stop the PostgreSQL service before performing a restore.
Conclusion
You have successfully configured pgBackRest to automate backups of your PostgreSQL databases. Regular backups are crucial for data protection and recovery. With pgBackRest, you can ensure that your PostgreSQL data is backed up efficiently and securely.
Feel free to adjust the configurations to fit your specific needs and explore more advanced features offered by pgBackRest for enhanced backup and restore capabilities.
