Database upgrade

I’m planning to split our postgresql database server from our ignition server soon, and do a version upgrade to the database in the process. Sometime down the road, I’ll actually upgrade ignition itself…

Any known issues with upgrading to the latest version (14.2) of Postgresql? I’m planning on installing the db server on Ubuntu 20.04. I do have a reasonably modern version of the JBDC driver installed in ignition, from my last attempt at upgrading a postgres db a few years ago.

Also, I’m planning to just do a pg_dumpall > copy backup files to new server > restore with psql. If any of the resident postgres wizards have suggestions other than that, I’m all ears :slight_smile:

I would not expect any trouble.

1 Like

Thanks Phil! I appreciate it!

If the DB is large, you might be able to upgrade in place if you go one version at a time.

I was just looking into the exact mechanics of doing the migration/upgrade.

ignition=> SELECT pg_size_pretty( pg_database_size('ignition') );
 pg_size_pretty
----------------
 83 GB
(1 row)

Not sure whether that is considered large. There’s a couple other databases on the same postgres server, but they are tiny (couple MB). The DB backup for this db is about 5.5 GB, compressed.

Less than 20:1 ? Not bad. (Modern databases trade space for speed.)

It’s making me wonder whether it’s worth figuring out how to pipe the dumpall data directly into a psql restore command. I suppose the benefit is that I would be able to use the dumpall command from the new version, which is recommended on basically every how-to page I’ve seen so far. Not sure how important that is.

I’m guessing I’d just have to setup network access for a user with the SUPERUSER privilege on the old db. The postgres user doesn’t have a password by default (I think), so that seems a bad choice. I have a secondary user setup on the system that I could promote, I guess. Hmm…

When you wrote “split” my brain glommed onto the idea that one or the other would remain on the original machine, with OS upgrade in place.

With new hardware, just go with dumpall → psql. Consider doing schema and data separately.

I suppose I didn’t describe it very well :confused:
My current vm has ignition on the same machine as the postgres db. I was young(er) and less informed :sweat_smile: It’s currently running Ubuntu 14.04, ignition 7.9.6, and postgres 9.6. And getting lower on diskspace as time goes on. To cut this mess down into bite-sized pieces, I figured I’d set up a new vm for just the db and migrate the data storage stuff to it first.

After that, I’ll look at the ignition side of things. I’m currently leaning towards spinning up a new vm and installing v8 and restoring a gateway backup to that (moving the license too). It seems that might be a better approach than going through several OS upgrades on the old vm.

Not sure when I’ll get around to the ignition upgrade. I have several 32 bit desktops/raspberry pi installs that I’ll have to deal with getting connected back to ignition if I don’t upgrade them first. It some point though, it’s just an excuse to drag my feet…

2 Likes

For anyone following, I did notice something when setting up my pg_hba.conf file on the new server. I was adding the users/access permissions when I noticed that there was a new default option for password authentication: scram-sha-256. (It can still be set to MD5 per line as needed.) It looks like Postgresql 13+ has the postgresql.conf default option set to encrypt new/changed passwords using this new method. I’m not sure if an existing MD5 hashed password will continue to be MD5 hashed on a changed password.

To use the new authentication method, you must use a sufficiently modern driver (and also update/rehash the password). It looks like the version I installed a few years ago to talk to a Postgresql 10 install just missed the mark :confused: So, I’ll upgrade that first during my DB upgrade and make sure nothing blows up with my exiting connections :upside_down_face: FWIW, the JBDC driver needs to be 42.2.0+ I’m not sure what Ignition8 is shipping with these days, but anyone trying to stand up a Postgres 13+ instance will need that or later to connect to it.

Edit: I’m able to create a test connection to the new db server from my existing install, so that’s good. However, I’m noticing that the v14.2 server is returning lots of system tables in the schema list in the Database Query Browser that don’t show up in the v9.6 server. I think similar system tables are there, but they are being hidden, whereas on the 14.2 server, they are not.

I think I first noticed this when I connected to my other v10.17 server. However, I’m rarely working with that server, so I just kind of ignored the system tables in the listing. The 14.2 server will be the one I’m working with all the time, so these extra tables will be annoying. It looks like I might be able to filter some through the translator. I also wonder if a newer JBDC driver would take care of this for me.

If anyone has translator table filter strings for the default postgres tables, please share :slight_smile:

1 Like

Well, my database upgrade went well for the most part. Slight hiccup occurred when trying to upgrade the connection security to the new scram-sha type. Ignition was throwing errors about the authentication method not being supported. This was confusing as the first thing I did was to upgrade the JDBC driver. Turns out there was a super old 9.something postgres JDBC driver still hanging around in the jdbc folder on the server. I deleted that one and restarted the ignition service and it connected right away.

1 Like