Re: nice'ing the postgres COPY backend process to make pg_dumps run more "softly"

On Sun, 2012-03-18 at 21:06 -0700, Aleksey Tsalolikhin wrote:
> Hi. When pg_dump runs, our application becomes inoperative (too
> slow). I was going to ask if nice'ing the postgres backend process
> that handles the COPY would help but I just realized probably the
> pg_dump takes out locks when it runs and nice'ing it would just make
> it run longer...
>
> However the man page says "pg_dump does not block other users
> accessing the database (readers or writers)." But if we run a
> pg_dump, the phone starts ringing, users are complaining that the web
> app is not working.
>
> Would appreciate some pointer to help me reconcile these two
> apparently contradictory facts.
>

Depends on what your app is doing. It doesn't block any usual use of the
database: DML are all accepted. But you cannot drop a table that pg_dump
must save, you cannot change its definition. So there are some DDL
commands you cannot use during a dump.

Other than this, your users shouldn't be blocked. If it happens again,
you should look at pg_locks and pg_stat_activity to understand what's
going on.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com