Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1de9VS-0003uI-BZ for pgsql-admin@arkaria.postgresql.org; Sun, 06 Aug 2017 00:33:22 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1de9VR-0006qR-RB for pgsql-admin@arkaria.postgresql.org; Sun, 06 Aug 2017 00:33:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1de9Te-0003ez-Uk for pgsql-admin@postgresql.org; Sun, 06 Aug 2017 00:31:31 +0000 Received: from bantum.comp.uvic.ca ([142.104.177.238]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1de9TX-0001ZH-9X for pgsql-admin@postgresql.org; Sun, 06 Aug 2017 00:31:27 +0000 Received: from [192.168.1.4] (S01060018f8676393.gv.shawcable.net [184.66.128.136]) (authenticated bits=0) by bantum.comp.uvic.ca (8.14.4/8.14.4) with ESMTP id v760VFAS011532 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-GCM-SHA384 bits=256 verify=NO) for ; Sat, 5 Aug 2017 17:31:15 -0700 Subject: Re: Logging: stderr vs syslog? Cc: pgsql-admin@postgresql.org References: From: Evan Rempel Message-ID: Date: Sat, 5 Aug 2017 17:31:15 -0700 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.2.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------9384148795ECBA71AE7C34C1" Content-Language: en-MW X-UVic-Virus-Scanned: OK - Passed virus scan by ClamAV (clamd) on bantum X-UVic-Virus-Scanned: OK - Passed virus scan by Sophos (sophie) on bantum X-UVic-Scan: bantum.comp.uvic.ca filter_version 3.7.9 X-UVic-Outbound-Scan: bantum.comp.uvic.ca Not_scanned_Remote X-UVic-Spam-Scan: bantum.comp.uvic.ca No_recips_rejected X-UVic-Spam-Status: No X-UVic-Spam-Score: 1.458 HELO_MISC_IP,HTML_MESSAGE,MISSING_HEADERS X-UVic-Spam-Level: Spam-Level S X-Scanned-By: MIMEDefang 2.78 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 This is a multi-part message in MIME format. --------------9384148795ECBA71AE7C34C1 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Tou are now asking questions that are more appropriate for the specific syslog product you are using. rsyslog, syslogd, syslog-ng etc. In general you need to ask yourself what the ramifications of loosing the most recent locally stored syslog messages. If you are forwarding you syslog message to a central syslog server, or perhaps event two syslog servers then the loss of some local syslog messages in the event of a local host crash is not very significant. If you can live with the local loss then adding a "no sync" option to your syslog configuration is a good idea. For further details please follow up with your syslog specific support channel. Evan. On 08/04/2017 10:22 PM, Don Seiler wrote: > Thanks everyone for your feedback. I've got a test setup running with > syslog and logrotate and it's working well. > > Is there any concern with the syslog file sync where it's suggested to > put a dash before the file name to remove the sync requirement? > > Don. > > On Aug 4, 2017 2:51 PM, "Evan Rempel" > wrote: > > I have been a systems administrator of PostgreSQL since version > 7.0. I am also a primary logging architect for a 4000+ device > network infrastructure and SEIM alerting infrastructure architect. > > Syslog is by far the best approach to logging. With the right > product it is way FASTER than stderr and there are a ton of tools > to parse, analyze, view and report on syslog streams. > > There is one caveat with PostgreSQL version <= 9.5 and that is > that syslog messages are wrapped at approx 80 characters which > makes parsing and error detection problematic. pgBadger may > address this limitation by unwrapping such logs messages whereas > generic log parsing engines do not have any specialized knowledge > about how PostgreSQL lines might be wrapped. PostgreSQL version <= > 9.5 this is a compile time only option but starting in 9.6 this is > a runtime configuration directive. > > There are two strong reasons for using syslog. > > 1. In a well architected logging solution the syslog process on > the host will also send the log messages to a central log server. > This means that if the database server is compromised leading to > an untrusted set of log files, there is a trusted copy of the logs > on another server. > > 2. When running a high availability or clustered database all of > the logs can be aggregated to a central log server which places > all of the logs from all of the database servers into one easy to > read/parse/process location. > > I hope this provides some rationale for using syslog. > > Evan. > > On 08/04/2017 09:26 AM, Don Seiler wrote: >> I've just inherited a few PostgreSQL DBs, having come from Oracle >> land. I'm looking to shore up the logging situation. Right now we >> use stderr logging and they get rotated based on size threshold. >> I'd like for those old logs to be gzipped so we can keep more on >> disk rather than current method of just deleting old logs to free >> up space. This is mostly on pgsql 9.2 with a couple of 9.3, but >> I'm planning to upgrade everything to 9.6.3 when I get my feet on >> solid ground. >> >> Couple of question around this: >> >> 1. I thought logrotate would be a no-brainer here, but it sounds >> like I should then change to use syslog rather than stderr. >> I've read some caveats around syslog needing to sync files >> and potentially slow things down. I'm wondering if any >> grizzled production postgres veterans could offer up their >> experience. >> 2. Alternatively I could just keep it going with stderr and have >> a separate script run find/gzip on log files beyond a certain >> mtime threshold. This would probably be the quickest to >> implement, but I'd much rather use logrotate facilities if >> there are no strong opinions against using syslog. >> >> Thanks in advance for your time, I'm sure I'll be making a lot of >> us of these mailing lists in the not-too-distant future. >> >> Don. >> >> -- >> Don Seiler >> www.seiler.us > > --------------9384148795ECBA71AE7C34C1 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit
Tou are now asking questions that are more appropriate for the specific syslog product you are using.
rsyslog, syslogd, syslog-ng etc.

