Re: pg_dump without explicit table locking

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

Joe

- -- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTJ6I1AAoJEDfy90M199hlDsAP/320vLhjKRjaxulBmcESmuze
LUoIOHLS2ZacybTjcMlZSoTBSzf6iXzc3A84ROhQ0c7ASYzWtF/YFkd039FzHz+e
TxtuodZd+CO18f1ZYBR5S7AhXhsA7oviXXdgPhqwb14mIqGAlvblTp9cvMODK+OS
O96NSLe2qe1AvmxwwthcKzhlXBChzoRvT8jXeS5A/G+VfM7UV1HApGmklJE0oe9+
ZaXhxQWGecKqZgkPwfZzIzOz9qQITDb3woi7GxbiXLv8Ds1lgAxPRz26qJB/mKBC
NqxQHViyty79TA8EFV8DrE0g++CUz33rSs1suY5Z1yzsQ7iEFBP1U52BJE5ZdB0J
8Zpz1eLT15fEIuV+64MeXN47U2refJdEjw9Ozx788MgDOu43k9m4+VHjzcH1AO2l
qfp0eqxpIjDpqH4Lu/0DAzl86yEW76tJX+pdieICGOHLdruLS/984gZGtDpjclNE
l/FaliLQQ4Bvqg8tMmmq/dyTxBG+BRmfCBbaBRdtQA762P9Lh7QsL/mGHukwRNVb
M5Ve7i/1HT7ZrazEnMkAotnYMrH5QTy1qTVfjiR0gjXzccdMXSOT8NN/yiWwOq6d
ZRuBvr8Ws+xCDDWwABj8Oh2mKpupy04/87EaTy2+sh6yJaIZPPV+n4ftjF4NF/gP
zBhXxMpFalKDqevkp52Z
=/Nl7
-----END PGP SIGNATURE-----