From: Karen Stone <kstone@eldocomp.com>
To: Svensson Peter <peter.svensson@smhi.se>, Joe Conway <mail@joeconway.com>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: RE: pgaudit and create postgis extension logs a lot inserts
Thread-Topic: pgaudit and create postgis extension logs a lot inserts
Thread-Index: AdOQVTcj4fahMDZSTfeLSa9ouAvnRgAYnG2AACYLjAAAC9gNIA==
Date: Fri, 19 Jan 2018 12:24:49 +0000
Message-ID: <004f4d185296497b8dd42a8469111a99@eldocomp.com>
References: 
 <1CC6B04B8F153C4CBE486B1C7FD897430269E49962@WINVMSERV463.ad.smhi.se>,<514a9172-df28-56ab-ee65-2c01e5cbeb6a@joeconway.com>
 <1CC6B04B8F153C4CBE486B1C7FD897430269E5D2EB@WINVMSERV464.ad.smhi.se>
In-Reply-To: 
 <1CC6B04B8F153C4CBE486B1C7FD897430269E5D2EB@WINVMSERV464.ad.smhi.se>
Accept-Language: en-US
Content-Language: en-US
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

Please remove me from this list. Thanks.

Karen Stone|=A0Technical Services| Eldorado=A0|a Division of MphasiS=20
5353 North 16th Street, Suite 400, Phoenix, Arizona 85016-3228=20
Tel (928) 892 5735 | www.eldoinc.com | www.mphasis.com |kstone@eldocomp.com=
=20


-----Original Message-----
From: Svensson Peter [mailto:peter.svensson@smhi.se]=20
Sent: Friday, January 19, 2018 4:04 AM
To: Joe Conway <mail@joeconway.com>; pgsql-performance@postgresql.org
Subject: SV: pgaudit and create postgis extension logs a lot inserts


A test to create postgis extension made 4 rsyslog processes run for several=
 minutes with high cpu util, and when you have only 8 cpu:s this take lot o=
f resources.=20
The create command also have to wait until all the log are written so there=
 are great impact.
Log file got 16 GB big only for this.

We have several databases in the same server, some of them with postgis.
Those databases are maintained bye different people, and tell them to disab=
le pgaudit every time they are doing something that can cause lot log will =
create a bad behaviour, especially when we cannot see in the logs that they=
 have disabled pgaudit.

I think postgis extension is not the only extention that creates both table=
s, functions and insert data, but if there are a way to configure pgaudit s=
o you get rid of the inserts maybe its a way to handle it.

/Peter
________________________________________
Fr=E5n: Joe Conway [mail@joeconway.com]
Skickat: den 18 januari 2018 17:54
Till: Svensson Peter; pgsql-performance@postgresql.org
=C4mne: Re: pgaudit and create postgis extension logs a lot inserts

On 01/18/2018 04:12 AM, Svensson Peter wrote:
> When I then install  postgis extension in a database it writes a huge=20
> amount of logs which slow down the server a lot.
> Not only table creation and functions are logged,  even  all inserts=20
> 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=20
> overhead in log-file write.

How often do you intend to install PostGIS? Disable pgaudit, install PostGI=
S, enable pgaudit?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Sour=
ce Development