From: | Jürgen Strobel <juergen+pg(at)strobel(dot)info> |
---|---|
To: | Joe Conway <mail(at)joeconway(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Craig Ringer <craig(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jim Nasby <jim(at)nasby(dot)net>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: pg_dump without explicit table locking |
Date: | 2014-03-18 12:25:50 |
Message-ID: | 53283B4E.40502@strobel.info |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)2ndquadrant(dot)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(dot)linnakangas(at)iki(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Fujii Masao | 2014-03-18 12:38:06 | Re: HEAD seems to generate larger WAL regarding GIN index |
Previous Message | Petr Jelinek | 2014-03-18 12:23:54 | Re: plpgsql.warn_shadow |