Thread

1. Clarification Needed on PostgreSQL Version Upgrade

   Hamza Godil <hgodil@realtimeca.com> — 2024-12-04T19:16:40Z

   Hello PostgreSQL Experts,
   
   I have a question regarding PostgreSQL versions and would appreciate some clarification. We have two servers:
   
   
     *   CCFMG-apps05 - Windows Server 2019
     *   CCFMG-VP - Windows Server 2019
   
   
   Both servers are currently running PostgreSQL version 14, while the latest version available is 16.1. My question is: if we upgrade to the newest version, will it cause any disruptions or errors in our work? Additionally, should we change or copy the database before proceeding with the upgrade?
   
   Please let me know your recommendations.
   
   Thank you.
   
   Cordially,
   
   Hamza Godil
   Real Time Information Services, Inc.
   191 W. Shaw Ave #106 | Fresno, CA 93704
   (559) 222-6456 | HGodil@realtimeca.com<mailto:HGodil@realtimeca.com>
   www.realtimeca.com<http://www.realtimeca.com/>
   
   [cid:image001.png@01DB463D.DE8F3C40]
   
   The information contained in this communication and its attachment(s) is intended only for the use of the individual(s) to whom it is addressed and may contain information that is privileged, confidential, or exempt from disclosure. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender and delete the communication without retaining any copies.
   
   

2. Re: Clarification Needed on PostgreSQL Version Upgrade

   Steve Midgley <science@misuse.org> — 2024-12-05T18:40:46Z

   On Thu, Dec 5, 2024 at 8:33 AM Hamza Godil <HGodil@realtimeca.com> wrote:
   
   > Hello PostgreSQL Experts,
   >
   >
   >
   > I have a question regarding PostgreSQL versions and would appreciate some
   > clarification. We have two servers:
   >
   >
   >
   >    - CCFMG-apps05 – Windows Server 2019
   >    - CCFMG-VP – Windows Server 2019
   >
   >
   >
   > Both servers are currently running PostgreSQL version 14, while the latest
   > version available is 16.1. My question is: if we upgrade to the newest
   > version, will it cause any disruptions or errors in our work? Additionally,
   > should we change or copy the database before proceeding with the upgrade?
   >
   >
   >
   > Please let me know your recommendations.
   >
   >
   >
   The answers deeply depend on the nature of the systems and businesses that
   depend on these systems. Let's assume these databases are highly sensitive
   to downtime and data loss (aka "production" systems). Even then, can you
   have a "cut-over" downtime period of 5 min or an hour? Or what's the
   window? You'll need to answer questions like that.
   
   Let's assume you can take a comfortable 3 hour maintenance window to
   cut-over.
   
   First you have to validate that all the systems riding on top of Pg don't
   break when you upgrade. Can you do that in a staging environment? If so,
   set up a v16.1 staging system and run and test all your apps on that.
   
   As you're getting comfortable that the apps work, you're going to write
   some scripts that can dump, copy, and restore everything. Do that process
   over and over from prod to staging to prove you know how to do it reliably.
   
   Then prove you know how to rollback changes from the new system back to the
   old system (a reverse data load from 16 back to 14). Don't skip this step.
   You hope you never need it, but if you do need it, it is unhealthy to try
   to come up with a plan and tech in the moment.
   
   Make an easy way to redirect traffic from one set of servers to the other
   (14 to 16).
   
   Then set up a prod v16 server and wait for your window.
   Shut off all inbound activity
   Wait for all activity to settle down to zero
   Dump data from Pg 14 servers with your scripts
   Restore data to new Pg 16 servers with your scripts
   Verify closely that everything worked (no errors you aren't expecting)
   Redirect traffic to 16
   Reopen inbound activity
   
   If all goes well, you're done. There are probably ways to simplify this,
   but if you are dealing with high value data, this is how I would think
   about it. Maybe you can get away with an "in-place upgrade" but I've been
   punished (not by Pg!) doing database upgrades in place and then stuff
   doesn't work and I can't go back to the old version.
   
   I hope this type of general input is helpful,
   Steve