Обсуждение: best practice to patch a postgresql version?

We are planning to use postgresq on production, but there is one question about how to patch a db. We don't want to overwrite the old version directly, so that we can rollback if the new version has issues.  So we want to install it a different location such as /home/postgres/14.1 for version 14.1 (all binary should be under 14.1 or sub-fold of 14.1) and /home/postgres/14.0 for 14.0, in this way we can easily switch between different versions. But apt install on ubuntu doesn't have the option for a customized location. So what's the best practice to patch postgres?

Thanks Ron,

But on a critical production database, we need to cut down the downtime as much as possible. If just remove a version, and then install a new version, both of them need a downtime. If we can install several versions on different location, switching version will have a shorter downtime: just stop the old version and start using the new binary, and we have no downtime when remove/install a new version.

On Mon, Dec 26, 2022 at 11:54 PM Ron <ronljohnsonjr@gmail.com> wrote:

Just downgrade the packages if you need to revert to a previous version.

Remove the 14.5 package, and install the 14.4 package (because no one's crazy enough to start with 14.0 in December 2022).  You'll have to explicitly specify the version number.

On 12/26/22 03:29, qihua wu wrote:

We are planning to use postgresq on production, but there is one question about how to patch a db. We don't want to overwrite the old version directly, so that we can rollback if the new version has issues.  So we want to install it a different location such as /home/postgres/14.1 for version 14.1 (all binary should be under 14.1 or sub-fold of 14.1) and /home/postgres/14.0 for 14.0, in this way we can easily switch between different versions. But apt install on ubuntu doesn't have the option for a customized location. So what's the best practice to patch postgres?

--
Born in Arizona, moved to Babylonia.

On 2022-12-27 13:33:06 +0800, qihua wu wrote:
> But on a critical production database, we need to cut down the downtime as much
> as possible. If just remove a version, and then install a new version, both of
> them need a downtime.

I'm pretty sure that an `apt install` first replaces all the binaries
and only then restarts the database. But even if does shudown the
database before replacing the binaries and starts it afterwards, the
database is down maybe for a second or two.

> If we can install several versions on different location,
> switching version will have a shorter downtime: just stop the old version and
> start using the new binary, and we have no downtime when remove/install a new
> version.

I doubt that you are much faster.

And if your application can't tolerate a downtime of a few seconds I
hope you have some cluster solution with multiple active nodes running
already.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

On Tue, 2022-12-27 at 00:48 -0600, Ron wrote:
> If it really is a critical production database, you will have a CAT/UAT (customer/user acceptance testing)
> server on which you rigorously run regression tests on a point release for a month before updating the production
server.
>
> Otherwise, it's a hope-and-pray database.

No, that is wrong.

You should not test your application when you install a minor update.  The reason is that
few people are willing to test the application thoroughly every few months, and the outcome
is that minor releases are *not* applied regularly, as they should be.

You are supposed to trust PostgreSQL development that they don't introduce new bugs.
Sure, this can happen, even though all possible care is taken with backpatches.  I have
seen it happen once or twice in the 15+ years I have been dealing with PostgreSQL.
In that case, a new minor release will come out soon afterwards.

Concerning the risks that you have to consider here (running into a known bug that is
fixed in the latest minor release versus running into a bug introduced by the latest
minor release), the PostgreSQL page makes this statement:

> For minor releases, the community considers not upgrading to be riskier than upgrading.

Of course you will update your test databases first, to make sure that your update
procedure is working well.

Yours,
Laurenz Albe

On 1/5/23 23:43, Laurenz Albe wrote:
> On Tue, 2022-12-27 at 00:48 -0600, Ron wrote:
>> If it really is a critical production database, you will have a CAT/UAT (customer/user acceptance testing)
>> server on which you rigorously run regression tests on a point release for a month before updating the production
server.
>>
>> Otherwise, it's a hope-and-pray database.
> No, that is wrong.
>
> You should not test your application when you install a minor update.  The reason is that
> few people are willing to test the application thoroughly every few months, and the outcome
> is that minor releases are *not* applied regularly, as they should be.
>
> You are supposed to trust PostgreSQL development that they don't introduce new bugs.
> Sure, this can happen, even though all possible care is taken with backpatches.  I have
> seen it happen once or twice in the 15+ years I have been dealing with PostgreSQL.
> In that case, a new minor release will come out soon afterwards.

It's absolutely standard practice "in the enterprise" to install the latest 
patch on the UAT (and possibly Dev and Staging) servers before rolling out 
to production.

Have I aver seen a problem in Postgresql?  No.  But I've seen problems with 
other RDBMSs.  If a problem did happen, and caused for example, an important 
report to suddenly take 3 hours instead of 3 minutes, the client will 
scream; there might even be SLA penalties.

Thus, we're cautious with "critical production databases".

-- 
Born in Arizona, moved to Babylonia.