I encountered an error on PostgresSQL. Here is details for the error which i have seen during the backup.
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: found toasted toast chunk for toast value 16431418 in pg_toast_16431418
pg_dump: The command was: COPY.XXXXXXXXXXX
So what i did i
VACUUM ANALYZE <table_name>;
it showed me the same errors, then i ran
SET zero_damaged_pages = on;
VACUUM FULL <table_name>;
it zeroed out the bad page header, then i ran
REINDEX table pg_toast.pg_toast_16306418;
After running this command, I checked the other process which could be waiting but nothing was in the waiting except this query taking up 99% of CPU cycles. I waited for an hour and seems nothing was happening, i terminated the query.
Then i ran full vacuum and reindexing of database but vacuum again failed
vacuum did not complete
I thought zero-ing out the invalid page header would allow me to back up the database but it did not do anything. Can someone suggest something please?
I am fairly new to postgres so please be specific, if you can. Any help will be much appreciated. Thanks!