postgres upgrade to 9.6

[djbrooke]

Long-ish details from AWS below, but they are driving us to upgrade postgres. AWS aside, we should upgrade anyway.

We can discuss and estimate 9.6 vs 10 (or something else) at sprint planning. We'll update this issue following that meeting.

---

Hello,

This communication is being sent to you to announce that Amazon RDS will be discontinuing support for version 9.3 of the PostgreSQL database in RDS, starting on September 5th, 2018.

Amazon RDS has been supporting the PostgreSQL major version 9.3 since 2013. That release has since been replaced by subsequent major versions that contain significant improvements in functionality, security, reliability, and performance. The PostgreSQL community has announced the end of life date for PostgreSQL 9.3 as September 2018 [1]. Aligning with the community support model, AWS will be ending support for the PostgreSQL major version 9.3 in Amazon RDS, including its minor engine versions including 9.3.10, 9.3.12, 9.3.14, 9.3.16, 9.3.17, 9.3.19, 9.3.20, and 9.3.22. We will continue to support 9.4 and more recent versions of the PostgreSQL database on Amazon RDS.

We recommend that you upgrade your Amazon RDS PostgreSQL database instances to either version 9.6 or version 10 at your earliest convenience. You can use the Major Version Upgrade feature of RDS to upgrade databases to the next higher version. You can initiate an upgrade by going to the Modify DB Instance page in the AWS Management Console and changing the database version setting to a newer major version of PostgreSQL. If you choose the “Apply Immediately” option, the upgrade will be initiated immediately after exiting the Modify page. If you choose not to apply the change immediately, the upgrade will be performed during your subsequent maintenance window. To learn more about upgrading PostgreSQL major versions in RDS, review the Upgrading Database Versions page [2].

As part of the retirement plan for Amazon RDS PostgreSQL 9.3, starting the week of August 6th 2018, you will no longer be able to create any new PostgreSQL 9.3 instances using the AWS console. In November 2018, your remaining 9.3 instances will be force upgraded to the most recent minor version of PostgreSQL 9.4. Once this occurs, any instance restored from a snapshot of a deprecated version will automatically and immediately be upgraded to a currently supported version. The exact dates and details on force upgrades will be communicated in September 2018.

Your affected instances in the US-EAST-1 Region:

---

Should you have any questions or concerns, the AWS Support Team is available on the community forums and via Premium Support [3]. To learn more about Amazon RDS' policy for supporting database versions, please see [4].

[1] https://www.postgresql.org/support/versioning/
[2] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_UpgradeDBInstance.PostgreSQL.html
[3] https://aws.amazon.com/support
[4] https://aws.amazon.com/rds/faqs/ [search for “guidelines for deprecating database engine versions”]

Sincerely,
Amazon Web Services

[donsizemore]

Danny, there is an alpha rhel8 installer at https://github.com/rhinstaller/anaconda/tree/rhel8-alpha-branch — it might be nice to take whichever version rhel8/centos8 will consider LTS into heavy consideration.

[pdurbin]

@donsizemore thanks, but I can't figure out which version of Postgres will be in RHEL 8. Can you? The default version in RHEL 7 seems to be 9.2 according to https://git.centos.org/summary/?r=rpms/postgresql.git

[donsizemore]

