Message-ID: <43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org>
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
To: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
From: David Steele <david@pgbackrest.org>
Subject: Bug in recovery of drop database?
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Date: Wed, 05 Feb 2025 20:24:52 +0000 (UTC)
Archived-At: 
 <https://www.postgresql.org/message-id/43f0c3df-a23c-4159-9610-934d3dceb80c%40pgbackrest.org>
Precedence: bulk

It appears that if recovery ends on a drop database command the database 
is only partially removed -- the files are removed but the entry in 
pg_database remains, leading to this error:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: 
FATAL:  cannot connect to invalid database "mytest"
HINT:  Use DROP DATABASE to drop invalid databases.

Here's pg_database:

$ test/pg/bin/psql postgres -c 'select oid, datname from pg_database 
order by oid';
   oid  |  datname
-------+-----------
      1 | template1
      4 | template0
      5 | postgres
  16384 | mytest
(4 rows)

And the base data dir:

$ ls -lah test/data/base
total 20K
drwx------  5 dev dialout 4.0K Feb  5 19:31 .
drwx------ 19 dev dialout 4.0K Feb  5 19:31 ..
drwx------  2 dev dialout 4.0K Feb  5 19:30 1
drwx------  2 dev dialout 4.0K Feb  5 19:30 4
drwx------  2 dev dialout 4.0K Feb  5 19:32 5

Here is a full repro:

test/pg/bin/pg_basebackup -c fast -X none -D test/backup/full -F plain
test/pg/bin/createdb mytest
test/pg/bin/psql mytest -t -c 'select now()'
  2025-02-05 19:18:26.245167+00

test/pg/bin/dropdb mytest
test/pg/bin/psql mytest
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: 
FATAL:  database "mytest" does not exist

test/pg/bin/pg_ctl -D test/data -w stop
rm -rf test/data
cp -rp test/backup/full test/data
touch test/data/recovery.signal
echo "restore_command = 'cp /home/dev/test/archive/%f %p'" >> 
test/data/postgresql.auto.conf
echo "recovery_target_time = '2025-02-05 19:18:26.245167+00'" >> 
test/data/postgresql.auto.conf
test/pg/bin/pg_ctl -D test/data -w start

test/pg/bin/psql mytest -c 'select count(*) from pgbench_accounts'
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: 
FATAL:  cannot connect to invalid database "mytest"
HINT:  Use DROP DATABASE to drop invalid databases.

However, if I insert this command before the drop database:

test/pg/bin/psql mytest -c 'create table test (id int)'

Then recovery works fine since it is able to stop before the the drop 
database. This is an issue on HEAD as well as all current back branches.

For completeness here is my configuration on this test system (though I 
have seen this issue in a variety of environments):

wal_level = replica
archive_mode = on
archive_command = 'cp %p /home/dev/test/archive/%f'
log_min_messages = debug1
max_wal_senders = 10
log_line_prefix = '%m %L '
autovacuum = off

We have seen this several times now because a favorite test for users is 
to drop a database and see if they can recover it with PITR. PITR in a 
test database is always a bit dicey since little/no WAL activity means 
recovery doesn't have much to go on RE recovery targets, but at the 
least the database should be fully there or fully not there.

Regards,
-David