Presented by:

2435beeff0947be9c9675b4ef2524e5e

Brian Fehrle

Command Prompt

Brian has worked in the open source world since 2008. Starting as an apprentice, he quickly dedicated his efforts to becoming a seasoned professional, promoting PostgreSQL as the forefront of his career. He works to understand not just database systems but the applications powered by them as well.

As a serial hobbyist, he is a “jack of random trades” and is always wanting to learn something new. From Raspberry Pi projects to making soap, homebrewing, to cheese making. He is working toward a future in organic regenerative agriculture in a decentralized ecosystem.

No video of the event yet, sorry!

Is your Postgres install looking more like a Woolly mammoth these days? Getting behind on version upgrades is tough, and the farther behind you get the more work may be needed to make it work, but there's many options to get it done.

This talk's goal is to help learn how to solve the "yeah but" when addressing an upgrade. "Just run pg_upgrade" - "yeah but that will take 12 hours, I only have 15 minutes!"

We will go over high level things that you need to know to do a very large version jump, such as version 9.6 to 17, and the questions that must be asked to ensure the best path is taken. Have a small database with few users? Easy! A giant database with 99.99% up-time guarantee and millions of active users? Buckle up.

Talk Sections:

Know your application and environment. Planning out your upgrade path will require knowing how your application works. The goal is to do the upgrade with the least amount of moving parts possible, all with the least amount of impact.

Ask questions like:

  1. How big is your database? How complex is it?
  2. What level of downtime can you afford?
  3. What type of rollback is required if something goes wrong (what amount of post-upgrade new data can be lost in a rollback)?
  4. What is your infrastructure? Bare metal? Cloud? Containers? Limited hardware / budget? Can you snapshot drives?

Things to know about major version releases Making a large jump from an old version to a new one transcends years of additions and changes all at once. It's best to know about all the changes, big or small, and understand how they can affect your applications, including bug fixes that may affect the way things work.

  1. Review the version feature matrix. New fancy things all on one page!
  2. Review the Release Notes. Learn what's added in major versions, performance improvements, and new configuration options you've never seen before.
  3. Learn about JIT.
  4. Ultimately: TEST TEST TEST. Do a test upgrade and test everything. Functionality, speed, stress testing, everything.

Clever Upgrade Options and Examples Based on the answers to knowing the environment and requirements, we can discuss the different options for the upgrade process. pg_upgrade works, of course, but unless you have a tiny boring database, it's not always the best option.

This is where we turn the "yeah but" responses into clever solutions to upgrade the database, while fulfilling all requirements and timeframes (within sane reason)

Example upgrade paths:

  1. Boring old pg_upgrade
  2. Spicy pg_upgrade with in-place option
  3. Dump / Restore into a new environment.
  4. Logical Replication to new environment.
  5. Are you really old? pglogical to new environment.
  6. Combination upgrades.

Real World Examples

This section would have some examples of upgrades we have done, some struggles that we experienced and our solutions for them.

Questions and answers Hopefully people arrived with questions and we can have a good discussion.

Date:
Duration:
50 min
Room:
Conference:
Postgres Conference 2025
Language:
Track:
Essentials
Difficulty:
Easy