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

Having continued my research, the problem I encountered is the exact same
that's been recorded here:

https://www.marshut.net/kstxxk/pitr-failing-to-stop-before-drop-database.html

I took the backup by following the procedures as laid forth in the
continuous archiving document (
http://www.postgresql.org/docs/9.2/static/continuous-archiving.html)

I configured the archiving to gzip the wal files to pgdata/archive
Then I started the backup process by issuing "select
pg_start_backup('mylabel')"
next tarball'd the contents of pgdata, excluding the pgdata/archive dir and
the pgdata/pg_xlog dir (although preserving the directory structure)
after that I issued "select pg_stop_backup()"
then I added the contents of pgdata/archive and pgdata/pg_xlog to the
tarball above
then I gzipped the tarball.

The above is how I archived and backed up..

To restore the backup, I shut down the server, moved pgdata to
pgdata_backup, untarballed the backup tarball, removed all the files in the
new pgdata/pg_xlog dir, copied the files from pgdata_backup/archive and
pgdata_backup/pg_xlog into the new pgdata dir, set up the recovery.conf
file giving it a timestamp gathered from the pgdata_backup/pg_log/<> log
files.. I copied ALL the pg_xlog files ... not simply the "unarchived
ones". All of the unarchived ones should have been removed when I removed
the contents of the pg_xlog dir after restoring the tarball..

I think I answered all the questions - please let me know if I missed
some. Based on the url I pasted at the top, though, it appears I'm not the
only one who's encountered this problem.

On Tue, Dec 2, 2014 at 3:39 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>

--
Joshua Boyd