v11.5- v15.3 upgrade (linux)

On 3/6/24 16:19, David Gauthier wrote:

Hi:
I'm a PG user in a big corp with an IT dept that administers a PG
server/instance that I use.  It's an old install, v11.5, and we need to
upgrade to v15.3.  They want to bring the upgraded DB up on a new linux
vm which has OS upgrades of its own.  So it's a move AND an upgrade.
There are 2 concerns....

First has to do with a jump from 11.5 - 15.3 ?  Is it safe to do this
given so many major intermediate versions being skipped ?

I would definitely read the release notes for 12.0, 13.0, 14.0 and 15.0.
They will show the breaking changes.

Second has to do with the 11.5 having the perlplu extension installed.
The DBA created a v15.3 instance on the new server and tried to restore
an 11.5 backup onto/into the 15.3 (as an experiment) but got several
error messages like this... "ERROR:extension "plperlu" is not
available".  I really didn't need any of the perlplu procs/funcs I

How are and from where are you installing the Postgres package(s)?

Generally the procedural languages packages are separate from the server
package. I'm betting the error is occurring because the plperlu package
was not installed for the 15.3 instance of Postgres.

created, so I dropped them all but the error persists.  I suggested to
drop the perlplu extension in the 11.5 (drop extension perlplu cascade)
but there is a concern that it might break something.  So the question
is... Will dropping the perlplu extension break anything (given that
there are no perlplu procs/funcs).  Just to be safe, "select
l.lanname,count(*) from pg_proc p, pg_language l WHERE p.prolang = l.oid
group by 1;" shows that there are no perlplu procs/fns.

The would be plan 2 if installing the plperlu package is not possible.

Finally, what is the best approach to making the server move AND PG
upgrade...
1) backup the 11.5 and restore into a 15.3 PG instance on the upgraded
server ?

I would backup the 11.5 instance whatever else you plan to do, better
safe then sorry.

2) upgrade the 11.3 DB to 15.3, back that up and then restore on the
upgraded server ?
3) upgrade the 11.3 DB to 15.3, then set the 15.3 destination server as
a replicated DB (let it populate) then designate the destination server
as the primary then cut the old server loose ?  (this approach would
probably minimize DB downtime by a lot I would think)

The above is going to depend on the size of the database and any
possible breaking/behavioral changes you find in the release notes.

If you have the ability to do a test migration on a test machine where
oops are not a problem that would be way to verify the 2) and 3) options.

Any other suggestions ?

Thanks for any advise/help !

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Mar 6, 2024 at 07:19:20PM -0500, David Gauthier wrote:

Hi:  
I'm a PG user in a big corp with an IT dept that administers a PG server/
instance that I use.  It's an old install, v11.5, and we need to upgrade to
v15.3.  They want to bring the upgraded DB up on a new linux vm which has OS
upgrades of its own.  So it's a move AND an upgrade. There are 2 concerns....

First has to do with a jump from 11.5 - 15.3 ?  Is it safe to do this given so
many major intermediate versions being skipped ?

Why are you going to 15.3 when 15.6 is the most recent minor PG 15
version?

https://www.postgresql.org/support/versioning/

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Wed, Mar 6, 2024 at 7:19 PM David Gauthier <dfgpostgres@gmail.com> wrote:

Hi:
I'm a PG user in a big corp with an IT dept that administers a PG
server/instance that I use. It's an old install, v11.5, and we need to
upgrade to v15.3. They want to bring the upgraded DB up on a new linux vm
which has OS upgrades of its own. So it's a move AND an upgrade.

Have them upgrade the current server to 11*.22*. It's trivially easy, with
only a few minutes of down time.

Remember (and tell your IT dept) that PG point releases do not add new
features: only bug fixes. That means point releases aren't nearly as risky
as other products.

Bug fixes for problems (like slow queries) you didn't realize you had, or
would have if you later implemented a feature with the bug. (For example,
an aggravating weekly manual activity disappeared when I was allowed to
update from 9.6.6 to 9.6.24.)

And, of course, security bug fixes, Won't you have egg on your face when
it turns out that black hats stole data because of a bug fixed in a later
release?

