Hi João, Thanks for testing v2 and for the detailed report. v3 is attached.
On Wed, Nov 11, 2026 at 11:27 PM João Rito <[email protected]> wrote: > 1. A time target between the boundary record and the commit record > still breaks the database. I don't plan to document this case. I'd expect an operator to take the target from the statement's log_statement line, or from the last commit before the command with recovery_target_inclusive = on, and with v3 both stop before the command. A target that falls between the boundary record and the commit behaves as it does on master, and covering it is not a goal of this patch. > 2. With recovery_target_inclusive = on and a target exactly equal to > the boundary time, recovery does not stop at the boundary (the test > is boundary_time > target), replays the file operation, and then > stops before the commit because the commit time is later. Agreed. The boundary record is not a transaction that recovery_target_inclusive could include. > 3. 0001 does not touch the documentation. The recovery_target_time > and recovery_target_inclusive text in config.sgml (or the PITR > chapter) does not mention that recovery now also stops before these > commands, nor that xid and LSN targets are not covered (the xid > case Shihao reported). The recovery_target_inclusive entry is unchanged, for the reason given in point 2. For xid and LSN targets I kept the docs as they are. As I wrote to Shihao, the xid and LSN cases are not specific to this patch, since master gives the same result. They are also not limited to database commands. With recovery_target_xid set to a CREATE TABLE transaction and recovery_target_inclusive = off, the table's file is left behind. > 4. Small thing about messages. "recovery stopping before recovery > boundary, time ..." and the pg_waldump line do not say which > command the boundary belongs to Thanks for the suggestion. I kept the record to the time stamp, which is all the stop check and the apply delay use. If others find the command type useful, I can add it.
From 804852b6375e47d32e9bd715a240c57ca716e6c4 Mon Sep 17 00:00:00 2001 From: JoongHyuk Shin <[email protected]> Date: Sun, 6 Sep 2026 13:37:00 +0900 Subject: [PATCH v3 2/2] Make recovery_min_apply_delay wait on recovery boundary records recovery_min_apply_delay delays only commit records, on the grounds that the effects of other records are not visible until the commit is applied. That does not hold for the file operations of database and tablespace commands. A delayed standby drops a database directory or creates a tablespace directory immediately, and only the catalog change waits for the delay. Use the timestamp of the recovery boundary record as the delay reference as well, so that a delayed standby applies none of these file operations before the configured delay has passed since the boundary record was written. Adjust the config.sgml paragraph that describes the commit-only behavior and add a test case to 005_replay_delay.pl. --- doc/src/sgml/config.sgml | 8 +++++++- src/backend/access/transam/xlogrecovery.c | 17 ++++++++++------- src/test/recovery/t/005_replay_delay.pl | 13 +++++++++++++ 3 files changed, 30 insertions(+), 8 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 26e6127a397..90a3057784d 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5694,10 +5694,16 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" than typical time deviations between servers. </para> <para> - The delay occurs only on WAL records for transaction commits. + The delay occurs only on WAL records for transaction commits and on + the time-stamped records that database and tablespace commands write + (see <xref linkend="guc-recovery-target-time"/>). Other records are replayed as quickly as possible, which is not a problem because MVCC visibility rules ensure their effects are not visible until the corresponding commit record is applied. + Database and tablespace commands change files without waiting for the + commit, but each such command writes its time-stamped record before + those changes in the WAL, so the changes are not replayed until that + record's delay has passed. </para> <para> The delay occurs once the database in recovery has reached a consistent diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c index 17201e3ab14..88e97e072f0 100644 --- a/src/backend/access/transam/xlogrecovery.c +++ b/src/backend/access/transam/xlogrecovery.c @@ -3032,20 +3032,23 @@ recoveryApplyDelay(XLogReaderState *record) return false; /* - * Is it a COMMIT record? + * Is it a COMMIT record, or a recovery boundary record? * * We deliberately choose not to delay aborts since they have no effect on * MVCC. We already allow replay of records that don't have a timestamp, * so there is already opportunity for issues caused by early conflicts on * standbys. */ - if (XLogRecGetRmid(record) != RM_XACT_ID) - return false; - - xact_info = XLogRecGetInfo(record) & XLOG_XACT_OPMASK; + if (XLogRecGetRmid(record) == RM_XACT_ID) + { + xact_info = XLogRecGetInfo(record) & XLOG_XACT_OPMASK; - if (xact_info != XLOG_XACT_COMMIT && - xact_info != XLOG_XACT_COMMIT_PREPARED) + if (xact_info != XLOG_XACT_COMMIT && + xact_info != XLOG_XACT_COMMIT_PREPARED) + return false; + } + else if (XLogRecGetRmid(record) != RM_XLOG2_ID || + (XLogRecGetInfo(record) & ~XLR_INFO_MASK) != XLOG2_RECOVERY_BOUNDARY) return false; if (!getRecordTimestamp(record, &xtime)) diff --git a/src/test/recovery/t/005_replay_delay.pl b/src/test/recovery/t/005_replay_delay.pl index caec921e549..8b2770b36d3 100644 --- a/src/test/recovery/t/005_replay_delay.pl +++ b/src/test/recovery/t/005_replay_delay.pl @@ -17,6 +17,7 @@ $node_primary->start; # And some content $node_primary->safe_psql('postgres', "CREATE TABLE tab_int AS SELECT generate_series(1, 10) AS a"); +$node_primary->safe_psql('postgres', "CREATE DATABASE db_dropped"); # Take backup my $backup_name = 'my_backup'; @@ -55,6 +56,18 @@ $node_standby->poll_query_until('postgres', # the configured apply delay. cmp_ok(time() - $primary_insert_time, '>=', $delay, "standby applies WAL only after replication delay"); + +my $oid_dropped = $node_primary->safe_psql('postgres', + "SELECT oid FROM pg_database WHERE datname = 'db_dropped'"); +my $primary_drop_time = time(); +$node_primary->safe_psql('postgres', "DROP DATABASE db_dropped"); +$node_standby->poll_query_until('postgres', + "SELECT pg_stat_file('base/$oid_dropped', true) IS NULL") + or die "standby never removed the directory of db_dropped"; +cmp_ok(time() - $primary_drop_time, + '>=', $delay, + "standby removes database files only after replication delay"); + # Check that recovery can be paused or resumed expectedly. my $node_standby2 = PostgreSQL::Test::Cluster->new('standby2'); $node_standby2->init_from_backup($node_primary, $backup_name, -- 2.54.0
From 4be3df99d58f91d0de76b3f0697aca1e6963ea7c Mon Sep 17 00:00:00 2001 From: JoongHyuk Shin <[email protected]> Date: Sun, 6 Sep 2026 13:37:00 +0900 Subject: [PATCH v3 1/2] Add recovery boundary WAL record for database and tablespace commands CREATE DATABASE, DROP DATABASE, ALTER DATABASE SET TABLESPACE, CREATE TABLESPACE and DROP TABLESPACE operate on the filesystem directly, and their WAL records take effect without waiting for the commit. The stop check for recovery_target_time only looks at commit and abort records, so a target set before such a command may not stop before it, and the changes the command makes before committing can already have been replayed when its commit record arrives. The recovered cluster can then be left with an invalid database, a tablespace without its directory, or an orphaned directory, although the target was earlier than the command. Emit a new XLOG2 record carrying a timestamp before the first irreversible record of each of these commands, and make recoveryStopsBefore() stop before it when the timestamp is at or after a recovery_target_time target. recovery_target_inclusive does not apply, as the record does not end a transaction. Other target types are not affected, and the record needs no redo action. Add test cases for the five commands to 003_recovery_targets.pl, and document the new stopping point under recovery_target_time. Bump XLOG_PAGE_MAGIC. --- doc/src/sgml/config.sgml | 9 ++ src/backend/access/rmgrdesc/xlogdesc.c | 10 ++ src/backend/access/transam/xlog.c | 13 +++ src/backend/access/transam/xlogrecovery.c | 31 +++++- src/backend/commands/dbcommands.c | 6 + src/backend/commands/tablespace.c | 4 + src/backend/replication/logical/decode.c | 1 + src/include/access/xlog.h | 1 + src/include/access/xlog_internal.h | 7 +- src/include/catalog/pg_control.h | 1 + src/test/recovery/t/003_recovery_targets.pl | 115 ++++++++++++++++++++ src/tools/pgindent/typedefs.list | 1 + 12 files changed, 196 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 7018ca1e3ea..26e6127a397 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4487,6 +4487,15 @@ restore_command = 'copy "C:\\server\\archivedir\\%f" "%p"' # Windows will proceed. The precise stopping point is also influenced by <xref linkend="guc-recovery-target-inclusive"/>. + <command>CREATE DATABASE</command>, <command>DROP DATABASE</command>, + <command>ALTER DATABASE ... SET TABLESPACE</command>, + <command>CREATE TABLESPACE</command> and + <command>DROP TABLESPACE</command> write a WAL record carrying a time + stamp before the record of their first change that takes effect + without waiting for the commit. + Recovery stops before the time-stamped record if its time stamp is at + or after the target time, regardless of + <varname>recovery_target_inclusive</varname>. </para> <para> diff --git a/src/backend/access/rmgrdesc/xlogdesc.c b/src/backend/access/rmgrdesc/xlogdesc.c index 323acd74467..b3076cefd6e 100644 --- a/src/backend/access/rmgrdesc/xlogdesc.c +++ b/src/backend/access/rmgrdesc/xlogdesc.c @@ -87,6 +87,13 @@ xlog2_desc(StringInfo buf, XLogReaderState *record) memcpy(&xlrec, rec, sizeof(xl_checksum_state)); appendStringInfoString(buf, get_checksum_state_string(xlrec.new_checksum_state)); } + else if (info == XLOG2_RECOVERY_BOUNDARY) + { + xl_recovery_boundary *xlrec = (xl_recovery_boundary *) rec; + + appendStringInfo(buf, "time %s", + timestamptz_to_str(xlrec->boundary_time)); + } } void @@ -290,6 +297,9 @@ xlog2_identify(uint8 info) case XLOG2_CHECKSUMS: id = "CHECKSUMS"; break; + case XLOG2_RECOVERY_BOUNDARY: + id = "RECOVERY_BOUNDARY"; + break; } return id; diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index fd37ea4f607..8b193b1cd46 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -9071,6 +9071,19 @@ XLogRestorePoint(const char *rpName) return RecPtr; } +void +XLogRecoveryBoundary(void) +{ + xl_recovery_boundary xlrec; + + xlrec.boundary_time = GetCurrentTimestamp(); + + XLogBeginInsert(); + XLogRegisterData(&xlrec, sizeof(xlrec)); + + (void) XLogInsert(RM_XLOG2_ID, XLOG2_RECOVERY_BOUNDARY); +} + /* * Write an empty XLOG record to assign a distinct LSN. * diff --git a/src/backend/access/transam/xlogrecovery.c b/src/backend/access/transam/xlogrecovery.c index 52ac6d58423..17201e3ab14 100644 --- a/src/backend/access/transam/xlogrecovery.c +++ b/src/backend/access/transam/xlogrecovery.c @@ -2420,8 +2420,8 @@ checkTimeLineSwitch(XLogRecPtr lsn, TimeLineID newTLI, TimeLineID prevTLI, * * If the record contains a timestamp, returns true, and saves the timestamp * in *recordXtime. If the record type has no timestamp, returns false. - * Currently, only transaction commit/abort records and restore points contain - * timestamps. + * Currently, only transaction commit/abort records, restore points and + * recovery boundary records contain timestamps. */ static bool getRecordTimestamp(XLogReaderState *record, TimestampTz *recordXtime) @@ -2435,6 +2435,11 @@ getRecordTimestamp(XLogReaderState *record, TimestampTz *recordXtime) *recordXtime = ((xl_restore_point *) XLogRecGetData(record))->rp_time; return true; } + if (rmid == RM_XLOG2_ID && info == XLOG2_RECOVERY_BOUNDARY) + { + *recordXtime = ((xl_recovery_boundary *) XLogRecGetData(record))->boundary_time; + return true; + } if (rmid == RM_XACT_ID && (xact_info == XLOG_XACT_COMMIT || xact_info == XLOG_XACT_COMMIT_PREPARED)) { @@ -2621,6 +2626,28 @@ recoveryStopsBefore(XLogReaderState *record) return true; } + if (recoveryTarget == RECOVERY_TARGET_TIME && + XLogRecGetRmid(record) == RM_XLOG2_ID && + (XLogRecGetInfo(record) & ~XLR_INFO_MASK) == XLOG2_RECOVERY_BOUNDARY) + { + (void) getRecordTimestamp(record, &recordXtime); + + if (recordXtime < recoveryTargetTime) + return false; + + recoveryStopAfter = false; + recoveryStopXid = InvalidTransactionId; + recoveryStopLSN = InvalidXLogRecPtr; + recoveryStopTime = recordXtime; + recoveryStopName[0] = '\0'; + + ereport(LOG, + errmsg("recovery stopping before recovery boundary, time %s", + timestamptz_to_str(recoveryStopTime))); + + return true; + } + /* Otherwise we only consider stopping before COMMIT or ABORT records. */ if (XLogRecGetRmid(record) != RM_XACT_ID) return false; diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c index 7e3fc59eafd..a427a86e7dc 100644 --- a/src/backend/commands/dbcommands.c +++ b/src/backend/commands/dbcommands.c @@ -1567,6 +1567,8 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt) if (dbstrategy == CREATEDB_WAL_LOG) LockSharedObject(DatabaseRelationId, dboid, 0, AccessShareLock); + XLogRecoveryBoundary(); + /* * Once we start copying subdirectories, we need to be able to clean 'em * up if we fail. Use an ENSURE block to make sure this happens. (This @@ -1859,6 +1861,8 @@ dropdb(const char *dbname, bool missing_ok, bool force) */ pgstat_drop_database(db_id); + XLogRecoveryBoundary(); + /* * Except for the deletion of the catalog row, subsequent actions are not * transactional (consider DropDatabaseBuffers() discarding modified @@ -2249,6 +2253,8 @@ movedb(const char *dbname, const char *tblspcname) */ copydir(src_dbpath, dst_dbpath, false); + XLogRecoveryBoundary(); + /* * Record the filesystem change in XLOG */ diff --git a/src/backend/commands/tablespace.c b/src/backend/commands/tablespace.c index 7c40176b7b9..6ec61ae3b21 100644 --- a/src/backend/commands/tablespace.c +++ b/src/backend/commands/tablespace.c @@ -365,6 +365,8 @@ CreateTableSpace(CreateTableSpaceStmt *stmt) create_tablespace_directories(location, tablespaceoid); + XLogRecoveryBoundary(); + /* Record the filesystem change in XLOG */ { xl_tblspc_create_rec xlrec; @@ -539,6 +541,8 @@ DropTableSpace(DropTableSpaceStmt *stmt) } } + XLogRecoveryBoundary(); + /* Record the filesystem change in XLOG */ { xl_tblspc_drop_rec xlrec; diff --git a/src/backend/replication/logical/decode.c b/src/backend/replication/logical/decode.c index f44f94d9f07..d3c66b30335 100644 --- a/src/backend/replication/logical/decode.c +++ b/src/backend/replication/logical/decode.c @@ -200,6 +200,7 @@ xlog2_decode(LogicalDecodingContext *ctx, XLogRecordBuffer *buf) switch (info) { case XLOG2_CHECKSUMS: + case XLOG2_RECOVERY_BOUNDARY: break; default: elog(ERROR, "unexpected RM_XLOG2_ID record type: %u", info); diff --git a/src/include/access/xlog.h b/src/include/access/xlog.h index 2bf5714b51e..381493c65a3 100644 --- a/src/include/access/xlog.h +++ b/src/include/access/xlog.h @@ -289,6 +289,7 @@ extern bool CreateRestartPoint(int flags); extern WALAvailability GetWALAvailability(XLogRecPtr targetLSN); extern void XLogPutNextOid(Oid8 nextOid); extern XLogRecPtr XLogRestorePoint(const char *rpName); +extern void XLogRecoveryBoundary(void); extern XLogRecPtr XLogAssignLSN(void); extern void UpdateFullPageWrites(void); extern void GetFullPageWriteInfo(XLogRecPtr *RedoRecPtr_p, bool *doPageWrites_p); diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h index bf609c3c703..fc7ff5c839b 100644 --- a/src/include/access/xlog_internal.h +++ b/src/include/access/xlog_internal.h @@ -32,7 +32,7 @@ /* * Each page of XLOG file has a header like this: */ -#define XLOG_PAGE_MAGIC 0xD122 /* can be used as WAL version indicator */ +#define XLOG_PAGE_MAGIC 0xD123 /* can be used as WAL version indicator */ typedef struct XLogPageHeaderData { @@ -294,6 +294,11 @@ typedef struct xl_checksum_state ChecksumStateType new_checksum_state; } xl_checksum_state; +typedef struct xl_recovery_boundary +{ + TimestampTz boundary_time; +} xl_recovery_boundary; + /* Overwrite of prior contrecord */ typedef struct xl_overwrite_contrecord { diff --git a/src/include/catalog/pg_control.h b/src/include/catalog/pg_control.h index c3c934d0012..dfd3ac7bb4f 100644 --- a/src/include/catalog/pg_control.h +++ b/src/include/catalog/pg_control.h @@ -88,6 +88,7 @@ typedef struct CheckPoint /* XLOG info values for XLOG2 rmgr */ #define XLOG2_CHECKSUMS 0x00 +#define XLOG2_RECOVERY_BOUNDARY 0x10 /* diff --git a/src/test/recovery/t/003_recovery_targets.pl b/src/test/recovery/t/003_recovery_targets.pl index db4a0ea74b2..776f00def9c 100644 --- a/src/test/recovery/t/003_recovery_targets.pl +++ b/src/test/recovery/t/003_recovery_targets.pl @@ -51,6 +51,14 @@ sub test_recovery_standby $node_standby->safe_psql('postgres', "SELECT count(*) FROM tab_int"); is($result, qq($num_rows), "check standby content for $test_name"); + if (defined $params{checks}) + { + $node_standby->poll_query_until('postgres', + "SELECT pg_get_wal_replay_pause_state() = 'paused'") + or die "Timed out while waiting for recovery to pause"; + $params{checks}->($node_standby); + } + # Stop standby node $node_standby->teardown_node; @@ -60,6 +68,11 @@ sub test_recovery_standby # Initialize primary node my $node_primary = PostgreSQL::Test::Cluster->new('primary'); $node_primary->init(has_archiving => 1, allows_streaming => 1); +$node_primary->append_conf( + 'postgresql.conf', qq( +autovacuum = off +allow_in_place_tablespaces = on +)); # Bump the transaction ID epoch. This is useful to stress the portability # of recovery_target_xid parsing. @@ -114,6 +127,40 @@ $node_primary->safe_psql('postgres', my $lsn6 = $node_primary->safe_psql('postgres', "SELECT pg_current_wal_lsn()"); +$node_primary->safe_psql('postgres', + "CREATE TABLESPACE ts_dropped LOCATION ''"); +$node_primary->safe_psql('postgres', + "CREATE TABLESPACE ts_target LOCATION ''"); +$node_primary->safe_psql('postgres', "CREATE DATABASE db_dropped"); +$node_primary->safe_psql('postgres', "CREATE DATABASE db_moved"); +my $oid_ts_dropped = $node_primary->safe_psql('postgres', + "SELECT oid FROM pg_tablespace WHERE spcname = 'ts_dropped'"); +my $oid_ts_target = $node_primary->safe_psql('postgres', + "SELECT oid FROM pg_tablespace WHERE spcname = 'ts_target'"); + +my $time_before_dbdrop = $node_primary->safe_psql('postgres', "SELECT now()"); +$node_primary->safe_psql('postgres', "DROP DATABASE db_dropped"); + +my $time_before_dbcreate = + $node_primary->safe_psql('postgres', "SELECT now()"); +$node_primary->safe_psql('postgres', "CREATE DATABASE db_created"); +my $oid_db_created = $node_primary->safe_psql('postgres', + "SELECT oid FROM pg_database WHERE datname = 'db_created'"); + +my $time_before_tscreate = + $node_primary->safe_psql('postgres', "SELECT now()"); +$node_primary->safe_psql('postgres', + "CREATE TABLESPACE ts_created LOCATION ''"); +my $oid_ts_created = $node_primary->safe_psql('postgres', + "SELECT oid FROM pg_tablespace WHERE spcname = 'ts_created'"); + +my $time_before_dbmove = $node_primary->safe_psql('postgres', "SELECT now()"); +$node_primary->safe_psql('postgres', + "ALTER DATABASE db_moved SET TABLESPACE ts_target"); + +my $time_before_tsdrop = $node_primary->safe_psql('postgres', "SELECT now()"); +$node_primary->safe_psql('postgres', "DROP TABLESPACE ts_dropped"); + # Force archiving of WAL file containing $lsn6 $node_primary->safe_psql('postgres', "SELECT pg_switch_wal()"); @@ -134,6 +181,74 @@ test_recovery_standby('name', 'standby_4', $node_primary, \@recovery_params, test_recovery_standby('LSN', 'standby_5', $node_primary, \@recovery_params, "5000", $lsn5); +@recovery_params = ("recovery_target_time = '$time_before_dbdrop'"); +test_recovery_standby( + 'time before DROP DATABASE', + 'standby_dbdrop', + $node_primary, + \@recovery_params, + "6000", $lsn6, + checks => sub { + my $node = shift; + is($node->psql('db_dropped', ''), 0, 'can connect to db_dropped'); + }); + +@recovery_params = ("recovery_target_time = '$time_before_dbcreate'"); +test_recovery_standby( + 'time before CREATE DATABASE', + 'standby_dbcreate', + $node_primary, + \@recovery_params, + "6000", $lsn6, + checks => sub { + my $node = shift; + ok( !-d $node->data_dir . "/base/$oid_db_created", + 'no orphaned directory of db_created'); + }); + +@recovery_params = ("recovery_target_time = '$time_before_tscreate'"); +test_recovery_standby( + 'time before CREATE TABLESPACE', + 'standby_tscreate', + $node_primary, + \@recovery_params, + "6000", $lsn6, + checks => sub { + my $node = shift; + ok(!-d $node->data_dir . "/pg_tblspc/$oid_ts_created", + 'no orphaned directory of ts_created'); + }); + +@recovery_params = ("recovery_target_time = '$time_before_dbmove'"); +test_recovery_standby( + 'time before ALTER DATABASE SET TABLESPACE', + 'standby_dbmove', + $node_primary, + \@recovery_params, + "6000", $lsn6, + checks => sub { + my $node = shift; + is( $node->safe_psql( + 'postgres', + "SELECT count(*) FROM pg_tablespace_databases($oid_ts_target)" + ), + '0', + 'no orphaned copy of db_moved in ts_target'); + }); + +@recovery_params = ("recovery_target_time = '$time_before_tsdrop'"); +test_recovery_standby( + 'time before DROP TABLESPACE', + 'standby_tsdrop', + $node_primary, + \@recovery_params, + "6000", $lsn6, + checks => sub { + my $node = shift; + ok(-d $node->data_dir . "/pg_tblspc/$oid_ts_dropped", + 'directory of ts_dropped exists'); + }); + # Regression: empty-string for one recovery_target_* GUC must not clobber # another non-empty target. Setting recovery_target_xid + recovery_target_time # = '' must recover to the xid, not run as no-target recovery. diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 8f65cee41b1..f8e92655f67 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -4509,6 +4509,7 @@ xl_multixact_create xl_multixact_truncate xl_overwrite_contrecord xl_parameter_change +xl_recovery_boundary xl_relmap_update xl_replorigin_drop xl_replorigin_set -- 2.54.0
