pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order
5+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2026-11-01 d903620acf 2bae29d7e4 9e20db542b 9580e29da9  Fix incremental backup data loss due to WAL summary merge order
* BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order
@ 2026-10-20 03:39  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: PG Bug reporting form @ 2026-10-20 03:39 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: kehan5800@gmail.com

The following bug has been logged on the website:

Bug reference:      19774
Logged by:          Ke
Email address:      kehan5800@gmail.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 22.04 (Linux 5.15, x86_64)
Description:        

An incremental backup taken after a relation was truncated by VACUUM and
then
extended again can omit the re-extended blocks, although the WAL summaries
list
them. pg_combinebackup then takes those blocks from the prior backup. In the
script below, every live row of the table is lost in the restored cluster
and
the 20000 rows deleted before the incremental backup come back.
pg_verifybackup
reports both input backups as valid.

Reproduced on master (45fc51fcbf) and 18.6, built from source with
./configure --without-icu.

The work directory must be on tmpfs, where readdir() returns the newest file
first. On ext4 the script also reproduced once the summaries directory was
large enough to be read in hash order (I created 150 extra summary files
first).

---- repro.sh: PGBIN=/usr/local/pgsql/bin sh repro.sh /dev/shm/inc ----
#!/bin/sh
set -e
W=$1; P=5440
rm -rf "$W"; mkdir -p "$W"
"$PGBIN/initdb" -D "$W/src" -A trust --no-locale >/dev/null
cat >> "$W/src/postgresql.conf" <<EOC
port = $P
unix_socket_directories = '$W'
listen_addresses = ''
summarize_wal = on
autovacuum = off
EOC
"$PGBIN/pg_ctl" -D "$W/src" -l "$W/src.log" -w start >/dev/null
q() { "$PGBIN/psql" -X -h "$W" -p "$1" -d postgres -Atq -c "$2"; }
sw() { q $P "SELECT pg_switch_wal()" >/dev/null; q $P "CHECKPOINT"; sleep 2;
}  # end the current WAL summary
q $P "CREATE TABLE t (id int PRIMARY KEY, pad text)"
q $P "INSERT INTO t SELECT g, md5(g::text) FROM generate_series(1, 30000) g"
q $P "INSERT INTO t SELECT g, repeat('w', 3000) FROM
generate_series(1000001, 1020000) g"
sw
"$PGBIN/pg_basebackup" -h "$W" -p $P -D "$W/full" -c fast
q $P "DELETE FROM t WHERE id > 1000000"
q $P "VACUUM t"                        # truncates the heap
sw
q $P "UPDATE t SET pad = pad || 'u'"   # the new row versions extend the
heap again
sw
"$PGBIN/pg_basebackup" -h "$W" -p $P -D "$W/incr" -c fast --incremental
"$W/full/backup_manifest"
REL=$(q $P "SELECT pg_relation_filepath('t')")
echo "source:   $(q $P "SELECT count(*), max(id), pg_relation_size('t') /
8192 FROM t")"
"$PGBIN/pg_ctl" -D "$W/src" -w stop >/dev/null
echo "summaries for t in the order the server reads them (ls -U):"
for f in $(ls -U "$W/src/pg_wal/summaries"); do
  s=$("$PGBIN/pg_walsummary" "$W/src/pg_wal/summaries/$f" | grep "REL
${REL##*/}, FORK main" | sed 's/.*FORK main: //' | tr '\n' ' ')
  [ -n "$s" ] && echo "  $f: $s"
done
set -- $(od -A n -t u4 -N 12 "$W/incr/$(dirname $REL)/INCREMENTAL.$(basename
$REL)")
echo "incr/$(dirname $REL)/INCREMENTAL.$(basename $REL): $2 blocks,
truncation_block_length $3"
"$PGBIN/pg_combinebackup" -o "$W/restored" "$W/full" "$W/incr"
echo "port = $((P + 1))" >> "$W/restored/postgresql.conf"
"$PGBIN/pg_ctl" -D "$W/restored" -l "$W/restored.log" -w start >/dev/null
echo "restored: $(q $((P + 1)) "SELECT count(*), max(id),
pg_relation_size('t') / 8192 FROM t")"
"$PGBIN/pg_ctl" -D "$W/restored" -w stop >/dev/null
---- end ----

Output on master (18.6 prints the same apart from the name of the last
summary file):

source:   30000|30000|500
summaries for t in the order the server reads them (ls -U):
  0000000100000000050000280000000006000028.summary: blocks 0..499
  0000000100000000030000280000000005000028.summary: limit 250 blocks 0..249
  0000000100000000016F4FE80000000002000028.summary: limit 0 blocks 0..436
incr/base/5/INCREMENTAL.16384: 250 blocks, truncation_block_length 500
restored: 20000|1020000|500

Expected "restored: 30000|30000|500". The restored table has none of the
30000 live rows, and it has the 20000 deleted rows (ids 1000001..1020000).

The summaries are correct. The one ending at 0/5000028 holds the truncation
(limit 250) and the one ending at 0/6000028 holds the re-extension (blocks
0..499). PrepareForIncrementalBackup() merges the summaries in the order
GetWalSummaries() returns them, and that is readdir() order:

https://github.com/postgres/postgres/blob/45fc51fcbf4534ea2f6f85e36f8155dbd5ba9851/src/backend/backu...
https://github.com/postgres/postgres/blob/45fc51fcbf4534ea2f6f85e36f8155dbd5ba9851/src/backend/backu...

Here the later summary is merged first, so blocks 0..499 are recorded. The
earlier summary's limit 250 then goes through
BlockRefTableEntrySetLimitBlock(),
which drops every recorded block at or above the new limit:

https://github.com/postgres/postgres/blob/45fc51fcbf4534ea2f6f85e36f8155dbd5ba9851/src/common/blkref...

As a result, the INCREMENTAL file holds blocks 0..249 only, while its
truncation_block_length is the file size (500). pg_combinebackup takes
blocks
250..436 from the full backup, which still holds the deleted wide rows
there,
and zero-fills 437..499. The new versions of the 30000 rows were in blocks
250..499, so they are lost. If the summaries are merged in LSN order, all
500
blocks are marked modified and the file is sent whole.

WalSummariesAreComplete() already sorts its own copy of the list by start
LSN
(walsummary.c line 151-153), but the merge loop uses the unsorted list.
Sorting
the list before the merge fixes this. With the patch below, the same script
on
master restores 30000|30000|500:

inline patch 1: src/backend/backup/basebackup_incremental.c (+0/-0 lines) download inline-1.patch--- a/src/backend/backup/basebackup_incremental.c +++ b/src/backend/backup/basebackup_incremental.c @@ -142,6 +142,7 @@ static void
manifest_process_wal_range(JsonManifestParseContext *context, pg_noreturn static void manifest_report_error(JsonManifestParseContext *context, const char *fmt, ...) pg_attribute_printf(2, 3); +static int CompareWalSummaryStartLSN(const ListCell *a, const ListCell *b); static int compare_block_numbers(const void *a, const void *b); /* @@ -568,6 +569,13 @@ PrepareForIncrementalBackup(IncrementalBackupInfo *ib, * memory usage. */ ib->brtab = CreateEmptyBlockRefTable(); + + /* + * Merge the summaries in LSN order: applying a limit block discards the + * blocks already recorded at or above it, so a summary's limit must be + * applied before the blocks of later summaries are added. + */ + list_sort(required_wslist, CompareWalSummaryStartLSN); foreach(lc, required_wslist) { WalSummaryFile *ws = lfirst(lc); @@ -614,6 +622,18 @@ PrepareForIncrementalBackup(IncrementalBackupInfo *ib, MemoryContextSwitchTo(oldcontext); } +/* + * list_sort comparator: order WalSummaryFile objects by start LSN. + */ +static int +CompareWalSummaryStartLSN(const ListCell *a, const ListCell *b) +{ + WalSummaryFile *ws1 = lfirst(a); + WalSummaryFile *ws2 = lfirst(b); + + return pg_cmp_u64(ws1->start_lsn, ws2->start_lsn); +} + /* * Get the pathname that should be used when a file is sent incrementally. * Related, not the same cause: "Incremental backup inconsistency after vacuum truncation + COPY regrowth" (pgsql-hackers, 2026-07-29, https://www.postgresql.org/message-id/CAPnDWbagY0Y91qZmJkuJX4+byVJ-dJysnx4QmW23wgR_AvECVg@mail.gmail...) has the same symptom. There, the re-extended blocks are zero pages that were never WAL-logged, so no summary lists them. Here, every re-extended block is WAL-logged and listed in a summary, and the server drops the blocks itself while merging. If I read the diff in that thread correctly, its proposed change (send any segment with a limit block in full) would avoid this case for new backups, but the merge order would still be wrong. ^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order
@ 2026-10-21 08:17  Fujii Masao <masao.fujii@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 5+ messages in thread

From: Fujii Masao @ 2026-10-21 08:17 UTC (permalink / raw)
  To: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org

On Tue, Oct 20, 2026 at 8:56 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
> Sorting
> the list before the merge fixes this.

Thanks for the report and patch!

Attached is an updated patch that retains your fix, makes minor naming and
comment changes, and adds a TAP test. The test truncates a table before
promoting a standby, re-extends it afterward, and verifies the restored data.
Using different timelines makes the test independent of directory
enumeration order.

Seems this should be backpatched to v17.

Regards,

-- 
Fujii Masao

Attachments:

v1-0001-Fix-incremental-backup-data-loss-due-to-WAL-summa.patch (application/octet-stream, 7.0K) download From df764ea49f4f5d6fe399a72e3f316e201f9526ac Mon Sep 17 00:00:00 2001 From: Fujii Masao <fujii@postgresql.org> Date: Wed, 21 Oct 2026 11:31:37 +0900 Subject: [PATCH v1] Fix incremental backup data loss due to WAL summary merge order An incremental backup taken after a relation is truncated and extended again can omit modified blocks. Combining it with an earlier backup can then restore stale data, causing live rows to disappear and deleted rows to reappear. WAL summaries were merged without ensuring LSN order. Applying a limit block from an older summary after processing a newer summary could discard references to blocks modified later, causing those blocks to be omitted from the backup. Sort the required summaries by ascending start LSN before merging them, preserving references to later block modifications so that the required blocks are included in the backup. Backpatch to 17, where incremental backup was introduced. Reported-by: Ke <kehan5800@gmail.com> Author: Ke <kehan5800@gmail.com> Author: Fujii Masao <masao.fujii@gmail.com> Discussion: https://www.postgresql.org/message-id/19774-8a1cbfd1774fcd0c%40postgresql.org Backpatch-through: 17 --- src/backend/backup/basebackup_incremental.c | 19 ++++ src/bin/pg_combinebackup/meson.build | 1 + .../pg_combinebackup/t/013_summary_order.pl | 94 +++++++++++++++++++ 3 files changed, 114 insertions(+) create mode 100644 src/bin/pg_combinebackup/t/013_summary_order.pl diff --git a/src/backend/backup/basebackup_incremental.c b/src/backend/backup/basebackup_incremental.c index 364b3f87aba..c899d9a1328 100644 --- a/src/backend/backup/basebackup_incremental.c +++ b/src/backend/backup/basebackup_incremental.c @@ -142,6 +142,7 @@ static void manifest_process_wal_range(JsonManifestParseContext *context, pg_noreturn static void manifest_report_error(JsonManifestParseContext *context, const char *fmt, ...) pg_attribute_printf(2, 3); +static int compare_wal_summary_start_lsn(const ListCell *a, const ListCell *b); static int compare_block_numbers(const void *a, const void *b); /* @@ -560,6 +561,12 @@ PrepareForIncrementalBackup(IncrementalBackupInfo *ib, break; } + /* + * Merge summaries in ascending start LSN order so that applying a + * limit block does not discard block references from later summaries. + */ + list_sort(required_wslist, compare_wal_summary_start_lsn); + /* * Read all of the required block reference table files and merge all of * the data into a single in-memory block reference table. @@ -1045,6 +1052,18 @@ manifest_report_error(JsonManifestParseContext *context, const char *fmt, ...) errmsg_internal("%s", errbuf.data)); } +/* + * List comparator for WAL summary files, ordered by ascending start LSN. + */ +static int +compare_wal_summary_start_lsn(const ListCell *a, const ListCell *b) +{ + WalSummaryFile *ws1 = lfirst(a); + WalSummaryFile *ws2 = lfirst(b); + + return pg_cmp_u64(ws1->start_lsn, ws2->start_lsn); +} + /* * Quicksort comparator for block numbers. */ diff --git a/src/bin/pg_combinebackup/meson.build b/src/bin/pg_combinebackup/meson.build index ba1c8cfa3d0..10702f36a5d 100644 --- a/src/bin/pg_combinebackup/meson.build +++ b/src/bin/pg_combinebackup/meson.build @@ -40,6 +40,7 @@ tests += { 't/010_hardlink.pl', 't/011_ib_truncation.pl', 't/012_vm_consistency.pl', + 't/013_summary_order.pl', ], } } diff --git a/src/bin/pg_combinebackup/t/013_summary_order.pl b/src/bin/pg_combinebackup/t/013_summary_order.pl new file mode 100644 index 00000000000..426247f4d1a --- /dev/null +++ b/src/bin/pg_combinebackup/t/013_summary_order.pl @@ -0,0 +1,94 @@ +# Copyright (c) 2026, PostgreSQL Global Development Group +# +# Check that an incremental backup preserves rows updated on a new +# timeline after VACUUM truncates the table on the previous timeline. +# Summaries are collected from newest to oldest timeline, but must be +# merged in ascending start LSN order to preserve references to blocks +# added after truncation. + +use strict; +use warnings FATAL => 'all'; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +my $mode = $ENV{PG_TEST_PG_COMBINEBACKUP_MODE} || '--copy'; + +my $primary = PostgreSQL::Test::Cluster->new('primary'); +$primary->init(allows_streaming => 1); +$primary->append_conf('postgresql.conf', <<EOF); +summarize_wal = on +autovacuum = off +EOF +$primary->start; + +# Keep one row per page by storing a half-page payload without compression +# or out-of-line storage. Four pages fit in one segment even with the +# six-block RELSEG_SIZE used by CI. +my $block_size = $primary->safe_psql('postgres', 'SHOW block_size'); +my $pad_size = $block_size / 2; +$primary->safe_psql('postgres', <<EOF); +CREATE TABLE t (id int, pad text STORAGE PLAIN); +INSERT INTO t SELECT g, repeat('x', $pad_size) FROM generate_series(1, 4) g; +EOF + +# Set hint bits before taking the full backup. +$primary->safe_psql('postgres', 'VACUUM t'); + +my $size_sql = "SELECT pg_relation_size('t') / $block_size"; +is($primary->safe_psql('postgres', $size_sql), '4', + 'initial heap has four blocks'); + +# The backup's checkpoint excludes table creation from the WAL summaries +# needed later, avoiding a zero limit block that would force a full copy. +$primary->backup('full'); +my $full_path = $primary->backup_dir . '/full'; + +my $standby = PostgreSQL::Test::Cluster->new('standby'); +$standby->init_from_backup($primary, 'full', has_streaming => 1); +$standby->start; + +# Truncate the table on the old timeline before promoting the standby. +$primary->safe_psql('postgres', 'DELETE FROM t WHERE id > 2'); +$primary->safe_psql('postgres', 'VACUUM t'); +is($primary->safe_psql('postgres', $size_sql), '2', + 'VACUUM truncates the heap to two blocks'); +$primary->wait_for_replay_catchup($standby); +$primary->stop; +$standby->promote; + +# Updating the remaining rows requires new pages, re-extending the heap. +$standby->safe_psql('postgres', "UPDATE t SET pad = pad || 'u'"); +is($standby->safe_psql('postgres', $size_sql), '4', + 'UPDATE re-extends the heap to four blocks'); + +# Check IDs and values: the deleted rows could reappear without changing +# the row count. +my $contents_sql = + "SELECT id, pad = (repeat('x', $pad_size) || 'u') FROM t ORDER BY id"; +my $expected = "1|t\n2|t"; +is($standby->safe_psql('postgres', $contents_sql), + $expected, 'source contains the two updated rows'); + +# Store both backups under $primary so init_from_backup() can combine them. +$standby->command_ok( + [ + 'pg_basebackup', '--no-sync', + '--pgdata' => $primary->backup_dir . '/incr', + '--checkpoint' => 'fast', + '--incremental' => "$full_path/backup_manifest", + ], + 'incremental backup after truncation and re-extension'); +$standby->stop; + +my $restored = PostgreSQL::Test::Cluster->new('restored'); +$restored->init_from_backup( + $primary, 'incr', + combine_with_prior => ['full'], + combine_mode => $mode); +$restored->start; +is($restored->safe_psql('postgres', $contents_sql), + $expected, 'restored rows match the updated source rows'); +$restored->stop; + +done_testing(); -- 2.56.0
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order
@ 2026-10-21 10:03  Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  parent: Fujii Masao <masao.fujii@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Ayush Tiwari @ 2026-10-21 10:03 UTC (permalink / raw)
  To: Fujii Masao <masao.fujii@gmail.com>; +Cc: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org

Hi,

On Wed, 21 Oct 2026 at 13:47, Fujii Masao <masao.fujii@gmail.com> wrote:
>
> On Tue, Oct 20, 2026 at 8:56 PM PG Bug reporting form
> <noreply@postgresql.org> wrote:
> > Sorting
> > the list before the merge fixes this.
>
> Thanks for the report and patch!
>
> Attached is an updated patch that retains your fix, makes minor naming and
> comment changes, and adds a TAP test. The test truncates a table before
> promoting a standby, re-extends it afterward, and verifies the restored data.
> Using different timelines makes the test independent of directory
> enumeration order.

Thanks for the updated patch, it was clear to read.

I think there might be one more corner case here.

IIUC, a standby can summarize WAL ahead of replay. If it gets promoted
right at the start of one of those summaries, FilterWalSummaries()
seems to keep that old-timeline file, and it can tie with the first
new-timeline summary in the sort..

Would it make sense for FilterWalSummaries() to use the same >= / <=
checks as GetWalSummaries()?

The patch looks good to me though.

Regards,
Ayush






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order
@ 2026-10-22 08:48  Fujii Masao <masao.fujii@gmail.com>
  parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Fujii Masao @ 2026-10-22 08:48 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org

On Wed, Oct 21, 2026 at 7:04 PM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:
> Would it make sense for FilterWalSummaries() to use the same >= / <=
> checks as GetWalSummaries()?

I haven't looked into this in detail, but your point sounds valid.
Since this seems to be a separate issue, I think it should be addressed
in a separate patch.

> The patch looks good to me though.

Thanks for the review! Barring any objections, I will commit v1-0001 patch.

Regards,

-- 
Fujii Masao






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order
@ 2026-11-01 15:31  Fujii Masao <masao.fujii@gmail.com>
  parent: Fujii Masao <masao.fujii@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Fujii Masao @ 2026-11-01 15:31 UTC (permalink / raw)
  To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: kehan5800@gmail.com; pgsql-bugs@lists.postgresql.org

On Thu, Oct 22, 2026 at 5:48 PM Fujii Masao <masao.fujii@gmail.com> wrote:
> Thanks for the review! Barring any objections, I will commit v1-0001 patch.

I've pushed the patch. Thanks!

Regards,

-- 
Fujii Masao






^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2026-11-01 15:31 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-20 03:39 BUG #19774: Incremental backup loses re-extended blocks when WAL summaries are merged out of LSN order PG Bug reporting form <noreply@postgresql.org>
2026-10-21 08:17 ` Fujii Masao <masao.fujii@gmail.com>
2026-10-21 10:03   ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-22 08:48     ` Fujii Masao <masao.fujii@gmail.com>
2026-11-01 15:31       ` Fujii Masao <masao.fujii@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox