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

-- 
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx:  250.763.1759
http://www.wavefire.com