From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Upgrading from Postgresql 9.3.8 to 9.6.10
Date: Tue, 26 Mar 2019 01:14:32 -0500
Message-ID: <0e3636ec-ba56-a765-2a5a-84ef01072604@gmail.com> (raw)
In-Reply-To: <CACuBw0i+mWJHT5-wO+SnERiED-oEGGSFkxw_M+4dGbnNm5VgAw@mail.gmail.com>
References: <CACuBw0i+mWJHT5-wO+SnERiED-oEGGSFkxw_M+4dGbnNm5VgAw@mail.gmail.com>
On 3/26/19 12:51 AM, Yuri Niyazov wrote:
> We have an old cluster, running 9.3.8, that we are trying to upgrade to
> 9.6.10.
>
> After running pg_upgrade and starting the server, and testing some common
> queries against it, we experienced the following error:
>
> PG::IndexCorrupted: ERROR: index "table_pkey" contains unexpected zero
> page at block 17021871 HINT: Please REINDEX it.
>
> So, if I am reading this correctly, there is an index that 9.3.8 created,
> and it recognizes that index as OK, but 9.6.10 thinks that index is
> corrupt. Since this happened on one index, it seems reasonable to assume
> that this could happen on any index in that database,
Yes, *could*.
> and we need to reindex the entire database.
That does not seem justified.
> Now, this is a live application, and we would like to minimize continuous
> downtime (multiple short downtimes are fine), so what we are considering
> doing is a manual reindex: for each index, create a new one, and then drop
> the old one, and after all that, upgrade.
Won't work if you've got lots of FK constraints.
But "REINDEX INDEX foo;" works just fine.
> However, we are leery of doing this reindexing using 9.3.8, since it's
> already demonstrated itself to be unreliable.
>
> Which version should we use to reindex? Just the latest 9.3.X? Was there a
> known bug with older versions missing corrupted pages in indices?
>
> PS: Just in case I am glaringly doing something wrong:
>
> Here's our mechanism of doing the upgrade: we create a streaming replica
> from a basebackup, and then at some point turn off writes to the primary,
> convert the replica to another primary, and then run pg_upgrade on this
> new primary.
--
Angular momentum makes the world go 'round.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: ronljohnsonjr@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Upgrading from Postgresql 9.3.8 to 9.6.10
In-Reply-To: <0e3636ec-ba56-a765-2a5a-84ef01072604@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox