[OGo-Users] Migration of ogo

[Helge Hess]

On 03.04.2008, at 16:22, Peter Brueckner wrote:
> pg_dump -aD -f ogo.sql -U OGo OGo
> and files:
> tar cvf ogo-files.tar .
>  in /var/lib/opengroupware.org

.. this always worked just fine for me, w/o any of the tweaks you are  
doing (takes ~1h). You need to load the dump, maybe perform UTF-8  
conversion, and add the schema additions (if you want, seems to be  
100% compat).

If you have a database dump, why do you run 'database_setup_psql.sh'?  
Thats sounds VERY VERY wrong and probably explains 95% of the mess you  
experienced.


> There are some questions:
> 1. Nobody uses the constraints? With the actual postgres
> release the inherits-feature is not usable with constraints!
> see: http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
>  paragraph: 5.8.1 caveats

Nothing changed here from 7.4 AFAIK. The link just says that  
constraints are not automatically inherited to child tables (hence,  
you need to define them on the child tables as well).

Anyways, the pg-build-schema.psql.constraints indeed includes  
constraints which do not work. Anymore? Most likely never did!  
Possibly 7.4 allowed to define the CONSTRAINT but didn't actually  
check it (this was common behaviour in very early PGs). Eg:
---snip---
ALTER TABLE company_value ADD CONSTRAINT
    company_value2company FOREIGN KEY (company_id)
     REFERENCES company(company_id);
---snap---


> 2. Is there a regular way for upgrading?

Not between unstable releases. Hence, no, because we only have one  
official stable release :-) And you didn't even use that (1.0.0), but  
a beta (1.0b..) :-)

But usually its just a dump/load/done. So far the DB schema has been  
superstable:
   http://svn.opengroupware.org/OpenGroupware.org/trunk/Database/PostgreSQL/ChangeLog

No surprises.


> 3. Is there a way for checking the constraints (possibly
>  other things) in the database.

Someone probably needs to fix the constraints script for PostgreSQL.  
Probably some REFERENCES constraints could be replaced with CHECK  
constraints.
Maybe the file got b0rked in the 2005-03-22 change, don't know.


> 4. We are working with OGO/Skyrix for years and are very
> comfortable but every upgrade is like hell

Ah, common. So far I'm not convinced that anything is inherently  
wrong. Why would you initialize a fresh database and then load a dump  
over it? This just doesn't make any sense, really.

Greets,
   Helge

PS: but sure, a smart upgrade script which checks the OGo environment  
would be quite nice. (since the process is so simple, no one bothered  
yet, I guess).
-- 
Helge Hess
http://www.helgehess.eu/