Forum OpenACS Q&A: Reloading a databasse

Before implementing Don's tips on backing up the database, I tried to verify that the dumped database would reload properly. Hence I did first

pg_dump my_database > my_database_bkup.

Next I created a new database my_database_test and did

psql -d my_database_test -f  

Unfortunately I got a number of errors on the reload. By the way I did all of the above as nsadmin. Some of the errors seem to be due to the order in which the orginal dump took place. Some create table commands required functions (eg sysdate) to be already defined. Others I am not sure what produced the errors.

Are there some hints that I am missing to do the dump and reload properly?

Here is some further data. I proceeded as before and I dumped the database to my_database_bkup. I broke the my_database_bkup file into 6 seperate files: load.1, load.2, load.3, load.4, load.5, and load.6.

The file load.1 has all the CREATE FUNCTION commands. The file load.2 has all the CREATE SEQUENCE and CREATE TABLE commands. The file load.3 has all the COPY commands. The file load.4 has all the index creation commands. File load.5 has all the CREATE CONSTRAINT TRIGGER commands. The remaining file load.6 has the CREATE RULE commands. With the exception of load.1 and load.2, which are reversed, this is the same order that the output appears in the dump file.

Loading load.1 through load.5 went fine with no errors. However, loading load.6 produced a number of errors. For example:

psql:load.6:9:ERROR: select rules target entry 7 has different size from attribute read_permission_p

This rule is called _RETgeneral_permissions_grid. Does anyone understand what is happening here?

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.