Slony1 PostgreSQL Replication - Re: Need to recover DB with partial data...into

Author

Re: Need to recover DB with partial data...into

Jan Wieck

2005-09-09, 8:24 pm

On 9/9/2005 9:54 AM, Philip Warner wrote:

> 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.

That might not have been such a smart idea, at least not at that moment.
I understand why something like this is done in the heat of the battle,
but a better course of action would have been to restore the the slony
tables into a new DB, then change the conninfo string in sl_path to
point to that and start slon for the slave. It should have picked up and
replicated whatever was saved with that backup.

The problem is that after the failover, the information to which SYNC
point the old slave had already applied the changes is lost. There might
be older than that update information in sl_log_1.

>
> 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.

If this was a 1.1 Slony, lets see.

I would create a similar setup on test systems. Just identical schema,
no data. Stop both slon's once the subscribe is complete. data-only dump
and resture the current master into the test-slave. Data-only dump and
restore the test-masters slony tables sl_event and sl_log_1 with the
contents from your dump (the partial one from the old master). Now hack
sl_confirm and sl_setsync on the test-slave so that it matches the
desired first SYNC to pick up and start the test-slave slon process with
the file-log-shipping option. The resulting SQL scripts are all yours.

Good luck

Jan

--
#===================
====================
====================
===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#===================
====================
=========== JanWieck- bwPqjjyvM7QAvxtiuMwx
3w@public.gmane.org #

Philip Warner

2005-09-10, 3:23 am

Jan Wieck wrote:

> The problem is that after the failover, the information to which SYNC
> point the old slave had already applied the changes is lost. There
> might be older than that update information in sl_log_1.

Thankfully the very first statement is an insert for what would have
been the next ID in a table; so it looks like it sl_log_1 was up to
date. We're just lucky we even have the slony tables (I did not know
they were being backed up!).

> If this was a 1.1 Slony, lets see.
>
> I would create a similar setup on test systems. Just identical schema,
> no data. Stop both slon's once the subscribe is complete. data-only
> dump and resture the current master into the test-slave. Data-only
> dump and restore the test-masters slony tables sl_event and sl_log_1
> with the contents from your dump (the partial one from the old
> master). Now hack sl_confirm and sl_setsync on the test-slave so that
> it matches the desired first SYNC to pick up and start the test-slave
> slon process with the file-log-shipping option. The resulting SQL
> scripts are all yours.

This sounds like an excellent plan. The previously suggested SQL
approach was producing results, but getting slony to do the work for me
is appealing.

Thanks.

Philip Warner

2005-09-12, 7:26 am

Jan Wieck wrote:

> I would create a similar setup on test systems. Just identical schema,
> no data. Stop both slon's once the subscribe is complete. data-only
> dump and resture the current master into the test-slave. Data-only
> dump and restore the test-masters slony tables sl_event and sl_log_1
> with the contents from your dump (the partial one from the old
> master). Now hack sl_confirm and sl_setsync on the test-slave so that
> it matches the desired first SYNC to pick up and start the test-slave
> slon process with the file-log-shipping option.

Something's not quite working for me here; no data is being updated (I
am trying to send to the slave db rather than log files -- hope that's
not a problem).

Do I need to hack the sl_* tables on the slave to make sure that the
tables and sets match the original replication table IDs etc?

Philip Warner

2005-09-12, 9:25 am

Philip Warner wrote:

>Jan Wieck wrote:
>
>
>
>
>Something's not quite working for me here;
>
>
For posterity (and anyone else who ends up in my particular hell) you
also need to make sure that the sl_table.tab_id matches the original
tab_id from the event table (which won't be the case unless your slony
scripts survived or are generated in a standard way that works across db
instances); if necessary you can load the sl_table table from the old
db, but it will have the wrong tab_reloid. So you then run:

update sl_table set tab_reloid = c.oid from pg_class c, pg_namespace
n where
c.relname = sl_table.tab_relname and n.nspname = tab_nspname and
n.oid = c.relnamespace;

to make sure they get the right reloid. And you probably need to do this
on both master and slave (not sure -- certainly slave).

Sponsored Links