Re: pg_dump without explicit table locking

On Mon, Mar 17, 2014 at 7:52 AM, Jürgen Strobel <juergen+pg@strobel.info> wrote:
> at work at my company I inherited responsibility for a large PG 8.1 DB,
> with a an extreme number of tables (~300000). Surprisingly this is
> working quite well, except for maintenance and backup. I am tasked with
> finding a way to do dump & restore to 9.3 with as little downtime as
> possible.
>
> Using 9.3's pg_dump with -j12 I found out that pg_dump takes 6 hours to
> lock tables using a single thread, then does the data dump in 1 more
> hour using 12 workers. However if I patch out the explicit LOCK TABLE
> statements this only takes 1 hour total. Of course no one else is using
> the DB at this time. In a pathological test case scenario in a staging
> environment the dump time decreased from 5 hours to 5 minutes.
>
> I've googled the problem and there seem to be more people with similar
> problems, so I made this a command line option --no-table-locks and
> wrapped it up in as nice a patch against github/master as I can manage
> (and I didn't use C for a long time). I hope you find it useful.

Fascinating report.  Whether we use your patch or not, that's
interesting to know about.  Please add your patch here so we don't
forget about it:

https://commitfest.postgresql.org/action/commitfest_view/open

See also https://wiki.postgresql.org/wiki/Submitting_a_Patch

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company