Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
add comment

1 Answer

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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