@pdurbin RHEL releases tend to solidify an earlier release of Fedora, though Red Hat has been expanding (cough modernizing) their package base through software collections. RHEL8 is allegedly based on Fedora 27 [which appears to ship with PostgresQL-9.6] (https://fedora.pkgs.org/27/fedora-x86_64/postgresql-9.6.5-1.fc27.x86_64.rpm.html)

It's really the "LTS" release of any software package for which I'm lobbying. For instance, when Odum migrated to Dataverse 4, Dataverse didn't explicitly support Postgres 9.2 so I went with 9.3. Postgres 9.3 will go EOL in September so we'll have to take an outage for reconfiguration later this year. BTW, a text-format pg_dump from 9.3 to RHEL7's "LTS" 9.2 seemed fine on a test instance, as best I could tell.

[pdurbin]

@donsizemore thanks, I had forgotten that we recommended a non RHEL LTS version of PostgreSQL back at http://guides.dataverse.org/en/4.0/installation/prerequisites.html

It seems like we're converging on version 9.6 so so hopefully that will work out for RHEL/CentOS 8 as well as AWS users.

[pdurbin]

Over at http://irclog.iq.harvard.edu/dataverse/2018-05-30#i_68050 @donsizemore volunteered to make a pull request for this issue so I assigned it to him. Thanks! I see the todo list as:

• update guides
• update vagrant
• ideally, update files in conf/docker-aio
• ideally, update files in conf/docker (these are the images we use in openshift and this work has been deferred, as explained below)

We can help! If you put a branch in motion, the "maintainers" of the upstream repo like myself can make commits as long as you leave the proper box checked when you make the pull request. Thanks again!

[djbrooke]

Thanks @donsizemore for taking this on and @pdurbin for setting this up!

[pdurbin]

@donsizemore thanks for making pull request #4721! I added a couple commits explaining how the core team can add commits to a pull request like this (27418c8) and to encourage devs on Mac to use PostgreSQL 9.6 (d176002). @pameyer said he'd take a look at the docker-aio stuff (thanks!) so I'll switch the assignment to him.

[pameyer]

release notes for postgres 42.1.4 jdbc driver don't mention which postgres versions that driver supports (unless I'm missing something from https://jdbc.postgresql.org/documentation/changelog.html#version_42.1.4). Not sure if it'll be an issue yet.

[donsizemore]

@pameyer 42.2.n supports Postgres 8.2 or newer

[pameyer]

@donsizemore - thanks, the "or newer" part had me cautious.

[pdurbin]

I think we're good as of 316c21a (thanks, @pameyer !). We can revisit the OpenShift stuff in the future. Off to QA.

[pdurbin]

To be clear, our OpenShift config currently uses PostgreSQL 9.4, so the future task would be to find an image that uses 9.6 instead. Here's how you can tell we're using 9.4 right now:

murphy:dataverse pdurbin$ git log --oneline | head -1
316c21a docker-aio passes integration tests w\ postgres 9.6
murphy:dataverse pdurbin$ ack postgresql-94 conf
conf/openshift/openshift.json
110:        "name": "centos-postgresql-94-centos7"
113:        "dockerImageRepository": "centos/postgresql-94-centos7"
269:                "name": "centos-postgresql-94-centos7",
270:                "image": "centos/postgresql-94-centos7",
354:                "centos-postgresql-94-centos7"
358:                "name": "centos/postgresql-94-centos7:latest"
murphy:dataverse pdurbin$ 

I did find https://hub.docker.com/r/centos/postgresql-96-centos7/ so maybe this task wouldn't be too bad.

[donsizemore]

Just a note that I've imported a dump of Odum's production 9.3 database using custom format into a 9.6 test instance and pointed https://dataverse-test.irss.unc.edu/ at it, using the 42.2 Postgres JDBC driver.

Basic dataverse/dataset creation, file upload and publishing seem to work, no complaints in the Glassfish log as of yet. Registration is open if anyone wants to beat up on it.

[pdurbin]

@donsizemore thanks for testing 9.6! At standup I mentioned that it might be nice to have our new intern Ben try out the new 9.6 for devs instructions at d176002 to make sure they work.

[pdurbin]

I decided to go ahead and switch my dev environment over to Postgres 9.6 (I think I was using 9.2 before) so I followed the instructions in the pull request and they worked fine but I did make a tweak at 21ab80e to note that pgAdmin 4 is now the tool to use. It's web-based and looks like this:

Over at https://www.pgadmin.org/download/ it says "WARNING: pgAdmin 3 is no longer supported. It is recommended that you download pgAdmin 4 instead."

So, onwards and upwards, I guess. I tested version 9.6.9 of Interactive installer by EnterpriseDB (the version noted in the pull request) and it includes pgAdmin 4.

I also merged the latest from develop in 33c11e5 while I was in there.

[pdurbin]

@donsizemore is going to update pull request #4721 to update the JDBC postgres drivers so I'm pulling this out of QA. See #3789 and http://irclog.iq.harvard.edu/dataverse/2018-06-08#i_68545

[pdurbin]

I gave @kcondon a brain dump of how @donsizemore further improved the pull request to get down to a single JDBC driver. This is what seems to be recommended by the Postgres docs. https://jdbc.postgresql.org/about/about.html says, "The current version of the driver should be compatible with PostgreSQL 8.2 and higher, and Java 6 (JDBC 4.0), Java 7 (JDBC 4.1) and Java 8 (JDBC 4.2)." I went ahead and moved this issue and #3789 to QA but @landreev might want to take a look.