From: David Steele <david@pgmasters.net>
To: Magnus Hagander <magnus@hagander.net>
To: Joe Conway <mail@joeconway.com>
Cc: Svensson Peter <peter.svensson@smhi.se>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: pgaudit and create postgis extension logs a lot inserts
Date: Fri, 19 Jan 2018 08:41:42 -0500
Message-ID: <d3351319-a6bb-6bf8-2d2b-7bcd0b731ea0@pgmasters.net> (raw)
In-Reply-To: <CABUevExuUaAg5tW0Mcg_YRRLSsh_zudNuYir=314xDJGrkyVSg@mail.gmail.com>
References: <1CC6B04B8F153C4CBE486B1C7FD897430269E49962@WINVMSERV463.ad.smhi.se>
<514a9172-df28-56ab-ee65-2c01e5cbeb6a@joeconway.com>
<CABUevExuUaAg5tW0Mcg_YRRLSsh_zudNuYir=314xDJGrkyVSg@mail.gmail.com>
On 1/19/18 6:05 AM, Magnus Hagander wrote:
>
>
> On Thu, Jan 18, 2018 at 6:54 PM, Joe Conway <mail@joeconway.com
> <mailto:mail@joeconway.com>> wrote:
>
> On 01/18/2018 04:12 AM, Svensson Peter wrote:
> > When I then install postgis extension in a database it writes a huge
> > amount of logs which slow down the server a lot.
> > Not only table creation and functions are logged, even all inserts in
> > spatial_ref_sys are written to the audit-log.
> >
> > LOG: AUDIT: SESSION,1,1,DDL,CREATE FUNCTION,,,"
> > ......
> > INSERT INTO ""spatial_ref_sys"" (""srid"",""auth_name"
> > ....
> >
> > This behaviour make pgaudit useless in our environment due to the
> > overhead in log-file write.
>
> How often do you intend to install PostGIS? Disable pgaudit, install
> PostGIS, enable pgaudit?
>
>
> Would it make sense for pgaudit to, at least by option, not include DDL
> statements that are generated as "sub-parts" of a CREATE EXTENSION? It
> should still log the CREATE EXTENSION of course, but not necessarily all
> the contents of it, since that's actually defined in the extension
> itself already?
That's doable, but I think it could be abused if it was always on and
installing extensions is generally not a daily activity.
It seems in this case the best action is to disable pgaudit before
installing postgis or install postgis first.
Regards,
--
-David
david@pgmasters.net
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david@pgmasters.net, magnus@hagander.net, mail@joeconway.com, peter.svensson@smhi.se
Subject: Re: pgaudit and create postgis extension logs a lot inserts
In-Reply-To: <d3351319-a6bb-6bf8-2d2b-7bcd0b731ea0@pgmasters.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox