What to do if PostgreSQL tables get corrupted -- Missing chunk 0 for toast value in pg_toast

Products

• CA API Developer Portal

Issue/Introduction

In the log, it shows error like this,

apiportal.muangthai.co.th dockerd[2876]: ERROR:  missing chunk number 0 for toast value 726586 in pg_toast_2619
apiportal.muangthai.co.th dockerd[2876]: STATEMENT:  SELECT * FROM RSA_KEYS

That indicates the rsa_keys table is corrupted.

Then the portal cannot load the data from the corrupted table and won't  be working well.

Environment

Release : 4.2

Component : API PORTAL

Cause

It's usually due to hardware failure.

In a case, this occurs when a backup job is running in the background but doesn't shutdown the portal

Resolution

NOTE:

1. The production portal should use external mysql database, please refer to product document, 

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-developer-portal/4-2/install-configure-and-upgrade/configure-an-external-database.html

2. Before backup or other maintenance job, or vmotion, the portal server should be shutdown

 

To recover the corrupted table,

• https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a
• Another solution is to dump and rebuild the corrupted table, here is an example for rsa_keys table,

1. dump the table to a sql file 

docker exec -it -u 0 <postgres db container> /bin/bash

pg_dump  -t rsa_keys portal -U admin > rsakey.sql

NOTE that the sql file is not a complete file because of the "missing chunk number" error.

 

2. drop the rsa_keys table

psql portal admin

drop table rsa_keys;

\q

3. import the sql file

psql portal admin < rsakey.sql

(We recovered 31 records.)

4. manually add the constraint and index back, (the statements are from a good portal postgres DB with same pg_dump command)

psql portal admin

ALTER TABLE ONLY public.rsa_keys
    ADD CONSTRAINT rsa_keys_pkey PRIMARY KEY (uuid);

CREATE INDEX uuid_createts_idx ON public.rsa_keys USING btree (create_ts);

5. run ./portal.sh to restart portal

 

 

 

Additional Information

https://gist.github.com/supix/80f9a6111dc954cf38ee99b9dedf187a

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-developer-portal/4-2/install-configure-and-upgrade/configure-an-external-database.html