am Tue, dem 27.02.2007, um 17:44:06 +0100 mailte Markus Schulz folgendes: > Hello, > > i have some trouble with dumping and restoring a database with > postgresql 7.4.7-6sarge4 (debian sarge). Is the new database also 7.4? Why not 8.1 oder 8.2? > > Some tables have plpgsql-functions as DEFAULT contraints. But these > functions are dumped after the CREATE TABLE command. Now these tables > can't be created on restore. IIRC a know issue with 7.x, but solved (i hope...) in 8.x. If you upgrade to such a version, you should use the pg_dump(all) from this against the old database. > > If i'm dumping only structure and import twice in a new and empty > database (this works), i can't restore the data-only dump. Cause in the > data-only dump the table order was wrong in respect to FOREIGN KEY > constraints and the constraints are now active (instead of a full-dump, > where constraints will be created after data was inserted). > > Why DEFAULT (and NOT NULL) constraints would'nt be added after data > insertion? (like Primary-Key and Foreign-Key) > > What can i do to enforce the order of plpgsql functions prior to all > table structures? You can use pg_restore with -l to generate a listfile for all objects in the database. Then you can reorder this ($EDITOR) and then use -L to use this ordered listfile to enforce the right order of objects. I hope this helps you. (man pg_restore) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
