Take the 2-minute tour ×

Is there a way to backup a PostgreSQL database with a huge number of tables?

An attempt with pg_dump for a database of about 28000 tables resulted in the following error message:

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 public.link10292 IN ACCESS SHARE MODE
pg_dump: *** aborted because of error

An increase of max_locks_per_transaction to 256 instead of 64 resulted in a failure to start the server.

Anything else I can try? (PostgreSQL 9.0, Mac OS X.)

share|improve this question
Could be taking file-level backups instead of using pg_dump and option for you? On the other hand, what sort of error do you get when you say 'failure to start the server'? –  dezso Dec 3 '12 at 10:12
@dezso: I always thought file-level backups should be avoided with PostgreSQL. How would you do that? -- I didn't find a log message that would explain the reason for the failure to start, but I haven't looked too hard either. I had to restart the machine because pg_ctl didn't succeed, and after that, the server was unavailable and only became available after reverting the config change and restarting the machine again. –  krlmlr Dec 3 '12 at 10:16
A file level backup is "safe" if you stop the server before copying the files and if you restore them to the same PostgreSQL version, operating system and architecture. –  a_horse_with_no_name Dec 3 '12 at 11:22
Check this post: dba.stackexchange.com/questions/29674/… –  dezso Dec 3 '12 at 12:12
@a_horse_with_no_name: Thank you. Precisely these conditions cannot be assumed :-) –  krlmlr Dec 3 '12 at 15:22

1 Answer 1

up vote 3 down vote accepted

You should really increase max_locks_per_transaction. As specified in http://www.postgresql.org/docs/9.0/static/runtime-config-locks.html , changing this parameter may require to also change the System V shared memory. You have to increase that value also. In OS X this can be done as explained in http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SYSVIPC (look for OS X section). I think you should really change at least shmmax.

share|improve this answer

Your Answer


