Christopher Browne wrote: > Alex Haugg <[EMAIL PROTECTED]> 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.
[EMAIL PROTECTED]:~$ datname="mydatabase"; clustername="myclustername"
[EMAIL PROTECTED]:~$ 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.
[EMAIL PROTECTED]:~$ ./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.
[EMAIL PROTECTED]:~$ psql -Uroot mydatname < schema.sql
5. Load the Data
[EMAIL PROTECTED]:~$ 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
deslon.pl
Description: Perl program
_______________________________________________ Slony1-general mailing list [email protected] http://lists.slony.info/mailman/listinfo/slony1-general
