Re: pg_dump without explicit table locking

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
>> <craig@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.linnakangas@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