Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id EFD742E0063 for ; Sat, 29 Mar 2008 17:41:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07261-05 for ; Sat, 29 Mar 2008 17:40:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.182]) by postgresql.org (Postfix) with ESMTP id 80E052E004C for ; Sat, 29 Mar 2008 17:40:55 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so1047774waf.5 for ; Sat, 29 Mar 2008 13:40:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=dywJ58KrQtEorfb6ccc0ldUeqFxLl9T5YYgGyW5h0II=; b=tC5rsHKFhGbVoSpAPBnXTexcKAd5ES0g9V9YUuX/Fjb9Ode8nPtCwF+vJ+HrfFARJCo+auZm1+k7JFGNcNuOp7fhr5lA1iFQ00ZBXOqKk0e2bj+FviDFZQXu1PVjIo1fZuVXYnlY8zhjG30XSKGzINYAi1lsV/q5biHUWrcdKME= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:mime-version:content-type; b=RlaaEwioNqpN1RtQkQc0FexoUXsJjAdn1/6GssNq33M5wUYTmpXusIt0WJzZKMSP3L3RMVVwifYGvwVnHlou/JDuwpw3pWgTC7nJmSNE3JzY07cxqaJLKL/6FhjDwqUHuDn25uUhZsg8bd6EiDU3Ph406JvUzaaXqQK1cbjthqo= Received: by 10.114.180.1 with SMTP id c1mr6565718waf.121.1206823254220; Sat, 29 Mar 2008 13:40:54 -0700 (PDT) Received: by 10.114.102.14 with HTTP; Sat, 29 Mar 2008 13:40:54 -0700 (PDT) Message-ID: <2ca799770803291340p75d8a4e2o7dfd6c61e73270f6@mail.gmail.com> Date: Sat, 29 Mar 2008 22:40:54 +0200 From: "Mikko Partio" To: pgsql-admin@postgresql.org Subject: Vacuum full crash MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_10462_21214972.1206823254217" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/253 X-Sequence-Number: 28736 ------=_Part_10462_21214972.1206823254217 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello list an interrupted vacuum full has just caused a PG instance to restart and recover. Background: select version(); version ---------------------------------------------------------------------------------------------------------- PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) (1 row) I have a largish ( >1TB ) database which is kind of a data warehouse. Recently I had to do some major operations to some of the tables (update all rows in a table etc) which caused major bloat. To remove the bloat, I run VACUUM FULL VERBOSE on the bloated tables. Before the vacuum got finished, I had to abort it due to problems with my own laptop. When I hit ctrl+c to the vacuum, the PG instance went suddenly to recover mode. The logs showed this: 2008-03-29 22:25:15 EET [26841]: [1-1] ERROR: canceling statement due to user request 2008-03-29 22:25:15 EET [26841]: [2-1] STATEMENT: vacuum full verbose xyz ; 2008-03-29 22:25:15 EET [26841]: [1-1] ERROR: canceling statement due to user request 2008-03-29 22:25:15 EET [26841]: [2-1] STATEMENT: vacuum full verbose xyz ; 2008-03-29 22:25:15 EET [26841]: [3-1] PANIC: cannot abort transaction 3778747509, it was already committed 2008-03-29 22:25:15 EET [6476]: [4-1] LOG: server process (PID 26841) was terminated by signal 6: Aborted 2008-03-29 22:25:15 EET [6476]: [5-1] LOG: terminating any other active server processes 2008-03-29 22:25:15 EET [24814]: [48-1] WARNING: terminating connection because of crash of another server process 2008-03-29 22:25:15 EET [24814]: [49-1] 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. 2008-03-29 22:25:15 EET [24814]: [50-1] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2008-03-29 22:25:15 EET [26841]: [3-1] PANIC: cannot abort transaction 3778747509, it was already committed 2008-03-29 22:25:15 EET [6476]: [4-1] LOG: server process (PID 26841) was terminated by signal 6: Aborted 2008-03-29 22:25:15 EET [6476]: [5-1] LOG: terminating any other active server processes 2008-03-29 22:25:15 EET [24814]: [48-1] WARNING: terminating connection because of crash of another server process 2008-03-29 22:25:15 EET [24814]: [49-1] 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. 2008-03-29 22:25:15 EET [24814]: [50-1] HINT: In a moment you should be able to reconnect to the database and repeat your command. 2008-03-29 22:25:15 EET [6476]: [6-1] LOG: archiver process (PID 6489) exited with exit code 1 2008-03-29 22:25:15 EET [18228]: [1-1] FATAL: the database system is in recovery mode 2008-03-29 22:25:16 EET [6476]: [7-1] LOG: all server processes terminated; reinitializing 2008-03-29 22:25:16 EET [18229]: [1-1] LOG: database system was interrupted; last known up at 2008-03-29 22:20:24 EET 2008-03-29 22:25:16 EET [18229]: [2-1] LOG: database system was not properly shut down; automatic recovery in progress 2008-03-29 22:25:16 EET [18229]: [3-1] LOG: redo starts at CB5/16399698 2008-03-29 22:25:22 EET [18229]: [4-1] LOG: unexpected pageaddr CB4/76FF6000 in log file 3253, segment 35, offset 16736256 2008-03-29 22:25:22 EET [18229]: [5-1] LOG: redo done at CB5/23FF4C80 2008-03-29 22:25:22 EET [18229]: [6-1] LOG: last completed transaction was at log time 2008-03-29 22:22:47.931231+02 2008-03-29 22:25:23 EET [18336]: [1-1] FATAL: the database system is in recovery mode 2008-03-29 22:25:27 EET [18337]: [1-1] FATAL: the database system is in recovery mode 2008-03-29 22:25:30 EET [18346]: [1-1] FATAL: the database system is in recovery mode 2008-03-29 22:25:41 EET [18424]: [1-1] FATAL: the database system is in recovery mode 2008-03-29 22:25:43 EET [18427]: [1-1] LOG: autovacuum launcher started 2008-03-29 22:25:43 EET [6476]: [8-1] LOG: database system is ready to accept connections Seems quite serious to me ("cannot abort a transaction that has already committed"), what can cause such behaviour? Regards Mikko ------=_Part_10462_21214972.1206823254217 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hello list

