TRENDING
A person sweeping a metal detector across a wet sandy beach with a sea fort on the horizon
November 22, 2026
How to Write YARA Rules With YARA-X to Hunt Malicious Python .pth Startup Files and Measure False Positives Before You Trust Them
A peeling black-and-white metal door standing ajar, with a dark empty hall and bright windows beyond
November 22, 2026
AWS’s “Found a Zero-Day on Their Own” Post Never Names the Product; the Advisory Behind Its Link Shows a Server That Ships With No Login, a README Now Saying Keep It Off the Internet, and a Fix Merged October 19 That PyPI and Docker Hub Still Do Not Carry
A hand paging through index cards in an open wooden library card-catalog drawer labeled Agriculture, with a second drawer open below to the right
November 22, 2026
Microsoft Tells Customers Unauthorized Parties Read Admin Email Addresses and Tenant Metadata Through an Entra Connect Health Flaw Introduced July 23, but Gives No Tenant Count or Start Date, and Its Own Docs Send Tenant Security Notices to the Same Kinds of Contacts
Swimmers in separate lanes of a blue swimming pool, seen from above the water, with lane lines running across the picture
November 22, 2026
How to Size a PostgreSQL Connection Pool in Python: Find Where Throughput Stops Growing, Split max_connections Across Workers, and Stop Slow Handlers From Hoarding Connections
Workers in caps lean over a 1913 Ford assembly line, each assembling parts as they pass on the moving line
November 22, 2026
OpenAI’s Performance Factory Turns Regression Response Into a Task Queue for Agents, but Its Numbers Stop at 600 Root-Caused Cases in 1,700 Investigations and a 70% Grade on 100 Suggestions, and in a Toy World Each of Its Reviewer’s Two Counterfactual Questions Can Return a Confident Wrong Answer
22 Nov 2026
SXZ.io SXZ.io
  • Home
Search the Site
Popular Searches:
Technology Amazon AI
Recent Posts
The sun setting over the sea behind a pyramid-shaped stone monument at Cape Soya, the northernmost point of Japan
How to Retire an API Endpoint in FastAPI: Send Deprecation and Sunset Headers, Find the Clients Still Calling It, Schedule Brownouts, and Block an Early Removal With oasdiff
November 22, 2026
Brass sextant in an open wooden case inside a museum display, an instrument for measuring the angle to the horizon
MIT Technology Review’s “Most Misunderstood Graph” Update Reports METR’s Judgment Findings Mostly Fairly, but It Never Says Each Point Is a 50% Success Rate or Gives the New 3.6-Month Doubling Time and 2.1 to 4.9 Month Range, and It Credits OpenAI With a Comparison METR Built
November 22, 2026
Brass telescope on a wooden tripod inside a white observatory dome with the roof slit open to the sky
Rackspace Says Leaked Credentials Let an Intruder Act as Legitimate Users in Some Spot Customer Organizations and Reach Their Kubernetes Secrets, a Platform9 Co-Founder Names a Breached Observability System as the Root Cause, and the Status Page Says Only “System Maintenance Operations”
November 22, 2026
SXZ.io SXZ.io
  • Home

Categories

Articles 310 Posts
News 316 Posts
Learning Hub 282 Posts
Home/Learning Hub/How to Take Incremental PostgreSQL 18 Backups With pg_basebackup, Restore the Chain With pg_combinebackup, and Predict How Big Each One Will Be
Learning Hub

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...

November 19, 2026 31 Min Read
33

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_combinebackup merges 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_verifybackup and pg_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 (python3 instead of python) 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:

  • initdb creates the data directory. --auth=trust lets 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 = on is the setting this tutorial is about. It is off by default, and without it pg_basebackup --incremental refuses to run (you will see that error in Step 8). You can switch it on in a running server with ALTER SYSTEM SET summarize_wal = on; and SELECT pg_reload_conf();; I used exactly that in Step 8 and the server picked it up without a restart.
  • wal_summary_keep_time is 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 = off is 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 = on until the day you want an incremental. It is off by default, and summaries are not created retroactively. Check: SHOW summarize_wal; and SELECT * 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 -full and -incr names above do that). Check: pg_combinebackup --dry-run on 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_verifybackup when it is taken and before it is combined (Step 8.5).
  • Using tar format. Plain format was the only one that worked with pg_combinebackup in 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_time above 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

  1. SHOW summarize_wal; prints on on the server you back up.
  2. Each backup folder verifies: pg_verifybackup backups/full1, and the same for every incremental, right after it is taken.
  3. The chain restores: pg_combinebackup on the folders from oldest to newest, then pg_verifybackup on the output.
  4. The restored server starts on a spare port and answers queries, and a fingerprint query (like sql/fingerprint.sql above, adapted to your tables) matches the source.
  5. 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_combinebackup has options I did not test: --link, --clone and --copy-file-range avoid or speed up copying files, and --tablespace-mapping relocates tablespaces. Read their caveats in the documentation before using them; for example, with --link starting 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.

Tags:

Database BackupsDisaster RecoveryIncremental BackupsPoint-in-Time RecoveryPostgreSQLWAL Summarization

Share

Jagged granite ridgeline of Sawtooth Ridge above a fenced pasture with grazing cattle
Previous Post

OpenAI’s Second Research-Acceleration Report Turns an 11x Rise in Agent Time Into a “Jagged Frontier,” but Its +62% Spend Jump Spans 18 Days From a Baseline Still Holding Part of a Harness Dip, Four of Seven Success Buckets Have Overlapping Bands, and Experiments per Experimenter Rose 1.34x Since May

Close-up of an eight-position DIP switch on a green circuit board with every switch in the off position
Next Post

PHP Fixes 18 Flaws Including Seven Rated Critical, and Its Advisories Tie Three of Them to October’s PHP2Shell Release; Upload Progress Now Defaults to Off While the Shipped php.ini Templates Still Say On, and PHP 8.1 and Older Get No Upstream Patch

No Comment! Be the first one.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Latest
22 Nov
How to Retire an API Endpoint in FastAPI: Send Deprecation and Sunset Headers, Find the Clients Still Calling It, Schedule Brownouts, and Block an Early Removal With oasdiff
22 Nov
MIT Technology Review’s “Most Misunderstood Graph” Update Reports METR’s Judgment Findings Mostly Fairly, but It Never Says Each Point Is a 50% Success Rate or Gives the New 3.6-Month Doubling Time and 2.1 to 4.9 Month Range, and It Credits OpenAI With a Comparison METR Built
Trending
November 22, 2026
How to Retire an API Endpoint in FastAPI: Send Deprecation and Sunset Headers, Find the Clients Still Calling It, Schedule Brownouts, and Block an Early Removal With oasdiff
November 22, 2026
MIT Technology Review’s “Most Misunderstood Graph” Update Reports METR’s Judgment Findings Mostly Fairly, but It Never Says Each Point Is a 50% Success Rate or Gives the New 3.6-Month Doubling Time and 2.1 to 4.9 Month Range, and It Credits OpenAI With a Comparison METR Built
November 22, 2026
Rackspace Says Leaked Credentials Let an Intruder Act as Legitimate Users in Some Spot Customer Organizations and Reach Their Kubernetes Secrets, a Platform9 Co-Founder Names a Breached Observability System as the Root Cause, and the Status Page Says Only “System Maintenance Operations”
November 21, 2026
How to Write YARA Rules With YARA-X to Hunt Malicious Python .pth Startup Files and Measure False Positives Before You Trust Them
November 21, 2026
AWS’s “Found a Zero-Day on Their Own” Post Never Names the Product; the Advisory Behind Its Link Shows a Server That Ships With No Login, a README Now Saying Keep It Off the Internet, and a Fix Merged October 19 That PyPI and Docker Hub Still Do Not Carry
November 21, 2026
Microsoft Tells Customers Unauthorized Parties Read Admin Email Addresses and Tenant Metadata Through an Entra Connect Health Flaw Introduced July 23, but Gives No Tenant Count or Start Date, and Its Own Docs Send Tenant Security Notices to the Same Kinds of Contacts

Related Posts

A laptop wrapped in a chain and padlock, illustrating least-privilege controls for AI agents.
Learning Hub

How to Secure Tool-Using AI Agents Before They Touch Production

June 8, 2026
Colorful sticky notes arranged on an office wall, symbolizing governance checklists and planning.
Learning Hub

AI Governance for Agentic Apps: A Practical Checklist for Builders

June 8, 2026
A technician connects green fiber optic cables at a data center, representing a private production inference endpoint.
Learning Hub

How to Deploy a Fine-Tuned LLM Behind a Private Production Inference Endpoint

June 8, 2026
Narrow aisle behind black supercomputer racks in a data center
Learning Hub

Kubernetes SELinux Volume Labeling: What Cluster Operators Should Audit Before v1.37

June 8, 2026
SXZ.io SXZ.io
  • [email protected]

Categories

Articles
Learning Hub
News

All Rights Reserved by SXZ.io ©2026