Hi, With data_checksums and wal_log_hints off, a cluster restored from an incremental backup can keep stale VM bits after VACUUM truncates a table. When the table grows again, index-only scans return deleted and rolled back rows.
The attached script shows it. One VACUUM empties the end of a table but skips the truncation on a lock conflict. After the full backup, a later VACUUM truncates it. On 17 and 18 the results stay wrong even after another VACUUM. On 19 and master, VACUUM fixes them. visibilitymap_prepare_truncate() clears bits in the last VM page, but writes that page to WAL only if XLogHintBitIsNeeded(). So the WAL summary never sees the change, and the incremental backup skips the page. 0001 always writes the page to WAL, as Andres suggested in [1]. Clusters with checksums on already do this. 0002 adds a test that fails without 0001. 0001 and 0002 apply to master, 19 and 18. 17 needs the attached nocfbot file. The standby PANIC fix in [2] touches the same code path but does not cover this, so I am reporting it separately. Command to run attached sh sh incr-vm-truncate.sh /pgxxx/bin [1] https://postgr.es/m/t7xd6gjxdxpzdzr2uamfbyi5rlkdsikiywmtmgwrwcsr77xyw7@i3qhbzy5rpzx [2] https://postgr.es/m/cal4mqlap562c1rcgg2dqx6tbsdok6vovalfjwfg9e6k4uwv...@mail.gmail.com Thanks for taking a look, Shihao
v1-0001-Always-WAL-log-the-VM-page-changed-by-truncation.patch
Description: Binary data
v1-0002-Test-incremental-backup-after-a-visibility-map-tr.patch
Description: Binary data
nocfbot-v1-REL_17_STABLE.patch
Description: Binary data
#!/bin/sh
# Incremental backup misses VM bits cleared by VACUUM truncation.
#
# usage: sh incr-vm-truncate.sh BINDIR
# BINDIR is the bin directory of a PostgreSQL 17 or later install, for example
# sh incr-vm-truncate.sh /usr/local/pgsql/bin
# It runs two throwaway servers on ports 54871 and 54872 in a temp directory.
export LC_ALL=C
BIN=$1
DIR=$(mktemp -d /tmp/ivt.XXXX)
PORT=54871
trap '"$BIN/pg_ctl" -D $DIR/primary stop >/dev/null; "$BIN/pg_ctl" -D $DIR/restored stop >/dev/null; rm -rf $DIR' EXIT
sql() { "$BIN/psql" -X -q -At -h $DIR -p $1 -d postgres -c "$2"; }
# 18 and later enable data checksums by default, so turn them off there
"$BIN/initdb" -D $DIR/primary -A trust $("$BIN/initdb" --help | grep -o -- --no-data-checksums) >/dev/null
printf "port=$PORT\nlisten_addresses=''\nunix_socket_directories='$DIR'\nsummarize_wal=on\nautovacuum=off\n" >> $DIR/primary/postgresql.conf
"$BIN/pg_ctl" -D $DIR/primary -l $DIR/primary.log -w start >/dev/null
# Empty the second half of the table, VACUUM it without truncating, full backup
sql $PORT "create table t (id int primary key)"
sql $PORT "insert into t select generate_series(1, 200000)"
sql $PORT "delete from t where id > 100000"
sql $PORT "vacuum (truncate false) t"
"$BIN/pg_basebackup" -h $DIR -p $PORT -D $DIR/full -c fast
# Now VACUUM truncates the table, then take an incremental backup and restore it
sql $PORT "vacuum t"
"$BIN/pg_basebackup" -h $DIR -p $PORT -D $DIR/incr -c fast --incremental=$DIR/full/backup_manifest
"$BIN/pg_combinebackup" -o $DIR/restored $DIR/full $DIR/incr
echo "port=$((PORT + 1))" >> $DIR/restored/postgresql.conf
"$BIN/pg_ctl" -D $DIR/restored -l $DIR/restored.log -w start >/dev/null
# On both servers, insert rows and roll back, then count them with an index-only scan
for server in "primary $PORT" "restored $((PORT + 1))"; do
set -- $server
sql $2 "begin; insert into t select generate_series(300001, 310000); rollback"
sql $2 "set enable_seqscan = off; set enable_bitmapscan = off;
select '$1: rolled back rows seen by index-only scan (expect 0): ' || count(*) from t where id > 300000"
done
