Hi, While doing some additional testing of (incremental) backups, I ran into a couple regular failures. After pulling my hair for a couple days, I realized the issue seems to affect regular backups, and incremental backups (which I've been trying to test) are likely innocent.
I'm using a simple (and admittedly not very pretty) bash scripts that
takes and verified backups, concurrently with this workload:
1) initialize a cluster
2) initialize pgbench in database 'db'
3) run short pgbench on 'db'
4) maybe do vacuum [full] on 'db'
5) drop a database 'db_copy' if it exists
6) create a database 'db_copy' by copying 'db' using one of the
available strategies (file_copy, wal_log)
7) run short pgbench on 'db_copy'
8) maybe do vacuum [full] on 'db_copy'
And concurrently with this, it takes a basebackup, starts a cluster on
it (on a different port, ofc), and does various checks on that:
a) verify checksums using pg_checksums (cluster has them enabled)
b) run amcheck on tables/indexes on both databases
c) SQL check (we expect all tables to be 'consistent' as if we did a
PITR - in particular sum(balance) is expected to be the same value on
all pgbench tables) on both databases
I believe those are reasonable expectations - that we get a database
with valid checksums, with non-broken tables/indexes, and that the
database looks as a snapshot taken at a single instant.
Unfortunately it doesn't take long for the tests to start failing with
various strange symptoms on the db_copy database (I'm yet to see an
issue on the 'db' database):
i) amcheck fails with 'heap tuple lacks matching index tuple'
ERROR: heap tuple (116195,22) from table "pgbench_accounts" lacks
matching index tuple within index "pgbench_accounts_pkey"
HINT: Retrying verification using the function
bt_index_parent_check() might provide a more specific error.
I've seen this with other tables/indexes too, e.g. system catalogs
pg_statitics or toast tables, but 'accounts' is most common.
ii) amcheck fails with 'could not open file'
ERROR: could not open file "base/18121/18137": No such file or
directory
LINE 9: lateral verify_heapam(relation => c.oid, on_error_stop =>
f...
^
ERROR: could not open file "base/18121/18137": No such file or
directory
iii) failures in the SQL check, with different tables have different
balance sums
SQL check fails (db_copy) (account 156142 branches 136132 tellers
136132 history -42826)
Sometimes this is preceded by amcheck issue, but not always.
I guess this is not the behavior we expect :-(
I've reproduced all of this on PG16 - I haven't tried with older
releases, but I have no reason to assume pre-16 releases are not affected.
With incremental backups I've observed a couple more symptoms, but those
are most likely just fallout of this - not realizing the initial state
is a bit wrong, and making it worse by applying the increments.
The important observation is that this only happens if a database is
created while the backup is running, and that it only happens with the
FILE_COPY strategy - I've never seen this with WAL_LOG (which is the
default since PG15).
I don't recall any reports of similar issues from pre-15 releases, where
FILE_COPY was the only available option - I'm not sure why is that.
Either it didn't have this issue back then, or maybe people happen to
not create databases concurrently with a backup very often. It's a race
condition / timing issue, essentially.
I have no ambition to investigate this part of the code much deeper, or
invent a fix myself, at least not in foreseeable future. But it seems
like something we probably should fix - subtly broken backups are not a
great thing.
I see there have been a couple threads proposing various improvements to
FILE_COPY, that might make it more efficient/faster, namely using the
filesystem cloning [1] or switching pg_upgrade to use it [2]. But having
something that's (maybe) faster but not quite correct does not seem like
a winning strategy to me ...
Alternatively, if we don't have clear desire to fix it, maybe the right
solution would be get rid of it?
regards
[1]
https://www.postgresql.org/message-id/ca+hukglm+t+swbu-chemuxjcogbxshlgzutv5zcwy4qrcce...@mail.gmail.com
[2] https://www.postgresql.org/message-id/Zl9ta3FtgdjizkJ5%40nathan
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
basebackup-test-scripts.tgz
Description: application/compressed-tar
