Upgrade Monitor postgres from 14x to 17x

DavidColey

Hello - I'm at Monitor 2025.0 and will shortly go to 2025.1. However before doing so I want to upgrade the postgres instance since it is still sitting at 14.x.

Is it possible using the following:

Upgrading PostgreSQL in Windows · GitHub

without going to 16.x first? It wasn't clear to me if I can use the pg_upgrade command when going up more than 2+ postgres versions...

thanks-

David

CodyPatterson

Hey @DavidColey

When I've upgraded in the past, it was always one version at a time, we had let ours go without update for a little long, and we had three major versions to go up on. Basically it was just the standard take backup of the instance, and then run the upgrade command, wait an hour or so to ensure everything is set, and then repeat. I think that may be the only way to do so, mostly because I've seen that there's changes each major version, and skipping may result in those changes causing errors, etc.

Cody

DavidColey

Ok thanks for the feedback @CodyPatterson. The methods in the GIT link above reads that there should not be an issue going from 14 to 16, so that is probably what I will do. That way I'm not trying to skip ahead 2 versions....

CodyPatterson

Hey @DavidColey

Let me know what the outcome is if you decide to do the 2 version jump, I'm very interested to know as it'd save quite a bit of time!

Cody

DavidColey

Oh I'm going to do it @CodyPatterson and yes I'll let you know. If it doesn't work I'll just roll back to a snap and take my medicine

DavidColey

This seems to have been successful @CodyPatterson - but I did only go from 14 to 16. However, maybe you can help me on this:

Failed to register ArcGIS Monitor database

no pg_hba.conf entry for host "1.2.3.4", user "agm", database "agm", no encryption

So when I upgraded postgres, I lost my pg 14 conf file.

This is what my conf file looks like at postgres 16:

# TYPE DATABASE USER ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all scram-sha-256

# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host agm postgres 10.140.xy.xyz/32 scram-sha-256

# IPv6 local connections:
host all all ::1/128 scram-sha-256
host agm postgres 10.140.xy.xyz/32 scram-sha-256

# Allow replication connections from localhost, by a user with the
# replication privilege.local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256

Do you see anywhere else I should be adding or removing the entry for the agm host?

Thanks,

David

DavidColey

Don't worry about it @CodyPatterson - I figured it out. The entry only goes here:

# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all scram-sha-256
host replication all 127.0.0.1/32 scram-sha-256
host replication all ::1/128 scram-sha-256
host agm agm 10.140.xy.xyz/32 scram-sha-256

We rolled back. There are other issues going on. Moving forward, I'll upgrade Monitor to 2025.1 first, then run the pg_upgrade