Ready to get going? Start your 14 days free trial today

Start free trial

Have an account? Sign in

Send data via PostgreSQL to your Logstash instance provided by Logit.io

Postgresql Logs

Collect and ship PostgreSQL logs to Logstash and Elasticsearch.

Filebeat is a lightweight shipper that enables you to send your PostgreSQL application logs to Logstash and Elasticsearch. Configure Filebeat using the pre-defined examples below to start sending and analysing your PostgreSQL application logs.

Step 1 - Install FilebeatCopy

deb (Debian/Ubuntu/Mint)

sudo apt-get install apt-transport-https
wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -
echo 'deb https://artifacts.elastic.co/packages/oss-6.x/apt stable main' | sudo tee /etc/apt/sources.list.d/beats.list

sudo apt-get update && sudo apt-get install filebeat

rpm (CentOS/RHEL/Fedora)

sudo rpm --import https://artifacts.elastic.co/GPG-KEY-elasticsearch
echo "[elastic-6.x]
name=Elastic repository for 6.x packages
baseurl=https://artifacts.elastic.co/packages/oss-6.x/yum
gpgcheck=1
gpgkey=https://artifacts.elastic.co/GPG-KEY-elasticsearch
enabled=1
autorefresh=1
type=rpm-md" | sudo tee /etc/yum.repos.d/elastic-beats.repo

sudo yum install filebeat

macOS

curl -L -O https://artifacts.elastic.co/downloads/beats/metricbeat/metricbeat-6.8.4-darwin-x86_64.tar.gz
tar xzvf metricbeat-6.8.4-darwin-x86_64.tar.gz

Windows

  • Download the Filebeat Windows zip file from the official downloads page.
  • Extract the contents of the zip file into C:\Program Files.
  • Rename the filebeat-<version>-windows directory to Filebeat.
  • Open a PowerShell prompt as an Administrator (right-click the PowerShell icon and select Run As Administrator). If you are running Windows XP, you may need to download and install PowerShell.
  • Run the following commands to install Filebeat as a Windows service: cd 'C:\Program Files\Filebeat' .\install-service-filebeat.ps1
If script execution is disabled on your system, you need to set the execution policy for the current session to allow the script to run. For example: PowerShell.exe -ExecutionPolicy UnRestricted -File .\install-service-filebeat.ps1

Step 2 - Locate Configuration FileCopy

deb/rpm /etc/filebeat/filebeat.yml
mac/win Open C:\Program Files\Filebeat\filebeat.yml

Step 3 - Enable the PostgreSQL ModuleCopy

There are several built in filebeat modules you can use. To enable the PostgreSQL module run.

deb/rpm

filebeat modules list
filebeat modules enable postgresql

macOS

./metricbeat modules list
./metricbeat modules enable PostgreSQL

Windows

.\Filebeat modules enable postgresql

Additional module configuration can be done using the per module config files located in the modules.d folder, most commonly this would be to read logs from a non-default location

deb/rpm /etc/filebeat/modules.d/
mac/win <EXTRACTED_ARCHIVE>/modules.d/

Step 4 - Configure OutputCopy

We'll be shipping to Logstash so that we have the option to run filters before the data is indexed.
Comment out the elasticsearch output block.

## Comment out elasticsearch output
#output.elasticsearch:
#  hosts: ["localhost:9200"]

Uncomment and change the logstash output to match below.

output.logstash:
    hosts: ["your-logstash-host:your-ssl-port"]
    loadbalance: true
    ssl.enabled: true

Step 5 - (Optional) Update Logstash FiltersCopy

All Logit stacks come pre-configured with popular Logstash filters. We would recommend that you add PostgreSQL specific filters if you don't already have them, to ensure enhanced dashboards and modules work correctly.

Edit your Logstash filters by choosing Stack > Settings > Logstash Filters

    if [fileset][module] == "postgresql" {
           grok {
              match => {
         "message" => "^%{LOCALDATETIME:[postgresql][log][timestamp]} %{WORD:[postgresql][log][timezone]} \[%{NUMBER:[postgresql][log][thread_id]}(-%{BASE16FLOAT:[postgresql][log][core_id]})?\] ((\[%{USERNAME:[postgresql][log][user]}\]@\[%{POSTGRESQL_DB_NAME:[postgresql][log][database]}\]|%{USERNAME:[postgresql][log][user]}@%{POSTGRESQL_DB_NAME:[postgresql][log][database]}) )?%{WORD:[postgresql][log][level]}:  (duration: %{NUMBER:[postgresql][log][duration]} ms  statement: %{GREEDYDATA:[postgresql][log][query]}|%{GREEDYDATA:[postgresql][log][message]})"
      }
      pattern_definitions => {
         "LOCALDATETIME" => "[-0-9]+ %{TIME}"
         "GREEDYDATA" => "(.|
|    )*"
"POSTGRESQL_DB_NAME" => "[a-zA-Z0-9_]+[a-zA-Z0-9_\$]*"
      }
   }
   date {
      match => [
         "[postgresql][log][timestamp]",
         "yyyy-MM-dd HH:mm:ss.SSS",
         "yyyy-MM-dd HH:mm:ss"
      ]
      target => "@timestamp"
      }
   }

Step 6 - Validate ConfigurationCopy

Let's check the configuration file is syntactically correct.

Run from the extracted archive dir:

filebeat -e -c filebeat.yml

Step 7 - Start filebeatCopy

Ok, time to start ingesting data!

deb/rpm

sudo systemctl enable filebeat
sudo systemctl start filebeat

Windows

Start-Service filebeat

Step 8 - PostgreSQL Logging OverviewCopy

PostgreSQL (often shortened to Postgres) is a highly stable open-source relational database that supports both relational & non-relational querying. Postgres can run across the majority of operating systems including Linux, Windows & macOS.

PostgreSQL is used by some of the world’s best known brands including Apple, IMDB, Red Hat & Cisco due to its robust feature set, useful addons & scalability.

Some of the benefits of using this database include their support for the majority of programming languages as well as it’s strengths as a reliable transactional database for companies of all sizes.

PostgreSQL users are encouraged to log as much as possible as with insufficient configuration you could easily lose access to key messages for troubleshooting and error resolution. Below are some of the most important logs you’ll likely need to analyse when running Postgres.

PostgreSQL transaction logs help the user to identify what queries a transaction encountered.

Remote Host IP/Name (w/ port) logs can serve to help security technicians identify suspicious activity that has occurred. If you are looking to pinpoint troublesome sessions affecting your infrastructure you might turn to Process ID logs for further insights.

When it comes to logging in Postgres there are twenty three other parameters which can be isolated for troubleshooting using the various keywords; ERROR, FATAL, WARNING, & PANIC.

With all these logs, directories & parameters it is easy to become overwhelmed at the prospect of having to thoroughly analyse your log data & you may wish to use a log management system to streamline your processes.

Our built in PostgreSQL log file analyser helps DBAs, sysadmins, and developers identify issues, create visualisations & set alerts when preconfigured and custom parameters are met.

If you need any assistance with analysing your PostgreSQL logs we're here to help. Feel free to reach out by contacting the Logit support team via live chat & we'll be happy to help you start analysing your data.

Toggle View

Expand View

Return to Search