CODEWITHSUNDEEP
postgresqlpostgresql-9.4data-corruption
user307080
user307080

Reputation: 1

Postgres is not accepting commands and Vacuum failed due to missing chunk number error

Version: 9.4.4

Exception while inserting a record in health_status.
org.postgresql.util.PSQLException: ERROR: database is not accepting commands to avoid wraparound data loss in database "db"
Hint: Stop the postmaster and vacuum that database in single-user mode.

As indicated in the above error, I tried logging to single-user mode and tried to run full vacuum but instead received below error:

PostgreSQL stand-alone backend 9.4.4
backend> vacuum full;
< 2019-11-06 14:26:25.179 UTC > WARNING:  database "db" must be vacuumed within 999999 transactions
< 2019-11-06 14:26:25.179 UTC > HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
    You might also need to commit or roll back old prepared transactions.
< 2019-11-06 14:26:25.215 UTC > ERROR:  missing chunk number 0 for toast value xxxx in pg_toast_1234
< 2019-11-06 14:26:25.215 UTC > STATEMENT:  vacuum full;

I tried to run vacuum but the same is leading to another error that indicates missing attributes for relid xxxxx

backend> vacuum;
< 2019-11-06 14:27:47.556 UTC > ERROR:  catalog is missing 3 attribute(s) for relid xxxxx
< 2019-11-06 14:27:47.556 UTC > STATEMENT:  vacuum;

I tried to do a vacuum freeze for the entire db but it is leading to the catalog error again after waiting for sometime.

Furthermore, I tried to run vacuum freeze for a single table which was working fine but when I do the vacuuming for all tables, it probably includes the corrupted one as well and ends up with the same error:

backend> vacuum full freeze
< 2019-11-07 08:54:25.958 UTC > WARNING:  database "db" must be vacuumed within 999987 transactions
< 2019-11-07 08:54:25.958 UTC > HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
    You might also need to commit or roll back old prepared transactions.
< 2019-11-07 08:54:26.618 UTC > ERROR:  missing chunk number 0 for toast value xxxxx in pg_toast_xxxx
< 2019-11-07 08:54:26.618 UTC > STATEMENT:  vacuum full freeze

Is there a way to figure out the corrupted table and a way to restore the integrity of the database so the application can access the rest of the database?

P.S. I do not have a backup to restore the data so deleting the corrupted data or somehow fixing it would be the only solution here.

Upvotes: 0

Views: 1800

Answers (0)

Related Questions