# Copyright (c) 2026, PostgreSQL Global Development Group # # Reproducer: standby crash recovery before a restartpoint destroys databases # created or relocated via XLOG_DBASE_CREATE_FILE_COPY once the source # directory has been removed by XLOG_DBASE_DROP. use strict; use warnings FATAL => 'all'; use PostgreSQL::Test::Cluster; use PostgreSQL::Test::Utils; use Test::More; my $tempdir = PostgreSQL::Test::Utils::tempdir_short(); my $ts_pri = "$tempdir/ts_pri"; my $ts_bkp = "$tempdir/ts_bkp"; my $ts_sby = "$tempdir/ts_sby"; mkdir($ts_pri) or die "mkdir $ts_pri: $!"; my $primary = PostgreSQL::Test::Cluster->new('primary'); $primary->init(allows_streaming => 1); $primary->start; $primary->safe_psql( 'postgres', qq[ CREATE TABLESPACE ts_dest LOCATION '$ts_pri'; CREATE DATABASE relocated_db; CREATE DATABASE tmpl_src; CREATE DATABASE tmpl_wal; ]); $primary->safe_psql('relocated_db', 'CREATE TABLE ledger (id int); INSERT INTO ledger VALUES (1);'); $primary->safe_psql('tmpl_src', 'CREATE TABLE seed_rows (id int); INSERT INTO seed_rows VALUES (1);'); $primary->safe_psql('tmpl_wal', 'CREATE TABLE seed_rows (id int); INSERT INTO seed_rows VALUES (1);'); my $tsoid = $primary->safe_psql('postgres', "SELECT oid FROM pg_tablespace WHERE spcname = 'ts_dest'"); $primary->backup('bkp', backup_options => ["-T$ts_pri=$ts_bkp"]); my $standby = PostgreSQL::Test::Cluster->new('standby'); $standby->init_from_backup( $primary, 'bkp', has_streaming => 1, tablespace_map => { $tsoid => $ts_sby }); $standby->start; $primary->safe_psql( 'postgres', q[ 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; ]); $primary->wait_for_replay_catchup($standby); is($standby->safe_psql('relocated_db', 'SELECT count(*) FROM ledger'), '1', 'relocated_db readable on standby before crash'); is($standby->safe_psql('copied_db', 'SELECT count(*) FROM seed_rows'), '1', 'copied_db readable on standby before crash'); is($standby->safe_psql('logged_db', 'SELECT count(*) FROM seed_rows'), '1', 'logged_db readable on standby before crash'); # Crash the standby before a restartpoint advances its redo pointer. $standby->stop('immediate'); $standby->start; my ($ret, $stdout, $stderr) = $standby->psql('relocated_db', 'SELECT count(*) FROM ledger'); is($ret, 0, "relocated_db readable on hot standby after crash ($stderr)"); $standby->promote; ($ret, $stdout, $stderr) = $standby->psql('logged_db', 'SELECT count(*) FROM seed_rows'); is($ret, 0, 'WAL_LOG database survives standby crash recovery'); is($stdout, '1', 'WAL_LOG database retains rows'); ($ret, $stdout, $stderr) = $standby->psql('relocated_db', 'SELECT count(*) FROM ledger'); is($ret, 0, "relocated_db survives promotion ($stderr)"); is($stdout, '1', 'relocated_db retains rows'); ($ret, $stdout, $stderr) = $standby->psql('copied_db', 'SELECT count(*) FROM seed_rows'); is($ret, 0, "copied_db survives promotion ($stderr)"); is($stdout, '1', 'copied_db retains rows'); done_testing();