pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump ERROR: missing chunk number 0 for toast value
2+ messages / 2 participants
[nested] [flat]
* pg_dump ERROR: missing chunk number 0 for toast value
@ 2017-03-22 09:21 ramonpadilla1@yahoo.co.uk
0 siblings, 1 reply; 2+ messages in thread
From: ramonpadilla1@yahoo.co.uk @ 2017-03-22 09:21 UTC (permalink / raw)
To: pgsql-admin
I'm using PostgreSQL 9.3.16 on Windows 7 and the data directory was created from a filesystem backup of an installation running on another machine, which failed.
After starting the postgres service I ran `pg_dump` using pgAdminIII to backup the databases.
pg_dump.exe --host localhost --port 5433 --username "postgres" --no-password --format custom --blobs --verbose --file "db-2017-03-22.backup" "mydb"
One of them reported the following error:
pg_dump: dumping contents of table "public.bandana"
pg_dump: dumping contents of table "public.bodycontent"
pg_dump: Dumping the contents of table "bodycontent" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: missing chunk number 0 for toast value 314936 in pg_toast_36791
pg_dump: The command was: COPY public.bodycontent (bodycontentid, body, contentid, bodytypeid) TO stdout;
Process returned exit code 1.
Since then I've done some digging around and run the following commands:
REINDEX table pg_toast.pg_toast_36791;
VACUUM ANALYZE bodycontent;
Both return:
Query returned successfully with no result in xx ms.
Using pgAdmin I've selected the Maintenace option and run a VACUUM (no options) , ANALYSE and REINDEX and no errors were reported.
I ran VACUUM with the FULL option and the following error was reported:
INFO: vacuuming "public.plugindata"
ERROR: missing chunk number 0 for toast value 289849 in pg_toast_36998
I don't have any experience with postgres or databases in general. Can anyone advise what I should do next?
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_dump ERROR: missing chunk number 0 for toast value
@ 2017-03-23 12:25 michael@sqlexec.com <michael@sqlexec.com>
parent: ramonpadilla1@yahoo.co.uk
0 siblings, 0 replies; 2+ messages in thread
From: michael@sqlexec.com @ 2017-03-23 12:25 UTC (permalink / raw)
To: ramonpadilla1@yahoo.co.uk; +Cc: pgsql-admin
As a start, zero in on the relation, index,column, table:
select 36791::regclass;
Restart the server with:
zero_damaged_pages=true
ignore_system_indexes=true
Do full vacuum again:
VACUUM FULL VERBOSE public.bodycontent;
If still a problem, consider dropping all the indexes and recreate
them. Then try to do the vacuum full again.
If you still have a problem:
Use a program like python/psycopg2 and write a loop that fetches every
row in the table and capture with exception the rows that it fails on.
Then delete those rows. Then do a dump of the table to /dev/null and
see if it completes without errors. If so, you will need to dump and
recreate the table.
Regards,
Michael Vitale
ramonpadilla1@yahoo.co.uk wrote:
> missing chunk
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2017-03-23 12:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-03-22 09:21 pg_dump ERROR: missing chunk number 0 for toast value ramonpadilla1@yahoo.co.uk
2017-03-23 12:25 ` michael@sqlexec.com <michael@sqlexec.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox