|
Home > Archive > Slony1 PostgreSQL Replication > September 2005 > Need to recover DB with partial data...into a
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Need to recover DB with partial data...into a
|
|
| Philip Warner 2005-09-09, 9:27 am |
|
Sorry to be asking this question (in more ways than one), but we were
recently evaluating slony for our replication needs when our db server
died....good timing apart from the fact the we had killed the 'slon'
replication processes about a week ago.
The loss was fairly catastrophic, meaning 90% of our data was lost and
the DB disk is completely dead, but we do have partial backups of the DB
-- including the slony schema and data from the still-installed slony
triggers.
We also still had the old slave DB which we were able to 'failover' to
using slony. We did not replicate sequences, but we've bumped them up a
long way to avoid possible overlap with any data we do recover.
My question is: is there as way I can just restore the slony tables then
make slony apply those changes to the current master? Or generate a
script to do this? If the old master was still around, we should be able
to apply the changes to the old slave, but the old master is gone, and
the old slave is now a master.
Ideally, I'd like to just apply changes to a subset of the old tables:
looking at the data in the slony schema, it seems all I need is there...
Any help or suggestions would be appreciated.
| |
| Alan Hodgson 2005-09-09, 11:24 am |
| On Fri, Sep 09, 2005 at 11:54:34PM +1000, Philip Warner wrote:
> My question is: is there as way I can just restore the slony tables then
> make slony apply those changes to the current master? Or generate a
> script to do this? If the old master was still around, we should be able
> to apply the changes to the old slave, but the old master is gone, and
> the old slave is now a master.
>
> Ideally, I'd like to just apply changes to a subset of the old tables:
> looking at the data in the slony schema, it seems all I need is there...
Everything you need should really be in sl_log_1 (and sl_table to get the table
names). The only question I would have is how to get the correct ordering on
the sl_log_1 data (can you just use log_actionseq?).
This will generate the SQL (if the ordering is right?):
SELECT CASE WHEN sl.log_cmdtype = 'I' THEN 'INSERT INTO ' || st.tab_nspname || '.' || st.tab_relname || ' ' || sl.log_cmddata WHEN sl.log_cmdtype = 'U' THEN 'UPDATE ' || st.tab_nspname || '.' || st.tab_relname || ' SET ' || sl.log_cmddata WHEN sl.log_cmd
type = 'D' THEN 'DELETE FROM ' || st.tab_nspname || '.' || st.tab_relname || ' WHERE ' || sl.log_cmddata END || ';' FROM sl_log_1 sl JOIN sl_table st ON (sl.log_tableid=st.tab_id) ORDER BY sl.log_actionseq;
Doesn't handle DDL changes of course.
--
Politicians only fear one thing: peasants with guns
|
|
|
|
|