Re: pg_upgrade and wraparound

On 06/09/2018 03:46 AM, Alexander Shutyaev wrote:
> Hello!
>
> I've been trying to upgrade a postgresql cluster from 9.6 to 10. I've
> executed the pg_upgrade with the following options:
>
>  /usr/lib/postgresql/10/bin/pg_upgrade -b /usr/lib/postgresql/9.6/bin/
> -B /usr/lib/postgresql/10/bin/ -d /var/lib/postgresql/9.6/main -D
> /var/lib/postgresql/10/main -o ' -c
> config_file=/etc/postgresql/9.6/main/postgresql.conf' -O ' -c
> config_file=/etc/postgresql/10/main/postgresql.conf'
>
> The upgrade operation failed after several hours with the following error:
>
> database is not accepting commands to avoid wraparound data loss in
> database with OID 0

Do you know which database has an OID of 0?

>
> Earlier in the log there are a lot of messages like
>
> pg_restore: executing BLOB 1740736966
> pg_restore: WARNING:  database with OID 0 must be vacuumed within
> 1000279 transactions
> HINT:  To avoid a database shutdown, execute a database-wide VACUUM in
> that database.
> You might also need to commit or roll back old prepared transactions.
> pg_restore: WARNING:  database with OID 0 must be vacuumed within
> 1000278 transactions
> HINT:  To avoid a database shutdown, execute a database-wide VACUUM in
> that database.
> You might also need to commit or roll back old prepared transactions.
>
> I've tried to do VACUUM FULL on my 9.6 cluster on all databases and then
> retried the pg_upgrade - it failed in the same way.
>
> Also to be noted, earlier this cluster was succesfully upgraded with
> pg_upgrade using similar parameters from older versions (at least 2
> times, something like 9.1 -> 9.3, 9.3 -> 9.6). The database is around
> 700 GB and has very many pg_largeobjects in it.
>
> What could be the reason of this and how can I perform my upgrade?
>
> Thanks in advance,
> Alexander

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com