Incremental Backup In PostgreSQL 17

PostgreSQL 17 has brought numerous enhancements to the table, but one feature that stands out for database administrators is incremental backup support. This addition simplifies the backup process, reduces storage requirements, and optimizes resource usage, making it a must-adopt for enterprises managing large and dynamic databases.

What Is Incremental Backup?

An incremental backup captures only the changes made since the last backup, whether it was a full or another incremental backup. This approach significantly reduces backup times and storage consumption compared to full backups, which duplicate all data regardless of changes.

Before PostgreSQL 17, achieving incremental backups required external tools like pgBackRest or Barman, which provided robust solutions but demanded additional configuration and maintenance. Now, with native support, PostgreSQL has simplified this process, integrating incremental backup seamlessly into its ecosystem.

Key Features of Incremental Backup in PostgreSQL 17

1. Native Integration

Incremental backup is built into PostgreSQL’s core functionality, reducing the dependency on third-party tools.

2. Efficiency in Data Storage

Only modified pages are backed up, minimizing storage requirements.

3. Faster Backup and Recovery Times

Incremental backups are quicker to create, and they streamline recovery by applying only the necessary changes on top of the last full backup.

How Incremental Backup Works in PostgreSQL 17

PostgreSQL 17 leverages the pg_basebackup utility, enhanced with incremental capabilities.

Enable the summarize_wal parameter in the postgresql.conf file.

Summarize_wal = on 

Note : Enables the WAL summarizer process. Note that WAL summarization can be enabled either on a primary or on a standby. This parameter can only be set in the postgresql.conf file or on the server command line. The default is off.

First create a full backup using pg_basebackup.

[mafiree@rhel8 ~]$ pg_basebackup -D /var/lib/pgsql/17/full_data_backup/ -c fast -p 5432 -v
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 9/7A000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3857937"
pg_basebackup: write-ahead log end point: 9/7A000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Next insert some data and now take an incremental backup.

mafiree=# INSERT INTO customer (first_name, last_name, email, phone) VALUES
mafiree-# ('Robert', 'Williams', 'robert.williams@example.com', '777-888-9999'),
mafiree-# ('Jessica', 'Davis', 'jessica.davis@example.com', '222-333-4444');
INSERT 0 2

Taking an incremental backup using the backup_manifest from the full backup which we took above.

[mafiree@rhel8 17]$ pg_basebackup --incremental=/var/lib/pgsql/17/full_data_backup/backup_manifest -D /var/lib/pgsql/17/increment_backup1/ -p 5432 -c fast -v
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 9/7E000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3862608"
pg_basebackup: write-ahead log end point: 9/7E000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Again insert some data and now take an incremental backup using the backup_manifest from the first incremental backup

mafiree=# INSERT INTO customer (first_name, last_name, email, phone) VALUES
mafiree-# ('William', 'Clark', 'william.clark@example.com', '999-111-2222'),
mafiree-# ('Olivia', 'Martinez', 'olivia.martinez@example.com', '888-999-0000'),
mafiree-# ('Sophia', 'Garcia', 'sophia.garcia@example.com', '333-444-5555');
INSERT 0 3

Taking an incremental backup,

[mafiree@rhel8 17]$ pg_basebackup --incremental=/var/lib/pgsql/17/increment_backup1/backup_manifest -D /var/lib/pgsql/17/increment_backup2/ -p 5432 -c fast -v
Password: 
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 9/80000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_3864096"
pg_basebackup: write-ahead log end point: 9/80000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Restoring the backups :

Now we can restore the backup by combining all the backup using pg_combinebackup utility

[mafiree@rhel8 17]$ pg_combinebackup -o /var/lib/pgsql/17/combined_backup /var/lib/pgsql/17/full_data_backup /var/lib/pgsql/17/increment_backup1 /var/lib/pgsql/17/increment_backup2

Change the port in the restored directory  /var/lib/pgsql/17/combined_backup/postgresql.conf file and start the database server with that data directory.

[mafiree@rhel8 combined_backup]$ /usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/combined_backup/ start 
waiting for server to start....2024-12-07 11:42:06 IST::@:[3867989]: LOG:  redirecting log output to logging collector process
2024-12-07 11:42:06 IST::@:[3867989]: HINT:  Future log output will appear in directory "log".
done
server started

Login the server and verify the data.

[mafiree@rhel8 combined_backup]$ psql -p 1717 -d mafiree
Password for user postgres: 
psql (17.0)
Type "help" for help.

mafiree=# select * from customer;
customer_id | first_name | last_name |            email            |    phone     |         created_at         
-------------+------------+-----------+-----------------------------+--------------+----------------------------
          1 | John       | Doe       | john.doe@example.com        | 123-456-7890 | 2024-12-07 11:11:16.3873
          2 | Jane       | Smith     | jane.smith@example.com      | 987-654-3210 | 2024-12-07 11:11:16.3873
          3 | Emily      | Jones     | emily.jones@example.com     | 555-666-7777 | 2024-12-07 11:11:16.3873
          4 | Michael    | Brown     | michael.brown@example.com   | 111-222-3333 | 2024-12-07 11:11:16.3873
          5 | Sarah      | Taylor    | sarah.taylor@example.com    | 444-555-6666 | 2024-12-07 11:11:16.3873
          6 | Robert     | Williams  | robert.williams@example.com | 777-888-9999 | 2024-12-07 11:27:20.2501
          7 | Jessica    | Davis     | jessica.davis@example.com   | 222-333-4444 | 2024-12-07 11:27:20.2501
          8 | William    | Clark     | william.clark@example.com   | 999-111-2222 | 2024-12-07 11:31:18.837552
          9 | Olivia     | Martinez  | olivia.martinez@example.com | 888-999-0000 | 2024-12-07 11:31:18.837552
          10 | Sophia     | Garcia    | sophia.garcia@example.com   | 333-444-5555 | 2024-12-07 11:31:18.837552
(10 rows)

What is pg_combinebackup?

The pg_combinebackup utility, introduced in PostgreSQL 17, is used to combine all incremental backups into a single full backup. This tool helps reconstruct a complete backup by merging incremental backups efficiently.

Advantages of Incremental Backup

Cost Savings: Reducing storage usage directly translates to lower costs for cloud or on-premises storage.

Improved Performance: Less data transfer reduces the load on the system, especially beneficial during peak hours.

Scalability: Ideal for environments with large databases or high-frequency changes.

Conclusion

The introduction of incremental backups in PostgreSQL 17 is a transformative enhancement for database management. This feature not only addresses the long-standing challenge of storage inefficiency and time-intensive backup processes but also paves the way for robust disaster recovery strategies.

Our team specializes in guiding organizations to fully leverage PostgreSQL 17’s capabilities for scalable and efficient data management. Whether it’s implementing incremental backups, designing robust backup policies, or ensuring high availability, Mafiree provides end-to-end solutions tailored to your needs.

For PostgreSQL support and advanced backup consulting, contact Mafiree at sales@mafiree.com . Let us help you redefine your database backup strategy with confidence.