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.