On 18.03.14 02:32, Joe Conway wrote: > On 03/17/2014 05:55 PM, Jeff Janes wrote: >> On Mon, Mar 17, 2014 at 5:48 PM, Craig Ringer >> <cr...@2ndquadrant.com I wonder if doing large batches of > >> LOCK TABLE table1, table2, table3, ... > >> would help, instead of doing individual statements? > >> If I recall correctly, someone did submit a patch to do that. It >> helped when dumping schema only, but not much when dumping data. > > Not surprising at all. The huge time is incurred in taking the locks, > but if you are trying to use pg_upgrade in link mode to speed your > upgrade, you are totally hosed by the time it takes to grab those locks. > > This patch applied to 9.3 substantially fixes the issue: > 8<----------------------- > commit eeb6f37d89fc60c6449ca12ef9e91491069369cb > Author: Heikki Linnakangas <heikki.linnakan...@iki.fi> > Date: Thu Jun 21 15:01:17 2012 +0300 > > Add a small cache of locks owned by a resource owner in ResourceOwner. > 8<----------------------- > > On my 8.4 database, with 500,000 tables there were about 2.5 million > locks taken including toast tables and indexes during the schema dump. > Without the patch grabbing locks took many, many days with that many > objects to lock. With a backported version of the patch, one hour. > > So if you have a problem due to many tables on an older than 9.3 > version of Postgres, this is the direction to head (a custom patch > applied to your old version just long enough to get successfully > upgraded). >
In a testing environment I restored my 8.1 DB with 300,000 tables to a 9.3 server (using my patched pg_dump). Then I ran the original 9.3 pg_dump against the 9.3 DB again, and it works reasonably well. So I can confirm the server side improvements in 9.3 do to work for my test case. Still when I finally get around to do this on production I plan to use my patched pg_dump rather than backporting the server fix to 8.1, as I'd rather not touch our already-patched-for-something-else 8.1 server. I can't wait to get my hand on 9.x replication features and other stuff :-) -Jürgen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers