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.

Why does this NOT work for a full dump when it appears to work CORRECTLY 
for a schema only dump?

Is it better to create a dump file using the "-F c" switch and use 
pg_restore - would that keep the original OIDs?

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