PostgreSQL is a powerful and widely used open-source relational database management system (RDBMS). As data grows exponentially, it's important for database administrators and developers to implement efficient and reliable backup strategies. While full backups are essential for data recovery, they can be time-consuming and resource-intensive, especially with large datasets. This is where incremental backups come into play.
Incremental backups capture only the changes made to the database since the last backup. By focusing on modifications—such as new records, updates, or deletions—incremental backups minimize the time, storage space, and resources required for regular backups. They provide an efficient way to ensure that your database can be quickly restored without needing a full backup each time.
In this blog post, we'll walk through the process of setting up incremental backups in PostgreSQL. We'll explain the steps required to create full and incremental backups, combine them, and restore your database from the backups.
Prerequisites
Before starting, make sure you have the following in place:
* PostgreSQL installed (version 17 used in this guide)
* Sufficient disk space for storing backups
* Basic understanding of PostgreSQL administration
Step 1: Setting Up the Test Environment
To begin, we need to set up a test environment. We’ll create a new database and populate it with sample data. Use the following commands:
CREATE DATABASE mytestdb;
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
salary NUMERIC
);
-- Populate with initial 1000 records
DO $$
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO employees (first_name, last_name, email, salary)
VALUES (
'FirstName' || i,
'LastName' || i,
'email' || i || '@example.com',
ROUND((RANDOM() * 10000 + 30000)::numeric, 2)
);
END LOOP;
END $$;
This creates a database named mytestdb and a table of employees containing 1000 records.
Step 2: Enable WAL Summarization
PostgreSQL’s Write-Ahead Logging (WAL) is used for data durability. To enable incremental backups, we need to configure PostgreSQL to summarize WAL information. This can be done by enabling the summarise_wal configuration parameter.
In your postgresql.conf, add the following line:
summarise_wal = on
After making this change, restart the PostgreSQL server to apply it:
sudo systemctl restart postgresql
Step 3: Setting Up Backup Directories
Next, we’ll create a directory structure to store our backups. Inside the PostgreSQL directory, create a folder named backup, and inside that, create three subdirectories:
1. fullbackup — For storing the full backup data.
2. incr_backup — For storing the incremental backup data.
3. combined_data — For storing the combined data from both full and incremental backups.
mkdir -p /home/postgres_code/postgresql/backup/fullbackup
mkdir -p /home/postgres_code/postgresql/backup/incr_backup
mkdir -p /home/postgres_code/postgresql/backup/combined_data
Step 4: Creating a Full Backup
A full backup is the foundation of any incremental backup strategy. To create a full backup, we use PostgreSQL's pg_basebackup tool. Run the following command:
bin/pg_basebackup -D /home/postgres_code/postgresql/backup/fullbackup
This will create a complete backup of your database and store it in the fullbackup directory.
Step 5: Simulating Database Changes
Now that we have a full backup, let's simulate some database changes to create data modifications. We’ll add more records to the employees table:
\c mytestdb
DO $$
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO employees (first_name, last_name, email, salary)
VALUES (
'FirstName' || i,
'LastName' || i || '@example.com',
ROUND(RANDOM() * 10000 + 30000, 2)
);
END LOOP;
END $$;
This will add another 1000 rows to the employees table, bringing the total number of records to 2000.
Step 6: Creating an Incremental Backup
After making changes to the database, we’ll create an incremental backup. To do this, we use pg_basebackup with the --incremental option. The incremental backup will capture only the changes since the last backup.
bin/pg_basebackup --incremental='/home/postgres_code/postgresql/backup/fullbackup/backup_manifest' -D /home/postgres_code/postgresql/backup/incr_backup
This command references the backup_manifest file from the full backup and stores the incremental changes in the incr_backup directory.
Next, rename the backup_label.old file in the fullbackup directory to backup_label:
mv /home/postgres_code/postgresql/backup/fullbackup/backup_label.old /home/postgres_code/postgresql/backup/fullbackup/backup_label
Step 7: Combining Full and Incremental Backups
Once you have both the full and incremental backups, the next step is to combine them. To merge the backups, use the pg_combinebackup tool:
bin/pg_combinebackup /home/postgres_code/postgresql/backup/fullbackup /home/postgres_code/postgresql/backup/incr_backup -o /home/postgres_code/postgresql/backup/combined_backup
This will merge the data from the full and incremental backups and store the combined data in the combined_backup directory.
Step 8: Restoring the Combined Backup
After combining the backups, you can restore the database using the combined backup directory. To do so, start the PostgreSQL server with the pg_ctl command:
bin/pg_ctl -D /home/postgres_code/postgresql/backup/combined_backup/ -l logfile start
The server will start successfully, and you’ll see a message indicating that the server is up and running:
waiting for server to start.... done
server started
Conclusion
In this guide, we've walked through the process of setting up PostgreSQL incremental backups. By following these steps, you can reduce backup time and resource usage by only capturing changes made since the last backup. Combining full and incremental backups allows for fast and efficient restores, ensuring your data is secure and easily recoverable.
PostgreSQL's incremental backup functionality is a powerful tool for database administrators, providing a reliable backup strategy for large and growing datasets. Let us know in the comments if you have any questions or if there’s anything else you'd like to learn about PostgreSQL backups!