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