2

Today I saw that Postgres produces error messages related to possible data corruption (?). Everything's working as expected so I didn't realize anything was wrong.

ERROR:  xlog flush request 6E/82FFED10 is not satisfied --- flushed only to 3D/CA02E920
CONTEXT:  writing block 1008 of relation base/118517/118823
CONTEXT:  writing block 1008 of relation base/118517/118823
WARNING:  could not write block 1008 of base/118517/118823
LOG:  request to flush past end of generated WAL; request 6E/82FFED10, currpos 3D/CA02E920
DETAIL:  Multiple failures --- write error might be permanent.

PostgreSQL 11. Configuration:

max_connections = 100
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 41943kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           6775130872383000604
Database cluster state:               in production
pg_control last modified:             Thu Mar 12 04:46:20 2020
Latest checkpoint location:           85/802C9268
Latest checkpoint's REDO location:    85/802C9230
Latest checkpoint's REDO WAL file:    000000010000008500000080
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:69393025
Latest checkpoint's NextOID:          511846
Latest checkpoint's NextMultiXactId:  11365
Latest checkpoint's NextMultiOffset:  23069
Latest checkpoint's oldestXID:        562
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  69393025
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Thu Mar 12 04:45:50 2020
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
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:            edec50c3ef6ee1a42351c2e593de539feb25343ad239e12b813b4c212ae2a1d6

I did a backup and then I restored it to another database and removed the old one. It didn't work. I still getting those errors.

P.S. PostgreSQL did not crash lately. There is also a plenty of space on SSD.

1
  • Which command/tool did you use to generate the third output from the top, first from the bottom? – dw8547 Mar 12 '20 at 7:46
0

Yes that looks like corruption, perhaps caused by storage hardware problems.

Perform a pg_dumpall of the cluster and restore it to a newly created cluster, that should get rid of the problem.

Check your hardware.

1
  • smartctl does not show any disk errors. hmm... – Bald Mar 26 '20 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.