Re: nice'ing the postgres COPY backend process to make pg_dumps run more "softly" - Mailing list pgsql-general

From Guillaume Lelarge
Subject Re: nice'ing the postgres COPY backend process to make pg_dumps run more "softly"
Date
Msg-id 1332190318.2235.26.camel@localhost.localdomain
Whole thread Raw
In response to nice'ing the postgres COPY backend process to make pg_dumps run more "softly"  (Aleksey Tsalolikhin <atsaloli.tech@gmail.com>)
Responses Re: nice'ing the postgres COPY backend process to make pg_dumps run more "softly"  (Steve Crawford <scrawford@pinpointresearch.com>)
Re: nice'ing the postgres COPY backend process to make pg_dumps run more "softly"  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
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


pgsql-general by date:

Previous
From: Mike Christensen
Date:
Subject: Re: current thinking on Amazon EC2?
Next
From: Florent THOMAS
Date:
Subject: Re: Conditionnal validation for transaction