Search Postgresql Archives

Partial solution to observed " MultiXactId ### has not been created yet -- apparent wraparound" issue with newly upgraded db

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I wanted to share an experience that we had this morning, after the upgrade from postgres 9.2.7 to postgres 9.3.4 on our production system.  (The data set was upgraded in QA without issues,but I rather suspect that I had done a full 'vacuum' on that data set more recently).  NOTE: this was within an Amazon Web Services machine, so it was using Amazon's packaged 9.2.7 version, and then we moved back to the RPMs pointed to by wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm in order to get up to the 9.3 level...

The symptom:

db# vacuum country_state;
ERROR:  MultiXactId 4800022 has not been created yet -- apparent wraparound

db# vacuum country;
ERROR:  MultiXactId 4800023 has not been created yet -- apparent wraparound


The solution (for small table)
Researching the mailing lists, I found this
http://postgresql.1045698.n5.nabble.com/MultiXactId-error-after-upgrade-to-9-3-4-td5797923.html

So, following this advice, I did the following:
select * from country_state for update \g /tmp/ignore;
select * from country for update \g /tmp/ignore;

and now vacuum is happy with those tables again.


What's not working , still...

Unfortunately, getting this same symptom on a 3rd table is getting a bit large (102GB, with 28million rows)
db=# vacuum article;
ERROR:  MultiXactId 4998189 has not been created yet -- apparent wraparound

I'm wondering if there is a query which would just return the rows which had a "bad t_xmax value"  (or if we can even differentiate those from correct values). - such that I can only search for those with the 'for update' construct.



Some common information that I've seen provided in the past by others:

db=# select txid_current();
 txid_current
--------------
   1008772125
(1 row)

db=# SELECT datname, datfrozenxid FROM pg_database where datname='db';
   datname   | datfrozenxid
-------------+--------------
 db |    851079261
(1 row)
 

db=# show autovacuum_freeze_max_age;
 autovacuum_freeze_max_age
---------------------------
 200000000
(1 row)

db=# show vacuum_freeze_min_age;
 vacuum_freeze_min_age
-----------------------
 50000000
(1 row)

db=# show vacuum_freeze_table_age;
 vacuum_freeze_table_age
-------------------------
 150000000
(1 row)


Thanks in advance for any advice
Bill


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux