Re: pg_upgrade and wraparound

The error log is like this. Here's its tail:

pg_restore: executing BLOB 1740737401
pg_restore: WARNING: database with OID 0 must be vacuumed within 1000003
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 1000002
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: executing BLOB 1740737402
pg_restore: WARNING: database with OID 0 must be vacuumed within 1000001
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: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 9759463; 2613 1740737402
BLOB 1740737402 bof_user
pg_restore: [archiver (db)] could not execute query: ERROR: database is
not accepting commands to avoid wraparound data loss in database with OID 0
HINT: Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions.
Command was: ALTER LARGE OBJECT 1740737402 OWNER TO bof_user;

Before that there is a lot of similar messages - the only things chainging
are the "executing BLOB nnn" number and "must be vacuumed within nnn
transactions" number.

As for the prepared transactions - no, I don't have them, our application
doesn't use this functionality.

2018-06-11 0:34 GMT+03:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 06/10/2018 02:09 PM, Alexander Shutyaev wrote:
>
>> Some more notes on databses.
>>
>> Although the pg_upgrade failed, I've decided to check the databases in
>> the new cluster (10.4). There is no database with oid 0 either. Also to be
>> noted that some system databases changed the oids while others retained
>> them.
>>
>
> If I am following the source code for pg_upgrade correctly that is
> expected. Pretty sure because the order of object creation is different.
>
>
>> And of my databases - sslentry. It had a very big oid (can that seem
>> strange?) and its oid has changed.
>>
>
> OID's are added at time of object creation so I would say the ssslentry
> database was created some time after the other databases in the 9.6
> cluster. Actually probably more accurate to say after 1016305714 -
> 16400(bof db) objects that have OID's where created.
>
> When the upgrade failed pg_upgrade should have pointed you at an error log.
>
> Did it and is there anything useful there?
>
> From your OP post:
>
> "You might also need to commit or roll back old prepared transactions."
>
> Do you have any of those in the 9.6 cluster?
>
> See:
>
> https://www.postgresql.org/docs/10/static/view-pg-prepared-xacts.html
>
>
>
>> select oid, datname from pg_database;
>> oid | datname
>> -------+-----------
>> 13011 | template0
>> 16400 | bof
>> 13012 | postgres
>> 16401 | sslentry
>> 1 | template1
>> (5 rows)
>>
>>
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>