6

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.)

Improve this question
5
  • 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'?
    – András Váczi
    Commented Dec 3, 2012 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
    Commented Dec 3, 2012 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.
    – user1822
    Commented Dec 3, 2012 at 11:22
  • Check this post: dba.stackexchange.com/questions/29674/…
    – András Váczi
    Commented Dec 3, 2012 at 12:12
  • @a_horse_with_no_name: Thank you. Precisely these conditions cannot be assumed :-)
    – krlmlr
    Commented Dec 3, 2012 at 15:22

1 Answer 1

Reset to default
4

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.