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

krlmlr
  • 361
  • 1
  • 3
  • 12
  • 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 Dec 03 '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 03 '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. –  Dec 03 '12 at 11:22
  • Check this post: http://dba.stackexchange.com/questions/29674/postgres-backup-queries-on-aws-after-reading-postgres-docs – András Váczi Dec 03 '12 at 12:12
  • @a_horse_with_no_name: Thank you. Precisely these conditions cannot be assumed :-) – krlmlr Dec 03 '12 at 15:22

1 Answers1

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.

eppesuig
  • 5,002
  • 1
  • 15
  • 14