Upgrading PostgreSQL from version 14 to 17 - Part 1

To start I should probably mention that I work for a healthcare company, and our PostgreSQL databases are hosted on Aptible.

Also this is NOT a tutorial. This is me writing about an experience I went through, which involved a fair amount of technical work, along with the usual emotional and everyday realities that come with it. Now that I have that out of the way, let's begin.

Our database is very write heavy. Incoming data is arriving from batch import jobs that run on demand and over-night. The outgoing data is typically consumed via the web app (some via API too), concentrated during normal PST work hours, with very little activity outside business hours or on weekends.

Database hardware:

• Disk: 6200 GB (Disk IOPS 16000)
• Memory: 60 GB
• CPU share: 7.5 (memory optimized container)

There are no failovers or read replicas configured. This is a single, chunky database.

Now for the reason we upgraded. It wasn't performance, new features, or anything particularly exciting (though we'll happily take the benefits of a newer version). The real reason was reducing cost. Once you factor in database backups required for disaster recovery and compliance, that unused space gets expensive very quickly.

Upgrading PostgreSQL on Aptible is usually straightforward. They offer several upgrade paths, and the easiest and best is an in-place upgrade. Unfortunately, that option wasn't available to us. Some underlying library binaries changed between these versions, which ruled it out. Major bummer.

That left two options: logical replication or dump and restore. At first glance, logical replication was the better choice. Downtime would be minimal compared to dump and restore. With the latter, we would need to stop the database entirely, or at least stop writes.

Next, we needed to do a dry run of the migration because when have things ever worked on the first try, especially at this scale. Data consistency and reliability are very important to us, arguably more important than availability. That said, like any business, we have customers and contracts to sign SLAs to meet, and all the usual constraints that come with that.

Going back to the actual work, I started by putting together a document to capture the steps and a few observations along the way. I won't go into too much detail here, but it looked roughly like this:

• Define terminology (source DB, target DB, etc.)
• Schemas and sizes (we have 200+ schemas, some north of 200 GB)
• Hot tables — a handful of tables per schema that hold most of the data, some with 280M+ rows
• Source and target configuration
• Grafana alerts (disk size would drop significantly and immediately trigger alerts)
• Environment variables
• Tables without primary keys (logical replication won't work without them)
• Creating the replica (Aptible command + environment variables)
• Speeding up the initial sync
• Monitoring replication progress
• Reindexing the data
• Autovacuum on the target
• Fixing sequences
• Aligning target DB settings with the source
• Cutover strategy (how we coordinate pointing the app to the upgraded database)
• Rollback strategy

I was genuinely excited to work on this. It was the end of the year, and if everything went well, we'd start the new year paying nearly 50% less for the database while also running a newer and faster version of PostgreSQL.

With the plan written down and reviewed by a peer (minor feedback, green light), I moved on to actually running the thing. Fast forward to kicking off aptible db:replicate to create the replica and start replication and… khaboom.

This is the error I immediately ran into:

INFO -- : ║ 2025-11-25 18:16:25 UTC HINT:  You might need to increase "max_locks_per_transaction".
INFO -- : ║ pg_restore: error: could not execute query: ERROR:  out of shared memory
INFO -- : ║ HINT:  You might need to increase "max_locks_per_transaction".
INFO -- : ║ Command was: CREATE SEQUENCE schema1.activities_id_seq
INFO -- : ║     START WITH 1
INFO -- : ║     INCREMENT BY 1
INFO -- : ║     NO MINVALUE
INFO -- : ║     NO MAXVALUE
INFO -- : ║     CACHE 1;

Given how many schemas and database objects we have, this wasn't entirely surprising. With the default settings, the replica simply couldn't be provisioned within a single transaction. We were blowing past the default limits.

To be clear about what I mean by object count, this is what I was looking at:

SELECT count(*) 
FROM pg_class 
WHERE relkind IN ('r','p','i','S','t','m','v','I');

The obvious fix here is to increase max_locks_per_transaction (and max_pred_locks_per_transaction), so that's what I did. I bumped them up significantly from the default value of 64. I even took a screenshot so I'd remember exactly how far I pushed them.

And yet the replica still failed to provision. At that point, I reached out to Aptible support.

They were friendly and responsive, but the back-and-forth dragged on. "Try increasing it more." Then more. Try this, try that. Then bump up even more. This went on for about a week. I'm compressing the exchange here, but troubleshooting something like this over email is hard enough, doing it live in production is even harder. Not trying to talk down on Aptible support here and henceforth.

Eventually, we jumped on a call (I should have done that sooner). That's when things finally clicked.

I was able to demonstrate that configuration changes I was applying to the source database were not being applied to the replica database at all. Every attempt, every tweak, every retry had been happening against a replica that never picked up the new settings.

Once they realized that, everything suddenly made sense. And honestly, it was great news.

After a couple more status updates over email, and one Thanksgiving holiday later, I got the notice that the new release had shipped:

That was my cue to start the replication test immediately. I followed the steps from the document I had put together earlier, and for the first day or so everything looked fine. Initialization was progressing as expected. I was keeping an eye on it using a small script for tracking progress, in case you're curious. Then, the very next day an unwelcome surprise.

Reading that update was frustrating. The issue only affected newly created replicas, not the one I was actively testing, but it still clearly needed to be fixed. This wasn't something we could just ignore. A day or two later, while replication was still ongoing, another email landed.:

At that point, I wasn't confident this was going to get resolved quickly enough for us to complete the upgrade over the holidays. Problems like this are hard, and as a customer you only ever see your own use case. There are always hidden complexities, especially the ones you don't have visibility into. Because of that, I empathize with the Aptible support and engineering teams.

I let my manager know that I was starting work on an alternative plan for the upgrade. This was supposed to be a side quest. It was clearly turning into the main one.

That's it for part one. If this was interesting and you're curious about what happened next, part two will cover that (maybe).

I used ChatGPT to help clean up grammar, since I'm not a native speaker. The ideas, opinions, and descriptions are mine. The header image was generated using this prompt:

I want you to generate image with postgresql logo in it and somehow we paint over 14 number 17, background needs to be dark because my web styling is white.