pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andy Shellam (Mailing Lists) <andy.shellam-lists@mailnetwork.co.uk>
To: pgsql-admin@postgresql.org
Subject: Recovering a deleted database problem
Date: Fri, 05 Jan 2007 11:13:32 +0000
Message-ID: <459E32DC.3030402@mailnetwork.co.uk> (raw)

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"




view thread (5+ messages)  latest in thread

Message-ID: <459E32DC.3030402@mailnetwork.co.uk>
Permalink:  ../459E32DC.3030402@mailnetwork.co.uk/
Also on:    postgresql.org/message-id/459E32DC.3030402@mailnetwork.co.uk

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: andy.shellam-lists@mailnetwork.co.uk
  Subject: Re: Recovering a deleted database problem
  In-Reply-To: <459E32DC.3030402@mailnetwork.co.uk>
  References: <459E32DC.3030402@mailnetwork.co.uk>

* 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