| From: | Michael Malis <malis(at)pgrust(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | dbase_redo: XLOG_DBASE_CREATE_FILE_COPY replay empties live database |
| Date: | 2026-11-16 20:28:10 |
| Message-ID: | CANVD+MB_JL5ydxD8NUUGJsdEGB1+nW56-PnS8QTfUtW+w_V9+A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Hi,
Reproduced on PostgreSQL 18.7 (REL_18_7, commit f5c41f6681c9) and verified
in the source on REL_18_STABLE (commit 2f613719fa4e) and master (commit
7fc2f0423320), where the relevant code in src/backend/commands/dbcommands.c
is unchanged.
On a streaming physical standby, if an existing database is moved with
ALTER DATABASE ... SET TABLESPACE (or a database is cloned with
CREATE DATABASE ... TEMPLATE ... STRATEGY FILE_COPY and the template
database is subsequently dropped), both XLOG_DBASE_CREATE_FILE_COPY and
XLOG_DBASE_DROP replay normally and the database is readable on the
standby. If the standby crashes before its next restartpoint advances
pg_control's redo pointer past XLOG_DBASE_CREATE_FILE_COPY, crash recovery
replays from the earlier restartpoint: dbase_redo() removes the surviving
destination directory with rmtree(dst_path, true), creates an empty
placeholder at the missing src_path via recovery_create_dbdir(), and copies
that empty directory to dst_path. Recovery completes with no error in the
server log, leaving the database directory completely empty (missing
PG_VERSION and all relation files) both in Hot Standby and after promotion.
The continuous archiving caveats in doc/src/sgml/backup.sgml (lines 1578 to
1586) only warn about modifying a template database while a base backup is
in progress. Here no base backup overlaps the DDL, and unlike CREATE
DATABASE (which defaults to STRATEGY WAL_LOG), ALTER DATABASE ... SET
TABLESPACE always emits XLOG_DBASE_CREATE_FILE_COPY.
Attached is 099_dbase_redo_file_copy.pl, a minimal src/test/recovery/t/ TAP
test covering ALTER DATABASE ... SET TABLESPACE, CREATE DATABASE ...
STRATEGY FILE_COPY, and a surviving STRATEGY WAL_LOG control.
Steps to reproduce manually (primary on 55520, streaming standby on 55521):
mkdir -p /tmp/pg4802/tp /tmp/pg4802/ts
initdb -D /tmp/pg4802/p -A trust
printf "port = 55520\nunix_socket_directories = '/tmp/pg4802'\n" \
>> /tmp/pg4802/p/postgresql.conf
pg_ctl -D /tmp/pg4802/p -l /tmp/pg4802/p.log start
psql -X -h /tmp/pg4802 -p 55520 -d postgres <<'SQL'
CREATE TABLESPACE ts_dest LOCATION '/tmp/pg4802/tp';
CREATE DATABASE relocated_db;
CREATE DATABASE tmpl_src;
CREATE DATABASE tmpl_wal;
\c relocated_db
CREATE TABLE ledger (id int);
INSERT INTO ledger VALUES (1);
\c tmpl_src
CREATE TABLE seed_rows (id int);
INSERT INTO seed_rows VALUES (1);
\c tmpl_wal
CREATE TABLE seed_rows (id int);
INSERT INTO seed_rows VALUES (1);
SQL
pg_basebackup -h /tmp/pg4802 -p 55520 -D /tmp/pg4802/s -R -X stream \
-T /tmp/pg4802/tp=/tmp/pg4802/ts
printf "port = 55521\nunix_socket_directories = '/tmp/pg4802'\n" \
>> /tmp/pg4802/s/postgresql.conf
pg_ctl -D /tmp/pg4802/s -l /tmp/pg4802/s.log start
psql -X -h /tmp/pg4802 -p 55520 -d postgres <<'SQL'
ALTER DATABASE relocated_db SET TABLESPACE ts_dest;
CREATE DATABASE copied_db TEMPLATE tmpl_src STRATEGY FILE_COPY;
DROP DATABASE tmpl_src;
CREATE DATABASE logged_db TEMPLATE tmpl_wal STRATEGY WAL_LOG;
DROP DATABASE tmpl_wal;
SQL
# Once replay catches up on 55521, all three queries return 1:
psql -X -h /tmp/pg4802 -p 55521 -d relocated_db \
-c "SELECT count(*) FROM ledger;"
psql -X -h /tmp/pg4802 -p 55521 -d copied_db \
-c "SELECT count(*) FROM seed_rows;"
psql -X -h /tmp/pg4802 -p 55521 -d logged_db \
-c "SELECT count(*) FROM seed_rows;"
# Crash the standby before a restartpoint and let it recover/promote:
pg_ctl -D /tmp/pg4802/s stop -m immediate
pg_ctl -D /tmp/pg4802/s -l /tmp/pg4802/s.log start
pg_ctl -D /tmp/pg4802/s promote
After recovery (both on Hot Standby before promote and after promote),
logged_db still returns 1, while connecting to relocated_db or copied_db
fails with:
FATAL: "pg_tblspc/16384/PG_18_202506291/16385" is not a valid data directory
DETAIL: File "pg_tblspc/16384/PG_18_202506291/16385/PG_VERSION" is missing.
FATAL: "base/16401" is not a valid data directory
DETAIL: File "base/16401/PG_VERSION" is missing.
Observations from the source:
1. Both CreateDatabaseUsingFileCopy() (src/backend/commands/dbcommands.c,
lines 642 to 677 on REL_18_STABLE; lines 663 to 698 on master) and
movedb() (lines 2249 to 2255 on REL_18_STABLE; lines 2301 to 2307 on
master) force a checkpoint before committing so that committed
XLOG_DBASE_CREATE_FILE_COPY records do not need to be replayed during
ordinary crash recovery on the primary. On a standby, however, replaying
XLOG_CHECKPOINT_ONLINE in xlog_redo()
(src/backend/access/transam/xlog.c) only records a candidate
restartpoint via RecoveryRestartPoint(); pg_control's redo pointer on
the standby does not advance until a restartpoint actually runs.
2. Immediately after committing, movedb() removes src_dbpath and logs
XLOG_DBASE_DROP for (db_id, src_tblspcoid) (lines 2293 to 2313 on
REL_18_STABLE; lines 2345 to 2365 on master). Dropping the template
database after CREATE DATABASE ... STRATEGY FILE_COPY similarly logs
XLOG_DBASE_DROP for src_dboid. Replaying those catalog updates dirties
shared buffers on the standby, so once bgwriter or buffer eviction
flushes them, UpdateMinRecoveryPoint() advances minRecoveryPoint in
pg_control past XLOG_DBASE_DROP while the redo pointer still precedes
XLOG_DBASE_CREATE_FILE_COPY.
3. When the standby crashes and restarts, recovery begins with
reachedConsistency == false. In dbase_redo()
(src/backend/commands/dbcommands.c, lines 3317 to 3388 on REL_18_STABLE;
lines 3345 to 3416 on master):
a) Lines 3334 to 3341 (3362 to 3369 on master) run rmtree(dst_path,
true), deleting the only surviving copy of the database.
b) Lines 3367 to 3368 (3395 to 3396 on master) see stat(src_path) fail
with ENOENT and call recovery_create_dbdir(src_path, false) (added in
commit 9e4f914b5eba, which resolved the standby startup failure in
BUG #17226) under the comment "The directory will be dropped soon by
recovery", assuming the database being created will itself be dropped
later in recovery rather than only its source directory.
c) Line 3384 (3412 on master) runs copydir(src_path, dst_path, false),
copying the empty directory to dst_path, and subsequent replay of
XLOG_DBASE_DROP removes the temporary src_path.
(Even if no buffer flush had advanced minRecoveryPoint before the crash
so reachedConsistency were already true, dbase_redo() still executes
rmtree(dst_path, true) before recovery_create_dbdir() panics on the
missing src_path.)
4. Notably, src/test/recovery/t/033_replay_tsp_drops.pl (also added by
commit 9e4f914b5eba) already hits the FILE_COPY template drop path:
test_tablespace("FILE_COPY") creates template_db before the base backup,
runs CREATE DATABASE newdb TEMPLATE template_db STRATEGY=FILE_COPY and
DROP DATABASE template_db, crashes the standby with stop('immediate'),
and checks only that $node_standby->start succeeds before stopping it.
Connecting to newdb right after that restart already fails with the same
missing PG_VERSION error (moveme_db in that test happens to be created
after the backup, so replaying its CREATE DATABASE earlier in the same
redo pass re-populates source_ts from template1 before ALTER DATABASE
... SET TABLESPACE replays).
5. Skipping rmtree(dst_path) only when src_path is ENOENT would not cover
cases where src_path still exists on disk in a modified state (for
example, a template database modified by DROP TABLE or TRUNCATE before
the standby's next restartpoint) or where the standby crashed partway
through copydir().
Cheers,
Michael
| Attachment | Content-Type | Size |
|---|---|---|
| 099_dbase_redo_file_copy.pl | text/plain | 3.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Michael Malis | 2026-11-16 20:28:11 | Logical replication commits duplicates in DEFERRABLE unique indexes |
| Previous Message | Michael Malis | 2026-11-16 20:28:08 | Hot Standby: missing snapshot conflicts in CREATE INDEX, rewrite, hash |