I'm using PostgreSQL 8.4.15. While running
pg_dump to backup a database, I got the following error:
pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 123456789 in pg_toast_987654321 pg_dump: The command was: COPY public.my_table (id, .... all the columns ...)
When searching for this error message, I found a couple of references (here and here) that suggested to reindex the table. (In these discussions, there was a reference to querying the
pg_class table to find the right
pg_toast_XXXXXX value, but it seemed that it was because it wasn't displayed in their error messages. I skipped this part because I had a value displayed in the error message. I guess it might be a convenience due to a later version of PostgreSQL.)
I ran the following:
REINDEX table pg_toast.pg_toast_987654321; VACUUM ANALYZE my_table;
I'm now able to use
pg_dump without errors.
pg_toast and what did these commands actually do? Are these merely about simple cleanup or could they have got rid of some rows in that table? What could have caused the problem in the first place?
There are about 300000 rows in this table, but I would expect there to be only about 250 new rows since the previous successful backup (this table is only used for INSERT/SELECT, no UPDATEs).