an interrupted vacuum full has just caused a PG instance to restart and recover. Background:

select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.1 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)
(1 row)

I have a largish ( >1TB ) database which is kind of a data warehouse. Recently I had to do some major operations to some of the tables (update all rows in a table etc) which caused major bloat. To remove the bloat, I run VACUUM FULL VERBOSE on the bloated tables. Before the vacuum got finished, I had to abort it due to problems with my own laptop. When I hit ctrl+c to the vacuum, the PG instance went suddenly to recover mode. The logs showed this:

2008-03-29 22:25:15 EET [26841]: [1-1] ERROR:  canceling statement due to user request
2008-03-29 22:25:15 EET [26841]: [2-1] STATEMENT:  vacuum full verbose xyz ;
2008-03-29 22:25:15 EET [26841]: [1-1] ERROR:  canceling statement due to user request
2008-03-29 22:25:15 EET [26841]: [2-1] STATEMENT:  vacuum full verbose xyz ;
2008-03-29 22:25:15 EET [26841]: [3-1] PANIC:  cannot abort transaction 3778747509, it was already committed
2008-03-29 22:25:15 EET [6476]: [4-1] LOG:  server process (PID 26841) was terminated by signal 6: Aborted
2008-03-29 22:25:15 EET [6476]: [5-1] LOG:  terminating any other active server processes
2008-03-29 22:25:15 EET [24814]: [48-1] WARNING:  terminating connection because of crash of another server process
2008-03-29 22:25:15 EET [24814]: [49-1] 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.
2008-03-29 22:25:15 EET [24814]: [50-1] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2008-03-29 22:25:15 EET [26841]: [3-1] PANIC:  cannot abort transaction 3778747509, it was already committed
2008-03-29 22:25:15 EET [6476]: [4-1] LOG:  server process (PID 26841) was terminated by signal 6: Aborted
2008-03-29 22:25:15 EET [6476]: [5-1] LOG:  terminating any other active server processes
2008-03-29 22:25:15 EET [24814]: [48-1] WARNING:  terminating connection because of crash of another server process
2008-03-29 22:25:15 EET [24814]: [49-1] 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.
2008-03-29 22:25:15 EET [24814]: [50-1] HINT:  In a moment you should be able to reconnect to the database and repeat your command.
2008-03-29 22:25:15 EET [6476]: [6-1] LOG:  archiver process (PID 6489) exited with exit code 1
2008-03-29 22:25:15 EET [18228]: [1-1] FATAL:  the database system is in recovery mode
2008-03-29 22:25:16 EET [6476]: [7-1] LOG:  all server processes terminated; reinitializing
2008-03-29 22:25:16 EET [18229]: [1-1] LOG:  database system was interrupted; last known up at 2008-03-29 22:20:24 EET
2008-03-29 22:25:16 EET [18229]: [2-1] LOG:  database system was not properly shut down; automatic recovery in progress
2008-03-29 22:25:16 EET [18229]: [3-1] LOG:  redo starts at CB5/16399698
2008-03-29 22:25:22 EET [18229]: [4-1] LOG:  unexpected pageaddr CB4/76FF6000 in log file 3253, segment 35, offset 16736256
2008-03-29 22:25:22 EET [18229]: [5-1] LOG:  redo done at CB5/23FF4C80
2008-03-29 22:25:22 EET [18229]: [6-1] LOG:  last completed transaction was at log time 2008-03-29 22:22:47.931231+02
2008-03-29 22:25:23 EET [18336]: [1-1] FATAL:  the database system is in recovery mode
2008-03-29 22:25:27 EET [18337]: [1-1] FATAL:  the database system is in recovery mode
2008-03-29 22:25:30 EET [18346]: [1-1] FATAL:  the database system is in recovery mode
2008-03-29 22:25:41 EET [18424]: [1-1] FATAL:  the database system is in recovery mode
2008-03-29 22:25:43 EET [18427]: [1-1] LOG:  autovacuum launcher started
2008-03-29 22:25:43 EET [6476]: [8-1] LOG:  database system is ready to accept connections


Seems quite serious to me ("cannot abort a transaction that has already committed"), what can cause such behaviour?

Regards

Mikko
------=_Part_10462_21214972.1206823254217--