X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 8D4E8528B1 for ; Fri, 10 Jun 2005 12:53:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68251-01 for ; Fri, 10 Jun 2005 15:53:42 +0000 (GMT) Received: from uis-gumail-9-stage2.georgetown.edu (postoffice-4.georgetown.edu [141.161.88.216]) by svr1.postgresql.org (Postfix) with ESMTP id BEE3A528CE for ; Fri, 10 Jun 2005 12:53:38 -0300 (ADT) Received: from uis-gumail-9.georgetown.edu (postoffice-4.georgetown.edu [141.161.88.216]) by uis-gumail-9-stage2.georgetown.edu (8.12.10/8.12.10) with ESMTP id j5AFZNm7013239 for ; Fri, 10 Jun 2005 11:35:23 -0400 (EDT) Received: from [141.161.18.159] ([141.161.18.159]) (user=cef6 mech=PLAIN bits=0) by uis-gumail-9.georgetown.edu (8.12.10/8.12.10) with ESMTP id j5AFZ9dd013117 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Fri, 10 Jun 2005 11:35:18 -0400 (EDT) Message-ID: <42A9B2B9.80907@georgetown.edu> Date: Fri, 10 Jun 2005 11:33:13 -0400 From: "Colin E. Freas" User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: How to recover when can't start database Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-GU-FilterVersion: 1.26 X-Scanned-By: MIMEDefang 2.39 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.777 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200506/69 X-Sequence-Number: 17824 Sorry if this post winds up as a duplicate on the list... --- In an effort to disable all of the foreign key restraints on a set of tables in my installation of PostgreSQL 8.0.1, I was fiddling with the pg_class table and managed to elicit behavior similar to what L. Boldareva ran into. (This is Red Hat AS 4; Opteron w/4GB...) pg_ctl can start and stop my postmaster, but I can't use psql to access a database. When I try, I get this on the command line: --- psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. --- ...and this in the log: --- 2005-06-07 16:44:37 EDTLOG: connection received: host=[local] port= 2005-06-07 16:44:37 EDTLOG: connection authorized: user=postgres database=caarray 2005-06-07 16:44:43 EDTLOG: server process (PID 14278) was terminated by signal 11 2005-06-07 16:44:43 EDTLOG: terminating any other active server processes 2005-06-07 16:44:43 EDTLOG: all server processes terminated; reinitializing 2005-06-07 16:44:43 EDTLOG: database system was interrupted at 2005-06-07 16:34:46 EDT 2005-06-07 16:44:43 EDTLOG: checkpoint record is at 0/81F18770 2005-06-07 16:44:43 EDTLOG: redo record is at 0/81F18770; undo record is at 0/0; shutdown TRUE 2005-06-07 16:44:43 EDTLOG: next transaction ID: 6196113; next OID: 6226772 2005-06-07 16:44:43 EDTLOG: database system was not properly shut down; automatic recovery in progress 2005-06-07 16:44:43 EDTLOG: record with zero length at 0/81F187AC 2005-06-07 16:44:43 EDTLOG: redo is not required 2005-06-07 16:44:43 EDTLOG: database system is ready --- I think I know exactly what caused this. Postmaster seemed to crash after I ran these two updates on pg_class: --- 2005-06-07 16:02:38 EDTLOG: statement: update pg_class set reltriggers=foo.c from (select relname,count(tgrelid) as c from pg_class,pg_trigger where pg_class.oid=tgrelid and relnamespace=2200 group by relname) foo; 2005-06-07 16:03:21 EDTLOG: statement: update pg_class set reltriggers=foo.c from (select relname,count(tgrelid) as c from pg_class,pg_trigger where pg_class.oid=tgrelid and relnamespace=2200 group by relname) foo where pg_class.relnamespace=2200; 2005-06-07 16:03:22 EDTLOG: connection received: host=[local] port= 2005-06-07 16:03:22 EDTLOG: connection authorized: user=postgres database=caarray 2005-06-07 16:03:22 EDTLOG: server process (PID 12845) was terminated by signal 11 2005-06-07 16:03:22 EDTLOG: terminating any other active server processes --- If I can't ever get this database back up and running, it's no big deal... this isn't something in production, and I wouldn't do this on a production machine. However, it would save me enough time to get this working that I'm trying this list. The only possible saving grace I seem to have is that immediately before I ran the updates, I made a copy of the pg_class table. So, if the only problem is the pg_class table, then I'm thinking I could just copy the valid data back in, and be more careful next time. But, right now, I can't even get the server to talk to me. Also, if there's an easier way to disable and re-enable the foreign key constraints, I'd certainly appreciate the tip as well. Any help appreciated. Colin Freas