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

Dinesh Bhandary wrote:
> Yes, it was upgraded a month ago using postgresql 9.3.4 binaries. It was
> just patched up last week with 9.3.5.

Ok, that makes a lot more sense.

> I checked all the other servers which we upgraded using postgres 9.3.5
> binaries, oldestMultiXid is other than 1 and it seems to be have picked
> the right number. But the server in question was upgraded using postgres
> 9.3.4 binaries. This sounds suspicious. Could it be pg_upgrade in 9.3.4 is
> buggy.

Well, yes, 9.3.4 had a bug fixed by this commit:

Author: Bruce Momjian <bruce(at)momjian(dot)us>
Branch: master [a61daa14d] 2014-07-02 15:29:38 -0400
Branch: REL9_4_STABLE [b446a384b] 2014-07-02 15:29:38 -0400
Branch: REL9_3_STABLE Release: REL9_3_5 [3d2e18510] 2014-07-02 15:29:38 -0400

pg_upgrade: preserve database and relation minmxid values

Also set these values for pre-9.3 old clusters that don't have values to
preserve.

Analysis by Alvaro

Backpatch through 9.3

> How do we fix the current issue with this one server? Is there an easy fix?
> Thanks.

As far as I am aware, you should
UPDATE pg_database SET datminmxid=20783

and that should fix it. The oldestMulti value in pg_control would get
updated by itself some time later. If you experience stalls before
oldestMulti fixes itself, you could stop the server (cleanly!) and then
pg_resetxlog -m x,y where x is the correct nextMulti value from
pg_controldata and y is 20783.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services