{"id":15073,"date":"2016-12-06T10:00:46","date_gmt":"2016-12-06T09:00:46","guid":{"rendered":"https:\/\/www.credativ.de\/blog\/credativ-inside\/howto-centralized-and-semantic-logging-for-postgresql\/"},"modified":"2016-12-06T10:00:46","modified_gmt":"2016-12-06T09:00:46","slug":"howto-centralized-and-semantic-logging-for-postgresql","status":"publish","type":"post","link":"https:\/\/www.credativ.de\/en\/blog\/postgresql-en\/howto-centralized-and-semantic-logging-for-postgresql\/","title":{"rendered":"[Howto] Centralized and Semantic Logging for PostgreSQL\u00ae<\/sup>"},"content":{"rendered":"

Introduction<\/h2>\n

Today, there is no need to argue why centralized logging<\/a> is useful or even necessary. Most medium-sized companies now have centralized logging or are currently implementing it. <\/p>\n

Once the infrastructure is in place, it is important to use it effectively and efficiently! Especially as an infrastructure operator or service provider, the goal is to optimally support different stakeholders with different requirements. For example, the development department should have continuous access to all logs from their test systems. However, from production, perhaps only all error messages are needed in real time, but more upon request. <\/p>\n

Such models can be easily implemented and tested with graylog<\/a>\u00ae or Kibana\u00ae. Classification and analysis may work well and efficiently in test operations or small environments with PostgreSQL\u00ae<\/sup> default settings. However, when operating a large number of databases or retaining logs for extended periods, usage can quickly become difficult. <\/p>\n

Problem Statement<\/h2>\n

Log entries are centrally captured and can in principle be used. In practice, however, it is difficult to impossible to extract all relevant information in a timely manner. Searching for specific entries requires full-text searches with wildcards, which is no longer practical with large data volumes. <\/p>\n

For example, if you want to grant specific groups access exclusively to logs that meet certain functional criteria, such as database name, error_severity, or similar, this must be implemented through full-text search and error-prone filters.<\/p>\n

If a DBA wants to see all messages from a specific user, a specific query, or a session, this requires particularly complex indexing for wildcard searches. Alternatively, such queries are very slow and cannot be answered immediately. <\/p>\n\n\n\n\n
Starting Point:<\/b><\/td>\nCentralized logging has already been implemented, e.g., ELK Stack or graylog\u00ae.<\/td>\n<\/tr>\n
Goal:<\/b><\/td>\nWe want to capture PostgreSQL\u00ae<\/sup> log messages semantically and thereby efficiently: classify, group, and analyze them.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Alternative<\/h2>\n

The alternative is to capture log messages semantically and store the individual fields in an appropriate data structure.<\/p>\n

If the normal stderr log is used, parsing becomes difficult to impossible, as the individual fields cannot be identified. However, PostgreSQL\u00ae<\/sup><\/a> also offers the option to produce log messages in CSV format (csvlog). This outputs all fields comma-separated. <\/p>\n\n\n\n
Excursus:<\/b> The idea is not new. For a long time, some DBAs have been loading their logs directly back into a PostgreSQL\u00ae<\/sup> table. This allows them to search and process the logs with SQL and all familiar built-in tools. DBAs are usually very satisfied with this construct, but it represents an isolated solution. Example table for PostgreSQL\u00ae<\/sup> 9.5: <\/em><\/p>\n
\n
CREATE TABLE postgres_log\n(\n  log_time timestamp(3) with time zone,\n  user_name text,\n  database_name text,\n  process_id integer,\n  connection_from text,\n  session_id text,\n  session_line_num bigint,\n  command_tag text,\n  session_start_time timestamp with time zone,\n  virtual_transaction_id text,\n  transaction_id bigint,\n  error_severity text,\n  sql_state_code text,\n  message text,\n  detail text,\n  hint text,\n  internal_query text,\n  internal_query_pos integer,\n  context text,\n  query text,\n  query_pos integer,\n  location text,\n  application_name text,\n  PRIMARY KEY (session_id, session_line_num)\n);<\/pre>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Structure<\/h2>\n
\"Logging\"<\/a><\/p>\n
Implementation<\/h2>\n
The csvlog can serve as the basis for efficiently populating a centralized logging system. To keep further processing as simple as possible and to avoid committing to a specific logging system, we translate the log from CSV to JSON. It can then be fed in as desired. In the following example, TCP is used.   <\/p>\n
To change the log format, the following options must be adjusted in PostgreSQL\u00ae<\/sup>:<\/p>\n
\n
#------------------------------------------------------------------------------\n# ERROR REPORTING AND LOGGING\n#------------------------------------------------------------------------------\n \n# - Where to Log -\n \nlog_destination = 'csvlog' # Valid values are combinations of\n  # stderr, csvlog, syslog, and eventlog,\n  # depending on platform. csvlog \n  # requires logging_collector to be on.\n \n# This is used when logging to stderr:\nlogging_collector = on # Enable capturing of stderr and csvlog\n  # into log files. Required to be on for \n  # csvlogs.\n  # (change requires restart)\n  # These are only used if logging_collector is on:\nlog_directory = '\/var\/log\/postgresql' # directory where log files are written,\n  # can be absolute or relative to PGDATA<\/pre>\n<\/div>\n
For parsing and translating to JSON as well as delivering to the logging system, we use logstash\u00ae with the following configuration as a Jinja2 template:<\/p>\n
\n
input {\n  file {\n  \"path\" =>  \"\/var\/log\/postgresql\/*.csv\"\n  \"sincedb_path\" =>  \"\/tmp\/sincedb_pgsql\"\n  # fix up multiple lines in log output into one entry\n  codec =>  multiline {\n  pattern =>  \"^%{TIMESTAMP_ISO8601}.*\"\n  what =>  previous\n  negate =>  true\n  }\n  }\n}\n \n# Filter is tested for PostgreSQL\u00ae<\/sup>  9.5\nfilter {\n  csv {\n  columns =>  [ \"pg_log_time\", \"pg_user_name\", \"pg_database_name\",\n  \"pg_process_id\", \"pg_connection_from\", \"pg_session_id\",\n  \"pg_session_line_num\", \"pg_command_tag\",\n  \"pg_session_start_time\", \"pg_virtual_transaction_id\",\n  \"pg_transaction_id\", \"pg_error_severity\", \"pg_sql_state_code\",\n  \"pg_sql_message\", \"pg_detail\", \"pg_hint\", \"pg_internal_query\",\n  \"pg_internal_query_pos\", \"pg_context\", \"pg_query\",\n  \"pg_query_pos\", \"pg_location\", \"pg_application_name\" ]\n  }\n \n  date {\n  #2014-05-22 17:02:35.069 CDT\n  match =>  [\"log_time\", \"YYYY-MM-dd HH:mm:ss.SSS z\"]\n  }\n \n  mutate {\n  add_field =>  {\n  \"application_name\" =>  \"postgres\"\n  }\n  }\n \n}\n \noutput {\n  tcp {\n  host =>  \"{{ log_server }}\"\n  port =>  {{ log_port }}\n  codec =>  \"json_lines\"\n  }\n}<\/pre>\n<\/div>\n
It is important that the filter for translating the CSV fields is adapted to the PostgreSQL\u00ae<\/sup> major version being used. The fields can differ from version to version. In most cases, new fields are added.  <\/p>\n
Also note that logstash\u00ae has difficulty recognizing the timestamp independently. The specific format should be specified (time zone). <\/p>\n
The following variables must be set:<\/p>\n\n\n\n\n
{{ log_server }}<\/b><\/td>\nLog server, in our case a graylog\u00ae<\/td>\n<\/tr>\n
{{ log_port }}<\/b><\/td>\nPort on the log server<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Configured this way, log files can be efficiently classified and searched. Permissions are also easier to manage. <\/p>\n