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"?

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?

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