Author: Helge

I upgraded our Open Journal Systems from 2.2.2 to 2.4.2 and afterwards we saw the email logs were gone. This was related to:

http://pkp.sfu.ca/support/forum/viewtopic.php?f=8&t=9140

My solution was this:
Established a new Postgresql server in a virtual enviroment (vmware, test-machine), and import the old and the new (upgraded) database.

# Log in to Prod server
ssh prodserver
# Dump the related and upgraded database to a sql file
pg_dump -U postgres -f /tmp/ojs_new.sql ojs_new
# Copy the file to a Test server where you can play around
scp ojs_new.sql username@my_vmware_test_server
# Log in to your Test server
ssh my_vmware_test_server
# Become Root, the system super user
sudo su -
# Become Postgres user, so that you can do all Postgresql stuff
su - postgres
# Create a user named "ojs"
createuser ojs
# Create the databases
createdb -O ojs ojs_new
createdb -O ojs ojs_old
# Import the databases to Postgresql on the Test Vmware server
psql -f ojs_old.sql ojs_old
psql -f ojs_new.sql ojs_new

ojs_old: was the original 2.2.2 database
and
ojs_new: was the upgraded 2.4.2 one

In order to fix the assoc_id numbers, I used this bash script:

myscript.sh:

#!/bin/bash
# Find first all log_id's in the old database (ojs_old)
ORIGLIST=`psql -t -d ojs_old -c "SELECT log_id FROM article_email_log" postgres`;
for i in $ORIGLIST;do
  OLD_ARTICLE_ID=`psql -t -d ojs_old -c "SELECT article_id FROM article_email_log WHERE log_id = '$i'" postgres`
  echo -e "psql -d boap_journals -c \"UPDATE email_log SET assoc_id = $OLD_ARTICLE_ID WHERE log_id = $i\" postgres"
done

The output from this script is sent to STDOUT. I redirected it to another script, and ran the script on our Production server.

ssh my_vmware_test_server
sudo su -
# Send the output of the script to file
./myscript.sh > runme-on-prod-server.sh
# copy the file to the Production server
scp runme-on-prod-server.sh username@prodserver:/tmp/
# log in to the Production server
ssh prodserver
# Become root, be careful!
sudo su -
cd /tmp/
# Run the script! (But! Remember to do a PG database dump before, so you can restore if something goes terrible wrong!)
# become Postgresql super user
su - postgres
pg_dumpall -f mybackup_just_in_case.sql
# become root again
exit
./runme-on_prod-server.sh