Skip site navigation (1) Skip section navigation (2)

Recovering a deleted database problem

From: "Andy Shellam (Mailing Lists)" <andy(dot)shellam-lists(at)mailnetwork(dot)co(dot)uk>
To: pgsql-admin(at)postgresql(dot)org
Subject: Recovering a deleted database problem
Date: 2007-01-05 11:13:32
Message-ID: 459E32DC.3030402@mailnetwork.co.uk (view raw or flat)
Thread:
Lists: pgsql-admin
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"


Responses

pgsql-admin by date

Next:From: Sistemas C.M.P.Date: 2007-01-05 14:14:06
Subject: Connectionstring
Previous:From: Markus SchiltknechtDate: 2007-01-05 10:42:28
Subject: Re: Replication

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group