Re: 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(dot)klaver(at)aklaver(dot)com