Could not read pg_multixact/offsets on

still Learner <stilllearner23@xxxxxxxxx> · Sat, 3 Feb 2024 00:47:17 +0530

Hi Team,

We are PG 11.5 with RHEL 7.9 as it has EOL, we planned to upgrade to PG16.1. When the server was ready with RHEL 9.3, installed 11.15 and set the replica with Master. As the DB is more than 20TB, unmounted the storage while installing RHEL 9 and reattached. 
DB started and once it sync with master, made this replica as master, once this done we have encountered several issues like.

1. Kicking on autovacuum (with to prevent wraparound) which hangs for a long time, and killed through pg_ctl kill as it was not killed by pg_terminate/pg_cancel commands.

2. Not able to run select and vacuum on the same tables as the below errors  returns for same transaction # every time.

Is there a way to resolve this other than restoring from a fresh backup?

vacuum VERBOSE ANALYZE applpersdet;
INFO:  aggressively vacuuming "appllive.applpersdet"
ERROR:  could not access status of transaction 2094386458
DETAIL:  Could not read from file "pg_multixact/offsets/7CD5" at offset 204800: Success.

ls -lh pg_multixact/offsets/7CD5
-rw------- 1 postgres postgres 192K Feb  2 01:11 pg_multixact/offsets/7CD5

./pg_controldata -D /u01/PostgreSQL/11/data_11
pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           6666003030655781720
Database cluster state:               in production
pg_control last modified:             Fri 02 Feb 2024 07:00:12 AM IST
Latest checkpoint location:           22C0C/9682CA68
Latest checkpoint's REDO location:    22C0C/8F224020
Latest checkpoint's REDO WAL file:    0000000800022C0C0000008F
Latest checkpoint's TimeLineID:       8
Latest checkpoint's PrevTimeLineID:   8
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          7:536851921
Latest checkpoint's NextOID:          2457594517
Latest checkpoint's NextMultiXactId:  2095273387
Latest checkpoint's NextMultiOffset:  2054987015
Latest checkpoint's oldestXID:        425868871
Latest checkpoint's oldestXID's DB:   16403
Latest checkpoint's oldestActiveXID:  536851921
Latest checkpoint's oldestMultiXid:   2086078238
Latest checkpoint's oldestMulti's DB: 16403
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Fri 02 Feb 2024 06:55:42 AM IST
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                22BF0/2401F7A8
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    logical
wal_log_hints setting:                on
max_connections setting:              1600
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            0bd2863c1a2c5c891a03eec387ad5c26f25e31ce17ede028833a80149841917d

Thanks in advance.
PG Learner