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
[email protected]
