Darcy

Thanks for the info - I did try updating the SL_TABLE and SL_SEQUENCE 
tables using the following scripts instead (since I couldn't recreate 
the dump file (for this test case)):

-- update table entries
-- seq_comment contains my table names
-- which are unique across schemas/namespaces
UPDATE _CLUSTERNAME.sl_table
SET tab_reloid = pgc.oid
FROM _bpixreplicate.sl_table T, pg_catalog.pg_class pgc
WHERE PGC.relname = T.tab_comment;

-- update sequence entries
-- seq_comment contains my sequence names
-- which are unique across schemas/namespaces
UPDATE _CLUSTERNAME.sl_sequence
SET seq_reloid = pgc.oid
FROM _bpixreplicate.sl_sequence S, pg_catalog.pg_class pgc
WHERE PGC.relname = S.seq_comment;

For some reason (that I don't understand) I had to drop the unique 
constraints on the reloid columns from both the SL_TABLE and SL_SEQUENCE 
tables before applying the above scripts - although the new oids were 
unique!

After doing that the uninstallnode still failed - although I'm pretty 
sure the OIDs were updated correctly. Here's the error:

<stdin>:9: PGRES_FATAL_ERROR select "_CUSTERNAME".uninstallNode();  - 
ERROR:  Slony-I: Table with id 4006 not found
CONTEXT:  PL/pgSQL function "uninstallnode" line 14 at perform

Then I wondered about deleting all records from the SL_TABLE and 
SL_SEQUENCE tables. This I did. Running the uninstallnode script 
(through slonik) now worked OK (unsurprisingly).

The slony replication schema and all the triggers on my tables appear to 
have gone too. Since the dump was from the master, there are no tables 
that need to be unlocked...

Is this method of producing a new clean version of the database from a 
dump of the master OK?

This would would be a useful trick and probably be the fastest way to 
get new database from the master dump without any slony artfifacts left 
over.

John Sidney-Woollett

Darcy Buskermolen wrote:

> On February 9, 2005 09:08 am, John Sidney-Woollett wrote:
> 
>>Thanks for the reply.
>>
>>Can you explain what the update statement needs to be - I'm confused by
>>the meaning of tab_reloid - is it "table real OID"?
> 
> tab_relod = Table Relation OID (this is the OID in pg_class for this the 
> relation)
> 
>>If a update script were available that could be applied before
>>uninstalling the node, this would be a useful thing, regardless of
>>whether it's coming in 1.1 or not.
>>
>>I'm sure that I'm not the only person with this issue...
>>
>>How complicated would the update statement be, and can you use a single
>>statement or function to fix all the tab_reloids in one go?
> 
> 
> The following should work, but I havn't tested it.
> 
> Before you dump:
> 
> CREATE TABLE "_CLUSTERNAME".repair_config_tab(
>   reloid OID,
>   tab_relname NAME,
>   tab_nspname NAME
> );
> 
> INSERT INTO  "_CLUSTERNAME".repair_config_tab
>   SELECT
>     slt.tab_reloid,
>     pgc.relname,
>     pgn.nspname
>   FROM
>     "_CLUSTERNAME".sl_table slt,
>     pg_catalog.pg_class pgc,
>     pg_catalog.pg_namespace pgn
>  WHERE
>     slt.tab_reloid = pgc.oid
>     AND pgc.relnamespace = pgn.oid
> ;
> 
> CREATE TABLE "_CLUSTERNAME".repair_config_seq(
>   reloid OID,
>   seq_relname NAME,
>   seq_nspname NAME
> );
> 
> INSERT INTO  "_CLUSTERNAME".repair_config_seq
>   SELECT
>     slq.seq_reloid,
>     pgc.relname,
>     pgn.nspname
>   FROM
>     "_CLUSTERNAME".sl_sequence slq,
>     pg_catalog.pg_class pgc,
>     pg_catalog.pg_namespace pgn
>  WHERE
>     slq.seq_reloid = pgc.oid
>     AND pgc.relnamespace = pgn.oid
> ;
> 
> on your freshly restored DB:
> 
> UPDATE "_CLUSTERNAME".sl_table
>   SET
>     tab_reloid = pgc.oid
>   FROM
>     "_CLUSTERNAME".repair_config_tab rct,
>     pg_catalog.pg_class pgc,
>     pg_catalog.pg_namespace pgn
>   WHERE
>       pg_catalog.quote_ident(rct.tab_relname)=pg_catalog.quote_ident(pgc.relname)
>     AND
>       pg_catalog.quote_ident(rct.tab_nspname)=pg_catalog.quote_ident(pgn.nspname)
>     AND pgc.relnamespace = pgn.oid
>     AND rct.reloid = tab_reloid;
> 
> UPDATE "_CLUSTERNAME".sl_sequence
>   SET
>     seq_reloid = pgc.oid
>   FROM
>     "_CLUSTERNAME".repair_config_seq rcs,
>     pg_catalog.pg_class pgc,
>     pg_catalog.pg_namespace pgn
>   WHERE
>       pg_catalog.quote_ident(rcs.seq_relname)=pg_catalog.quote_ident(pgc.relname)
>     AND
>       pg_catalog.quote_ident(rcs.seq_nspname)=pg_catalog.quote_ident(pgn.nspname)
>     AND pgc.relnamespace = pgn.oid
>     AND rcs.reloid = seq_reloid
> ;
> 
> at this point you should bae able to run uninstall node() against this freshy 
> restored database and have it return to a virgin state.
> 
>>Can you give me some more pointers on exactly what needs doing? I'm not
>>really up to speed on OIDs, slony and the system catalogs.
>>
>>Thanks
>>
>>John Sidney-Woollett
>>
>>Darcy Buskermolen wrote:
>>
>>>On February 9, 2005 08:29 am, John Sidney-Woollett wrote:
>>>
>>>>Following some testing, it appears that this strategy may well work with
>>>>the schema only dump.
>>>>
>>>>Still not sure if it's safe though?
>>>>
>>>>When trying to remove the node information after importing a full
>>>>(schema + data dump), I get as error reported by slonik which appears to
>>>>halt the removal of the replication info:
>>>>
>>>><stdin>:9: PGRES_FATAL_ERROR select "_bpreplicate".uninstallNode();  -
>>>>ERROR:  Slony-I: Table with id 4006 not found
>>>>CONTEXT:  PL/pgSQL function "uninstallnode" line 14 at perform
>>>>
>>>>Looking at altertablerestore(integer) I suspect that the table cannot be
>>>>found because the tab_reloid cannot be found on the pg_catalog.pg_class
>>>>table.
>>>
>>>This is correct.
>>>
>>>
>>>>Why does this NOT work for a full dump when it appears to work CORRECTLY
>>>>for a schema only dump?
>>>
>>>Because there are no rows in the tables that hold replication info.
>>>
>>>
>>>>Is it better to create a dump file using the "-F c" switch and use
>>>>pg_restore - would that keep the original OIDs?
>>>
>>>No this won't help because the OIDS don't get kept for tables themself.
>>>
>>>
>>>This whole problem has been addressed in the upcomming 1.1, through the
>>>slonik command REPAIR CONFIG
>>>
>>>
>>>What you could do to your newly restored copy is a bunch of updates to
>>>sl_table and sl_sequence to update the reloid's to reflect the new oids
>>>of the relations. Then do your uninstall node.
>>>
>>>
>>>>Thanks for listening again.
>>>>
>>>>John Sidney-Woollett
>>>>
>>>>John Sidney-Woollett wrote:
>>>>
>>>>>We need to set up:
>>>>>
>>>>>1) a test version of our database from a recent (full) dump file
>>>>>
>>>>>2) and prepare a new slave node for replication using slony
>>>>>
>>>>
>>>>>from full and schema only dumps of the master node where the full
>>>>
>>>>>database is dumped with the slony replication schema + triggers.
>>>>>
>>>>>Is it safe to take these dump files, import the dump file, and then run
>>>>>a slonik script to remove the cluster replication info from the new test
>>>>>DB and slave nodes?
>>>>>
>>>>>Or will this damage our existing cluster?
>>>>>
>>>>>Here's what we're attempting (to build the test database):
>>>>>
>>>>># DUMP FULL DATABASE
>>>>>/usr/local/pgsql/bin/pg_dump $SRC_DATABASE > $BACKUPFILE
>>>>>
>>>>># IMPORT THE DUMP FILE
>>>>>psql $DEST_DATABASE < $BACKUPFILE
>>>>>
>>>>># Use the following script to remove all the slony replication info
>>>>>#!/bin/bash
>>>>>
>>>>>/usr/local/pgsql/bin/slonik << _END_
>>>>>
>>>>># define the cluster namespace
>>>>>cluster name = $CLUSTERNAME;
>>>>>
>>>>># define the new test or slave node connection information
>>>>>node 999 admin conninfo = 'dbname=$DEST_DATABASE host=$DEST_HOST
>>>>>user=postgres';
>>>>>
>>>>># uninstall the node
>>>>>uninstall node (id=999);
>>>>>
>>>>>---------------------
>>>>>
>>>>>The thing is that this appears to have worked OK, even though the dump
>>>>>came from the master db (node=1), and we removed the replication info
>>>>>using node=999 (ie a different node number). The replication schema is
>>>>>now gone, and the triggers on replicated tables are also gone.
>>>>>
>>>>>Is this an OK strategy for getting to use a dump file quickly and
>>>>>easily. Trying to extract the slony info from a full dump is
>>>>>mindbendingly tedious and error prone otherwise...
>>>>>
>>>>>Can anyone confirm that this is OK? And thanks for staying with me so
>>>>>far... ;)
>>>>>
>>>>>John Sidney-Woollett
>>>>>
>>>>>
>>>>>
>>>>>_______________________________________________
>>>>>Slony1-general mailing list
>>>>>Slony1-general at gborg.postgresql.org
>>>>>http://gborg.postgresql.org/mailman/listinfo/slony1-general
>>>>
>>>>_______________________________________________
>>>>Slony1-general mailing list
>>>>Slony1-general at gborg.postgresql.org
>>>>http://gborg.postgresql.org/mailman/listinfo/slony1-general
>>
>>_______________________________________________
>>Slony1-general mailing list
>>Slony1-general at gborg.postgresql.org
>>http://gborg.postgresql.org/mailman/listinfo/slony1-general
> 
>