In general you need to ask yourself what the ramifications of loosing the most recent locally stored syslog messages. If you are forwarding you syslog message to a central syslog server, or perhaps event two syslog servers then the loss of some local syslog messages in the event of a local host crash is not very significant.

If you can live with the local loss then adding a "no sync" option to your syslog configuration is a good idea.

For further details please follow up with your syslog specific support channel.

Evan.

On 08/04/2017 10:22 PM, Don Seiler wrote:
Thanks everyone for your feedback. I've got a test setup running with syslog and logrotate and it's working well.

Is there any concern with the syslog file sync where it's suggested to put a dash before the file name to remove the sync requirement?

Don.

On Aug 4, 2017 2:51 PM, "Evan Rempel" <erempel@uvic.ca> wrote:
I have been a systems administrator of PostgreSQL since version 7.0. I am also a primary logging architect for a 4000+ device network infrastructure and SEIM alerting infrastructure architect.

Syslog is by far the best approach to logging. With the right product it is way FASTER than stderr and there are a ton of tools to parse, analyze, view and report on syslog streams.

There is one caveat with PostgreSQL version <= 9.5 and that is that syslog messages are wrapped at approx 80 characters which makes parsing and error detection problematic. pgBadger may address this limitation by unwrapping such logs messages whereas generic log parsing engines do not have any specialized knowledge about how PostgreSQL lines might be wrapped. PostgreSQL version <= 9.5 this is a compile time only option but starting in 9.6 this is a runtime configuration directive.

There are two strong reasons for using syslog.

1. In a well architected logging solution the syslog process on the host will also send the log messages to a central log server. This means that if the database server is compromised leading to an untrusted set of log files, there is a trusted copy of the logs on another server.

2. When running a high availability or clustered database all of the logs can be aggregated to a central log server which places all of the logs from all of the database servers into one easy to read/parse/process location.

I hope this provides some rationale for using syslog.

Evan.

On 08/04/2017 09:26 AM, Don Seiler wrote:
I've just inherited a few PostgreSQL DBs, having come from Oracle land. I'm looking to shore up the logging situation. Right now we use stderr logging and they get rotated based on size threshold. I'd like for those old logs to be gzipped so we can keep more on disk rather than current method of just deleting old logs to free up space. This is mostly on pgsql 9.2 with a couple of 9.3, but I'm planning to upgrade everything to 9.6.3 when I get my feet on solid ground.

Couple of question around this:
  1. I thought logrotate would be a no-brainer here, but it sounds like I should then change to use syslog rather than stderr. I've read some caveats around syslog needing to sync files and potentially slow things down. I'm wondering if any grizzled production postgres veterans could offer up their experience.
  2. Alternatively I could just keep it going with stderr and have a separate script run find/gzip on log files beyond a certain mtime threshold. This would probably be the quickest to implement, but I'd much rather use logrotate facilities if there are no strong opinions against using syslog.
Thanks in advance for your time, I'm sure I'll be making a lot of us of these mailing lists in the not-too-distant future.

Don.

--
Don Seiler
www.seiler.us



--------------9384148795ECBA71AE7C34C1--