Forum OpenACS Q&A: Problems with moving db to another machine

Hello,

I decided to move my openacs db to another machine (48mb ram shared between aolserver and postgres was not nice). The problem is that I cannot seem to move the database in a consistent way. The database is the RPM version of postgresql 7.02. Anyway, I have tried

  • Shutting down postmaster on both machines, copy the db directory manually, restart and add the database to pg_database. It didn't work:
    mydbname=# select * from users;
    ERROR: RelationBuildTriggers: unexpected record found for rel users
  • I have tried various pg_dump options:
    pg_dump mydbname > dump.db
    Problem: doing (in psql)
    =# o relations
    =# d
    on both db:s and diff:ing I see that some views have been recreated as tables. example: users_contact_null
  • Creating the openacs datamodel separately on the new db, i.e. ./load- geo-data and psql -e load-data-model.sql or whatever they were called. Then dump the data with
    pg_dump -a -D mydbname > dump.db
    This seemed to work, but I couldn't log in to acs. In the aolserver logs I found something about a ns_db 0or1row returning more than 1 row, called from im_user_is_intranet_admin or something similar.
  • pg_dump -c -D mydbname >dump.db
    Well, this didn't work either. Obviously I didn't create the datamodel in this scenario.. In all the pg_dump things I tried, I loaded the dump back into the database using the same command as in the pg_dump man pages, ie.
    psql -e mydbname < dump.db

Hmmm... At the same time as writing this fine ol´ piece of whinery, I succeeded in logging in to openacs using the second model. But trying to access the employee page (in the intranet module) bangs, I guess it's trying to access those blobs that apparently pg_dump doesn't know how to handle. Yes, I know there is a beta version of pg_dump that supports blobs, but I haven't downloaded it (yet)

Any suggestions?
Collapse
Posted by Don Baccus on
The pseudo-blobs used by OpenACS are fully dumpable, I've moved dumps from machine-to-machine with no problem.  They aren't actual PG large objects.

One problem with your #2 scenario is that you're expecting the dump to  equal what was input from the datamodel, while what's dumped is actually a reflection of what PG did to your datamodel.  Views, for instance, are implemented as a subset of the generalized rules system,  so won't show up as "views" but rather the rules generated in order to implement the semantics of an SQL view.  Likewise contraints will be dumped in a canonical form, etc.

So don't let this difference throw you.

Since you could log in, obviously much of the database got moved over correctly, and if you got no errors using psql -e then it all did.

What kind of error did your server log show trying to access the employee page?  If there was an explicit reference to type BLOB then you probably just ran across an unported page in OpenACS, i.e. a bug, unrelated to moving the database.

If it indeed is an OpenACS pseudo-blob remember that they need to be spooled to disk and that AOLserver needs to be able to write to /tmp (which it should be able to do).  Check your logfile for the exact error that's being generated and we can help you better.