Re: Upgrade 9.5 cluster on Ubuntu 16.04

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(at)gmail(dot)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 <vanuatoo(at)gmail(dot)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
>>
>