Extract transactions from wals ??
Hi,
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 ?
thanks,
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
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
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.albe@cybertec.at>
wrote:
Show quoted text
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 businesspeople 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
On Thu, 2019-11-21 at 17:07 +0100, Marc Millas wrote:
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 ??
Well, you asked for the contents of a table AS OF TIMESTAMP <something>.
That means you know which table and which timestamp.
So restore the PostgreSQL cluster to that timestamp, connect
and SELECT from the table.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Hi Laurenz,
I was writing select from ""table"" as a template. We have to do this for a
bunch of tables.
So, to my understanding, what you suggest is to PITR up to the first
timestamp, extract all meaningfull tables, and then pitr to the second
timestamp
so as to be able to script a kind of "diff" between the 2 to get what I
want.
Yes ?
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Thu, Nov 21, 2019 at 5:16 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Thu, 2019-11-21 at 17:07 +0100, Marc Millas wrote:
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 ??Well, you asked for the contents of a table AS OF TIMESTAMP <something>.
That means you know which table and which timestamp.
So restore the PostgreSQL cluster to that timestamp, connect
and SELECT from the table.Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Nov 21, 2019, at 9:35 AM, Marc Millas <marc.millas@mokadb.com> wrote:
Hi Laurenz,
I was writing select from ""table"" as a template. We have to do this for a bunch of tables.
So, to my understanding, what you suggest is to PITR up to the first timestamp, extract all meaningfull tables, and then pitr to the second timestamp
so as to be able to script a kind of "diff" between the 2 to get what I want.Yes ?
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com <http://www.mokadb.com/>On Thu, Nov 21, 2019 at 5:16 PM Laurenz Albe <laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>> wrote:
On Thu, 2019-11-21 at 17:07 +0100, Marc Millas wrote: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 ??Well, you asked for the contents of a table AS OF TIMESTAMP <something>.
That means you know which table and which timestamp.
So restore the PostgreSQL cluster to that timestamp, connect
and SELECT from the table.Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com <https://www.cybertec-postgresql.com/>
Are you by any chance logging sql at a sufficient level of detail to allow a restore to PIT and sql replay?
On Thu, 2019-11-21 at 17:35 +0100, Marc Millas wrote:
I was writing select from ""table"" as a template. We have to do this for a bunch of tables.
So, to my understanding, what you suggest is to PITR up to the first timestamp,
extract all meaningfull tables, and then pitr to the second timestamp
so as to be able to script a kind of "diff" between the 2 to get what I want.
Sure, you can do that.
The description of what you wanted to do was rather unclear, all I could
make out is that you want to query AS OF TIMESTAMP.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
I think he wants to see data from different tables at different timestamp
(like flashback query in Oracle). As per my understanding question here is
can PITR be done for specific table and for specific timestamp.
On Fri, Nov 22, 2019 at 2:37 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Thu, 2019-11-21 at 17:35 +0100, Marc Millas wrote:
I was writing select from ""table"" as a template. We have to do this
for a bunch of tables.
So, to my understanding, what you suggest is to PITR up to the first
timestamp,
extract all meaningfull tables, and then pitr to the second timestamp
so as to be able to script a kind of "diff" between the 2 to get what Iwant.
Sure, you can do that.
The description of what you wanted to do was rather unclear, all I could
make out is that you want to query AS OF TIMESTAMP.Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Yes !
We are looking for something providing a functionnality similar to
Oracle's :-)
Through PITR or a tool or extension around wals.
Still, as wals are containing enough info for replication to work,
It should be possible to extract from wals a list of objects that have been
written, and elements about what was written.
OS files written and position should be enough to provide this.
Something ?
thanks,
Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com
On Fri, Nov 22, 2019 at 11:02 AM Ganesh Korde <ganeshakorde@gmail.com>
wrote:
Show quoted text
I think he wants to see data from different tables at different timestamp
(like flashback query in Oracle). As per my understanding question here is
can PITR be done for specific table and for specific timestamp.On Fri, Nov 22, 2019 at 2:37 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:On Thu, 2019-11-21 at 17:35 +0100, Marc Millas wrote:
I was writing select from ""table"" as a template. We have to do this
for a bunch of tables.
So, to my understanding, what you suggest is to PITR up to the first
timestamp,
extract all meaningfull tables, and then pitr to the second timestamp
so as to be able to script a kind of "diff" between the 2 to get what Iwant.
Sure, you can do that.
The description of what you wanted to do was rather unclear, all I could
make out is that you want to query AS OF TIMESTAMP.Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com