Forum OpenACS Q&A: Response to Backups with OACS4-5

Hi Ola,

Using OpenACS 4.5 beta 1 with Postgres 7.1.3. The steps that I use to backup and restore.

Backup

1. pg_dump -d [db_name] > [dump_file]

Restore

1. createdb [db_name]
2. createlang plpgsql [db_name]
3. psql -d [db_name] -f [acs_root]/packages/acs-kernel/sql/postgresql/postgresql.sql
4. psql -d [db_name] -f [dump_file]
5. psql -d [db_name] -f patch.sql

My patch.sql contains this:

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;

Before doing restore step #4 you want to check the "connect" string of the dump file and make sure it the correct user. You may also want to use "psql -d [db_name] -f [dump_file] 2> error.log". The after the restore do a "grep -i error error.log | grep -v "already exists", you should get 3 errors which the patch will fix. Good luck I hope this has been helpful to you and others with the same problem.