scheu_postgresql wrote: > In my Postgresql 8.4.0 server, since this morning some tables are unavailable, see example below : > > --> pg_dump MY_DB > bkp_MY_DB.dmp > pg_dump: SQL command failed > pg_dump: Error message from server: ERROR: invalid memory alloc request size 18446744073709551613 > pg_dump: The command was: COPY <schema>.<unavailable_table> (col1, col2, ...). > > --> vacuum analyze <schema>.<unavailable_table> ; > WARNING: terminating connection because of crash of another server process > DETAIL: The postmaster has commanded this server process to roll back the current > transaction and exit, because another server process exited abnormally and possibly corrupted shared > memory. > HINT: In a moment you should be able to reconnect to the database and repeat your > command. > > --> select * from <schema>.<unavailable_table> ; > ERROR: invalid memory alloc request size 18446744073709551613 > > --> server log file > Feb 29 05:31:44 my_server postgres[6686]: [17-1] user=,db= LOG: server process (PID 3887) was > terminated by signal 11: Segmentation fault > Feb 29 05:31:44 my_server postgres[6686]: [18-1] user=,db= LOG: terminating any other active server > processes > Feb 29 05:31:44 my_server postgres[6686]: [19-1] user=,db= LOG: all server processes terminated; > reinitializing > Feb 29 05:31:44 my_server postgres[3892]: [20-1] user=,db= LOG: database system was interrupted; last > known up at 2012-02-29 05:22:33 CET > Feb 29 05:31:44 my_server postgres[3892]: [21-1] user=,db= LOG: database system was not properly shut > down; automatic recovery in progress > Feb 29 05:31:44 my_server postgres[3892]: [22-1] user=,db= LOG: redo starts at 10/67C2A3B8 > Feb 29 05:31:45 my_server postgres[3892]: [23-1] user=,db= LOG: record with zero length at > 10/68BCF990 > Feb 29 05:31:45 my_server postgres[3892]: [24-1] user=,db= LOG: redo done at 10/68BCF960 > Feb 29 05:31:45 my_server postgres[3892]: [25-1] user=,db= LOG: last completed transaction was at log > time 2012-02-29 05:31:42.618352+01 > Feb 29 05:31:45 my_server postgres[6686]: [20-1] user=,db= LOG: database system is ready to accept > connections > Feb 29 05:32:52 my_server postgres[4469]: [21-1] user=[unknown],db=[unknown] LOG: incomplete startup > packet > Feb 29 05:33:52 my_server postgres[6686]: [21-1] user=,db= LOG: server process (PID 5151) was > terminated by signal 11: Segmentation fault > Feb 29 05:33:52 my_server postgres[6686]: [22-1] user=,db= LOG: terminating any other active server > processes > Feb 29 05:33:52 my_server postgres[6686]: [23-1] user=,db= LOG: all server processes terminated; > reinitializing > Feb 29 05:33:52 my_server postgres[5152]: [24-1] user=,db= LOG: database system was interrupted; last > known up at 2012-02-29 05:31:45 CET > Feb 29 05:33:52 my_server postgres[5152]: [25-1] user=,db= LOG: database system was not properly shut > down; automatic recovery in progress > Feb 29 05:33:52 my_server postgres[5152]: [26-1] user=,db= LOG: record with zero length at > 10/68BCF9D8 > Feb 29 05:33:52 my_server postgres[5152]: [27-1] user=,db= LOG: redo is not required > Feb 29 05:33:52 my_server postgres[5153]: [24-1] user=match,db=MY_DB FATAL: the database system is in > recovery mode > Feb 29 05:33:52 my_server postgres[6686]: [24-1] user=,db= LOG: database system is ready to accept > connections > Feb 29 05:37:19 my_server postgres[6686]: [25-1] user=,db= LOG: server process (PID 8065) was > terminated by signal 11: Segmentation fault > Feb 29 05:37:19 my_server postgres[6686]: [26-1] user=,db= LOG: terminating any other active server > processes > Feb 29 05:37:19 my_server postgres[6686]: [27-1] user=,db= LOG: all server processes terminated; > reinitializing > Feb 29 05:37:19 my_server postgres[8066]: [28-1] user=,db= LOG: database system was interrupted; last > known up at 2012-02-29 05:33:52 CET > Feb 29 05:37:19 my_server postgres[8066]: [29-1] user=,db= LOG: database system was not properly shut > down; automatic recovery in progress > Feb 29 05:37:19 my_server postgres[8066]: [30-1] user=,db= LOG: redo starts at 10/68BCFA20 > Feb 29 05:37:19 my_server postgres[8066]: [31-1] user=,db= LOG: record with zero length at > 10/68BD5BD0 > Feb 29 05:37:19 my_server postgres[8066]: [32-1] user=,db= LOG: redo done at 10/68BD5BA0 > Feb 29 05:37:19 my_server postgres[8066]: [33-1] user=,db= LOG: last completed transaction was at log > time 2012-02-29 05:35:44.468968+01 > Feb 29 05:37:19 my_server postgres[6686]: [28-1] user=,db= LOG: database system is ready to accept > connections > Feb 29 05:38:27 my_server postgres[8639]: [29-1] user=[unknown],db=[unknown] LOG: incomplete startup > packet > Feb 29 05:38:53 my_server postgres[6686]: [29-1] user=,db= LOG: server process (PID 8809) was > terminated by signal 11: Segmentation fault > > > I have tried to restart Postgresql but it did not solve these issues > I cannot backup the full database because some tables have become unreadable > I have got 7 databases on this server and only 2 have got this problem > > What could be the cause of the problem ? If a sequential scan fails, I would say that the table is corrupted. The cause could be faulty hardware, a corrupted file system or a software bug. I notice that you are running 8.4.0 which is a really bad idea. A number of data corruption bugs have been fixed since. Check the hardware and the file systems. > Is there a way to fix it without losing data and without dropping and recreating the db with my > nightly pg_dump backup ? Without losing data? Not unless you can poke around in the guts of the corrupted blocks and make sense of what you find there... If your requirement is "no data loss", you'll have to use a different backup strategy. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
