Hi hackers, While studying how limit_block is handled during incremental backup, I found a scenario that leads to data inconsistency: a table is truncated by vacuum and then regrown by a bulk insert (COPY). After combining the incremental backup with the full backup, deleted rows reappear. Here is a POC:
POC
===
See the attached script for raw content.
------------------------------POC BEGIN------------------------------
#!/usr/bin/env bash
set -euo pipefail
PG_BIN=${PG_BIN:-/home/postgres/build/bin}
WORKDIR=/tmp/incr-poc
PRIMARY_DIR=$WORKDIR/primary
FULL_BACKUP_DIR=$WORKDIR/full_backup
INCR_BACKUP_DIR=$WORKDIR/incr_backup
STANDBY_DIR=$WORKDIR/standby
PRIMARY_PORT=5544
STANDBY_PORT=5545
export PGUSER=postgres
primary_sql(){ $PG_BIN/psql -X -h localhost -p $PRIMARY_PORT -d postgres
-Atc "$1"; }
standby_sql(){ $PG_BIN/psql -X -h localhost -p $STANDBY_PORT -d postgres
-Atc "$1"; }
rm -rf $WORKDIR
mkdir -p $WORKDIR
trap '$PG_BIN/pg_ctl -D $PRIMARY_DIR -m immediate stop >/dev/null 2>&1 ||
true
$PG_BIN/pg_ctl -D $STANDBY_DIR -m immediate stop >/dev/null 2>&1 ||
true' EXIT
# step 1: init and start the primary with WAL summarization enabled
$PG_BIN/initdb -D $PRIMARY_DIR -U postgres >/dev/null
cat >> $PRIMARY_DIR/postgresql.conf <<EOF
port = $PRIMARY_PORT
summarize_wal = on
autovacuum = off
max_wal_senders = 10
EOF
$PG_BIN/pg_ctl -D $PRIMARY_DIR -l $PRIMARY_DIR.log -w start >/dev/null
# step 2: create a 40-block table, one row per block (~4.2kB payload forces
# 1 tuple/page)
primary_sql "CREATE TABLE t(id int, data text STORAGE PLAIN)
WITH (autovacuum_enabled=false, fillfactor=100)" >/dev/null
primary_sql "INSERT INTO t SELECT i, repeat('x', 4200) FROM
generate_series(1,40) i" >/dev/null
primary_sql "VACUUM t" >/dev/null
# step 3: take the full backup
$PG_BIN/pg_basebackup -h localhost -p $PRIMARY_PORT \
-D $FULL_BACKUP_DIR -c fast -X stream -R >/dev/null
# step 4: vacuum truncation to 1 block -> WAL summary records limit_block=1
primary_sql "DELETE FROM t WHERE id > 1" >/dev/null
primary_sql "VACUUM (TRUNCATE) t" >/dev/null
# step 5: regrow via COPY: bulk extension overshoots; file grows to ~33
blocks of
# which the tail blocks are all-zero and never WAL-logged
python3 -c "
for i in range(1000,1020): print(i, 'y'*4200, sep='\t')
" | $PG_BIN/psql -X -h localhost -p $PRIMARY_PORT -d postgres -c "COPY t
FROM stdin" >/dev/null
echo "primary now : $(primary_sql 'SELECT count(*) FROM t') rows,
$(primary_sql "SELECT pg_relation_size('t')/8192") blocks"
# step 6: let the WAL summarizer catch up
target_lsn=$(primary_sql "SELECT pg_current_wal_lsn()")
primary_sql "CHECKPOINT" >/dev/null
for i in $(seq 1 60); do
summarized=$(primary_sql "SELECT summarized_lsn >= '$target_lsn'::pg_lsn
FROM pg_get_wal_summarizer_state()")
[ "$summarized" = t ] && break
sleep 1
done
# step 7: take the incremental backup, combine it with the full backup, and
# start the result as a standby
$PG_BIN/pg_basebackup -h localhost -p $PRIMARY_PORT \
-D $INCR_BACKUP_DIR -c fast -X stream -R \
--incremental $FULL_BACKUP_DIR/backup_manifest >/dev/null
# (stderr hidden: pg_combinebackup warns that the manifest has no entry
# for the -R-generated standby.signal; harmless)
$PG_BIN/pg_combinebackup $FULL_BACKUP_DIR $INCR_BACKUP_DIR -o $STANDBY_DIR
2>/dev/null
$PG_BIN/pg_ctl -D $STANDBY_DIR -l $STANDBY_DIR.log -w -o "-p $STANDBY_PORT"
start >/dev/null
# step 8: compare primary and standby
echo "standby in recovery : $(standby_sql 'SELECT pg_is_in_recovery()')"
echo "walreceiver status : $(standby_sql "SELECT COALESCE((SELECT
status FROM pg_stat_wal_receiver),'none')")"
echo "primary sees standby : $(primary_sql "SELECT
COALESCE(string_agg(state,','),'none') FROM pg_stat_replication")"
echo "primary rows : $(primary_sql 'SELECT count(*) FROM t')"
echo "standby rows : $(standby_sql 'SELECT count(*) FROM t')"
echo "standby ids : $(standby_sql "SELECT
string_agg(id::text,',' ORDER BY id) FROM t")"
# step 9: prove the standby is really live: a new insert on the primary
reaches it
primary_sql "INSERT INTO t SELECT 3000, repeat('z', 4200);" >/dev/null
for i in $(seq 1 30); do
[ "$(standby_sql 'SELECT count(*) FROM t WHERE id = 3000')" = "1" ] &&
break
sleep 1
done
echo "standby saw new insert : $(standby_sql 'SELECT count(*) FROM t WHERE
id = 3000') (1 = streaming works)"
echo "primary rows after : $(primary_sql 'SELECT count(*) FROM t')"
echo "standby rows after : $(standby_sql 'SELECT count(*) FROM t')"
------------------------------POC END------------------------------
Output
primary now : 21 rows, 33 blocks
standby in recovery : t
walreceiver status : streaming
primary sees standby : streaming
primary rows : 21
standby rows : 33
standby ids : 1,22,23,...,33,1000,1001,...,1019
standby saw new insert : 1 (1 = streaming works)
primary rows after : 22
standby rows after : 34
Analyze
=======
Here is a view for blocks layout where only "id" is shown here.
(1) In full backup
[1, 2, 3, 4, .... , 20, 21, 22, .... , 39 , 40]
(2) After truncate
[1]
limit_block = 1
(3) After bulk insert
[1, 1000, 1001, 1002, ... , 1019 , NULL, NULL, NULL, ... , NULL]
33 blocks totally
(4) In the incremental Backup, the INCREMENTAL.relfilenode
blocks changed = [1000, 1001, .... , 1019]
truncation_block_length = 33
(5) combine backup
[1, 1000, 1001, .... , 1018 ,1019, 22, 23 , ...39 , 40]
|___ From incremental ___| |___ From full ____|
Fix
===
My proposed fix is admittedly brute-force: if a truncation happened anywhere
within a segment, we cannot tell how many of the blocks that were later
added
back are all-zero and never WAL-logged, so the safest option is to back up
that entire segment fully.
So: instead of comparing limit_block with segno * RELSEG_SIZE, we can
compare it
with (segno + 1) * RELSEG_SIZE
- if (limit_block <= segno * RELSEG_SIZE)
+ if (BlockNumberIsValid(limit_block) &&
+ limit_block / RELSEG_SIZE <= segno)
+ return BACK_UP_FILE_FULLY;
Regards,
Boyu Yang
0001-Fix-incremental-backup-of-relations-truncated-and-re.patch
Description: Binary data
#!/usr/bin/env bash
set -euo pipefail
PG_BIN=${PG_BIN:-/home/postgres/build/bin}
WORKDIR=/tmp/incr-poc
PRIMARY_DIR=$WORKDIR/primary
FULL_BACKUP_DIR=$WORKDIR/full_backup
INCR_BACKUP_DIR=$WORKDIR/incr_backup
STANDBY_DIR=$WORKDIR/standby
PRIMARY_PORT=5544
STANDBY_PORT=5545
export PGUSER=postgres
primary_sql(){ $PG_BIN/psql -X -h localhost -p $PRIMARY_PORT -d postgres -Atc "$1"; }
standby_sql(){ $PG_BIN/psql -X -h localhost -p $STANDBY_PORT -d postgres -Atc "$1"; }
rm -rf $WORKDIR
mkdir -p $WORKDIR
trap '$PG_BIN/pg_ctl -D $PRIMARY_DIR -m immediate stop >/dev/null 2>&1 || true
$PG_BIN/pg_ctl -D $STANDBY_DIR -m immediate stop >/dev/null 2>&1 || true' EXIT
# step 1: init and start the primary with WAL summarization enabled
$PG_BIN/initdb -D $PRIMARY_DIR -U postgres >/dev/null
cat >> $PRIMARY_DIR/postgresql.conf <<EOF
port = $PRIMARY_PORT
summarize_wal = on
autovacuum = off
max_wal_senders = 10
EOF
$PG_BIN/pg_ctl -D $PRIMARY_DIR -l $PRIMARY_DIR.log -w start >/dev/null
# step 2: create a 40-block table, one row per block (~4.2kB payload forces
# 1 tuple/page)
primary_sql "CREATE TABLE t(id int, data text STORAGE PLAIN)
WITH (autovacuum_enabled=false, fillfactor=100)" >/dev/null
primary_sql "INSERT INTO t SELECT i, repeat('x', 4200) FROM generate_series(1,40) i" >/dev/null
primary_sql "VACUUM t" >/dev/null
# step 3: take the full backup
$PG_BIN/pg_basebackup -h localhost -p $PRIMARY_PORT \
-D $FULL_BACKUP_DIR -c fast -X stream -R >/dev/null
# step 4: vacuum truncation to 1 block -> WAL summary records limit_block=1
primary_sql "DELETE FROM t WHERE id > 1" >/dev/null
primary_sql "VACUUM (TRUNCATE) t" >/dev/null
# step 5: regrow via COPY: bulk extension overshoots; file grows to ~33 blocks of
# which the tail blocks are all-zero and never WAL-logged
python3 -c "
for i in range(1000,1020): print(i, 'y'*4200, sep='\t')
" | $PG_BIN/psql -X -h localhost -p $PRIMARY_PORT -d postgres -c "COPY t FROM stdin" >/dev/null
echo "primary now : $(primary_sql 'SELECT count(*) FROM t') rows, $(primary_sql "SELECT pg_relation_size('t')/8192") blocks"
# step 6: let the WAL summarizer catch up
target_lsn=$(primary_sql "SELECT pg_current_wal_lsn()")
primary_sql "CHECKPOINT" >/dev/null
for i in $(seq 1 60); do
summarized=$(primary_sql "SELECT summarized_lsn >= '$target_lsn'::pg_lsn FROM pg_get_wal_summarizer_state()")
[ "$summarized" = t ] && break
sleep 1
done
# step 7: take the incremental backup, combine it with the full backup, and
# start the result as a standby
$PG_BIN/pg_basebackup -h localhost -p $PRIMARY_PORT \
-D $INCR_BACKUP_DIR -c fast -X stream -R \
--incremental $FULL_BACKUP_DIR/backup_manifest >/dev/null
# (stderr hidden: pg_combinebackup warns that the manifest has no entry
# for the -R-generated standby.signal; harmless)
$PG_BIN/pg_combinebackup $FULL_BACKUP_DIR $INCR_BACKUP_DIR -o $STANDBY_DIR 2>/dev/null
$PG_BIN/pg_ctl -D $STANDBY_DIR -l $STANDBY_DIR.log -w -o "-p $STANDBY_PORT" start >/dev/null
# step 8: compare primary and standby
echo "standby in recovery : $(standby_sql 'SELECT pg_is_in_recovery()')"
echo "walreceiver status : $(standby_sql "SELECT COALESCE((SELECT status FROM pg_stat_wal_receiver),'none')")"
echo "primary sees standby : $(primary_sql "SELECT COALESCE(string_agg(state,','),'none') FROM pg_stat_replication")"
echo "primary rows : $(primary_sql 'SELECT count(*) FROM t')"
echo "standby rows : $(standby_sql 'SELECT count(*) FROM t')"
echo "standby ids : $(standby_sql "SELECT string_agg(id::text,',' ORDER BY id) FROM t")"
# step 9: prove the standby is really live: a new insert on the primary reaches it
primary_sql "INSERT INTO t SELECT 3000, repeat('z', 4200);" >/dev/null
for i in $(seq 1 30); do
[ "$(standby_sql 'SELECT count(*) FROM t WHERE id = 3000')" = "1" ] && break
sleep 1
done
echo "standby saw new insert : $(standby_sql 'SELECT count(*) FROM t WHERE id = 3000') (1 = streaming works)"
echo "primary rows after : $(primary_sql 'SELECT count(*) FROM t')"
echo "standby rows after : $(standby_sql 'SELECT count(*) FROM t')"