On Thu, Mar 7, 2024 at 10:49 AM David Gauthier <dfgpostgres@gmail.com>
wrote:

On 3/7/24 07:47, David Gauthier wrote:
Please reply to list also.
Ccing list

I would definitely read the release notes for 12.0, 13.0, 14.0 and 15.0.

Check !  None of the breaking changes affect me.

How are and from where are you installing the Postgres package(s)?

IT does the installs.  I'd have to check with them.
What would you recommend ? (again, linux installs)

I would use the packaging system for whatever Linux distro you are on.
In particular, if available, the community repos from here:

https://www.postgresql.org/download/

They generally offer more choice of Postgres versions for a given distro
version.

.The would be plan 2 if installing  the plperlu package is not possible

We do not want or need plperlu on the 15.3 DB

Then you can skip installing that package, assuming you have cleared out
all references to plperlu in the 11 instance.

I would backup the 11.5 instance...

Absolutely.  In fact, IT was planning on doing an 11.5 backup and
restore on 15.3.  Unsure if restoring an 11.5 into a 15.3 is a wise
approach.  Any thoughts on that?

According to item below that was already tested and worked. Is that correct?

The above is going to depend on the size of the database

Relative to what PG can do, this one is small (a few million
records/table tops).  No partitioning.

If you have the ability to do a test migration on a test machine

where oops are not a problem that would be way to verify the 2) and 3)
options.
Already done.  He restored the 11.5 on 15.3 and I tested it.  Works fine.

So good to go, depending on extent of testing.

I don't think I'll be able to sell the "3" option above to the IT
group.  I think they'll want to do something simpler and with fewer steps.
So the question is... option 1 or 2 ?  Or does it matter, either can work ?

If it where me it would be 1) as that gives you a fallback provision to
the original 11 instance.

--
Adrian Klaver
adrian.klaver@aklaver.com

Greetings,

On 3/6/24 19:19, David Gauthier wrote:

Hi:
I'm a PG user in a big corp with an IT dept that administers a PG
server/instance that I use.  It's an old install, v11.5, and we need
to upgrade to v15.3.  They want to bring the upgraded DB up on a new
linux vm which has OS upgrades of its own.  So it's a move AND an
upgrade. There are 2 concerns....

First has to do with a jump from 11.5 - 15.3 ?  Is it safe to do this
given so many major intermediate versions being skipped ?

Generally speaking, it is safe from database point of view but you have
to verify that application is working as expected with PostgreSQL 15,
driver update, any query performance issues, any deprecate features in
use, collation differences, and performance verification, etc.

PostgreSQL supports dump/restore (slow and longer downtime), binary
upgrade using pg_upgrade (faster and low downtime), and logical
replication (complex and least downtime). Since OS upgrade is also part
of the equation, dump/restore or logical  are better candidates. Due to
OS collation difference I would avoid binary upgrade path.

--
Kind Regards,
Yogesh Sharma
PostgreSQL, Linux, and Networking Expert
Open Source Enthusiast and Advocate
PostgreSQL Contributors Team @ RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com

Thanks for the reply.

When you say "dump/restore" do you mean pg_dump then running the resulting
SQL into the destination DB?
I like the replication option myself best (min downtime), especially as we
use a DB alias for connections. But I don't think I'll be able to sell
that to the IT group.

Regarding the safety of running a "drop extension plperlu cascade" on the
v11.5, would you consider that to be safe GIVEN that there are no plperlu
procs that it will affect? I need to be able to tell IT that I'm not the
only one who thinks that it's safe.

On Fri, Mar 8, 2024 at 11:28 AM Yogesh Sharma <
yogesh.sharma@catprosystems.com> wrote:

On 3/8/24 08:57, David Gauthier wrote:

Thanks for the reply.

When you say "dump/restore" do you mean pg_dump then running the
resulting SQL into the destination DB?
I like the replication option myself best (min downtime), especially as
we use a DB alias for connections.  But I don't think I'll be able to
sell that to the IT group.

You said the dump/restore has been tested.

How long did that take?

Regarding the safety of running a "drop extension plperlu cascade" on
the v11.5, would you consider that to be safe GIVEN that there are no
plperlu procs that it will affect?  I need to be able to tell IT that
I'm not the only one who thinks that it's safe.

BEGIN;

DROP LANGUAGE plperlu;

ROLLBACK;

See if the above complains about anything depending on it.

If not:

DROP EXTENSION plperlu;

On Fri, Mar 8, 2024 at 11:28 AM Yogesh Sharma
<yogesh.sharma@catprosystems.com
<mailto:yogesh.sharma@catprosystems.com>> wrote:

Greetings,

On 3/6/24 19:19, David Gauthier wrote:

Hi:
I'm a PG user in a big corp with an IT dept that administers a PG
server/instance that I use.  It's an old install, v11.5, and we need
to upgrade to v15.3.  They want to bring the upgraded DB up on a new
linux vm which has OS upgrades of its own.  So it's a move AND an
upgrade. There are 2 concerns....

First has to do with a jump from 11.5 - 15.3 ?  Is it safe to do

this

given so many major intermediate versions being skipped ?

Generally speaking, it is safe from database point of view but you have
to verify that application is working as expected with PostgreSQL 15,
driver update, any query performance issues, any deprecate features in
use, collation differences, and performance verification, etc.

PostgreSQL supports dump/restore (slow and longer downtime), binary
upgrade using pg_upgrade (faster and low downtime), and logical
replication (complex and least downtime). Since OS upgrade is also part
of the equation, dump/restore or logical  are better candidates. Due to
OS collation difference I would avoid binary upgrade path.

--
Kind Regards,
Yogesh Sharma
PostgreSQL, Linux, and Networking Expert
Open Source Enthusiast and Advocate
PostgreSQL Contributors Team @ RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com <https://aws.amazon.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

On 3/8/24 09:09, Adrian Klaver wrote:

On 3/8/24 08:57, David Gauthier wrote:

Thanks for the reply.

When you say "dump/restore" do you mean pg_dump then running the
resulting SQL into the destination DB?
I like the replication option myself best (min downtime), especially
as we use a DB alias for connections.  But I don't think I'll be able
to sell that to the IT group.

You said the dump/restore has been tested.

How long did that take?

Regarding the safety of running a "drop extension plperlu cascade" on
the v11.5, would you consider that to be safe GIVEN that there are no
plperlu procs that it will affect?  I need to be able to tell IT that
I'm not the only one who thinks that it's safe.

BEGIN;

DROP LANGUAGE plperlu;

Arrgh. That should be

DROP EXTENSION plperlu;

ROLLBACK;

See if the above complains about anything depending on it.

If not:

DROP EXTENSION plperlu;

On Fri, Mar 8, 2024 at 11:28 AM Yogesh Sharma
<yogesh.sharma@catprosystems.com
<mailto:yogesh.sharma@catprosystems.com>> wrote:

    Greetings,

    On 3/6/24 19:19, David Gauthier wrote:
     > Hi:
     > I'm a PG user in a big corp with an IT dept that administers a PG
     > server/instance that I use.  It's an old install, v11.5, and we
need
     > to upgrade to v15.3.  They want to bring the upgraded DB up on
a new
     > linux vm which has OS upgrades of its own.  So it's a move AND an
     > upgrade. There are 2 concerns....
     >
     > First has to do with a jump from 11.5 - 15.3 ?  Is it safe to do
    this
     > given so many major intermediate versions being skipped ?

    Generally speaking, it is safe from database point of view but you
have
    to verify that application is working as expected with PostgreSQL 15,
    driver update, any query performance issues, any deprecate
features in
    use, collation differences, and performance verification, etc.

    PostgreSQL supports dump/restore (slow and longer downtime), binary
    upgrade using pg_upgrade (faster and low downtime), and logical
    replication (complex and least downtime). Since OS upgrade is also
part
    of the equation, dump/restore or logical  are better candidates.
Due to
    OS collation difference I would avoid binary upgrade path.

    --     Kind Regards,
    Yogesh Sharma
    PostgreSQL, Linux, and Networking Expert
    Open Source Enthusiast and Advocate
    PostgreSQL Contributors Team @ RDS Open Source Databases
    Amazon Web Services: https://aws.amazon.com <https://aws.amazon.com&gt;

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks.

On Fri, Mar 8, 2024 at 12:12 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote: