Upgrade Test Boxes to PostgreSQL 9.6

[pdurbin]

Now that pull request #5866 has been fixed we have a new problem with getting the API test suite running again on the phoenix server.... the IF NOT EXISTS syntax is new as of PostgreSQL 9.6 (according to https://stackoverflow.com/questions/12597465/how-to-add-column-if-not-exists-on-postgresql/38721951#38721951 anyway) and the phoenix server is running PostgreSQL 9.3.25 according to psql --version.

https://build.hmdc.harvard.edu:8443/job/phoenix.dataverse.org-deploy-develop/408/console shows remote failure: Error occurred during deployment: Exception while loading the app : javax.ejb.CreateException: Initialization failed for Singleton StartupFlywayMigrator. Please see server.log for more details. and server.log shows the following:

Caused by: org.flywaydb.core.internal.sqlscript.FlywaySqlScriptException: 
Migration V4.14.0.1__5822-export-var-meta.sql failed
----------------------------------------------------
SQL State  : 42601
Error Code : 0
Message    : ERROR: syntax error at or near "NOT"
  Position: 44
Location   : db/migration/V4.14.0.1__5822-export-var-meta.sql (/usr/local/glassfish4/glassfish/domains/domain1/applications/dataverse/WEB-INF/classes/db/migration/V4.14.0.1__5822-export-var-meta.sql)
Line       : 1
Statement  : ALTER TABLE variablemetadata
ADD COLUMN IF NOT EXISTS postquestion text

[pdurbin]

I just upgraded postgres to 9.6 on the phoenix server with the following commands (it's running CentOS 6):

/usr/pgsql-9.3/bin/pg_dump dvndb -f /tmp/dvndb.sql

service postgresql-9.3 stop

https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

yum install postgresql96-server

service postgresql-9.6 initdb

service postgresql-9.6 start

/usr/pgsql-9.6/bin/psql -f /tmp/dvndb.sql

cp -a /var/lib/pgsql/9.6/data/pg_hba.conf /var/lib/pgsql/9.6/data/pg_hba.conf.orig

vi /var/lib/pgsql/9.6/data/pg_hba.conf # set to trust

service postgresql-9.6 stop

service postgresql-9.6 start

psql -U postgres -c "CREATE ROLE dvnapp UNENCRYPTED PASSWORD 'secret' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN" template1

Now we know the API tests are failing: #5882 😢

[donsizemore]

@pdurbin which jdbc driver are you using? https://jdbc.postgresql.org/

[pdurbin]

@donsizemore as we discussed on IRC, it turned out phoenix needed a new Solr schema for the API tests to pass: 2d88761

[landreev]

With demo having been upgraded, this appears to be done.
(did we forget any systems? - please let me know)