How to Take Incremental PostgreSQL 18 Backups With pg_basebackup, Restore the Chain With pg_combinebackup, and Predict How Big Each One Will Be
Turn on WAL summarization in PostgreSQL 18.7, take a full backup and four incrementals, measure why 6 percent of rows changed made a backup 91.5 percent of a full one, restore the chain with...
A full backup copies everything, every time. On the 355 MiB test database in this tutorial that takes four seconds, but on a database of a few terabytes it takes hours and fills a lot of disk, even though most of the data did not change since yesterday. Since version 17, PostgreSQL can take incremental backups that copy only the parts of the files that changed since an earlier backup, and a tool called pg_combinebackup that turns a chain of them back into a normal, restorable data directory.
Table Of Content
- How incremental backups work, in plain words
- What you need before you start
- Step 1: Start a throwaway server with WAL summarization on
- Step 2: Load a database big enough to matter
- Step 3: Take the full backup
- Step 4: Take the first incremental backup after a change you can predict
- Step 5: See why where the changes land matters more than how many rows change
- The 90 percent rule
- What this means for your own database
- Step 6: Look at what the server uses, the WAL summaries
- Step 7: Combine the chain, verify it, start it, and compare it with the original
- Step 8: Break the chain on purpose
- 8.1 Wrong order, a missing link, or no full backup
- 8.2 Tar format
- 8.3 A manifest from a different server
- 8.4 WAL summarization turned off, then on again
- 8.5 One damaged byte in an incremental file
- Step 9: Automate it with a script that verifies every backup
- Common mistakes and how to check for them
- Confirm the whole thing works end to end
- Where to go next
- Sources
In this tutorial you will set up a throwaway PostgreSQL 18 server, take one full backup and four incremental ones, and measure what each costs. You will learn the one fact that decides whether incrementals help you: they follow changed blocks, not changed rows, so in my test, changing 6 percent of the rows produced a backup that was 91.5 percent as large as a full one. Then you will restore the chain, prove the restored server holds exactly the same data, break the chain five different ways on purpose so you recognise each error message, and finish with a small script that takes full and incremental backups and verifies each one. This is the sequel to the point-in-time recovery tutorial, which ended with the sentence “I did not run incremental backups for this article.” Here I did.
How incremental backups work, in plain words
Seven terms carry the whole idea, so here they are before the first command.
- A block is the 8 KiB (8,192 byte) unit PostgreSQL uses to store tables and indexes. Each table or index lives in a file named after its relfilenode (for example
base/5/16394), and a file never grows past 1 GiB; a bigger table continues in a numbered second file. - A full backup copies every file. An incremental backup copies the non-table files in full, but replaces some table and index files with smaller incremental files that hold only the blocks that changed since an earlier backup, plus enough metadata to rebuild the current file. The PostgreSQL documentation puts it this way: “some relation files may be replaced by smaller incremental files which contain only the blocks which have been changed since the earlier backup and enough metadata to reconstruct the current version of the file.”
- The WAL (write-ahead log) is the journal PostgreSQL writes every change to first. A position in it is an LSN, which looks like
0/4F000028. - A WAL summary is a small file that lists which blocks changed in a range of WAL. A background process, the WAL summarizer, writes them when you set
summarize_wal = on. In the documentation’s words: “To figure out which blocks need to be backed up, the server uses WAL summaries, which are stored in the data directory, inside the directory pg_wal/summaries. If the required summary files are not present, an attempt to take an incremental backup will fail.” - A backup manifest (
backup_manifest) is a JSON file inside every backup that lists its files, their sizes and checksums, and the WAL range it covers. When you take an incremental backup you hand the manifest of the previous backup to the server. The documentation: “You must supply, as an argument to--incremental, the backup manifest to an earlier backup from the same server.” - A chain is a full backup followed by incrementals, each taken relative to the one before. To restore, you need every backup in the chain.
pg_combinebackupmerges a chain into one synthetic full backup: an ordinary data directory that you start like any restored base backup.
Two limits are worth knowing now, because they shape the rest of the tutorial. First, PostgreSQL does not keep track of your chain: “PostgreSQL has no built-in mechanism to figure out which backups are still needed as a basis for restoring later incremental backups.” Second, pg_combinebackup checks that the folders you give it fit together, but not that each one is intact: it “only attempts to verify that the backups have the correct relationship to each other, not that each individual backup is intact; for that, use pg_verifybackup.” Both come back in the failure experiments.
What you need before you start
- PostgreSQL 17 or newer on the server, and client tools of the same major version. I used PostgreSQL 18.7, the release dated November 12, 2026. The tools you need are
initdb,pg_ctl,psql,pg_basebackup,pg_combinebackup,pg_verifybackupandpg_walsummary. The documentation notes that incremental backup “only works with server version 17 and later.” - A throwaway machine or folder. I ran everything on Windows 11 with the PostgreSQL 18.7 binaries and the Git for Windows tools (
ls,du,cat,bash). On Linux the commands are the same; only the folder paths and the name of the Python launcher (python3instead ofpython) differ. - About 2.5 GB of free disk for the test database, five backups and one restored copy.
- A recent Python 3 for three tiny scripts that use only the standard library (I used 3.13.14), and bash for the last script.
- No prior backup experience. If you have never restored a PostgreSQL backup, read the point-in-time recovery tutorial first; it explains WAL archiving, which this tutorial deliberately leaves out.
Everything below runs against a server created just for this tutorial, with passwordless local access. Do not repeat it on a production server until you have restored a backup somewhere safe.
Step 1: Start a throwaway server with WAL summarization on
Create a fresh cluster, add a few settings, start it, and check the three settings that matter for incremental backups.
$ initdb --pgdata=primary --username=postgres --auth=trust --encoding=UTF8 --locale=C
Data page checksums are enabled.
Success. You can now start the database server using:
$ cat >> primary/postgresql.conf <<'EOF'
port = 55440
listen_addresses = '127.0.0.1'
summarize_wal = on
autovacuum = off
shared_buffers = 256MB
max_wal_size = 4GB
checkpoint_timeout = 30min
EOF
$ pg_ctl --pgdata=primary --log=primary.log start
server started
$ psql -p 55440 -c "SHOW summarize_wal" -c "SHOW wal_summary_keep_time" -c "SHOW data_checksums"
summarize_wal
---------------
on
(1 row)
wal_summary_keep_time
-----------------------
10d
(1 row)
data_checksums
----------------
on
(1 row)
What each part does:
initdbcreates the data directory.--auth=trustlets any local connection in without a password, which is fine for a lab and wrong for anything else. The line “Data page checksums are enabled.” matters later: the PostgreSQL 18 release notes list “Change initdb default to enable data checksums”, and checksums are what lets the server notice a damaged block.summarize_wal = onis the setting this tutorial is about. It is off by default, and without itpg_basebackup --incrementalrefuses to run (you will see that error in Step 8). You can switch it on in a running server withALTER SYSTEM SET summarize_wal = on;andSELECT pg_reload_conf();; I used exactly that in Step 8 and the server picked it up without a restart.wal_summary_keep_timeis how long summary files are kept. The default is 10 days. The documentation advises to “set this comfortably higher than the time that could pass between a backup and a later incremental backup that depends on it.” If you take incrementals daily, the default is generous.autovacuum = offis a lab-only choice. It stops background cleanup from adding changes between my steps, so the numbers below repeat from run to run. Leave autovacuum on in real life.- The other settings (
port,listen_addresses,shared_buffers,max_wal_size,checkpoint_timeout) only keep the lab on its own port and stop checkpoints from happening at random moments.
Check it worked: SHOW summarize_wal prints on. If it prints off, the setting did not reach the server and nothing later will work.
Step 2: Load a database big enough to matter
An incremental backup of a 5 MB database teaches nothing, so create three tables of about 300 MB in total. Each plays a different role: customers never changes, orders is large and will be updated in different patterns, and events is an append-only log.
CREATE TABLE customers (
id int PRIMARY KEY,
name text NOT NULL,
plan text NOT NULL
);
INSERT INTO customers
SELECT g, 'customer ' || g, (ARRAY['free', 'pro', 'team'])[1 + g % 3]
FROM generate_series(1, 50000) AS g;
CREATE TABLE orders (
id bigint PRIMARY KEY,
customer_id int NOT NULL,
status text NOT NULL,
amount numeric(10,2) NOT NULL,
note text NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO orders
SELECT g, 1 + (g::bigint * 7919) % 50000, 'shipped', round((g % 9973) / 7.0, 2),
md5(g::text) || md5((g * 3)::text) || md5((g * 5)::text),
timestamptz '2026-01-01 00:00:00+00' + g * interval '20 seconds'
FROM generate_series(1, 1500000) AS g;
CREATE TABLE events (
id bigserial PRIMARY KEY,
order_id bigint NOT NULL,
kind text NOT NULL,
at timestamptz NOT NULL DEFAULT '2026-11-01 00:00:00+00'
);
INSERT INTO events (order_id, kind)
SELECT g, 'created' FROM generate_series(1, 300000) AS g;
VACUUM (ANALYZE);
CHECKPOINT;
Run it and look at the sizes:
$ psql -p 55440 -f sql/load.sql
CREATE TABLE
INSERT 0 50000
CREATE TABLE
INSERT 0 1500000
CREATE TABLE
INSERT 0 300000
VACUUM
CHECKPOINT
$ psql -p 55440 -c "SELECT relname, relpages, reltuples::bigint AS rows, pg_size_pretty(pg_total_relation_size(oid)) AS total FROM pg_class WHERE relname IN ('customers', 'orders', 'events') ORDER BY relname;"
relname | relpages | rows | total
-----------+----------+---------+---------
customers | 319 | 50000 | 3704 kB
events | 2206 | 300000 | 24 MB
orders | 30613 | 1499999 | 271 MB
(3 rows)
$ psql -p 55440 -c "SELECT pg_size_pretty(pg_database_size('postgres')) AS database_size, oid AS database_oid FROM pg_database WHERE datname = 'postgres';"
database_size | database_oid
---------------+--------------
307 MB | 5
(1 row)
$ psql -p 55440 -c "SELECT relname, pg_relation_filepath(oid) FROM pg_class WHERE relname IN ('orders', 'orders_pkey', 'events', 'events_pkey') ORDER BY relname;"
relname | pg_relation_filepath
-------------+----------------------
events | base/5/16408
events_pkey | base/5/16419
orders | base/5/16394
orders_pkey | base/5/16405
(4 rows)
The last query is the one to remember. A table’s data lives in a file whose name you can look up with pg_relation_filepath(). base/5/16394 means: the default tablespace (base), database number 5 (the postgres database, as the second query shows), file 16394, which is orders. Its primary key index is a separate file, 16405, and the backup decides what to do with every file on its own. The orders table has 30,613 blocks, about 49 rows each; keep that number in mind for Step 5.
Step 3: Take the full backup
The full backup is the foundation of the chain. Nothing special is needed for it; the only requirement for later is that you keep its folder, and in particular its backup_manifest file.
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=full1 -D backups/full1
$ cat backups/full1/backup_label
START WAL LOCATION: 0/1D000028 (file 00000001000000000000001D)
CHECKPOINT LOCATION: 0/1D000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-11-19 06:26:46 EST
LABEL: full1
START TIMELINE: 1
$ head -n 3 backups/full1/backup_manifest
{ "PostgreSQL-Backup-Manifest-Version": 2,
"System-Identifier": 7709842489520281712,
"Files": [
{ "Path": "pg_xact/0000", "Size": 8192, "Last-Modified": "2026-11-19 11:26:44 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "daf65029" },
{ "Path": "postgresql.auto.conf", "Size": 90, "Last-Modified": "2026-11-19 11:26:29 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "c1f5f088" },
{ "Path": "postgresql.conf", "Size": 33699, "Last-Modified": "2026-11-19 11:26:33 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "480a95a8" },
{ "Path": "global/pg_control", "Size": 8192, "Last-Modified": "2026-11-19 11:26:47 GMT", "Checksum-Algorithm": "CRC32C", "Checksum": "93513075" }
],
"WAL-Ranges": [
{ "Timeline": 1, "Start-LSN": "0/1D000028", "End-LSN": "0/1D000120" }
],
"Manifest-Checksum": "b1fdc7116957c0f5c753b8c175888ad784ffca2aa37582e2ec89e6425faf56b8"}
$ du -sm backups/*
356 backups/full1
What the command does: pg_basebackup connects to the server like a replica, asks for a checkpoint (--checkpoint=fast runs it immediately instead of spreading it out over time), and copies the whole data directory into backups/full1. It writes a plain copy of the files by default and, also by default, streams the WAL generated during the backup into the copy’s pg_wal folder, which is why the copy is 355 MiB for a 307 MB database (33 MiB of that is two WAL segments). The backup took 3.8 seconds.
Two files tell you what you have. backup_label records where recovery must start (START WAL LOCATION: 0/1D000028) and the label you chose. backup_manifest is JSON with the server’s System-Identifier (this identifies this server, which Step 8 uses), one line per file with a CRC32C checksum, and a WAL-Ranges entry with the start and end LSN. The next backup will use that start LSN to ask “what changed since here?”
Check it worked: the folder exists, it has a backup_manifest, and pg_verifybackup backups/full1 prints “backup successfully verified”. I run that check on every backup from now on.
Step 4: Take the first incremental backup after a change you can predict
Pretend a day passes. The business appended 100,000 rows to the event log, and 15,000 of the newest orders were marked as returned. These are the friendliest changes a database can make: new rows go to the end of a file, and the newest orders sit next to each other.
$ psql -p 55440 -c "INSERT INTO events (order_id, kind) SELECT g, 'shipped' FROM generate_series(1, 100000) AS g;"
INSERT 0 100000
$ psql -p 55440 -c "UPDATE orders SET status = 'returned' WHERE id > 1485000;"
UPDATE 15000
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=inc1 -D backups/inc1 --incremental=backups/full1/backup_manifest
The new part is the last flag. --incremental=backups/full1/backup_manifest uploads the previous backup’s manifest to the server. The server reads the LSN where that backup started, looks at the WAL summaries from there to now, and builds a list of changed blocks. Look at what came out:
$ du -sm backups/*
356 backups/full1
34 backups/inc1
$ ls -lS backups/inc1/base/5 | head -n 6
total 15045
-rw-r--r-- 1 Administrator 197121 6045696 Nov 19 06:26 INCREMENTAL.16408
-rw-r--r-- 1 Administrator 197121 5029888 Nov 19 06:26 INCREMENTAL.16394
-rw-r--r-- 1 Administrator 197121 2277376 Nov 19 06:26 INCREMENTAL.16419
-rw-r--r-- 1 Administrator 197121 712704 Nov 19 06:26 INCREMENTAL.16405
-rw-r--r-- 1 Administrator 197121 81920 Nov 19 06:26 16394_fsm
$ ls backups/inc1/base/5 | grep -c INCREMENTAL
223
The incremental backup is 34 MiB instead of 356 MiB. The four largest files are all named INCREMENTAL.number: incremental versions of the events table (16408), the orders table (16394), the events index (16419) and the orders index (16405). The count of 223 is higher than the four files you changed because every non-empty relation file in the database gets an incremental entry; the ones that did not change at all are stored as 12-byte stubs, which you can see by listing the smallest ones in the next backup, inc2:
$ ls -lS backups/inc2/base/5 | grep INCREMENTAL | tail -n 3
-rw-r--r-- 1 Administrator 197121 12 Nov 19 06:27 INCREMENTAL.6352
-rw-r--r-- 1 Administrator 197121 12 Nov 19 06:27 INCREMENTAL.827
-rw-r--r-- 1 Administrator 197121 12 Nov 19 06:27 INCREMENTAL.828
Now open one incremental file. It has a simple layout, which this small script reads. The header is three 4-byte numbers (a magic number, how many blocks the file stores, and how many blocks the relation has once rebuilt), followed by the numbers of the stored blocks, padded to a whole block, followed by the blocks themselves.
"""inc_header.py: read the header of an incremental relation file written by
pg_basebackup --incremental (PostgreSQL 17 and later) and check its size.
usage: python inc_header.py PATH [TOTAL_BLOCKS_IN_THE_TABLE]
"""
import os
import struct
import sys
BLCKSZ = 8192 # the default PostgreSQL block size
path = sys.argv[1]
size = os.path.getsize(path)
with open(path, "rb") as f:
# three 4-byte numbers: a magic number, how many blocks the file stores,
# and how many blocks the relation file has when it is rebuilt
magic, nblocks, truncation_blocks = struct.unpack("<3I", f.read(12))
first = struct.unpack(f"<{min(nblocks, 5)}I", f.read(4 * min(nblocks, 5)))
header = 12 + 4 * nblocks
if nblocks and header % BLCKSZ:
header += BLCKSZ - header % BLCKSZ # the header is padded to a whole block
expected = header + nblocks * BLCKSZ
print(f"magic number 0x{magic:08x}")
print(f"blocks stored {nblocks}")
print(f"truncation length {truncation_blocks} blocks")
print(f"first block numbers {list(first)}")
print(f"header size {header} bytes")
print(f"expected file size {expected} bytes")
print(f"actual file size {size} bytes")
if len(sys.argv) > 2:
total = int(sys.argv[2])
print(f"share of the table {100 * nblocks / total:.1f} percent of {total} blocks")
Run it on the incremental file of orders, passing the table’s current size in blocks:
$ python inc_header.py backups/inc1/base/5/INCREMENTAL.16394 30919
magic number 0xd3ae1f0d
blocks stored 613
truncation length 30919 blocks
first block numbers [30306, 30307, 30308, 30309, 30310]
header size 8192 bytes
expected file size 5029888 bytes
actual file size 5029888 bytes
share of the table 2.0 percent of 30919 blocks
# orders_pkey (base/5/16405): incremental file with 86 of 4157 blocks
# events (base/5/16408): incremental file with 737 of 2942 blocks
Reading the output: the file stores 613 blocks of the table’s 30,919, so 2.0 percent. The first stored block numbers (30,306 and up) are at the very end of the table, where the newest orders live. The size check at the bottom is the proof that the layout above is right: a header of 8,192 bytes plus 613 blocks of 8,192 bytes is 5,029,888 bytes, exactly the size of the file. One honest note: a comment in the PostgreSQL source lists the three header numbers in a different order than the file holds them, and my first version of the script read them in the comment’s order and failed this very size check. If you write tools that read these files, test them against real ones.
The other two lines show that each file gets its own decision: the orders_pkey index stored 86 of its 4,157 blocks, and the events table 737 of 2,942.
Step 5: See why where the changes land matters more than how many rows change
Now repeat the cycle three times with bigger changes, each time taking the incremental backup relative to the previous one, so each backup holds exactly one change. These updates pick rows by a hash of their id, which scatters them evenly through the table, like updates to “last seen” columns or status flags do in a real application: 1 percent of the rows, then 3 percent, then 6 percent.
$ psql -p 55440 -c "UPDATE orders SET status = 'refunded' WHERE (hashint8(id)::bigint & 2147483647) % 100 = 7;"
UPDATE 14910
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=inc2 -D backups/inc2 --incremental=backups/inc1/backup_manifest
$ psql -p 55440 -c "UPDATE orders SET status = 'disputed' WHERE (hashint8(id)::bigint & 2147483647) % 100 IN (20, 21, 22);"
UPDATE 45049
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=inc3 -D backups/inc3 --incremental=backups/inc2/backup_manifest
$ psql -p 55440 -c "UPDATE orders SET status = 'cancelled' WHERE (hashint8(id)::bigint & 2147483647) % 100 BETWEEN 40 AND 45;"
UPDATE 90009
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=inc4 -D backups/inc4 --incremental=backups/inc3/backup_manifest
Here is how the orders table was stored in each backup. For inc2 and inc3 it was an incremental file, so the header script applies:
$ python inc_header.py backups/inc2/base/5/INCREMENTAL.16394 31223
magic number 0xd3ae1f0d
blocks stored 12630
truncation length 31223 blocks
first block numbers [1, 3, 4, 5, 6]
header size 57344 bytes
expected file size 103522304 bytes
actual file size 103522304 bytes
share of the table 40.5 percent of 31223 blocks
$ python inc_header.py backups/inc3/base/5/INCREMENTAL.16394 31933
magic number 0xd3ae1f0d
blocks stored 27473
truncation length 31933 blocks
first block numbers [0, 1, 2, 3, 4]
header size 114688 bytes
expected file size 225173504 bytes
actual file size 225173504 bytes
share of the table 86.0 percent of 31933 blocks
For inc4 there is no INCREMENTAL.16394 at all. The table was written as a whole file:
$ ls -lS backups/inc4/base/5 | head -n 6
total 298065
-rw-r--r-- 1 Administrator 197121 269828096 Nov 19 06:27 16394
-rw-r--r-- 1 Administrator 197121 34054144 Nov 19 06:27 16405
-rw-r--r-- 1 Administrator 197121 90112 Nov 19 06:27 16394_fsm
-rw-r--r-- 1 Administrator 197121 24576 Nov 19 06:27 1247_fsm
-rw-r--r-- 1 Administrator 197121 24576 Nov 19 06:27 1249_fsm
$ ls backups/inc4/base/5 | grep -c INCREMENTAL
223
# orders (base/5/16394) was written as a whole file: 257.3 MiB
# orders_pkey (base/5/16405): whole file, 32.5 MiB
# events (base/5/16408): incremental file with 0 of 2942 blocks
The summary of the whole experiment is below. The first table lists each backup’s size and time; the second compares what changed with what had to be stored.
backup kind MiB seconds share of a full
full1 full 355.0 3.8 100.0%
inc1 incremental 32.6 11.0 9.2%
inc2 incremental 150.3 2.1 42.4%
inc3 incremental 266.3 2.2 75.0%
inc4 incremental 324.9 4.6 91.5%
chain 5 backups 1129.1
5 fulls 1774.8
combined synthetic 381.4 2.1
backup rows changed % of rows model % of blocks blocks sent of stored as
inc1 15,000 1.0 2.0 613 30,919 incremental file
inc2 14,910 1.0 39.3 12,630 31,223 incremental file
inc3 45,049 3.0 78.7 27,473 31,933 incremental file
inc4 90,009 6.0 97.9 32,938 32,938 whole file
Read the second table slowly, because it is the main lesson of this tutorial:
- inc1: 1 percent of the rows changed, next to each other, and 2.0 percent of the blocks had to be stored. The backup is 9.2 percent of a full one.
- inc2: the same 1 percent of rows, scattered, touched 40.5 percent of the blocks. Every block holds about 49 rows, so a block stays untouched only if none of its 49 rows changed.
- inc3: 3 percent of the rows touched 86.0 percent of the blocks. The incremental file was 215 MiB, against 250 MiB for the whole table at that point, so it saved a little and made the backup 75.0 percent of a full one.
- inc4: 6 percent of the rows would touch about 98 percent of the blocks, so PostgreSQL did not even try: it wrote the whole file. The backup is 91.5 percent of a full one although only 6 percent of the rows changed.
The “model” column comes from a small calculation you can reproduce. If each row changes independently with chance p, a block with 49 rows is untouched with probability (1 − p) to the power 49, and every changed row also writes a new row version into new blocks at the end of the table. This script prints the model for the four steps (the inputs are the numbers from the lab above):
"""model.py: estimate what share of a table's blocks an incremental backup has to store.
A block holds about 49 rows. If every row changes on its own with chance p, a block is untouched only when
none of its 49 rows changed, so the share of touched blocks is 1 - (1 - p) ** 49. Every changed row also
writes a new row version, which lands in blocks added at the end of the table.
"""
ROWS_TOTAL = 1_500_000
ROWS_PER_BLOCK = 49.0 # reltuples / relpages right after the load: 1,499,999 / 30,613
steps = [ # name, rows changed, blocks in the table when the backup ran, where the changed rows sit
("inc1", 15_000, 30_919, "the newest rows, next to each other"),
("inc2", 14_910, 31_223, "scattered"),
("inc3", 45_049, 31_933, "scattered"),
("inc4", 90_009, 32_938, "scattered"),
]
blocks_before = 30_613
print(f"{'backup':<8}{'rows changed':>14}{'% of rows':>11}{'model % of blocks':>19} verdict")
for name, changed, blocks_after, where in steps:
if where.startswith("the newest"):
touched = changed / ROWS_PER_BLOCK
else:
p = changed / ROWS_TOTAL
touched = blocks_before * (1 - (1 - p) ** ROWS_PER_BLOCK)
touched += changed / ROWS_PER_BLOCK
share = 100 * min(touched, blocks_after) / blocks_after
verdict = "whole file (more than 90 percent)" if share > 90 else "incremental file"
print(f"{name:<8}{changed:>14,}{100 * changed / ROWS_TOTAL:>11.1f}{share:>19.1f} {verdict}")
blocks_before = blocks_after
$ python model.py
backup rows changed % of rows model % of blocks verdict
inc1 15,000 1.0 2.0 incremental file
inc2 14,910 1.0 39.3 incremental file
inc3 45,049 3.0 78.7 incremental file
inc4 90,009 6.0 97.9 whole file (more than 90 percent)
The model is close for small changes (39.3 against 40.5 measured) and low for 3 percent (78.7 against 86.0). I did not isolate the reason for the gap. Treat the model as a way to see the shape, not as a forecast.
The 90 percent rule
Why did inc4 store the whole table? Because the server decides per file, and the source code of PostgreSQL 18 (basebackup_incremental.c) says so in a comment: “if we’d need to send 90% of the blocks anyway, give up and send the whole file.” The test is a single comparison, nblocks * BLCKSZ > size * 0.9. The same file lists the other cases that always go whole: a file that was not part of the previous backup (a new table, or one rewritten by VACUUM FULL, which gets a new file number; I read that in the source and did not test a rewrite), and the free-space map. The source comment on that case reads: “The free-space map fork is not properly WAL-logged, so we need to backup the entire file every time.” That is why 16394_fsm shows up as a whole file in every backup above. In inc2 and inc3 the orders_pkey index was also written whole (32.5 MiB each); I did not measure why, but scattered updates also insert new index entries all over the index.
What this means for your own database
- Incrementals shine for append-heavy data: logs, events, time-series tables, and anything partitioned by time where old partitions stop changing. The documentation says it plainly: “Incremental backups typically only make sense for relatively large databases where a significant portion of the data does not change, or only changes slowly.”
- They help little for tables where updates are spread across the whole table, however small each update is. “For a large database all of which is heavily modified, incremental backups won’t be much smaller than full backups.”
- Do not guess. Take a full backup, let a normal day pass, take an incremental one, and compare the two folder sizes. That ratio is the number to plan around.
- The time did not follow the size. The first incremental took 11.0 seconds, against 2 to 5 seconds for the others, even though it was the smallest. I did not trace the delay. The documentation says the server may have to wait for WAL summaries that are not on disk yet (“the server will wait for any missing files to show up”), so a backup taken right after a burst of writes can wait for the summarizer; take that as a hypothesis, not a measurement.
Step 6: Look at what the server uses, the WAL summaries
You do not need to touch summaries to take backups, but looking at them once makes the mechanism concrete. Three tools expose them: the function pg_get_wal_summarizer_state(), the function pg_available_wal_summaries(), and the command-line program pg_walsummary.
$ psql -p 55440 -c "SELECT * FROM pg_get_wal_summarizer_state();"
summarized_tli | summarized_lsn | pending_lsn | summarizer_pid
----------------+----------------+-------------+----------------
1 | 0/4F000028 | 0/50000060 | 10924
(1 row)
$ psql -p 55440 -c "SELECT * FROM pg_available_wal_summaries() ORDER BY end_lsn DESC LIMIT 4;"
tli | start_lsn | end_lsn
-----+------------+------------
1 | 0/4E647690 | 0/4F000028
1 | 0/3B000028 | 0/4E647690
1 | 0/3AA34868 | 0/3B000028
1 | 0/2A000028 | 0/3AA34868
(4 rows)
$ ls -lS primary/pg_wal/summaries | head -n 4
total 113
-rw-r--r-- 1 Administrator 197121 25210 Nov 19 06:26 000000010000000001805800000000001C1B1F30.summary
-rw-r--r-- 1 Administrator 197121 16496 Nov 19 06:27 00000001000000002A000028000000003AA34868.summary
-rw-r--r-- 1 Administrator 197121 16496 Nov 19 06:27 00000001000000003B000028000000004E647690.summary
$ pg_walsummary primary/pg_wal/summaries/000000010000000001805800000000001C1B1F30.summary | head -n 6
TS 1663, DB 5, REL 1247, FORK main: block 14
TS 1663, DB 5, REL 1247, FORK vm: block 0
TS 1663, DB 5, REL 1249, FORK main: block 16
TS 1663, DB 5, REL 1249, FORK main: blocks 64..65
TS 1663, DB 5, REL 1249, FORK vm: block 0
TS 1663, DB 5, REL 1259, FORK main: blocks 0..9
The first query shows the summarizer process and how far it has got: it had summarized up to 0/4F000028 on timeline 1 (summarized_tli and summarized_lsn). I could not find pending_lsn described in the documentation pages I read, so I leave that column uninterpreted. The second lists the summary files as ranges of WAL: each file covers one range from start_lsn to end_lsn on a timeline (tli), and consecutive files meet exactly. The file names encode the same three numbers. pg_walsummary prints the contents of one file: for every relation file (TS is the tablespace, DB the database, REL the file number) the blocks that changed, as single blocks or ranges such as blocks 64..65. The first lines are low-numbered system catalog files, touched while the tables were created and loaded.
The rule for taking an incremental backup follows from this picture. The summaries must cover every LSN from the start of the previous backup to the start of the new one. If there is a hole, no incremental backup is possible until you take a new full one; Step 8 shows you that.
Step 7: Combine the chain, verify it, start it, and compare it with the original
Now restore. pg_combinebackup takes the backup folders from oldest to newest (“Specify all of the required backups on the command line from oldest to newest”) and writes a new folder with -o.
$ pg_combinebackup backups/full1 backups/inc1 backups/inc2 backups/inc3 backups/inc4 -o restored
$ du -sm restored
382 restored
$ cat restored/backup_label
START WAL LOCATION: 0/4F000028 (file 00000001000000000000004F)
CHECKPOINT LOCATION: 0/4F000080
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-11-19 06:27:15 EST
LABEL: inc4
START TIMELINE: 1
$ pg_combinebackup --dry-run --debug backups/full1 backups/inc1 backups/inc2 backups/inc3 backups/inc4 -o restored_dry 2>&1 | grep -E '16394|16408' | grep -v -E '_fsm|_vm'
pg_combinebackup: would copy "backups/inc4/base/5/16394" to "restored_dry/base/5/16394" using strategy CopyFile
pg_combinebackup: would reconstruct "restored_dry/base/5/16408" (2942 blocks, checksum CRC32C)
pg_combinebackup: reconstruction plan: 0-2204:backups/full1/base/5/16408@18055168 2205-2941:backups/inc1/base/5/INCREMENTAL.16408@6037504
pg_combinebackup: would have read 2205 blocks from "backups/full1/base/5/16408"
pg_combinebackup: would have read 737 blocks from "backups/inc1/base/5/INCREMENTAL.16408"
What happened: 382 MiB were written in 2.1 seconds. The restored folder’s backup_label carries the label and start position of the last backup (inc4, 0/4F000028), so the synthetic backup behaves like a full backup taken at that moment. The --dry-run --debug lines show where two files came from. The orders table was taken straight from inc4, which held it whole, so nothing older was needed. The events table had to be rebuilt, and the “reconstruction plan” line says how: blocks 0 to 2,204 come from the full backup and blocks 2,205 to 2,941 from the incremental file in inc1, which is the only later backup that stored any of its blocks. That is 2,205 plus 737, which is 2,942 blocks, the table’s size, and a useful sanity check.
Next, verify the combined folder, start it on another port, and compare it with the original server. The comparison uses a fingerprint query that hashes every row of every table:
SELECT 'customers' AS tbl, count(*) AS n, sum(hashtextextended(t::text, 0)) AS fingerprint FROM customers t
UNION ALL
SELECT 'orders', count(*), sum(hashtextextended(t::text, 0)) FROM orders t
UNION ALL
SELECT 'events', count(*), sum(hashtextextended(t::text, 0)) FROM events t;
$ pg_verifybackup restored
backup successfully verified
$ pg_ctl --pgdata=restored --log=restored.log --options="-p 55441" start
server started
$ psql -p 55440 -f sql/fingerprint.sql
tbl | n | fingerprint
-----------+---------+-------------------------
customers | 50000 | -915632840378786038895
orders | 1500000 | 2982982759909971930670
events | 400000 | -3672072005027463771669
(3 rows)
$ psql -p 55441 -f sql/fingerprint.sql
tbl | n | fingerprint
-----------+---------+-------------------------
customers | 50000 | -915632840378786038895
orders | 1500000 | 2982982759909971930670
events | 400000 | -3672072005027463771669
(3 rows)
$ grep -E 'backup recovery|redo|ready to accept' restored.log
LOG: starting backup recovery with redo LSN 0/4F000028, checkpoint LSN 0/4F000080, on timeline ID 1
LOG: redo starts at 0/4F000028
LOG: completed backup recovery with redo LSN 0/4F000028 and end LSN 0/4F000120
LOG: redo done at 0/50000028 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOG: database system is ready to accept connections
$ psql -p 55441 -c "SELECT status, count(*) FROM orders GROUP BY status ORDER BY status"
status | count
-----------+---------
cancelled | 90009
disputed | 45049
refunded | 14910
returned | 13531
shipped | 1336501
(5 rows)
Walk through the output. pg_verifybackup accepted the folder. The server log shows it starting “backup recovery” at the backup’s start LSN (0/4F000028), completing it at the backup’s end LSN 248 bytes later, replaying the rest of the WAL in the folder, and then accepting connections. The two fingerprint tables match line for line: 50,000 customers, 1,500,000 orders and 400,000 events with identical row hashes on the primary and on the restored copy. The status counts at the end show every change from the four incremental backups: 90,009 cancelled, 45,049 disputed, 14,910 refunded and 13,531 returned orders.
Check it worked: the fingerprints agree and the status counts add up to 1,500,000. A restore you have not compared with the source is not yet a tested restore.
One limit: this restore ends where the last backup ended. To replay more WAL up to a chosen moment you also need a WAL archive and a recovery.signal file, exactly as in the point-in-time recovery tutorial. The documentation is explicit that incremental backups add a step, not a replacement: “The requirement to have earlier backups available at restore time and to use pg_combinebackup is an additional requirement on top of everything else.”
Step 8: Break the chain on purpose
You will meet each of these in real life, usually at the worst time. Meeting them now, with messages in front of you, is cheaper.
8.1 Wrong order, a missing link, or no full backup
I gave pg_combinebackup three bad chains: a full backup after an incremental one, a chain that skips inc1, and a chain with no full backup at all.
$ pg_combinebackup backups/inc1 backups/full1 -o x_wrong_order
pg_combinebackup: error: backup at "backups/full1" is a full backup, but only the first backup should be a full backup
# exit status 1
# output folder exists afterwards: False
$ pg_combinebackup backups/full1 backups/inc2 -o x_gap
pg_combinebackup: error: backup at "backups/full1" starts at LSN 0/1D000028, but expected 0/20000028
# exit status 1
# output folder exists afterwards: False
$ pg_combinebackup backups/inc1 backups/inc2 -o x_nofull
pg_combinebackup: error: backup at "backups/inc1" is an incremental backup, but the first backup should be a full backup
# exit status 1
# output folder exists afterwards: False
The messages are accurate once you know how to read them. The first says only the first folder may be a full backup. The second is the most useful one: inc2 was taken relative to a backup that started at 0/20000028 (that is inc1), but the folder before it in my command, full1, started at 0/1D000028, so the chain has a hole. The third says the first folder must be a full backup. In all three cases the tool refused to create the output folder, so a failed attempt leaves nothing behind to clean up.
8.2 Tar format
You might want compressed backups, so you try the tar format:
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --format=tar --label=tar_inc -D backups/tar_inc --incremental=backups/inc4/backup_manifest
# exit status 0
$ ls backups/tar_inc
backup_manifest
base.tar
pg_wal.tar
$ pg_combinebackup backups/full1 backups/inc1 backups/inc2 backups/inc3 backups/inc4 backups/tar_inc -o x_tar
pg_combinebackup: error: could not open file "backups/tar_inc/PG_VERSION": No such file or directory
# exit status 1
pg_basebackup accepted --format=tar together with --incremental and wrote base.tar and pg_wal.tar, but pg_combinebackup could not use the result, and its message (“could not open file backups/tar_inc/PG_VERSION”) does not say why. In my runs only plain-format backups could be combined. I did not find a sentence in the pg_combinebackup documentation that says tar backups are unsupported, so treat this as what I observed, not as a documented rule. If you want compression, compress the finished folders yourself after verifying them, and unpack them before a restore.
8.3 A manifest from a different server
I created a second, unrelated cluster, took a full backup of it, and tried to use its manifest as the base of an incremental backup of the first server:
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast -D x_foreign --incremental=backups_other/full/backup_manifest
pg_basebackup: error: could not upload manifest: ERROR: system identifier in backup manifest is 7709842794458723156, but database system identifier is 7709842489520281712
pg_basebackup: removing data directory "x_foreign"
# exit status 1
# output folder exists afterwards: False
The server compares the System-Identifier in the manifest with its own and refuses, before copying a byte. This is the check that protects you when a backup folder is mixed up between servers or between a server and its rebuilt twin.
8.4 WAL summarization turned off, then on again
Someone edits the configuration and turns summarize_wal off for a while, then back on. First the commands (psql output follows below):
ALTER SYSTEM SET summarize_wal = off;
SELECT pg_reload_conf();
-- ... time passes, writes continue ...
ALTER SYSTEM SET summarize_wal = on;
SELECT pg_reload_conf();
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast -D x_off --incremental=backups/inc4/backup_manifest
pg_basebackup: error: could not initiate base backup: ERROR: incremental backups cannot be taken unless WAL summarization is enabled
pg_basebackup: removing data directory "x_off"
# exit status 1
$ psql -p 55440 -c "INSERT INTO events (order_id, kind) SELECT g, 'late' FROM generate_series(1, 5000) AS g;"
INSERT 0 5000
$ psql -p 55440 -c "CHECKPOINT;"
CHECKPOINT
$ psql -p 55440 -c "SELECT pg_switch_wal();"
pg_switch_wal
---------------
0/610C3D10
(1 row)
$ psql -p 55440 -c "ALTER SYSTEM SET summarize_wal = on;"
ALTER SYSTEM
$ psql -p 55440 -c "SELECT pg_reload_conf();"
pg_reload_conf
----------------
t
(1 row)
$ psql -p 55440 -c "SELECT * FROM pg_get_wal_summarizer_state();"
summarized_tli | summarized_lsn | pending_lsn | summarizer_pid
----------------+----------------+-------------+----------------
1 | 0/610C3C28 | 0/62000060 | 17248
(1 row)
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast -D x_off --incremental=backups/inc4/backup_manifest
WARNING: aborting backup due to backend exiting before pg_backup_stop was called
pg_basebackup: error: could not initiate base backup: ERROR: WAL summaries are required on timeline 1 from 0/4F000028 to 0/63000028, but the summaries for that timeline and LSN range are incomplete
DETAIL: The first unsummarized LSN in this range is 0/60000028.
pg_basebackup: removing data directory "x_off"
# exit status 1
# took 0.1 s
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=full2 -D backups/full2
# exit status 0
$ psql -p 55440 -c "UPDATE orders SET status = 'archived' WHERE id <= 1000;"
UPDATE 1000
$ psql -p 55440 -c "CHECKPOINT;"
CHECKPOINT
$ pg_basebackup -h 127.0.0.1 -p 55440 -U postgres --checkpoint=fast --label=inc5 -D backups/inc5 --incremental=backups/full2/backup_manifest
# exit status 0
$ du -sm backups/full2 backups/inc5
366 backups/full2
21 backups/inc5
With summarization off, the very first attempt fails with a clear message: “incremental backups cannot be taken unless WAL summarization is enabled.” After I wrote 5,000 more rows, switched it back on and waited, the server still refused, with a different message: the summaries for the range from inc4’s start to now are incomplete, and “the first unsummarized LSN in this range is 0/60000028”. Turning the setting back on did not backfill the gap. The fix is the last step in the transcript: take a new full backup (full2) and base the next incremental on it. That worked (inc5 is 21 MiB). The backups taken before the gap are untouched files, but no new incremental backup can extend that chain.
A similar failure follows if wal_summary_keep_time is shorter than the time between two of your backups: the summaries you need are deleted before you ask for them, and, in the documentation’s words, “if not, the incremental backup will fail.” That is why the documentation says to set it “comfortably higher” than that time.
8.5 One damaged byte in an incremental file
This is the experiment I most want you to remember. Disks and copy jobs do corrupt backups. I copied inc2, flipped every bit of one byte inside a stored block of its orders file with this script, and then asked the tools what they thought.
"""flip_byte.py: damage a COPY of an incremental backup file on purpose: flip every bit of one byte inside
the first stored block (OFFSET bytes after the end of the header).
usage: python flip_byte.py INCREMENTAL_FILE OFFSET
"""
import struct
import sys
BLCKSZ = 8192
path, offset = sys.argv[1], int(sys.argv[2])
with open(path, "r+b") as f:
magic, nblocks, truncation_blocks = struct.unpack("<3I", f.read(12))
first_block = struct.unpack("<I", f.read(4))[0]
header = 12 + 4 * nblocks
if header % BLCKSZ:
header += BLCKSZ - header % BLCKSZ
where = header + offset
f.seek(where)
old = f.read(1)[0]
f.seek(where)
f.write(bytes([old ^ 0xFF]))
print(f"flipped the byte at file offset {where} (table block {first_block}): 0x{old:02x} became 0x{old ^ 0xFF:02x}")
$ cp -r backups/inc2 bad_inc2
$ python flip_byte.py bad_inc2/base/5/INCREMENTAL.16394 4000
flipped the byte at file offset 61344 (table block 1): 0x30 became 0xcf
# exit status 0
$ pg_verifybackup bad_inc2
pg_verifybackup: error: checksum mismatch for file "base/5/INCREMENTAL.16394"
# exit status 1
$ pg_combinebackup backups/full1 backups/inc1 bad_inc2 -o restored_bad
# exit status 0
$ pg_verifybackup restored_bad
backup successfully verified
# exit status 0
$ pg_ctl --pgdata=restored_bad --log=restored_bad.log --options="-p 55441" start
server started
$ psql -p 55441 -c "SELECT count(*) FROM orders"
ERROR: invalid page in block 1 of relation "base/5/16394"
# exit status 1
Read the results in order. pg_verifybackup on the damaged input caught it (“checksum mismatch for file base/5/INCREMENTAL.16394”). But pg_combinebackup used the damaged file without a word (exit status 0), and pg_verifybackup on the combined folder then reported “backup successfully verified”, because the combined folder’s manifest describes the files the tool had just written. The damage only showed when the restored server read that part of the table: ERROR: invalid page in block 1 of relation "base/5/16394". Here the server noticed because data checksums are on, the PostgreSQL 18 default; on a cluster without them I would not count on being told, and I did not test that case.
The lesson is the documentation’s sentence from the beginning, now with evidence: pg_combinebackup does not check that each backup is intact. Run pg_verifybackup on every backup in the chain right after taking it, and again before you combine them. Verifying only the combined result proves nothing about the inputs.
Step 9: Automate it with a script that verifies every backup
This bash script takes a full backup when the folder is empty or the chain has grown long, otherwise an incremental backup based on the newest backup, and always verifies the new folder before it reports success. Folder names are timestamps ending in -full or -incr, so a plain ls shows the chain structure: a full folder and every folder after it, up to the next full folder, belong together.
#!/usr/bin/env bash
# backup_nightly.sh DIR: take a full backup when DIR is empty or the chain is long, otherwise an
# incremental one based on the newest backup, and verify the new backup before trusting it.
# Needs PostgreSQL 17 or newer client tools and summarize_wal = on on the server.
set -euo pipefail
dir=${1:?usage: backup_nightly.sh DIR}
max_chain=${MAX_CHAIN:-6} # at most this many incrementals after a full backup
mkdir -p "$dir"
stamp=$(date -u +%Y%m%dT%H%M%SZ)
latest=$(ls -1 "$dir" | sort | tail -n 1)
since_full=$(ls -1 "$dir" | sort | awk '/-full$/ {n = 0; next} {n++} END {print n + 0}')
if [ -z "$latest" ] || [ "$since_full" -ge "$max_chain" ]; then
kind=full
target="$dir/$stamp-full"
pg_basebackup -D "$target" --checkpoint=fast --label="$stamp-full"
else
kind=incremental
target="$dir/$stamp-incr"
pg_basebackup -D "$target" --checkpoint=fast --label="$stamp-incr" \
--incremental="$dir/$latest/backup_manifest"
fi
pg_verifybackup "$target"
echo "$kind backup ok: $target"
How it works, line by line: set -euo pipefail makes the script stop at the first error, so a failed backup or a failed verification fails the whole run and your scheduler sees it. since_full counts the incremental folders after the newest -full folder. If that count reaches MAX_CHAIN (default 6), the script takes a new full backup, which keeps chains short; a shorter chain means fewer folders to lose and fewer to combine. Otherwise it passes the newest folder’s manifest to --incremental. The connection settings come from the usual PG* environment variables (in my run PGHOST, PGUSER and PGPORT), and pg_verifybackup checks the new folder against its own manifest.
I ran it three times, the third with MAX_CHAIN=1 to force a new full backup:
$ ./backup_nightly.sh nightly
backup successfully verified
full backup ok: nightly/20261119T112848Z-full
$ ./backup_nightly.sh nightly
backup successfully verified
incremental backup ok: nightly/20261119T112859Z-incr
$ MAX_CHAIN=1 ./backup_nightly.sh nightly
backup successfully verified
full backup ok: nightly/20261119T112902Z-full
$ ls nightly
20261119T112848Z-full
20261119T112859Z-incr
20261119T112902Z-full
$ du -sm nightly/*
383 nightly/20261119T112848Z-full
21 nightly/20261119T112859Z-incr
367 nightly/20261119T112902Z-full
# exit statuses and seconds: [(0, 9.3), (0, 2.1), (0, 11.9)]
The first run found an empty folder and took a full backup, the second took a 21 MiB incremental, and the third started a new chain because the limit was one incremental per full. All three verified. Two cautions. This script is a starting point, not a tested production tool: it has no locking, no retention and no alerting. And retention is yours to design, because PostgreSQL will not do it for you: delete a whole chain (a -full folder and the incrementals after it), oldest first, and never a folder that a later incremental depends on.
Common mistakes and how to check for them
- Forgetting
summarize_wal = onuntil the day you want an incremental. It is off by default, and summaries are not created retroactively. Check:SHOW summarize_wal;andSELECT * FROM pg_get_wal_summarizer_state();. - Basing an incremental on the wrong manifest. Use the manifest of the backup directly before, and keep the chain in order (the
-fulland-incrnames above do that). Check:pg_combinebackup --dry-runon the chain, which stops with the LSN message from Step 8.1 if there is a hole. - Deleting an old backup that a newer incremental needs. The tool will not stop you. Delete whole chains only.
- Trusting the combined result. Verify every input folder with
pg_verifybackupwhen it is taken and before it is combined (Step 8.5). - Using tar format. Plain format was the only one that worked with
pg_combinebackupin my tests (Step 8.2). - Expecting small backups from update-heavy tables. Measure first (Step 5).
- Letting summaries expire or turning summarization off. Keep
wal_summary_keep_timeabove your backup interval and take a new full backup after any gap (Step 8.4). - Taking an incremental backup of a standby with no activity. The documentation warns that an incremental backup of a standby “can fail if there has been very little activity since the previous backup”, because replay must begin from a later restartpoint. I tested the primary only.
- Never restoring. A backup is only as good as the last restore drill.
Confirm the whole thing works end to end
SHOW summarize_wal;printsonon the server you back up.- Each backup folder verifies:
pg_verifybackup backups/full1, and the same for every incremental, right after it is taken. - The chain restores:
pg_combinebackupon the folders from oldest to newest, thenpg_verifybackupon the output. - The restored server starts on a spare port and answers queries, and a fingerprint query (like
sql/fingerprint.sqlabove, adapted to your tables) matches the source. - You know your own ratio: the size of an incremental after a normal day, against a full backup.
Where to go next
- Combine this with WAL archiving to restore to a chosen moment: the point-in-time recovery tutorial builds the archive and a scripted restore drill that you can point at the combined folder.
- Read how the same idea works in a small Python program in the changed block tracking tutorial.
- Make time-series tables partitions by month, so closed partitions never change and cost nothing in later incrementals, then repeat the measurement in Step 5.
pg_combinebackuphas options I did not test:--link,--cloneand--copy-file-rangeavoid or speed up copying files, and--tablespace-mappingrelocates tablespaces. Read their caveats in the documentation before using them; for example, with--linkstarting the server on the output folder can change the input folders.- If you also run large schema migrations, remember that a table rewrite produces a new file, which the next incremental backup stores whole.
Sources
- PostgreSQL 18 documentation, Continuous Archiving and Point-in-Time Recovery (section “Making an Incremental Backup”).
- PostgreSQL 18 documentation: pg_basebackup, pg_combinebackup, pg_verifybackup and pg_walsummary.
- PostgreSQL 18 documentation, Write Ahead Log settings (
summarize_wal,wal_summary_keep_time) and System Administration Functions. - PostgreSQL source, basebackup_incremental.c and reconstruct.c on the REL_18_STABLE branch.








No Comment! Be the first one.