Re: BUG #8673: Could not open file "pg_multixact/members/xxxx" on slave during hot_standby

Serge Negodyuck wrote:
> Hello,
>
> I've upgraded postgresql to version 9.3.4 and did fresh initdb and restored
> database from sql backup.
> According to 9.4.3 changelog issue with multixact wraparound was fixed.

Ouch. This is rather strange. First I see the failing multixact has
8684 members, which is totally unusual. My guess is that you have code
that creates lots of subtransactions, and perhaps does something to one
tuple in a different subtransaction; doing sometihng like that would be,
I think, the only way to get subxacts that large. Does that sound
right?

> An ugly hack "cp pg_multixact/members/14077 pg_multixact/members/14078"
> helped me to start master server in replica.

This is the second weird thing. How come you needed to create 14078
when you already had that file, according to the listing you pasted
above? Or is the 14078 file present in the listing only because you
copied it?

> Then, did pg_basebackup to slave database. It does not help
> 2014-06-02 09:58:49 EEST 172.18.10.17 db2 DETAIL: Could not open file
> "pg_multixact/members/1112D": No such file or directory.
> 2014-06-02 09:58:49 EEST 172.18.10.18 db2 DETAIL: Could not open file
> "pg_multixact/members/11130": No such file or directory.
> 2014-06-02 09:58:51 EEST 172.18.10.34 db2 DETAIL: Could not open file
> "pg_multixact/members/11145": No such file or directory.
> 2014-06-02 09:58:51 EEST 172.18.10.38 db2 DETAIL: Could not open file
> "pg_multixact/members/13F76": No such file or directory

Are these the only files missing? Are intermediate files there?

> What additional information should I provide?
> If I will increase autovacuum_multixact_freeze_max_age will it help? (Now I
> have default value)

No, you'd need to *decrease* the freezing age, so that things are
cleaned up sooner. If you increase the freezing age, the problematic
multis would persist longer, causing more trouble.

I think I will have to reproduce your scenario to be able to understand
what is going on. If you can describe it in detail, that would make it
easier for me.

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