Upgrade to PostgreSQL 13+

[jomtov]

In the present Installation Guide there is a strong recommendation of using version PostgreSQL 9.6 which reaches End-of-Life 2021-11-01, while the latest version is already at v.13. Evaluate risk of obsolescense.
Together with PostgresSQL 9.6 goes use of RHEL/CentOS 7, which is now only in maintanence support 2, full support ended 2019-08-06, while latest version RHEL/CentOS 8 was released already 2019-05-07 - more than a year ago.

[djbrooke]

@jomtov, thanks for opening this issue. When we discuss more we may retitle as "upgrade to version..." before we pull this into a sprint.

[donsizemore]

@djbrooke FWIW dataverse-ansible quietly bumped the PG version to 10 this summer, as the version of python3-psycopg2 packaged with RHEL/CentOS8 isn't backwards-compatible with PG 9.6. I did some testing against 12 this summer; nothing major comes to memory.

[djbrooke]

• We'll try to handle this with a discussion in dv-tech or tech hours
• We should make sure to read the release notes as there may be incompatibilities

[donsizemore]

@djbrooke FWIW I just ran the Jenkins IT test suite against Postgres 13.1 using the most recent JDBC driver

1311 tests run, 0 errors, 0 failures, no SQL errors in server.log.*

I can leave the instance up if anybody wants to poke at it, or tear it down if you say.

[poikilotherm]

thought: I would be glad and honored to be part of the discussion.

[landreev]

Everything appears to be working, with newer PostgreSQL versions, up to v. 13. Special thanks to @donsizemore for confirming that the Jenkins/RestAssured tests are passing under v.13.
Before I make a PR and put it up for review, this is what I'm planning to do, in case anyone has any objections/suggestions, etc.:

• Remove the language about v. 9.6 being "strongly recommended";
• I don't feel like it is necessary to require upgrading to the latest version. So I'm planning to make the guide say that the application has been tested with versions up to 13; and that we "recommend installing the latest version that's supported and available for your OS distribution".
• I believe there are legitimate cases where an installation may want to stay on an older version. For example, ours here. We are using Amazon RDS that's managed for us by another Harvard group. In the past when they would no longer support an older version, they would create a new instance for us and ask us to migrate the database. Presumably we'll need to do this again, but I don't feel like nagging them to make it happen ahead of their schedule.
• I'm updating the version of the jdbc driver we are bundling with the war file. (it's minor - we already have 42.2.18 in pom.xml, the latest available is 42.2.19).
• Another small thing - the Prerequisites section of the Installation Guide still says that our installer comes packaged with a jdbc driver that may need to be updated if they want to use a newer version of PostgreSQL - which is no longer true, now that the driver is bundled with the war file. Will remove.
• It's going to say that if anyone needs to upgrade an existing installation, the recommended and tested way is to export (via pg_dump) the old database, then re-import under newer version of PostgreSQL.

Any feedback is welcome.
@poikilotherm: it doesn't sound like any of this is going to affect your use cases... but I may be missing something.

[qqmyers]

re: removing the old info about how to update the driver: Is it worth still having a note about how to update the driver now that it is in the war? Presumably we can keep it nearly up-to-date in the new releases, but for older versions to run on newer postgres (or if the releases don't update every time postgress' driver does) - is there a place some guidance should go?

[landreev]

@qqmyers
I agree, it should be an "edit", not a "remove".
I still hope that few people would ever have a reason to actually do that... But we should mention it.

re: removing the old info about how to update the driver: Is it worth still having a note about how to update the driver now that it is in the war?

[pdurbin]

I'm fine with removing language to strong recommend a specific version but our guides are currently very specific about versions when it comes to actually installing postgres. It looks like yum install -y postgresql96-server for example. I assume this is staying the same. It's like... if you don't know which version to choose, here's a specific version mentioned in the guides.

I think we should update the version in Vagrant.

I think we should update the version in docker-aio too.

Overall, the plan sounds great.

[landreev]

I was going to update the yum command lines with something newer; and also to spell out clearly that they are just an example.
Not sure about touching vagrant and docker in the same pr.
(will commit my changes shortly)