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

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.


After a month it seems I have almost the same issue.


ls -lat pg_multixact/members

-rw------- 1 postgres postgres 262144 Jun 2 15:37 000D
-rw------- 1 postgres postgres 262144 Jun 2 14:46 000C
-rw------- 1 postgres postgres 262144 Jun 2 14:46 000B
-rw------- 1 postgres postgres 262144 Jun 2 14:46 000A
-rw------- 1 postgres postgres 262144 Jun 2 14:46 0009
-rw------- 1 postgres postgres 262144 Jun 2 13:22 0008
-rw------- 1 postgres postgres 262144 Jun 2 13:22 0007
-rw------- 1 postgres postgres 262144 Jun 2 12:54 0006
-rw------- 1 postgres postgres 262144 Jun 2 12:49 0005
-rw------- 1 postgres postgres 262144 Jun 2 12:43 0004
-rw------- 1 postgres postgres 262144 Jun 2 12:33 0003
-rw------- 1 postgres postgres 262144 Jun 2 12:23 0002
-rw------- 1 postgres postgres 262144 Jun 2 12:10 0001
-rw------- 1 postgres postgres 262144 Jun 2 10:37 0000
-rw------- 1 postgres postgres 262144 Jun 2 08:44 14078
-rw------- 1 postgres postgres 262144 Jun 2 08:44 14077
-rw------- 1 postgres postgres 262144 Jun 2 08:20 14076


/var/log/postgresql/postgresql.log:

2014-06-02 08:20:55 EEST 172.18.10.4 db PANIC: could not access status of
transaction 2080547
2014-06-02 08:20:55 EEST 172.18.10.4 db DETAIL: Could not open file
"pg_multixact/members/14078": No such file or directory.
2014-06-02 08:20:55 EEST 172.18.10.4 db CONTEXT: SQL statement "  UPDATE
....."
2014-06-02 08:22:30 EEST LOG: server process (PID 51704) was terminated by
signal 6: Aborted
.......
2014-06-02 08:22:30 EEST LOG: all server processes terminated;
reinitializing
2014-06-02 08:22:30 EEST LOG: database system was interrupted; last known
up at 2014-06-02 08:22:16 EEST
2014-06-02 08:22:30 EEST LOG: database system was not properly shut down;
automatic recovery in progress
2014-06-02 08:22:30 EEST LOG: redo starts at 55/26D22820
2014-06-02 08:22:30 EEST 172.18.10.17 db FATAL: the database system is in
recovery mode
2014-06-02 08:22:30 EEST FATAL: could not access status of transaction
2080547
2014-06-02 08:22:30 EEST DETAIL: Could not read from file
"pg_multixact/members/14078" at offset 24576: Success.
2014-06-02 08:22:30 EEST CONTEXT: xlog redo create mxid 2080547 offset
4294961608 nmembers 8684: 6193231 (keysh) 6193233 (fornokeyupd) 6193234
(keysh) 6193235 (fornokeyupd) 6193236 (keysh) 6193237 (fornokeyupd) 6193238
(keysh) 6193239 (fornokeyupd) 6193240 (keysh) 6193241 (fornokeyupd) 6193242
(keysh) 6193243 (fornokeyupd) 6193244 (keysh) 6193245 (fornokeyupd) 6193246
(keysh) 6193247 (fornokeyupd) 6193248 (keysh) 6193249 (fornokeyupd) 6193250
(keysh) 6193251 (fornokeyupd) 6193252 (keysh) 6193253 (fornokeyupd) 6193254
(keysh) 6193255 (fornokeyupd) 6193256 (keysh) 6193257 .......
2014-06-02 08:27:40 EEST LOG: startup process (PID 55275) exited with exit
code 1
2014-06-02 08:27:40 EEST LOG: aborting startup due to startup process
failure
2014-06-02 08:32:46 EEST LOG: database system was interrupted while in
recovery at 2014-06-02 08:27:39 EEST
2014-06-02 08:32:46 EEST HINT: This probably means that some data is
corrupted and you will have to use the last backup for recovery.
2014-06-02 08:32:46 EEST LOG: database system was not properly shut down;
automatic recovery in progress
2014-06-02 08:32:46 EEST LOG: redo starts at 55/26D22820
2014-06-02 08:32:46 EEST FATAL: could not access status of transaction
2080547
2014-06-02 08:32:46 EEST DETAIL: Could not read from file
"pg_multixact/members/14078" at offset 24576: Success.
2014-06-02 08:32:46 EEST CONTEXT: xlog redo create mxid 2080547 offset
4294961608 nmembers 8684: 6193231 (keysh) ....

... And so on.

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


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

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