From: | Abdul Qoyyuum <aqoyyuum(at)cardaccess(dot)com(dot)bn> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Fixing or Mitigating this ERROR: invalid page in block 35217 of relation base/16421/3192429 |
Date: | 2023-11-29 04:12:00 |
Message-ID: | CAA3DN=X-ZT27Knq5BOAcdD1LsiZoBuTm6UVso=n5g0LRUdHsOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
Knowing that it's a data corruption issue, the only way to fix this is to
vacuum and reindex the database. What was suggested was the following:
SET zero_damaged_pages = 0; # This is so that we can have the application
to continue to run
VACUUM FULL VERBOSE ANALYSE; # Do a full vacuum and analyse the problem if
possible.
REINDEX DATABASE "core"; # Then do a reindex and clean it up.
We're on Postgresql 12. This has worked before it happened (almost exactly
a year ago) and I think this needs a more permanent solution. I've looked
at routine vacuuming and checked the autovacuum is set to on and the
following configurations:
core=> select name, setting from pg_settings where name like 'autovacuum%';
name | setting
-------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | 2
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
autovacuum_work_mem | -1
(12 rows)
Can anyone advise if there's anything else we can do? We have no clue what
causes the invalid page block and we are running a High Availability
cluster set up but we are hoping that there may be a way to mitigate it.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2023-11-29 05:23:01 | Re: Understanding partial index selection |
Previous Message | Owen Nelson | 2023-11-29 02:13:43 | Re: Understanding partial index selection |