[freeside-users] Upgrade database 1.5.8 => 1.7.2

[Richard Siddall]

John Saunders wrote:
> I've installed 1.7.2 on a new machine and now wish to migrate the database
> from 1.5.8.

You'll need to refer to these pages in the wiki while working through this:

Upgrading to 1.7:
http://www.freeside.biz/mediawiki/index.php/Freeside:1.7:Documentation:Upgrading
Disaster recovery:
http://www.freeside.biz/mediawiki/index.php/Freeside:1.7:Documentation:DisasterRecovery

You're in the same situation as someone who is trying to recover from a 
disaster by installing 1.7.2 on a new machine, except that you can still 
get data off the old machine.

I think the steps are:

1/ Dump the database on the 1.5.8 machine.  If you haven't made any 
changes since freeside-daily last ran, you can just use the backup it 
made.  Otherwise turn off anything that can modify the database and run, 
as the freeside user, "pg_dump $database > /path/to/backup.sql"

2/ Copy the database dump and all the configuration files from the 1.5.8 
machine to the 1.7.2 machine, including the dbdef file.  (Not just the 
contents of /usr/local/etc/freeside/conf*, but most files under 
/usr/local/etc/freeside; you don't need the Mason cache contents.)  Make 
sure the ownership of the files stays the same.

3/ Start PostgreSQL on the 1.7.2 machine and follow the steps in the 
disaster recovery guide to create a freeside database user and an empty 
freeside database:
	su postgres -c 'createuser -P -A -d freeside'
	su freeside -c 'createdb -E sql_ascii freeside'

4/ Load your database dump into the 1.7.2 machine:
	su postgres -c 'psql freeside < /path/to/backup.sql'

Your installation should now be in about the same state as if you had 
installed 1.7.2 over an existing 1.5.8 installation.  Follow the steps 
in the "Upgrading to 1.7" wiki page, except that you shouldn't have to 
actually install anything.  So, something like:

5a/ "Apply the following changes to your database:
ALTER TABLE cust_pay_batch ADD COLUMN payinfo varchar(512);" and so on.

5b/ "Apply the following changes to your database (if you have a paymask 
column): UPDATE cust_main SET paymask = NULL WHERE paymask LIKE 'x%';"

5c/ "As the freeside UNIX user, run freeside-upgrade username, passing 
the username of an internal Freeside user."

5d/ "ACL bootstrapping:"

5e/ "If you have any records in the cust_tax_exempt table, you *MUST* 
migrate them to the new cust_tax_exempt_pkg table. An example script to 
get you started is in bin/fs-migrate-cust_tax_exempt - it may need to be 
customized for your specific data."

5f/ All the "Optional" database changes.

Your 1.7.2 machine should now be upgraded, so it's time to check that 
the upgrade was successful and finish up.

6/ Restart httpd and try logging into Freeside.  You may find that your 
htpasswd file is corrupt and you have to restore it from the 1.5.8 
machine or just re-enter the passwords.

7/ Spot check the database to make sure it looks correct.  (I usually 
look at the GUI user's preferences, then list all the customers.)  Try 
making a minor change to make sure updates work.

8/ Check all your exports.  You'll probably have to regenerate SSH keys 
and upload them to all the machines you need to access via exports, 
self-service, etc.

I hope this helps.  Let us know whether or not this works.

Regards,

	Richard Siddall