4

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

|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
  • By the way, having 18500 tables in your database is outrageous, not because it is a large number, but because it is almost certainly indicative of a schema gone very wrong. And the solution is not merely to split your database into multiple databases. – Acumenus Nov 4 '14 at 23:07
  • @A-B-B I I agree, please see my answer below to understand the problem and the way I solved it. Thanks – Amit Nov 5 '14 at 6:18
3

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.

|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
1

Actually the problem was that some database procedures who did following tasks stopped working due to some newly introduced errors. The procedures performed below tasks:-

  1. summarize the data
  2. insert summary data in other table
  3. truncate table (to reduce the size and prevent creation of new table)

That is why almost 15K+ tables were created due to this side effect.

I did below things to solve this problem:

  1. Solved the errors in procedure logic so that it starts doing its works so that no new tables are NOT created.
  2. Created Special procedures to periodically summarize the backlogs of tables (those 15K+ tables which were not summarized).
  3. Once summary is created correctly instead of truncating I just dropped the table.

Instead of doing step 2 and 3 at once I did in batches and within 2-3 days I got database reduced significantly and after that I was able to take pg_dump.

Thanks all for your help..

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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