Well folks I think I understand alot better what is wrong. Why the output of pg_dump can't loaded without hand editing. There are two basic problems.
The first one is the order that pg_dump dumps things in. This is not too hard to fix. I added a trace function to the pg_dump that adds comments like
-- From Trace: start dumpRules
From this it is easy to write a Perl script that rewrites the pg_dump output to put things in the order needed to recreate the database. Unfortunately there is a second problem. The output of the dumpRules function in pg_dump creates an output that cannot be loaded without errors. These rules are created originally from the views. So that a view named user_group_member_field_mapping should produce a rule named "_RETuser_group_member_field_mapping." There are two related bugs. The name of the rule is truncated when parsed during the creation of the view if it is too long. (e.g. "_RETuser_group_member_field_mapping." becomes "_RETuser_group_field_map") This produces an error when reloaded. The second problem is the implementation of the rule can be loaded as view but produces errors when loaded as is. My work around for this is a Perl script that modifies the output of dumpRules so that they become a combination of DROP TABLES and CREATE VIEWS instead. So for example:
DROP TABLE user_group_member_field_mapping;
CREATE VIEW user_group_member_field_mapping .... blah... blah ...blah;
The bottom line is that I can basically create a Perl script that takes the output of pg_dump and creates files that can be reloaded without errors. I am still testing it to see whether one gets back the same database.
