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