From: David Steele <david@pgbackrest.org>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Bug in recovery of drop database?
Date: Wed, 05 Feb 2025 20:24:52 +0000 (UTC)
Message-ID: <43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: david@pgbackrest.org, pgsql-bugs@lists.postgresql.org
Subject: Re: Bug in recovery of drop database?
In-Reply-To: <43f0c3df-a23c-4159-9610-934d3dceb80c@pgbackrest.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox