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!


>>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. 
>>>      
>>>
>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 at gborg.postgresql.org
>http://gborg.postgresql.org/mailman/listinfo/slony1-general
>
>
>  
>