Recovering a deleted database problem

Earlier this evening I made the usual mistake someone makes at some
point in their lives - and dropped a database thinking I didn't need it,
then realised later I did.
So, because I have DDL statement logging turned on, I could find the
exact time/date it happened, and attempted to restore from my
file-system level backup taken at 2am this morning, and rolled forward
all my WAL logs archived throughout the day (98 files.)

In the recovery.conf, I specified the date/time from the log file that
the database was dropped and set recovery_target_inclusive so it would
not include this transaction.

However the restore has finished, and PostgreSQL thinks the database is
there, but the relevant data directory in "base" is missing - so it's
removed the file-system objects but not the system database entry.
I've checked the base backup, and this directory is in the backup, hence
it has been removed at some point during the restore.

What I'm going to do now is to set the recovery target to about a minute
earlier to make sure the transaction has not started when the recovery
finishes - but I'm just asking if I'm missing something obvious, as this
is the first time I've done a restore from WAL logs.

(Note, after writing this, I tried restoring to a minute earlier (ie.
18:57:40) and still have the same problem.
As a quick fix, I copied the base/35290 directory from the backup before
I had run the recovery - does anyone know any caveats to doing this, as
the DB seems to be working OK?)

My recovery.conf is:

# PostgreSQL database recovery config file

restore_command = 'cp /path/to/wal/archive/%f "%p"'
recovery_target_time = '"2007-01-04 18:58:40 -00:00"'
recovery_target_inclusive = 'false'

The log entry where I discovered the date/time is:

2007-01-04 18:58:40 GMT 84.45.66.158 postgres postgresql - LOG:
statement: DROP DATABASE [dbname];

The error I get when I try to connect to [dbname] after the restore is:

FATAL: database "[dbname]" does not exist
DETAIL: The database subdirectory "base/35290" is missing.

But the [dbname] database is still in the system catalogues:

/usr/local/pgsql/bin/psql -U postgresql -d postgres -c "select datname
from pg_database;"
    datname
----------------
 postgres
 [db1]
 template1
 template0
 [dbname]
 [db2]
 [db3]
 [db4]
(8 rows)


This is PostgreSQL 8.1.5 on FreeBSD 6.1.

Many thanks,

--
Andy Shellam
NetServe Support Team

the Mail Network
"an alternative in a standardised world"