Index: openacs.org-dev/www/doc/misc/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs.org-dev/www/doc/misc/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs.org-dev/www/doc/misc/index.adp 15 Jan 2003 20:22:35 -0000 1.1 @@ -0,0 +1,18 @@ + +{Miscellaneous Documentation} +OpenACS Documentation + + + + + + + + + + + + +
DocumentAuthorLast Update
+ Migrating OpenACS 4 from PostgreSQL 7.1 to 7.2 + Roberto MelloJanuary 15th, 2003
Index: openacs.org-dev/www/doc/misc/openacs-pg-migration.txt =================================================================== RCS file: /usr/local/cvsroot/openacs.org-dev/www/doc/misc/openacs-pg-migration.txt,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs.org-dev/www/doc/misc/openacs-pg-migration.txt 15 Jan 2003 20:22:35 -0000 1.1 @@ -0,0 +1,142 @@ +This document describes how we did the migration of OpenACS.org +from PG 7.1 to PG 7.2.3, with both instances of PG running. +The migration was done by Roberto Mello and Dave Bauer. +Ben Adida installed PostgreSQL 7.2.3. Roberto wrote this document. + +1) Install PostgreSQL 7.2.3 + 1.1. Have a file .profile-7.2 to be sourced by every user that wanted + to use PG 7.2 instead of the default 7.1. The file had this: + + export PATH=/usr/local/pgsql-7.2.3/bin:/var/netsaint/bin:$PATH + export PGLIB=/usr/local/pgsql-7.2.3/lib + export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PGLIB + export PGDATA=/usr/local/pgsql-7.2.3/data + + 1.2. Install PL/pgSQL on template1 so it's used by other DBs + (createlang plpgsql template1) + +2) Install intarray + 2.1. Go to the intarray dir in the PG contrib source tree, and to + 'make install' + 2.2. Load the {PG}/share/_int.sql into template1 so it's used by + other DBs (psql -f _int.sql template1) + +3) Create users/database + 3.1. Create 'nsadmin' user as super user because of some function in + the dump (will change it back to regular user later). + 3.2. createdb -U nsadmin openacs.org + +4) Compile new AOLserver PG driver for 7.2.3 + 4.1. Install under AOLserver's bin directory as postgres-7.2.so + +5) Compile new OpenFTS for PG 7.2.3 + 5.1. Had to do 'make install-all-headers' in the PG source tree + 5.2. Had to ln -s /usr/local/pgsql-7.2.3/include/server /usr/local/pgsql-7.2.3/include/pgsql + because OpenFTS 0.2 looks for {PG}/include/pgsql/postgres.h + 5.3. ./configure --with-aolserver-src=/usr/local/src/as3.3/aolserver --with-pgsql=/usr/local/pgsql-7.2.3 --with-tcl=/usr/lib + --with-tcl=/usr/lib because of some strange setup on the openacs.org box). + 5.4. 'make' as regular user. 'make install' as root, so it can write + to the PG directories and install openfts .so's + 5.5. Go into aolserver directory and do 'make' (not make install, because + it would re-write our current nsfts.so) + 5.6. cp nsfts.so {aolserver}/bin/nsfts-new.so + 5.6. Load the {openfts}/func_pgsql/create_func.sql into new + openacs database. + + We don't need to copy the openfts .tcl files because we already + have them. We don't install OpenFTS 0.3.2 because we don't have + the new drivers/packages in our OpenACS install. That's for later. + +6) Make the pg_dump of old openacs.org + 6.1. (in 7.1) pg_dump -o openacs.org-dev > openacs-2002-01-14.dmp + 6.2. Edit dump and remove the creation of all PL/pgSQL handlers + 6.3. Also remove all statements that create int_* functions. + They belong to intarray, and were recreated when we loaded + the _int.sql from intarray, which has correct paths. + +7) Loading the dump of old database + 7.1. Load acs-kernel/postgresql/sql/postgresql.sql into new DB + (psql -U nsadmin -f postgresql.sql openacs.org) + 7.2. At this point you are ready to load the dump into new DB. + Let's redirect stderr to a file, so we can examine it for errors + later: + psql -U nsadmin -f openacs-2002-01-14.dmp openacs.org 2> errors.log + + 7.3. Look for ERROR string in errors.log + 7.3.1. There will be some "blah blah already exists" errors. That's normal. + They happen because we loaded acs-kernel's postgresql.sql. + 7.3.2. However, you'll see that 3 VIEWs fail to be recreated: + party_approved_member_map, party_element_map and wf_users_task + They are not recreated because of a bug in pg_dump where it + outputs parenthesis in the wrong order for VIEWs with UNION in + the definition (ugh!). AFAIK, this has been fixed in 7.3 pg_dump. + + So you have to load the following VIEWs into your new DB: + + create view party_approved_member_map + as select distinct segment_id as party_id, member_id + from rel_seg_approved_member_map + union + select distinct group_id as party_id, member_id + from group_approved_member_map + union + select party_id, party_id as member_id + from parties; + + create view party_element_map + as select distinct group_id as party_id, element_id + from group_element_map + union + select distinct segment_id as party_id, party_id as element_id + from rel_segment_party_map + union + select party_id, party_id as element_id + from parties; + + create view wf_user_tasks as + select distinct ta.task_id, + ta.case_id, + ta.workflow_key, + ta.transition_key, + tr.transition_name, + ta.enabled_date, + ta.started_date, + u.user_id, + ta.state, + ta.holding_user, + ta.hold_timeout, + ta.deadline, + ta.estimated_minutes + from wf_tasks ta, + wf_task_assignments tasgn, + wf_cases c, + wf_transition_info tr, + party_approved_member_map m, + users u + where ta.state in ( 'enabled','started') + and c.case_id = ta.case_id + and c.state = 'active' + and tr.transition_key = ta.transition_key + and tr.workflow_key = ta.workflow_key + and tr.trigger_type = 'user' + and tr.context_key = c.context_key + and tasgn.task_id = ta.task_id + and m.party_id = tasgn.party_id + and u.user_id = m.member_id; + +8) AOLserver setup + 8.1. (in AOLserver's config dir) + cp openacs.org-dev.tcl new-openacs.org.tcl + 8.2. Edit new-openacs.org.tcl: + 8.2.1. Modify port to 8888 so we can test + 8.2.2. Modify servername so we get different logs + 8.2.3. Modify PG driver line so it loads postgres-7.2.so + 8.2.4. Modify pools to use port 5433 (or whatever you set + your new PG to use) + 8.2.5. Modify db_name variable so it points to the new database + 8.2.6. Modify nsfts.so line so it loads nsfts-new.so + 8.3. Modify Tcl library param to point to old site's Tcl library. + +9) Start AOLserver + 9.1. Start in foreground so you can watch what happens. + 9.2. Fix any problems.