Janning Vygen wrote:
> Hi,
>
> tonight my database got corruppted. before it worked fine.
>
> since two days i do the following tasks every night
>
> psql -c 'CLUSTER;' $DBNAME
> psql -c 'VACUUM FULL ANALYZE;' $DBNAME
>
> before these opertaions i stop all web access. The last months i only did a
> "VACUUM ANALYZE" each night and didn't get any failures.
>
> in the morning some sql queries failed. it seems only one table was affected.
> i stopped all web access and tried to backup the current database:
>
>
> pg_dump: ERROR: invalid memory alloc request size 0
> pg_dump: SQL command to dump the contents of table "fragentipps" failed:
> PQendcopy() failed.
> pg_dump: Error message from server: ERROR: invalid memory alloc request size
> 0
> pg_dump: The command was: COPY public.fragentipps (tr_kurzname, mg_name,
> fr_id, aw_antworttext) TO stdout;
Does it do this consistently at the same place?
> so i did:
> /etc/init.d/postgresql stop
> cp -rp /home/postgres/data /home/postgres/datafailure
Good - we know we've got a copy of everything.
> and i tried to recover from backup which was made just before clustering but i
> got
> ERROR: index row requires 77768 bytes, maximum size is 8191
>
> is there any chance to get my database keep going again?
There are a few steps - you've already done the first
1. Stop PG and take a full copy of the data/ directory
2. Check your installation - make sure you don't have multiple
versions of pg_dump/libraries/etc installed
3. Try dumping individual tables (pg_dump -t table1 ...)
4. Reindex/repair files
5. Check hardware to make sure it doesn't happen again.
Once you've dumped as many individual tables as you can, you can even
try selecting data to a file avoiding certain rows if they are causing
the problem.
Then, stop PG and restart a standalone backend. This will let you
reindex the database, in case a corrupt index is the problem. The
REINDEX and postgres documentation details this, also check the mailing
list archives.
There's more you can do after that, but let's see how that works out.
PS - your next mail mentions sig11 which usually implies hardware
problems, so don't forget to test the machine thoroughly once this is over.
> pg version is 7.4.2
Download 7.4.5 - that's got the latest bugfixes in it.
--
Richard Huxton
Archonet Ltd