Re: Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files - Mailing list pgsql-general

From Joshua Boyd
Subject Re: Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files
Date
Msg-id CAAQP4vcdGnb=YG2GKJcPPUHNwNFnecwob6pxpAjrB+XsZo3xJQ@mail.gmail.com
Whole thread Raw
In response to Re: Postgres 9.2 PITR testing to before DROP DATABASE ends up removing file system files  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
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.klaver@aklaver.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.klaver@aklaver.com



--
Joshua Boyd

pgsql-general by date:

Previous
From: M Tarkeshwar Rao
Date:
Subject: Re: FW: getting error while running sql on mm_activealrm table
Next
From: Eric Svenson
Date:
Subject: Re: Fwd: Problem with pg_dump and decimal mark