Re: PITR failing to stop before DROP DATABASE

On 11/25/2014 06:06 PM, Christoph Berg wrote:
> In 9.3.5, if I set up archiving, create a database, pull a base
> backup, look at the clock, drop database, stop the server, rm -rf
> datadir, put back the backup, edit recovery.conf:
>
> cd /tmp; initdb foo
> edit postgresql.conf with archive_mode = on, archive_command,
> max_wal_senders = 1, wal_level = hot_standby
> edit pg_hba.conf
> pg_ctl start
> psql -c 'create database db1'
> psql -c 'checkpoint'
> pg_basebackup -D /tmp/back1
> date
> Di 25. Nov 17:00:31 CET 2014
> psql -c 'drop database db1'
> pg_ctl stop
> rm -rf foo; cp -a back1 foo
>
> restore_command = 'cp /tmp/%f %p'
> standby_mode = on
> recovery_target_time = '2014-11-25 17:00:31'
>
> ... and then restart the server, I get:
>
> postgres=# \c db1
> FATAL: database "db1" does not exist
> DETAIL: The database subdirectory "base/70262" is missing.
>
> 2014-11-25 16:11:22 CET [15225-1] LOG: database system was interrupted; last known up at 2014-11-25 15:56:51 CET
> 2014-11-25 16:11:22 CET [15225-2] LOG: creating missing WAL directory "pg_xlog/archive_status"
> 2014-11-25 16:11:22 CET [15225-3] LOG: entering standby mode
> 2014-11-25 16:11:22 CET [15225-4] LOG: restored log file "000000010000000D0000007B" from archive
> 2014-11-25 16:11:22 CET [15225-5] LOG: redo starts at D/7B000028
> 2014-11-25 16:11:22 CET [15225-6] LOG: consistent recovery state reached at D/7B0000F0
> 2014-11-25 16:11:22 CET [15225-7] LOG: restored log file "000000010000000D0000007C" from archive
> 2014-11-25 16:11:22 CET [15225-8] LOG: recovery stopping before commit of transaction 60681, time 2014-11-25 15:59:57.071137+01
> 2014-11-25 16:11:22 CET [15225-9] LOG: redo done at D/7C001110
> cp: cannot stat '/tmp/00000002.history': No such file or directory
> 2014-11-25 16:11:22 CET [15225-10] LOG: selected new timeline ID: 2
> cp: cannot stat '/tmp/00000001.history': No such file or directory
> 2014-11-25 16:11:23 CET [15225-11] LOG: archive recovery complete
> 2014-11-25 16:11:23 CET [15236-1] [unknown](at)[unknown] LOG: incomplete startup packet
> 2014-11-25 16:11:23 CET [15238-1] LOG: autovacuum launcher started
> 2014-11-25 16:11:23 CET [15224-1] LOG: database system is ready to accept connections
> 2014-11-25 16:11:41 CET [15268-1] postgres(at)db1 FATAL: database "db1" does not exist
> 2014-11-25 16:11:41 CET [15268-2] postgres(at)db1 DETAIL: The database subdirectory "base/70262" is missing.
>
> db1 is registered in pg_database, but the directory is missing on
> disk.

Yeah, DROP DATABASE cheats. It deletes all the files first, and commits
the transaction only after that. There's this comment at the end of
dropdb() function:

> /*
> * Force synchronous commit, thus minimizing the window between removal of
> * the database files and commital of the transaction. If we crash before
> * committing, we'll have a DB that's gone on disk but still there
> * according to pg_database, which is not good.
> */

So you could see the same after crash recovery, but it's a lot easier to
reproduce with PITR.

This could be fixed by doing DROP DATABASE the same way we do DROP
TABLE. At the DROP DATABASE command, just memorize the OID of the
dropped database, but don't delete anything yet. Perform the actual
deletion after flushing the commit record to disk. But then you would
have the opposite problem - you might be left with a database that's
dropped according to pg_database, but the files are still present on disk.

- Heikki