Thread

1. v11.5- v15.3 upgrade (linux)

   David Gauthier <dfgpostgres@gmail.com> — 2024-03-07T00:19:20Z

   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 ?
   
   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 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.
   
   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 ?
   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)
   
   Any other suggestions ?
   
   Thanks for any advise/help !
   

2. Re: v11.5- v15.3 upgrade (linux)

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-07T01:55:49Z

   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
   
   
   
   
   

3. Re: v11.5- v15.3 upgrade (linux)

   Bruce Momjian <bruce@momjian.us> — 2024-03-07T02:41:44Z

   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.
   
   
   
   

4. Re: v11.5- v15.3 upgrade (linux)

   Ron Johnson <ronljohnsonjr@gmail.com> — 2024-03-07T05:53:01Z

   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.
   

5. Re: v11.5- v15.3 upgrade (linux)

   Ron Johnson <ronljohnsonjr@gmail.com> — 2024-03-07T15:56:55Z

   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:
   
   > Because 15.3 is the latest they have installed at this time.  I could
   > petition to get something even more recent.  But it would only make sense
   > if it's a significant win and not just options/features that we don't use.
   >
   > On Thu, Mar 7, 2024 at 12:53 AM Ron Johnson <ronljohnsonjr@gmail.com>
   > wrote:
   >
   >> 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.
   >>
   >>
   

6. Re: v11.5- v15.3 upgrade (linux)

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-07T16:16:19Z

   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
   
   
   
   
   

7. Re: v11.5- v15.3 upgrade (linux)

   Yogesh Sharma <yogesh.sharma@catprosystems.com> — 2024-03-08T16:28:20Z

   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
   
   
   
   
   

8. Re: v11.5- v15.3 upgrade (linux)

   David Gauthier <dfgpostgres@gmail.com> — 2024-03-08T16:57:18Z

   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:
   
   > 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
   >
   >
   

9. Re: v11.5- v15.3 upgrade (linux)

   Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-08T17:09:52Z

   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>
   > 
   
   -- 
   Adrian Klaver
   adrian.klaver@aklaver.com
   
   
   
   
   

10. Re: v11.5- v15.3 upgrade (linux)

    Adrian Klaver <adrian.klaver@aklaver.com> — 2024-03-08T17:12:07Z

    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>
    >>
    > 
    
    -- 
    Adrian Klaver
    adrian.klaver@aklaver.com
    
    
    
    
    

11. Re: v11.5- v15.3 upgrade (linux)

    David Gauthier <dfgpostgres@gmail.com> — 2024-03-08T18:24:52Z

    Thanks.
    
    On Fri, Mar 8, 2024 at 12:12 PM Adrian Klaver <adrian.klaver@aklaver.com>
    wrote:
    
    > 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
    > >
    > >>
    > >
    >
    > --
    > Adrian Klaver
    > adrian.klaver@aklaver.com
    >
    >