Обсуждение: Could not read pg_multixact/offsets on

Поиск
Список
Период
Сортировка

Could not read pg_multixact/offsets on

От
still Learner
Дата:
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


Re: Could not read pg_multixact/offsets on

От
Ron Johnson
Дата:
On Sun, Feb 4, 2024 at 1:20 PM still Learner <stilllearner23@gmail.com> wrote:
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.

What replica?
Physical replication from 11.5 to 16.1?  I don't think that was possible.  Or do you mean logical replication from the local 11.5 instance to 16.1? 

Anyway, if you have PG 11.5 and 16.1 installed on the same RHEL9 server, I think that, after mounting the old disk, I'd have done a pg_upgrade instead of replication.  Copying 20TB doesn't take that long.