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 <samokhva...@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/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 <vanua...@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 >> >
