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: