Incremental Backups in PostgreSQL 17

Table of Contents

• 1. WAL Summarization (Similar to Oracle Block Change Tracking)
• 2. Incremental File System Backups with pg_basebackup
• 3. New Functions and Utilities for WAL Summarization
• 4. How to Enable and Use summarize_wal
  • 4.1. Check Current Status of summarize_wal
  • 4.2. Enable WAL Summarization System-wide
  • 4.3. Reload the Configuration to Apply Changes
  • 4.4. Verify summarize_wal is Enabled
  • 4.5. List Available WAL Summaries by Start LSN
• 5. Weekly Backup Strategy (PostgreSQL 17 / Oracle Style)
• 6. Restore Scenarios

1. WAL Summarization (Similar to Oracle Block Change Tracking)

PostgreSQL 17 introduces a background process called the walsummarizer, which tracks which blocks in data files have changed over ranges of WAL (Write Ahead Log) records.
This feature enables efficient incremental backups by identifying only the blocks changed since the previous backup.
WAL summaries are stored in: PGDATA/pg_wal/summaries/

New Configuration Settings:
1. summarize_wal (boolean) – Enables or disables the summarizer. Default: off.
2. wal_summary_keep_time – Duration to retain summary files. Default: 10 days.

2. Incremental File System Backups with pg_basebackup

-- PostgreSQL 17 adds an --incremental option to pg_basebackup.
-- When WAL summarization is enabled, you can create backups that include only the changed blocks since the previous full or incremental backup.
-- A new utility (e.g., pg_combinebackup) is used to merge full and incremental backups during restoration.

3. New Functions and Utilities for WAL Summarization

 	pg_available_wal_summaries()
 	pg_wal_summary_contents()
 	pg_get_wal_summarizer_state()

Tool: pg_walsummary — A CLI utility to inspect WAL summary files.

4. How to Enable and Use summarize_wal

4.1. Check Current Status

postgres=# SHOW summarize_wal;
 summarize_wal
---------------
 off
(1 row)

postgres=#

[postgres@lxicbpgdsgv01 ~]$ ps -ef | grep postgres
postgres    1390       1  0 12:15 ?        00:00:00 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
postgres    1456    1390  0 12:15 ?        00:00:00 postgres: logger
postgres    1466    1390  0 12:15 ?        00:00:00 postgres: checkpointer
postgres    1467    1390  0 12:15 ?        00:00:00 postgres: background writer
postgres    1479    1390  0 12:15 ?        00:00:00 postgres: walwriter
postgres    1480    1390  0 12:15 ?        00:00:00 postgres: autovacuum launcher
postgres    1481    1390  0 12:15 ?        00:00:00 postgres: archiver
postgres    1484    1390  0 12:15 ?        00:00:00 postgres: logical replication launcher
root        3205    3150  0 12:48 pts/0    00:00:00 su - postgres
postgres    3212    3205  0 12:48 pts/0    00:00:00 -bash
postgres    3290    3212 99 12:49 pts/0    00:00:00 ps -ef
postgres    3291    3212  0 12:49 pts/0    00:00:00 grep --color=auto postgres
[postgres@lxicbpgdsgv01 ~]$

	  

4.2. Enable WAL Summarization

postgres=# ALTER SYSTEM SET summarize_wal = 'on';
ALTER SYSTEM
postgres=#

4.3. Reload PostgreSQL Configuration

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=#
	  

4.4. Confirm summarize_wal is Enabled

postgres=# SHOW summarize_wal;
 summarize_wal
---------------
 on <----
(1 row)

postgres=#

[postgres@lxicbpgdsgv01 ~]$ ps -ef | grep postgres
postgres    1390       1  0 12:15 ?        00:00:00 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
postgres    1456    1390  0 12:15 ?        00:00:00 postgres: logger
postgres    1466    1390  0 12:15 ?        00:00:00 postgres: checkpointer
postgres    1467    1390  0 12:15 ?        00:00:00 postgres: background writer
postgres    1479    1390  0 12:15 ?        00:00:00 postgres: walwriter
postgres    1480    1390  0 12:15 ?        00:00:00 postgres: autovacuum launcher
postgres    1481    1390  0 12:15 ?        00:00:00 postgres: archiver
postgres    1484    1390  0 12:15 ?        00:00:00 postgres: logical replication launcher
root        3205    3150  0 12:48 pts/0    00:00:00 su - postgres
postgres    3212    3205  0 12:48 pts/0    00:00:00 -bash
postgres    3445    1390  0 12:51 ?        00:00:00 postgres: walsummarizer <-------
postgres    3537    3212  0 12:53 pts/0    00:00:00 ps -ef
postgres    3538    3212  0 12:53 pts/0    00:00:00 grep --color=auto postgres
[postgres@lxicbpgdsgv01 ~]$

