Re: Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files

On 11/28/2014 02:29 PM, Joshua Boyd wrote:

I am testing out point in time recovery from a hot physical backup in a
disaster recovery situation - I turned on archiving of files, created a
hot physical backup,

How did you take the backup?

Archiving how and to where?

then (after letting it run for a few days) issued a

"DROP DATABASE".  The pg_log file shows the DROP DATABASE command was
issued at '2014-11-28 10:20:00.010 PST'.  I shut down the server, moved
the pgdata directory to pgdata_backup ... restored the files in the hot
physical backup I made, copied the wal archive files from pgdata_backup
to the (new) pgdata archive,

The above I do not understand.
You where archiving the WALs in your pgdata directory?

Restored the backup how?

 cleared out the new pg_xlog dir and copied

the files from the old pg_xlog into the new..  Set up a recovery.conf

All the files or only the unarchived ones?

file as such:

restore_command = 'gunzip -c /home/pg2dev/joshtest/pgdata/archive/%f.gz
 > %p'
recovery_target_time = '2014-11-28 10:20:00.010 PST'
recovery_target_inclusive = false

then I started the server up.  the pg_log shows the following:

And then I look in pgdata/base .. and sure enough, that directory is
missing.  I examine my hot physical backup file and that directory
exists within it.

So .... even though the recovery SAYS "recovery stopping before commit
of transaction 235078" ... it doesn't appear that it's 100% accurate.
It didn't commit the transaction, clearly, because the database is still
listed in the data dictionary ... however, the filesystem files are
gone.  Please - am I doing something wrong, or would this be considered
a bug?

--
Joshua Boyd

--
Adrian Klaver
adrian.klaver@aklaver.com