Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: jtkells(at)verizon(dot)net
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another
Date: 2011-08-07 16:37:22
Message-ID: 23190.1312735042@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

jtkells(at)verizon(dot)net writes:
> I am having a problem running "pg_dump -s database "on one system
> while it runs fine on another system.

> when I run the following dump on the Ubuntu system I get :
> pg_dump -s DB >/tmp/DB_schema_only.dmp
> pg_dump: WARNING: out of shared memory
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: out of shared memory
> HINT: You might need to increase max_locks_per_transaction.
> pg_dump: The command was: LOCK TABLE schema_x.x_table IN ACCESS SHARE
> MODE

> I don't understand what I am doing wrong since I have given a larger
> amount of resources on the Ubuntu system and continue to fail. Am I
> missing anything else?

The HINT told you what you need to do: increase max_locks_per_transaction.

The exact point at which you run out of shared memory after exceeding
max_locks_per_transaction will vary depending on a number of
hard-to-predict factors (in this case I'll bet 32-bitness vs 64-bitness
has a lot to do with it), so the fact that it fails on one machine and
not another is not that surprising. You can be sure though that if the
databases are identical, the "working" machine has not got a lot of
headroom; so you'd be well advised to apply the max_locks_per_transaction
adjustment to both.

regards, tom lane

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message antismarmy 2011-08-07 17:30:30 All the functionality I need is pgsql2shp.exe-- isolated installation
Previous Message jtkells 2011-08-07 16:23:32 pg_dump: Error message from server: ERROR: out of shared memory on one system works fine on another