4.5. List Available WAL Summaries (Ordered by Start LSN)

postgres=# SELECT * FROM pg_available_wal_summaries() ORDER BY start_lsn;
 tli | start_lsn  |  end_lsn
-----+------------+------------
   1 | 0/95000028 | 0/9501BA38
   1 | 0/9501BA38 | 0/96000028
(2 rows)

postgres=#

postgres=# select pg_get_wal_summarizer_state();
  pg_get_wal_summarizer_state
--------------------------------
 (1,0/96000028,0/960000A0,3445)
(1 row)

postgres=#

postgres=# SELECT * FROM pg_available_wal_summaries() LIMIT 1;
 tli | start_lsn  |  end_lsn
-----+------------+------------
   1 | 0/95000028 | 0/9501BA38
(1 row)

postgres=#

postgres=# SELECT * FROM pg_wal_summary_contents(1, '0/95000028', '0/9501BA38');
 relfilenode | reltablespace | reldatabase | relforknumber | relblocknumber | is_limit_block
-------------+---------------+-------------+---------------+----------------+----------------
        1247 |         16542 |       16544 |             0 |             14 | f
        1249 |         16542 |       16544 |             0 |             54 | f
        1249 |         16542 |       16544 |             0 |             55 | f
        1259 |         16542 |       16544 |             0 |              1 | f
        2608 |         16542 |       16544 |             0 |             12 | f
        2610 |         16542 |       16544 |             0 |              1 | f
        2658 |         16542 |       16544 |             0 |             15 | f
        2659 |         16542 |       16544 |             0 |             10 | f
        2662 |         16542 |       16544 |             0 |              2 | f
        2663 |         16542 |       16544 |             0 |              1 | f
        2663 |         16542 |       16544 |             0 |              2 | f
        2673 |         16542 |       16544 |             0 |              4 | f
        2673 |         16542 |       16544 |             0 |              9 | f
        2674 |         16542 |       16544 |             0 |              7 | f
        2674 |         16542 |       16544 |             0 |              5 | f
        2678 |         16542 |       16544 |             0 |              1 | f
        2679 |         16542 |       16544 |             0 |              1 | f
        2703 |         16542 |       16544 |             0 |              2 | f
        2704 |         16542 |       16544 |             0 |              4 | f
        2704 |         16542 |       16544 |             0 |              1 | f
        3455 |         16542 |       16544 |             0 |              1 | f
       16558 |         16542 |       16544 |             0 |              0 | t
       16558 |         16542 |       16544 |             0 |              0 | f
       16561 |         16542 |       16544 |             0 |              0 | t
       16562 |         16542 |       16544 |             0 |              0 | t
       16562 |         16542 |       16544 |             0 |              0 | f
(26 rows)

postgres=#
	  

5. Weekly Backup Strategy (PostgreSQL 17 / Oracle Style)

 
# Sunday - Full backup
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Sun_Full -l "This is Sunday Full backup (Level 0)" -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Sunday_Full.log 2>&1 &

# Monday - Incremental based on Sunday full
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Mon_Incr -l "This is Monday Incremental (Level 1)" -i /pgBackup/pgsql17/backup/Sun_Full/backup_manifest -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Mon_Incr.log 2>&1 &

# Tuesday - Incremental based on Monday incremental
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Tue_Incr -l "This is Tuesday Incremental (Level 1)" -i /pgBackup/pgsql17/backup/Mon_Incr/backup_manifest -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Tue_Incr.log 2>&1 &

# Wednesday - Incremental based on Tuesday incremental
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Wed_Incr -l "This is Wednesday Incremental (Level 1)" -i /pgBackup/pgsql17/backup/Tue_Incr/backup_manifest -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Wed_Incr.log 2>&1 &

# Thursday - Incremental based on Wednesday incremental
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Thu_Incr -l "This is Thursday Cumulative" -i /pgBackup/pgsql17/backup/Wed_Incr/backup_manifest -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Thu_Incr.log 2>&1 &

# Friday - Incremental based on Thursday incremental
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Fri_Incr -l "This is Friday Incremental (Level 1)" -i /pgBackup/pgsql17/backup/Thu_Incr/backup_manifest -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Fri_Incr.log 2>&1 &

# Saturday - Incremental based on Friday incremental
nohup pg_basebackup -U postgres -D /pgBackup/pgsql17/backup/Sat_Incr -l "This is Saturday Incremental (Level 1)" -i /pgBackup/pgsql17/backup/Fri_Incr/backup_manifest -Ft -Xs -P -v > /pgBackup/pgsql17/backup/log/Sat_Incr.log 2>&1 &

6. Restore Incremental Path

A new utility (e.g., pg_combinebackup) is used to merge full and incremental backups during restoration.

