On Fri, Jun 15, 2018 at 03:01:37PM -0700, Vimalraj A wrote:
> Hi,
>
> After pg_upgrade, the data in Postgres is corrupted.
>
> 1. Postgres 9.4, stop db with "immediate" mode
> 2. Run pg_upgrade, to upgrade to Postgres 10.4
> 3. Start Postgres 10 and run vacuum full, got a bunch of "concurrent insert in
> progress". Looks like the data is corrupted. (Loading the old data back on
> Postgres 9.4 works just fine)
>
> But if I stop the 9.4 DB with "smart" mode, the data after pg_upgrade looks
> fine.
>
> pg_upgrade doesn't stop or throw warnings if the upgraded db gets into
> corrupted state.
>
>
> I would like to understand why it happens so.
> 1. What transient state corrupts the db?
> 2. Is it a known issue with pg_upgrade?
> 3. Is there a way to get the data from pg_upgrade after "immediate" mode stop
> of previous version?
Well, that's interesting. We document to shut down the old and new
sever with pg_ctl stop, but don't specify to avoid immediate.
The reason you are having problems is that pg_upgrade does not copy the
WAL from the old cluster to the new one, so there is no way to replay
the needed WAL during startup of the new server, which leads to
corruption. Did you find this out in testing or in actual use?
What is also interesting is how pg_upgrade tries to avoid problems with
_crash_ shutdowns --- if it sees a postmaster lock file, it tries to
start the server, and if that works, it then stops it, causing the WAL
to be replayed and cleanly shutdown. What it _doesn't_ handle is pg_ctl
-m immediate, which removes the lock file, but does leave WAL in need of
replay. Oops!
Ideally we could detect this before we check pg_controldata and then do
the start/stop trick to fix the WAL, but the ordering of the code makes
that hard. Instead, I have developed the attached patch which does a
check for the cluster state at the same time we are checking
pg_controldata, and reports an error if there is not a clean shutdown.
Based on how rare this is, this is probably the cleanest solution, and I
think can be backpatched.
Patch attached.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +