Thread: Re: BUG #12990: Missing pg_multixact/members files (appears to have wrapped, then truncated)

Timothy Garnett <tgarnett@panjiva.com> wrote:

>>> - why didn't vacuum_db -a -F free up more members files?
>>
>> Are you sure that while the problem was developing the primary
>> cluster didn't have any long-running transactions (including
>> those left "idle in transaction" or prepared transactions)?  Was
>> there a checkpoint following the vacuumdb -a -F run?
>
> The vacuum_db was run after we recovered from backup (which
> involved restarting the server) so at the time the vacuum_db
> started there were no open transactions.  There have have been
> checkpoints since the vacuum_db finished as well.
>
> In the lead up to the problem there wouldn't have been any
> transactions open more then a couple of hours (the oldest members
> file was over 6 months old).

Thanks; that helps narrow where we need to look for the bug.  Just
to be sure, what are the results of?:

SHOW max_prepared_transactions;

If that is non-zero, do you have any monitoring for rows in the
pg_prepared_xacts view with a "prepared" value older than some
reasonable threshold?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

>
> Thanks; that helps narrow where we need to look for the bug.  Just
> to be sure, what are the results of?:
>
> SHOW max_prepared_transactions;


We don't use prepared transactions so that's prob. not involved.

SHOW max_prepared_transactions;
 max_prepared_transactions
---------------------------
 0
(1 row)

Tim