Re: ShmemAlloc errors

Tom Lane wrote:
> Nick Burrett <nick@dsvr.net> writes:
>
>>$ pg_dumpall >full.db
>>pg_dump: WARNING:  ShmemAlloc: out of memory
>>pg_dump: Attempt to lock table "vs_dfa554862ac" failed.  ERROR:
>>LockAcquire: lock table 1 is out of memory
>>pg_dumpall: pg_dump failed on bandwidth, exiting
>
>
> Looks like you need to increase max_locks_per_transaction in postgresql.conf.
> (You'll need to restart the postmaster to make this take effect.)

I've tried that and indeeed it works.  Thanks.

> We don't normally hear of people needing that --- is there anything
> unusual about the schema of this database?

Not particularly.  The database consists of around 3000 tables created
using this:

CREATE TABLE vs_foo (date date NOT NULL,
                      time time NOT NULL,
                      bytesin int8 CHECK (bytesin >= 0),
                      bytesout int8 CHECK (bytesout >= 0));


Each table has around 1500 rows.

Incidently the dump and import reduced the disk space requirements from
25Gb to 9Gb.  The database is vacummed monthly (data is only deleted
monthly) using VACUMM FULL.  I can only presume that vacumming is not
designed to be *that* aggressive.


Cheers,


Nick.


--
Nick Burrett
Network Engineer, Designer Servers Ltd.   http://www.dsvr.co.uk