[OPEN-ILS-GENERAL] PostGreSQL upgrade to 9.4

[Blake Henderson]

Glen,

It might be a good idea to dump the database to a series of sql commands 
using pg_dump. Then replace the database engine with 9.5 and then 
restore the database from that backup. Here are some commands to help 
give you the idea:

Shut the database down
Make a raw tar backup of the folder (just in case)
time tar -zcvf /path/to/save/postgres94backup/db1.main.tar.gz 
/var/lib/postgresql/9.4/main
Start the database again
time pg_dump -i -Fd -Z 9 -f /path/to/save/dbmain 
--serializable-deferrable evergreen
The above command assumes that your database is called evergreen. Be 
sure and choose an empty folder because that command will fill it up 
with a ton of files/folders.

When I was doing this from 9.3 to 9.5, I had an issue with the 
permission schema, so I backed that up special:
pg_dump evergreen -a -n permission -f 
/path/to/save/main.permissionschema.sql

Now you should be done getting all the stuff. Fire up 9.5 (It sounds 
like you got 9.5 installed)
Create an empty database called evergreen, making sure it's 'C' instead 
of UTF-8
from the psql prompt:
CREATE DATABASE evergreen TEMPLATE template0 ENCODING 'UNICODE' 
LC_COLLATE 'C' LC_CTYPE 'C';
\connect evergreen
CREATE LANGUAGE plperlu;
CREATE EXTENSION tablefunc;
CREATE EXTENSION xml2;
CREATE EXTENSION hstore;
CREATE EXTENSION intarray;
CREATE EXTENSION pgcrypto;
CREATE ROLE evergreen PASSWORD 'YourDBpassword' SUPERUSER LOGIN INHERIT 
CREATEDB CREATEROLE;
GRANT ALL PRIVILEGES ON DATABASE "evergreen" to evergreen;
GRANT ALL ON DATABASE "evergreen" to evergreen;

The above assumes that your database is called evergreen and your user 
is called evergreen.
Now get to your folder where you backed up your database:
cd /path/to/save/dbmain
and restore it to the 9.5 cluster
time pg_restore -d evergreen -Fd ./ > 
/path/to/log/db1main_upgraderestore.log

Inspect the log and double check for any possible issues.

I also had a path issue when I did the upgrade, and I had to run this 
command to correct it:
ALTER ROLE evergreen SET search_path TO evergreen,public;


I hope this helps! Good luck, and as always, practice first!


-Blake-
Conducting Magic
MOBIUS

On 10/14/2017 12:50 PM, Glen Modell wrote:
> Hello, this is Glen from Ann Arbor.  We are not yet on Evergreen, but 
> we have a test server which is now on 2.12.4, OpenSRF 2.5.2, and 
> PostGreSQL 9.3.  We just attempted to move it to PostGreSQL 9.4 in 
> preparation for an upgrade to Evergreen 3.0.  We tried the 
> pg_upgradecluster method, and it did not work for us.  We had a lot of 
> errors pointing out various parts of the database that it could not 
> find, and, while the operation did complete, not all the data was 
> migrated.
>
> I am wondering if anyone has any advice for getting through this 
> PostGreSQL upgrade.  Thanks.  --  Glen.
>
> *****************************
> Glen Modell
> Library Automation Specialist
> Ann Arbor District Library
> 734-327-8322
> modellg at aadl.org

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-general/attachments/20171015/8cf645d1/attachment.html>