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

On 12/03/2014 03:52 PM, Joshua Boyd wrote:
> 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.

Yea, DROP DATABASE is not transactional(or maybe semi-transactional) and
lives in a gray area.

>
> Anyway .. thanks for the assistance.  :)
>



--
Adrian Klaver
adrian.klaver@aklaver.com