| Philip Warner 2005-09-13, 9:24 am |
|
Another posterity-piece (and because I seem to like talking to myself);
the story is not all over if the slon daemon on the master was stopped
prematurely (in our case 4 days prior to the crash). Since it is
responsible for writing periodic SYNC records to the sl_event table,
they will all be missing.
The, I hope final, step in this process is to work out which sl_log_1
records have not got corresponsing sl_event records, then write sl_event
records for them on the master (and make really sure that the
sl_event_seq sequence is set to a value greater than the last restore
sl_event record).
If you forget this last step then not all your sl_log_1 records will be
processed by the slave. And you can't use the helpfully named
"generate_syncs.sh" since that uses GetLastXid (or somesuch name) to get
the most recent XID on the master -- but we're running is a new
database, so the XID will be different (most likely smaller).
My advice to anyone with a smaller database than ours is toconsider
using the query suggested by Alan hodgson:
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_cmdtype = '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;
....in our case we had 5GB of logs, and wanted slony to break up the load.
I hope this is of use to noone. And I really hope it's my last addendum!
>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).
>
>
>
>
> ____________________
____________________
_______
>Slony1-general mailing list
>Slony1-general- AuKwsB3Fm+ugFIWk8tvy
RWD2FQJk+8+b@public.gmane.org
>http://gborg.postgresql.org/mailman.../slony1-general
>
>
>
>
|