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

I tried that when I was testing .. if I stopped at the most recent
insert/update/delete previous to the drop database (with telling it to
include the change) it DIDN'T include the change (I assume because the
commit timestamp was slightly after the transaction timestamp) .. and if I
told it to stop a little later than that, it removed the files (because it
got to the drop database statement and stopped before the commit, but still
deleted the files). For some reason it ignored SELECT statements - I assume
those are not actually written into the wal files, and that would be why.
But .. that's only a guess. I'm not that educated with regard to the inner
workings of Postgres.. :)

For the meantime, until the patch is released, the method I have wrangled
to "get around the issue" is to actually restore twice ... the first time
using a timestamp and I record the xid reported in the pg_log that it
stopped before commit of.. Then re-restore by using the xid. That works,
keeps the most recent insert/update/delete, and DOESN'T delete files..
Kinda a pain, but it works.

Anyway .. thanks for the assistance. :)

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

> On 12/02/2014 03:50 PM, Joshua Boyd wrote:
>
>> 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 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.
>>
>>
> Re-read the initial post and realized you wanted the state of the
> recovered cluster to include the database that was dropped. In that case I
> would say stop the recovery just before the DROP DATABASE.
>
>
>> --
>> Joshua Boyd
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

--
Joshua Boyd