PostgreSQL 9.3 is required, 9.6 strongly recommended

[pdurbin]

I ran the phoenix tests for the first time since the pull request #5349 was merged. That's where we added a dependency on Flyway (#5344) for automatic SQL script execution on deployment.

Here was the failure on phoenix as seen from the Jenkins log at https://build.hmdc.harvard.edu:8443/job/phoenix.dataverse.org-deploy-develop/368/console

Command deploy failed.

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.

I looked at server.log and found this:

Exception while loading the app : javax.ejb.CreateException: Initialization failed for Singleton StartupFlywayMigrator javax.ejb.CreateException: Initialization failed for Singleton StartupFlywayMigrator

Caused by: org.flywaydb.core.internal.exception.FlywayDbUpgradeRequiredException: PostgreSQL upgrade required: PostgreSQL 8.4 is outdated and no longer supported by Flyway. Flyway currently supports PostgreSQL 9.0 and newer.

	at org.flywaydb.core.internal.database.base.Database.ensureDatabaseIsRecentEnough(Database.java:156)
	at org.flywaydb.core.internal.database.postgresql.PostgreSQLDatabase.ensureSupported(PostgreSQLDatabase.java:65)
	at org.flywaydb.core.internal.database.DatabaseFactory.createDatabase(DatabaseFactory.java:97)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1670)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:1356)
	at edu.harvard.iq.dataverse.flyway.StartupFlywayMigrator.migrateDatabase(StartupFlywayMigrator.java:37)

The above message about PostgreSQL 9.0 is true according to https://flywaydb.org/documentation/database/postgresql but PostgreSQL 9.3 is the oldest version that Flyway supports for free:

Definition of done for this issue:

• The ability to run the API test suite on the phoenix server is currently blocked. We need to either upgrade PostgreSQL on phoenix to 9.6 (used in production at Harvard Dataverse) or install a fresh server (perhaps with CentOS 7 instead of CentOS 6) to take the place of dvnweb-vm6.hmdc.harvard.edu.
• Update http://guides.dataverse.org/en/4.11/installation/prerequisites.html#postgresql to say that PostgreSQL 9.6 is strongly recommended.
• Delete source/_static/util/pg8-createsequence-prep.sql and the "For systems using Postgresql 8.4 or older..." reference to it at http://guides.dataverse.org/en/4.11/installation/config.html#identifiergenerationstyle (added for running createsequence.sql against PostgreSQL 8.4 results in ERROR: language "plpgsql" does not exist #3744)
• Add a file under doc/release-notes to warn anyone running PostgreSQL 8.x that they must upgrade.

[sekmiller]

I have completed the "definition of done" steps outlined above except for the upgrade to the phoenix server.

[pdurbin]

@sekmiller thanks for getting the branch started.

@landreev and I just discussed this issue during tech hours. He is going to upgrade Postgres to 9.6 on the phoenix server.

I'm upgrading Postgres to 9.6 on the dev1 server. (Maybe I'll do apitest too but not promises.) I'm taking some notes that I might push into the branch so I'll keep myself assigned as well. I'm already seeing a couple things in the guides I want to edit some more.

@pameyer here's the commit where I messed with your docker stuff without testing it: ff78321 . You were still using pg8-createsequence-prep.sql but I assume you don't need it.

[donsizemore]

@pdurbin @landreev just a note that i've imported our production 9.3 db_dump into a 9.6 service many times and haven't suffered any ill effects to my knowledge.

[landreev]

@donsizemore I've done it on my own dev. environment between 9.1 and 9.6 - also without any signs of a problem.

[landreev]

@pdurbin I know that "9.6 is strongly recommended" - but we are running 9.3 on dvn-build and dvn-vm4, and Flyway seems to be happy. To get the phoenix server to work, and for uniformity with the other CentOS6 boxes, I've installed posgresql93 there as well. The configuration is identical to how it was before:

[root@dvnweb-vm6 bin]# psql -p 5432 -h localhost -U dvnapp -d dvndb
psql (9.3.25)
Type "help" for help.

dvndb=>

The startup scripts are properly checked on and off.

[landreev]

(if we decide we do need 9.6, I'll upgrade all the remaining CentOS6 boxes at once)

[pdurbin]

@landreev great! Thanks! Do you want to have the honor of kicking off a build at https://build.hmdc.harvard.edu:8443/job/phoenix.dataverse.org-build-develop/ ? (This is the first of three chained jobs.)

[pdurbin]

@landreev I went ahead and clicked "build now" and the deployment to phoenix succeeded:

That screenshot is from https://build.hmdc.harvard.edu:8443/job/phoenix.dataverse.org-deploy-develop/

Now I'm waiting to see if the API test suite passes or not but I suspect it won't and that we'll see errors reported at #5662.

[pdurbin]

The deploy step worked on the first run but not the second. Here's the failure:

[pdurbin]

I rebooted the phoenix server and now I'm getting this (job 372):

ERROR: permission denied to create database

[pdurbin]

I dropped and recreated the "dvnapp" user because it didn't have any permissions:

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 dvnapp    |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

-bash-4.1$ psql -U postgres -c "DROP ROLE dvnapp" template1
DROP ROLE
-bash-4.1$ psql -U postgres -c "CREATE ROLE dvnapp UNENCRYPTED PASSWORD 'secret' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN" template1
CREATE ROLE
-bash-4.1$ psql 
psql (9.3.25)
Type "help" for help.

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 dvnapp    | Superuser, Create role, Create DB              | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# 

I'll try another run.

[pdurbin]

Ok, now the "deploy" step continues to work. Phew.

[landreev]

Yes, I created that user with /usr/pgsql-9.3/bin/createuser, without giving it any extra permissions; I didn't realize it needed to create the db.
Glad to hear it's working now.

[pdurbin]

@landreev yeah, here's where the CREATE DATABASE happens: https://github.com/IQSS/dataverse/blob/v4.11/scripts/deploy/phoenix.dataverse.org/rebuild#L15

[pdurbin]

@kcondon @landreev my biggest concern about this pull request is that we may have missed something in the "Launching Glassfish on system boot" section of the installation guide. I didn't touch it (or look at it).