Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9916C476260 for ; Fri, 24 Jan 2003 20:10:07 -0500 (EST) Received: from filer (12-235-198-106.client.attbi.com [12.235.198.106]) by postgresql.org (Postfix) with ESMTP id 1D8CB476852 for ; Fri, 24 Jan 2003 20:04:33 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) (uid 1000) by filer with local; Fri, 24 Jan 2003 17:04:36 -0800 Date: Fri, 24 Jan 2003 17:04:36 -0800 From: Kevin Brown To: PgSQL Performance ML Subject: Re: Having trouble with backups (was: Re: Crash Recovery) Message-ID: <20030125010436.GB28252@filer> Mail-Followup-To: Kevin Brown , PgSQL Performance ML References: <2147483647.1043361178@[10.0.1.133]> <1043416377.29437.70.camel@haggis> <3E3158DA.7080807@mochima.com> <22650.1043442101@sss.pgh.pa.us> <3E31DA80.7000000@mochima.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline In-Reply-To: <3E31DA80.7000000@mochima.com> User-Agent: Mutt/1.4i Organization: Frobozzco International X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/309 X-Sequence-Number: 956 Carlos Moreno wrote: > And no, as I said above, I don't think the software locks > any tables -- in fact, if you ask me, I would say *there > is* bad coding in the application, but precisely because > there are no locks, no transactions (I know, shame on me! > That's near the top in the list of most important things > to do...), so that's why I was so reluctant to believe > my colleague when he insisted that the pg_dump's were > "freezing" the application... I had to see it with my > own eyes, and on two different occasions, to be convinced > :-( > > In case this tells you something... The size of the > backup files (in plain ASCII) are around 300MB (the > command is "nice pg_dump -c -f file.sql dbname"). One thing you can do to help track this down is to place stats_command_string = on in your postgresql.conf and restart the database (it may be sufficient to tell the database to reread the config file via "pg_ctl reload"). Then, when the backup is going, run the application. When it "freezes", connect to the database via psql as the user postgres and do a "select * from pg_stat_activity". You'll see the list of connected processes and the current query being executed by each, if any. Do that multiple times and you should see the progress, if any, the application is making in terms of database queries. Hope this helps... -- Kevin Brown kevin@sysexperts.com