Обсуждение: v11.5- v15.3 upgrade (linux)
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 !
Thanks for any advise/help !
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:
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.
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.
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
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
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
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