Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UAed6-0006io-OF for pgsql-admin@arkaria.postgresql.org; Wed, 27 Feb 2013 10:52:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UAed6-00008h-8h for pgsql-admin@arkaria.postgresql.org; Wed, 27 Feb 2013 10:52:56 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UAed4-00006b-2r for pgsql-admin@postgresql.org; Wed, 27 Feb 2013 10:52:54 +0000 Received: from biglist.com ([207.99.53.219]) by makus.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UAed2-0000tM-6J for pgsql-admin@postgresql.org; Wed, 27 Feb 2013 10:52:52 +0000 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d=biglist.com; s=bravo-dkim; t=1361962372; bh=rt2wqxO8KmGKYZbvnOced6oRU1Y=; h=Received:Received:References:In-Reply-To:Mime-Version: Content-Type:Message-Id:Content-Transfer-Encoding:Cc:From:Subject: Date:To:X-Mailer; b=AAY6FrEbQmpBZ/OUV/wFYMOGNrfPaBzOF2vR+/xQuGp/UA9k9cyUInYcNup6/PyWJ bLOKz6NjldmIi94Ojdrd8vyBHaQ2XoK7pARp2VSQEvpCDqA4Vkth+TaFuFp7hmg8dA HAH57LEAdJ3vJdJ4UKKiGwx7Rf+KA5WBfEt6oUSw= Received: (qmail 95381 invoked from network); 27 Feb 2013 10:52:50 -0000 Received: from foon.sporktines.com (HELO toasty.sporklab.com) (spork@96.57.144.66) by biglist.com with ESMTPA; 27 Feb 2013 10:52:50 -0000 References: <20130225145938.GL7039@renesys.com> In-Reply-To: <20130225145938.GL7039@renesys.com> Mime-Version: 1.0 (Apple Message framework v1085) Content-Type: text/plain; charset=windows-1252 Message-Id: <2D6ECD19-EFF2-4BBC-BB6D-FE7E56DA78EA@biglist.com> Content-Transfer-Encoding: quoted-printable Cc: pgsql-admin@postgresql.org From: Charles Sprickman Subject: Re: logging full queries separately Date: Wed, 27 Feb 2013 05:52:49 -0500 To: John Rouillard X-Mailer: Apple Mail (2.1085) X-Pg-Spam-Score: -2.2 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org On Feb 25, 2013, at 9:59 AM, John Rouillard wrote: > On Sat, Feb 23, 2013 at 12:55:07AM -0500, Charles Sprickman wrote: >> [...] >> The one huge downside to this is that the logs are so noisy, it's >> hard to track down errors and stats real-time since the query logging >> is so noisy. >> [...] >> Right now I'm considering just switching to syslog-ng or rsyslog >> and seeing if there's a way I can parse the non-query messages >> out and dump them in another logfile=85 Any pointers on that? >=20 > I use SEC for log analysis and I identify query logs by looking for > the duration entry in the first line. Then pull the triplet of: >=20 > hostname, pid and log line id (the 5 in [5-2], [5-3] etc.) >=20 > and find all the rest of the lines by matching that triplet. I'm having sort of the opposite problem - for pgbadger, I simply log everything and that's easy enough. For real-time human consumption, I want to filter out all the statements so I'm just left with errors, periodic stats, notices of temporary tables being created, etc. But what really screws me up on that match is the multi-line output: Feb 19 05:06:16 j15 postgres[32818]: [28-2] db=3Dcollects,user=3Dcollects S= TATEMENT: select count(distinct s.sub_id) as subscribers, count(distinct l= s.id) as subscriptions Feb 19 05:06:16 j15 postgres[32818]: [28-3] from list l=20 Feb 19 05:06:16 j15 postgres[32818]: [28-4] join list_sub ls o= n (l.list_id =3D ls.list_id) First line, easy enough, if I see "STATEMENT", throw it away. It's those next two lines. And rsyslog is doing line-by-line processing on filters. I suppose my other option is to find every possible prefix (STATEMENT, LOG, ERROR, and who knows how many others) and do a positive match for all of those rather than trying to negate the statements. Does any of that make sense? Here's a rough example in rsyslog's config format: } else if $fromhost-ip =3D=3D '10.99.88.22' then { local0.* /var/log/j15/pgsql-full if not $msg contains 'STATEMENT' then /var/log/j15/pgsql-brief } I suspect I'm going to have to instead explicitly match everything that's not a "STATEMENT". Just need to figure out what the full list is. I think that part of the log line corresponds to "%i" in log_line_prefix. Thanks, Charles >=20 >> Without breaking compatibility with pgbadger, what >> log_line_prefix might make it easier to pick out >> statements (even when they span multiple lines)? >=20 > Just for reference I use: >=20 > '%t %u@%d(%p): ' >=20 > --=20 > -- rouilj >=20 > John Rouillard System Administrator > Renesys Corporation 603-244-9084 (cell) 603-643-9300 x 111 >=20 >=20 > --=20 > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin --=20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin