Re: pg_dump in a production environment

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: "Thomas F(dot) O'Connell" <tfo(at)sitening(dot)com>
Cc: PgSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: pg_dump in a production environment
Date: 2005-05-23 20:25:23
Message-ID: 20050523202514.GD12731@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

What's you pg_dump command? Some options may take a lot of memory.

If you list the processes while this is going on, do you see one
chewing all your memory? i.e what's really causing the problem...

Hope this helps,

On Mon, May 23, 2005 at 02:54:46PM -0500, Thomas F. O'Connell wrote:
> I have a web application backed by a PostgreSQL 7.4.6 database. It's
> an application with a fairly standard login process verified against
> the database.
>
> I'd like to use pg_dump to grab a live backup and, based on the
> documentation, this would seem to be a realistic possibility. When I
> try, though, during business hours, when people are frequently
> logging in and otherwise using the application, the application
> becomes almost unusable (to the point where logins take on the order
> of minutes).
>
> According to the documentation, pg_dump shouldn't block other
> operations on the database other than operations that operate with
> exclusive locks. Ordinarily, I run pg_autovacuum on the box, so I
> tried again after killing that, thinking that perhaps any substantial
> vacuum activity might affect pg_dump. I tried again to no avail.
>
> Excepting the rest of the application, the login process should be
> completely read-only and shouldn't require any exclusive locks.
>
> Connections don't really pile up excessively, and load on the machine
> does not get in the red zone. Is there anything else I should be
> noticing?
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Scott Frankel 2005-05-23 20:27:01 Re: urgent: another postmaster
Previous Message Simon Riggs 2005-05-23 20:22:33 Re: PITR restore hot standby