Обсуждение: Upgrade 9.5 cluster on Ubuntu 16.04
Hello,
I've got 2 VMs with Postgresql 9.5 cluster with streaming replication.
VMs have Ubuntu 16.04.
I would like to upgrade Ubuntu and Postgresql to newer versions.
Ubuntu 16.04 supports upgrading to 18.04.
What is the safest way to upgrade Postgresql cluster along with it?
The database is not big and I can afford completely shutdown the cluster during the upgrade.
What would you suggest?
Kind regards,
Vano
On 9/2/21 10:38 AM, Vano Beridze wrote: > Hello, > > I've got 2 VMs with Postgresql 9.5 cluster with streaming replication. > VMs have Ubuntu 16.04. > I would like to upgrade Ubuntu and Postgresql to newer versions. > Ubuntu 16.04 supports upgrading to 18.04. > What is the safest way to upgrade Postgresql cluster along with it? What repo are you getting Postgres from, Ubuntu or PGDG? > The database is not big and I can afford completely shutdown the cluster > during the upgrade. That is going to happen whether you want it or not 16.04 -> 18.04 is going to involve a reboot. > > What would you suggest? > > Kind regards, > Vano -- Adrian Klaver adrian.klaver@aklaver.com
Logical way – dump/restore.
Bringing PGDATA physically may lead to corrupted indexes due to glibc version changes. 16.04 -> 18.04 shouldn't cause it, but it may. You can check btree index with amcheck and GIN indexes with a patched version of it (some backporting would be needed). You can find examples here: https://gitlab.com/-/snippets/2001962
But back to the question, if you do dump/restore, indexes will be freshly created, on new machine - therefore there is no question about corruption for them. I'd choose this path in your situation.
On Thu, Sep 2, 2021 at 10:38 AM Vano Beridze <vanuatoo@gmail.com> wrote:
Hello,I've got 2 VMs with Postgresql 9.5 cluster with streaming replication.VMs have Ubuntu 16.04.I would like to upgrade Ubuntu and Postgresql to newer versions.Ubuntu 16.04 supports upgrading to 18.04.What is the safest way to upgrade Postgresql cluster along with it?The database is not big and I can afford completely shutdown the cluster during the upgrade.What would you suggest?Kind regards,Vano
> What repo are you getting Postgres from, Ubuntu or PGDG?
Ubuntu
On Thu, Sep 2, 2021 at 10:13 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/2/21 10:38 AM, Vano Beridze wrote:
> Hello,
>
> I've got 2 VMs with Postgresql 9.5 cluster with streaming replication.
> VMs have Ubuntu 16.04.
> I would like to upgrade Ubuntu and Postgresql to newer versions.
> Ubuntu 16.04 supports upgrading to 18.04.
> What is the safest way to upgrade Postgresql cluster along with it?
What repo are you getting Postgres from, Ubuntu or PGDG?
> The database is not big and I can afford completely shutdown the cluster
> during the upgrade.
That is going to happen whether you want it or not 16.04 -> 18.04 is
going to involve a reboot.
>
> What would you suggest?
>
> Kind regards,
> Vano
--
Adrian Klaver
adrian.klaver@aklaver.com
On 9/2/21 11:34 AM, Vano Beridze wrote: > > What repo are you getting Postgres from, Ubuntu or PGDG? > Ubuntu So you are going to get version 10. Is that what you want? If you want to move to something newer then this maybe the time to move to the PGDG repos, as they allow you to install any of the current supported versions of Postgres. -- Adrian Klaver adrian.klaver@aklaver.com
I just want to upgrade to the OS and Postgres that is supported.
If I just stop the service on both VMs and upgrade Ubuntu, then start Postgres, will it work or I should go with clean install and dump/restore?
On Thu, Sep 2, 2021 at 10:41 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 9/2/21 11:34 AM, Vano Beridze wrote:
> > What repo are you getting Postgres from, Ubuntu or PGDG?
> Ubuntu
So you are going to get version 10.
Is that what you want?
If you want to move to something newer then this maybe the time to move
to the PGDG repos, as they allow you to install any of the current
supported versions of Postgres.
--
Adrian Klaver
adrian.klaver@aklaver.com
On Thursday, September 2, 2021, Vano Beridze <vanuatoo@gmail.com> wrote:
I just want to upgrade to the OS and Postgres that is supported.If I just stop the service on both VMs and upgrade Ubuntu, then start Postgres, will it work or I should go with clean install and dump/restore?
Personally I find the concept of upgrading an O/S to be distasteful, especially when using VMs. Build out the new environment, migrate the data, then destroy the old one. In any case I would hope upgrading to 18.04 wouldn’t automatically upgrade PostgreSQL.
For me, the two main questions are whether to use pg_dump or pg_upgrade, and whether to upgrade PostgreSQL before or after upgrading the OS. Upgrading both simultaneously isn’t a valid choice, even if you could figure out a way to actually make that possible. Either way a pg_basebackup and restoring that is probably the simplest way to get the database itself moved from one server to the other.
David J.
On 9/2/21 11:55 AM, Vano Beridze wrote: > I just want to upgrade to the OS and Postgres that is supported. > If I just stop the service on both VMs and upgrade Ubuntu, then start > Postgres, will it work or I should go with clean install and dump/restore? Prudent behavior before you do any upgrading would be to: 1) Take pg_dumpall of cluster just before shutting it down. 2) Copy that dump off the VM's for extra safety. Then you have choice of: 1) On new upgraded OS there will be a new 10 cluster created that has only the template0, template1 and postgres databases. psql -f dump.sql postgres per example here https://www.postgresql.org/docs/10/app-pg-dumpall.html 2) Or use the pg_upgrade procedure shown here: https://askubuntu.com/questions/1126847/will-a-release-upgrade-take-care-of-postgresql-upgrade-automatically I would say if the database(s) are not that big I would go with 1). > > On Thu, Sep 2, 2021 at 10:41 PM Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 9/2/21 11:34 AM, Vano Beridze wrote: > > > What repo are you getting Postgres from, Ubuntu or PGDG? > > Ubuntu > > So you are going to get version 10. > > Is that what you want? > > If you want to move to something newer then this maybe the time to move > to the PGDG repos, as they allow you to install any of the current > supported versions of Postgres. > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com
Forgot to mention: the same concerns about possible index corruption are relevant for the pg_upgrade option too (because it doesn't rebuild indexes). So, I'd definitely choose dump/restore if the database is small. In the case of pg_upgrade, I would rebuild all the indexes during the maintenance window or just some of them (which ones – amcheck can help to answer, for 9.5 it should be taken from here: https://github.com/petergeoghegan/amcheck).
On Thu, Sep 2, 2021 at 11:17 AM Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
Logical way – dump/restore.Bringing PGDATA physically may lead to corrupted indexes due to glibc version changes. 16.04 -> 18.04 shouldn't cause it, but it may. You can check btree index with amcheck and GIN indexes with a patched version of it (some backporting would be needed). You can find examples here: https://gitlab.com/-/snippets/2001962But back to the question, if you do dump/restore, indexes will be freshly created, on new machine - therefore there is no question about corruption for them. I'd choose this path in your situation.On Thu, Sep 2, 2021 at 10:38 AM Vano Beridze <vanuatoo@gmail.com> wrote:Hello,I've got 2 VMs with Postgresql 9.5 cluster with streaming replication.VMs have Ubuntu 16.04.I would like to upgrade Ubuntu and Postgresql to newer versions.Ubuntu 16.04 supports upgrading to 18.04.What is the safest way to upgrade Postgresql cluster along with it?The database is not big and I can afford completely shutdown the cluster during the upgrade.What would you suggest?Kind regards,Vano