Christopher Browne wrote:
> Alex Haugg <haugg at ...> writes:
> > i have a tiny problem,
> >
> > i need a db dump command without slony configurations (without:
> > slony_cluster, slony functions and without slony trigger).
> >
> > thanks for your answer,
> 
> If it is acceptable to do this in two parts, there are two tools that
> can be used:
> 
> 1.  tools/slony1_extract_schema.sh is a script that extracts the user
> schema for a Slony-I node in the original state with all the
> Slony-I-related "cruft" removed.
> 
> You could run that against the "master" node, and that will give you a
> suitable schema.
> 
> 2.  You could subsequently run "pg_dump --data-only" against a node
> (could be any node) and that will give you a dump of all the data.
> The dump will include Slony-I-internal "cruft", but since the schema
> doesn't have those tables, the data won't load :-)
> 
> Ideally, you would split apart the schema from #1 into two pieces:
>   - Firstly, all the CREATE TABLE DDL;
> 
>   - Then, you would load the data from #2...
> 
>   - Finally, you would run the remainder of the schema from #1, to
>     create indexes, triggers, and such like.


I've been working on putting this into use at my site.  I ran into a
problem loading the data-only dump because it contained an extra
_Slony-I_* column for my serial tables (tables without a primary key
where Slony adds its own sequence as an extra column).  The DDL
extracted using slony1_extract_schema.sh does not include this column,
so the data fails to load into these tables.

I wrote a script, deslon.pl (attached), to strip out this extra column
from serial tables.  With that in mind, here's the full procedure I'm
using to extract data from a subscriber node and load it into a new
database:

1. Extract the Schema 
Use tools/slony1_extract_schema.sh to extract the DDL from the origin
node.  Save these schema-only statements to file "schema.sql"

2. Data-Only Dump 
On the subscriber database, use pg_dump to get create a data-only file
"data.sql" that can be loaded into a skeleton database.

user at subscriber:~$ datname="mydatabase"; clustername="myclustername"
user at subscriber:~$ pg_dump -Uroot --data-only \
                      --exclude-schema="_$clustername" \
                      --disable-triggers \
                      $datname \
              > /tmp/$datname.data-only.sql

3. Clean the Dump 
Any serial tables in the database will still have slony cruft in the
dump file because Slony adds a unique sequence to non-indexed tables.
This Slony column must be removed from all serial tables in the dump
file.

The perl script deslon.pl will remove this column from all tables. 

user at anyhost:~$ ./deslon.pl "myclustername" < data-only.sql >
data-only.deslon.sql

4. Load the Schema 
Prerequisites:

      * postgresql installed
      * db created and empty
      * db roles created
      * tablespaces created

Load the table definitions and other DDL into an empty database by
applying the schema.sql file.

user at host:~$ psql -Uroot mydatname < schema.sql

5. Load the Data 

user at host:~$ psql -Uroot mydatname < data-only.deslon.sql



Hope this helps.  This would be a good wiki topic.  Is there a Slony
wiki for community documentation topics like this?

Dane
-------------- next part --------------
A non-text attachment was scrubbed...
Name: deslon.pl
Type: application/x-perl
Size: 701 bytes
Desc: not available
Url : http://lists.slony.info/pipermail/slony1-general/attachments/20080419/65a4ac7b/deslon.bin