?hel kenal p?eval, R, 2006-03-03 kell 23:07, kirjutas Yuan HOng:
> I have a database on a Postgres 7.4 server with slony-1.0.5
> replication enabled. Now I want to the content of the database to
> another db-server with Postgresql 8.1 and no slony. I just want to
> move the data, not the replication information, i.e. without the slony
> schema.
> 
> There doesn't seem to be an straigtforward to do this. I tried backup
> only my data schema, then restore it on the 8.1 server. But since the
> tables in data schema all have slony triggers, the restore doesn't
> succeed.
> 
> When I backup the whole database including the slony schema, the
> restore complains that the slony shared libraries are not found.
> 
> So I tried to install slony-1.1.5 on the 8.1 server and then restore
> the database backup. Now I get the error during restore:
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  could not
> find function "_Slony_I_terminateNodeConnections" in file
> "/usr/lib/postgresql/8.1/lib/slony1_funcs.so"
> 
> It seems that the 1.1.5 library is not downward compatible with 1.0.5 schema.
> 
> Can someone kindly points out how to achieve the data migration? I
> think one solution is to drop the replication on the source 7.4
> database, and then do the backup. But since it is a production
> database, I can't do that. Any other ideas?

1) dump only database schema on 7.4 (pg_dump dbname -s > dbname.ddl)
2) create tmp database on same server or any with slony 1.0.5 installed
(createdb dbname_tmp)
3) load the schema into tmp database (psql dbname_tmp < dbname.ddl)
4) remove slony from tmp table
 psql dbname_tmp
 >>> select _your_slony_cluster.uninstallnode();
 >>> drop schema _your_slony_cluster cascade
5) dump the slony-less schema from dbname_tmp (pg_dump dbname -s >
dbname.ddl)
6) use the clean schema and data-only dump (pg_dump -a dbname) to
rebuild it on 8.1 server
 you may want to split the clean db schema so that table creation comes
before data load and indexes after

-----------
Hannu