Re: pg_upgrade and wraparound - Mailing list pgsql-general

From Alexander Shutyaev
Subject Re: pg_upgrade and wraparound
Date
Msg-id CAGBp8g_VhOOYXT8iBK7Db4LdLv=xLAtzvD7BJ26P0uz7JHHh7w@mail.gmail.com
Whole thread Raw
In response to Re: pg_upgrade and wraparound  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: pg_upgrade and wraparound
List pgsql-general
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.klaver@aklaver.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.klaver@aklaver.com

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: pg_upgrade and wraparound
Next
From: Adrian Klaver
Date:
Subject: Re: pg_upgrade and wraparound