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.

I have a production database server running Postgres 8.4 on Cent OS 6.5.The volume of data has increased recently and within few one month database now has 18500 tables amounting to approx 350GB database size.

Problem is now I can't even take a pg_dump of this database, Every time I try I get OutOfSharedMemory error for max_number_of_shared_locks (current value is 256 instead of default 64) ?

How can we take dump of such a huge database ? please help..

share|improve this question
    
Have you restarted the DB after changing the parameter? Are all the tables in the same schema? –  dezso Oct 18 '13 at 10:08
    
Yes!! there is just one schema I started database several times after changing the parameter.. What can be the max value of this parameter for a 32GB machine ? –  Amit Oct 18 '13 at 10:11
add comment

1 Answer 1

up vote 2 down vote accepted

Hello you need to change max_locks_per_transaction in the postgresql.conf file typically located in /var/lib/pgsql/data or wherever you installed the data directory for PostgreSQL.

Which was already answered here

You will have to restart the PostgreSQL Service.

The answer above also talks about having to possibly increase shared memory. Here is a link to a PostgreSQL article on that subject.

share|improve this answer
    
Thanks for the link... However I have already changed the value of max_locks_per_transaction parameter, hope the link will give me some useful pointers.. Thanks !! –  Amit Oct 18 '13 at 10:17
    
@Amit I just included a link to the other part of the answer which includes increasing the maximum amount of shared memory available. –  Craig Efrein Oct 18 '13 at 10:19
    
I already read that link.. But I have no idea what will be its consequences for other applications and for OS itself. Since this is a live system and without backup I didn't want to screw up things.. –  Amit Oct 18 '13 at 10:23
    
@Amit Can you add the pg_dump command you are using? –  Craig Efrein Oct 18 '13 at 10:26
    
@Amit and what is the current setting of max_connections? –  dezso Oct 18 '13 at 11:11
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.