Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 790049F9D1C for ; Mon, 15 Oct 2007 00:41:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 08732-06 for ; Mon, 15 Oct 2007 00:41:03 -0300 (ADT) X-Greylist: from auto-whitelisted 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 AF0239F9B6E for ; Mon, 15 Oct 2007 00:41:03 -0300 (ADT) Received: from compute1.internal (compute1.internal [10.202.2.41]) by out1.messagingengine.com (Postfix) with ESMTP id 2F74230207; Sun, 14 Oct 2007 23:41:03 -0400 (EDT) Received: from heartbeat1.messagingengine.com ([10.202.2.160]) by compute1.internal (MEProxy); Sun, 14 Oct 2007 23:41:03 -0400 X-Sasl-enc: vo0XqffWl6Ta6+8WiepxLpcLg6BRkZ3z6ofayp+fNgmY 1192419662 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 607533B07; Sun, 14 Oct 2007 23:41:02 -0400 (EDT) Message-ID: <4712E15B.8000405@fastmail.net> Date: Sun, 14 Oct 2007 20:41:15 -0700 From: Darren Reed User-Agent: Thunderbird 2.0.0.0 (Windows/20070326) MIME-Version: 1.0 To: Scott Marlowe CC: "Joshua D. Drake" , pgsql-admin@postgresql.org Subject: Re: Is my database now too big? References: <1191730382.18880.1214536197@webmail.messagingengine.com> <47087723.7040403@commandprompt.com> <1191744007.14918.1214548241@webmail.messagingengine.com> <4708A9DC.5060306@fastmail.net> <47097EA7.902@fastmail.net> <4712D97E.7010004@fastmail.net> In-Reply-To: <4712D97E.7010004@fastmail.net> 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/121 X-Sequence-Number: 27035 So having upgraded to 8.1.10, I thought I'd try out a few things... # /usr/pkg/bin/pg_dumpall -- -- PostgreSQL database cluster dump -- \connect postgres -- -- Roles -- CREATE ROLE postgres; ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN; CREATE ROLE root; ALTER ROLE root WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN; -- -- Database creation -- REVOKE ALL ON DATABASE template1 FROM PUBLIC; REVOKE ALL ON DATABASE template1 FROM postgres; GRANT ALL ON DATABASE template1 TO postgres; \connect postgres pg_dump: SQL command failed pg_dump: Error message from server: ERROR: out of memory DETAIL: Failed on request of size 20. pg_dump: The command was: LOCK TABLE public.foo IN ACCESS SHARE MODE pg_dumpall: pg_dump failed on database "postgres", exiting # SELECT * FROM foo LIMIT 1; ERROR: out of memory DETAIL: Failed on request of size 20. I don't know if this is of any significance: # \l List of databases Name | Owner | Encoding -----------+----------+----------- postgres | postgres | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (3 rows) # /usr/pkg/bin/psql -U postgres template1 psql: FATAL: out of memory DETAIL: Failed on request of size 20. What puzzles me is why the transaction log hasn't resulted in postgresql being able to restore itself to a known clean state. Darren