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"
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