Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id D3F1E9F99E4 for ; Sat, 6 Oct 2007 23:59:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 84498-07 for ; Sat, 6 Oct 2007 23:59:00 -0300 (ADT) X-Greylist: delayed 00:05:36.541742 by SQLgrey-1.7.5 Received: from out1.smtp.messagingengine.com (out1.smtp.messagingengine.com [66.111.4.25]) by postgresql.org (Postfix) with ESMTP id BC17A9F99DD for ; Sat, 6 Oct 2007 23:59:05 -0300 (ADT) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id 743AD2F270 for ; Sat, 6 Oct 2007 22:53:27 -0400 (EDT) Received: from heartbeat2.messagingengine.com ([10.202.2.161]) by compute1.internal (MEProxy); Sat, 06 Oct 2007 22:53:27 -0400 X-Sasl-enc: 0ngwzq6404m+sZUgknRKpqDY2BHJzY/S3Bs99vCjbjNU 1191725607 Received: from [192.168.1.235] (64-142-85-108.dsl.dynamic.sonic.net [64.142.85.108]) by mail.messagingengine.com (Postfix) with ESMTP id E48BAB679 for ; Sat, 6 Oct 2007 22:53:26 -0400 (EDT) Message-ID: <47084A40.3020807@fastmail.net> Date: Sat, 06 Oct 2007 19:53:52 -0700 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: postgres: will not recognise existing tables Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/46 X-Sequence-Number: 26960 I've run into this problem twice in the last few days. postgresql is running, shutdown, reboot, restart, and tables are missing. The shutdown is clean: LOG: unexpected EOF on client connection LOG: received smart shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2007-10-06 18:50:33 PDT LOG: checkpoint record is at 2/3310F20C LOG: redo record is at 2/3310F20C; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 228741; next OID: 16449 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: received smart shutdown request LOG: shutting down LOG: database system is shut down The startup is clean: FATAL: lock file "postmaster.pid" already exists HINT: Is another postmaster (PID 99) running in data directory "/home/postgres/ db"? LOG: database system was shut down at 2007-10-06 18:50:44 PDT LOG: checkpoint record is at 2/3310F250 LOG: redo record is at 2/3310F250; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 228741; next OID: 16449 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" LOG: received SIGHUP, reloading configuration files but then I discover a tabel is gone: ERROR: relation "ifl" does not exist LOG: unexpected EOF on client connection All the disk space is still used, so the data is there... I try to do a dump, but now I get: ERROR: out of memory DETAIL: Failed on request of size 20. Ok, this I can fix, put the correct "ulimit -d" in my postgresql startup script and it goes away - for now. Version: psql (PostgreSQL) 8.1.3 Questions. 1) At some point I expect the ulimit solution is going to stop working. What do I need to do to stop postgresql from returning this error? Is it a shread memory setting? Can I change something in a .conf file? Should I be thinking about changing the way the schemas are designed to put relations in different schemas rather than all being in the same one? The database only has about 8 million entries (going by a "wc -l" of the dump'd file.) 2) Why has it forgotten about my tables? How can I encourage it to not forget about them? The only solution I currently have to (2) is to wipe out the database (rm -rf) and restore from an earlier dump. I am slowly getting tired of doing this. Will upgrading help? Thanks, Darren