# Incremental Restore Path
# Combine all backups to single directory /pgBackup/pgsql17/backup/combined_full_backup

# Extract first using tar command

for dir in Sun_Full Mon_Incr Tue_Incr Wed_Incr Thu_Incr Fri_Incr Sat_Incr
do
echo "Extracting in $dir ..."
cd /pgBackup/pgsql17/backup/$dir || continue
for f in *.tar; do
tar -xf "$f"
done
done

[postgres@lxicbpgdsgv01 ~]$ for dir in Sun_Full Mon_Incr Tue_Incr Wed_Incr Thu_Incr Fri_Incr Sat_Incr
do
echo "Extracting in $dir ..."
cd /pgBackup/pgsql17/backup/$dir || continue
for f in *.tar; do
tar -xf "$f"
done
done
Extracting in Sun_Full ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13627.701332157 s in the future
Extracting in Mon_Incr ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13627.308655658 s in the future
Extracting in Tue_Incr ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13626.906480756 s in the future
Extracting in Wed_Incr ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13626.534470296 s in the future
Extracting in Thu_Incr ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13626.217307586 s in the future
Extracting in Fri_Incr ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13626.03629929 s in the future
Extracting in Sat_Incr ...
tar: pg_stat: time stamp 2025-10-13 20:15:19 is 13625.670340241 s in the future
[postgres@lxicbpgdsgv01 Sat_Incr]$

# Combine backup to /pgBackup/pgsql17/backup/combined_full_backup

nohup pg_combinebackup /pgBackup/pgsql17/backup/Sun_Full \
/pgBackup/pgsql17/backup/Mon_Incr \
/pgBackup/pgsql17/backup/Tue_Incr \
/pgBackup/pgsql17/backup/Wed_Incr \
/pgBackup/pgsql17/backup/Thu_Incr \
/pgBackup/pgsql17/backup/Fri_Incr \
/pgBackup/pgsql17/backup/Sat_Incr \
-o /pgBackup/pgsql17/backup/combined_full_backup \
> /pgBackup/pgsql17/backup/log/restore_incremental.log 2>&1 &

[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgBackup/pgsql17/backup/combined_full_backup
total 39296
-rw-------. 1 postgres postgres 240 Oct 13 16:29 backup_label
-rw-------. 1 postgres postgres 1174528 Oct 13 16:29 16542.tar
-rw-------. 1 postgres postgres 16778752 Oct 13 16:29 pg_wal.tar
-rw-------. 1 postgres postgres 1171968 Oct 13 16:29 16545.tar
-rw-------. 1 postgres postgres 4002816 Oct 13 16:29 base.tar
drwx------. 4 postgres postgres 32 Oct 13 16:29 PG_17_202406281
-rw-------. 1 postgres postgres 68 Oct 13 16:29 tablespace_map
drwx------. 5 postgres postgres 33 Oct 13 16:29 base
drwx------. 2 postgres postgres 4096 Oct 13 16:29 global
drwx------. 2 postgres postgres 188 Oct 13 16:29 log
-rw-------. 1 postgres postgres 5600 Oct 13 16:29 pg_hba.conf
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_dynshmem
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_commit_ts
drwx------. 4 postgres postgres 68 Oct 13 16:29 pg_logical
-rw-------. 1 postgres postgres 2640 Oct 13 16:29 pg_ident.conf
drwx------. 4 postgres postgres 36 Oct 13 16:29 pg_multixact
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_twophase
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_tblspc
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_subtrans
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_stat_tmp
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_stat
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_snapshots
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_serial
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_replslot
drwx------. 2 postgres postgres 6 Oct 13 16:29 pg_notify
drwx------. 2 postgres postgres 18 Oct 13 16:29 pg_xact
-rw-------. 1 postgres postgres 3 Oct 13 16:29 PG_VERSION
-rw-------. 1 postgres postgres 1169 Oct 13 16:29 postgresql.conf.bkp_10sep2025
-rw-------. 1 postgres postgres 30702 Oct 13 16:29 postgresql.conf.bkp
-rw-------. 1 postgres postgres 109 Oct 13 16:29 postgresql.auto.conf
drwx------. 4 postgres postgres 45 Oct 13 16:29 pg_wal
-rw-------. 1 postgres postgres 1169 Oct 13 16:29 postgresql.conf
-rw-------. 1 postgres postgres 30 Oct 13 16:29 current_logfiles
-rw-------. 1 postgres postgres 227 Oct 13 16:29 backup_label.old
-rw-------. 1 postgres postgres 16777216 Oct 13 16:29 0000000100000000000000B2
-rw-------. 1 postgres postgres 238882 Oct 13 16:29 backup_manifest
[postgres@lxicbpgdsgv01 ~]$

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/