Re: BUG #11264: Auto vacuum wraparound job blocking everything

On 01.10.2014 23:32, Alvaro Herrera wrote:
> David Gould wrote:
>
>> We ran into this on two instances last night and I'm starting to worry that
>> many others may hit it as well. As far as I can tell any postgres instance
>> that was upgraded to 9.3 by a pre-9.3.5 pg_upgrade is at risk as they all
>> will have:
>>
>> Latest checkpoint's oldestMultiXid: 1
>>
>> The workaround above is good if you still have the old cluster around from
>> before the upgrade. We did not, that was all cleaned up months ago. Which
>> raises the question: how do you fix a 9.3 instance that has oldestMultiXid =
>> 1 without access to the pre-upgrade instance. That is, where do you get the
>> correct value of oldestMultiXid to set into pg_database.datxminmxid?
>>
>> I took a guess that the oldest pg_class.relminmxid for all the tables in all
>> the databases would be ok and updated pg_database.datminmxid witt that. That
>> is, in each database I ran:
>>
>> select relname, relminmxid, age(relminmxid)
>> from pg_class
>> where relkind = 'r'
>> and age(relminmxid) < 2147483647
>> order by 3 desc limit 1 ;
> Unfortunately, the age() function is only valid for transaction IDs, not
> multixacts. In certain cases the values returned might be sensible, but
> they don't have to be, and if the counters are not relatively close to
> one another, most likely they won't be sensible. A new function was
> added to 9.4, but that doesn't help those in 9.3.
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=36ad1a87a368d837d5c2260b2fd2ff83e976425a
> (I guess you could have an extension containing that function.)
> My bet is that is the reason you had to add the condition that the value
> is less than 2^31 - 1.
>
> Offhand, other than the way to obtain the multixact age, the procedure
> seems sensible.
>
> A better way not involving mxid_age() would be to use pg_controldata to
> extract the current value of the mxid counter, then subtract the current
> relminmxid from that value.
>
>> 3) Is this likely to affect all 9.3.x<5 upgraded databases?
> Yes.
>
Hi,

I just have 700 databases which could been affected and i just don't
want to dump and restore all of them, so is there a prober way to detect
if one is affected or not?
E.g. vacuum of all tables or something else?

And is a dump, init, restore a way to make 100% sure this bug is fixed?

regards

Bernhard Schrader