Select to view content in your preferred language

Upgrading Enterprise Geodatabase in AWS

1721
5
08-10-2020 09:29 AM
jcarlson
MVP Esteemed Contributor

According to the database requirements for PostgreSQL, I need PostGIS 3.0.1 to meet the minimum requirements for 10.8.1 and 2.6.

According to the AWS RDS PostgreSQL documentation, the currently supported version of PostGIS in 11.7 is 2.5.2, and even at the next major version, 12.2 only supports PostGIS 3.0.0.

Am I simply out of luck if I want to upgrade my enterprise geodatabase to the latest version?

- Josh Carlson
Kendall County GIS
0 Kudos
5 Replies
George_Thompson
Esri Frequent Contributor

That is interesting that Amazon only supports PostGIS 2.5.x on PostgreSQL. You can install PostGIS 3.x.x on PostgreSQL 11.x - UsersWikiPostgreSQLPostGIS – PostGIS / Index of /postgis/windows/pg11/ 

That may be a question for Amazon to respond to on why they cannot support that version of PostGIS on their installations.

--- George T.
0 Kudos
MichaelMannion
New Contributor III

Have you learned anything new on this topic since posting your original question?

I need to upgrade an RDS PostgreSQL instance that is hosting a geodatabase to a newer PostgreSQL release before Amazon drops support for it. I was hoping to go to something that is compatible with the latest ArcGIS (10.8.1) and Pro (2.7) releases.

If I'm reading the respective documentation correctly, however, there's not a lot of overlap between the recent PostgreSQL / PostGIS versions that both Esri and Amazon support. I think the latest compatible versions that I can upgrade to are:

  • ArcGIS 10.7.1
  • Pro 2.4
  • PostgreSQL 11.2
  • PostGIS 2.5.1

As you mentioned in the OP, RDS only supports PostGIS 3.0.0 up through the PostgreSQL 12.x releases; their doc is still reporting that as of today.

RDS adds support for PostGIS 3.0.2 at PostgreSQL 13.x, but ArcGIS doesn't support 13.x yet (again, per the doc, not from testing).

Thanks in advance for any updated information you can provide.

0 Kudos
George_Thompson
Esri Frequent Contributor

This may be something best suited for Technical Support to investigate and comment on. I would recommend creating a new support case.

--- George T.
0 Kudos
AndrewThePrivateSectorDrone
New Contributor II

Hey @MichaelMannion - did you ever get to a resolution on this? I'm evaluating whether PostGreSQL RDS is feasible for an enterprise geodatabase, but the performance is abysmal. Have you had success with your PostgreSQL RDS enterprise geodatabase?

0 Kudos
MichaelMannion
New Contributor III

Regarding the upgrade, yes. Sort of.

To make a very long and painful story short - there was a narrow upgrade path that required multiple rounds of upgrading ArcGIS / Postgres / PostGIS in little increments in order to hop from one supported combination of versions to another. We had to do full Postgres dumps > reloads throughout, and ultimately ended up a major version behind in ArcGIS.

The problem is that one has little flexibility to manage the software versions when working with RDS compared to "normal" Postgres, and ArcGIS has specific requirements for what it supports. In particular, RDS only supports one version of each Postgres extension (e.g. PostGIS) per Postgres release. To Amazon's credit, they are constantly releasing new Postgres/extension versions, so there's lots to choose from. But, unless Esri also certifies that platform, you can get stuck on older releases.

If we'd been running our own Postgres instance on our own host (i.e. not RDS) we could have easily worked around our compatibility issue. But we would have had to do a lot of things for ourselves that RDS was doing for us. So, I don't want turn you off of RDS. It's not perfect - but neither is running your own database instance. Let me address your second question .

With respect to success with RDS, yes. I've used it a bunch (with and without ArcGIS) and it's generally been great.

Performance has been fine, in my experience. In fact, on one project we run a tiny instance most of the time to save money, then restart it with tons of processor / memory / fast disk to do big data processing jobs. When we're done, we restart it back on the tiny specs. If performance has been bad for you, maybe you need to provision more resources to your RDS instance? (I know that's obvious to say; I'm just guessing.)

More generally, RDS is a double-edged sword: you don't have to manage much, but you don't get to manage much.

I find that RDS is a great fit for clients who don't have a full-time database person. Relying on AWS for automatic backups, automatic minor version upgrades - and one-click replication! - is fantastic. It works great. The AWS Console UI is even easy enough for technical staff without database skills to clone databases for testing. This kind of stuff is going to put me out of a job 🙂

On the other hand, to ensure that all of this magic automation is stable, Amazon necessarily restricts your access to some administrative things. For example, as we discussed above, you can't install any old version of any old extension, because you don't have access to the underlying host on which your RDS instance is running.

Perhaps more importantly, you don't have access to SUPERUSER. Lots of admin-type stuff in Postgres depends on SUPERUSER access. Again, Amazon can't give that to us, lest we break something like backups or replication - the very things that we're paying them to do for us by choosing RDS.

The problem with this is that there's no real substitute for SUPERUSER. This isn't like the "DBA" role in Oracle, which is just some arbitrary role that happens to come with the software and which you can recreate yourself by granting the same privileges. As far as I can tell, SUPERUSER is baked-in to the Postgres software, kind of like "sysadmin" in SQL Server. You can't fake it with other privileges; you have to have the actual, built-in SUPERUSER role for certain operations that require it.

This lack of control is a pain. But, is it more or less of a pain than managing by hand all of the things that RDS does for you? It depends on how well you know Postgres and how much time you have.

RDS is neither inherently good nor bad, nor is there some generic answer about which platform, RDS or normal Postgres, is best. It's a matter of finding the best fit for the task at hand.

0 Kudos