Re: Extract transactions from wals ??

Hi Laurenz,

you say "extract the data you need"
That is exactly the point of my question, as the PITR step was obvious.
How to guess "what is the data" I need ??

The timestamp stuff within Oracle was providing exactly that: get all mods
from a given table that did occur within a given timeframe.
Quite clearly, an option, for the future, would be to modify ALL tables and
add a timestamp column and a trigger to fill/update it.
a tad boring to do...
This is why I was wondering if it exits another possibility, like getting,
from the wals, a list of modify objects.

so ??

regards,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Thu, Nov 21, 2019 at 3:54 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Thu, 2019-11-21 at 14:50 +0100, Marc Millas wrote:
> > due to a set of bugs and wrong manip, an inappropriate update have been
> done into a production DB.
> > After that, quite a long set of valuables inserts and updates have been
> done and needs to be kept.
> > Obviously getting a backup and applying pitr will get us just before the
> offending update.
> > Now, we need to find a way of extracting, either from the ex prod db, or
> from the wals, the "good" transactions to be able to re-apply them.
> >
> > This did already happen on a Prod Oracle DB, and recovering was
> possible with a :
> > select * from table_name AS OF TIMESTAMP TO_TIMESTAMP('09052019
> 0900','MMDDYYYY HH24MI');
> > to get most things done after the problem.
> > As we are currently moving out of Oracle, we must prove to the business
> people that our new postgres env is fine.
> > So, ... any idea ?
>
> Sure.
>
> Restore a backup and perform point-in-time-recovery.
> Then extract the data you need.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>