Import nginx accesslog to PostgreSQL

We want to load access.log to our PostgreSQL database for further analysis. This is how we proceed.

Configure nginx to produce access.log in json format

In the nginx.conf we need to define the log format for the json access logfile.

 log_format json_combined escape=json '{ "time_local": "$time_local", '
 '"remote_addr": "$remote_addr", '
 '"remote_user": "$remote_user", '
 '"request": "$request", '
 '"status": "$status", '
 '"body_bytes_sent": "$body_bytes_sent", '
 '"request_time": "$request_time", '
 '"http_referrer": "$http_referer", '
 '"http_user_agent": "$http_user_agent" }';

This is the relevant part of the nginx vhost. Please note that for contingency reasons we define two access_log file: one standard format and one in json format.

# dont log bots, crawler etc see also access_log  
map $http_user_agent $loggable {
   "~*(bot|crawler|slurp|robot|spider)" 0;
   default     1;
}

server {
    listen 443 ssl http2;
    server_name www.myservername.com;
    access_log /var/log/nginx-access-sn.log combined if=$loggable buffer=8k;
    access_log /var/log/nginx-accessjson_sn.log json_combined if=$loggable buffer=8k;
    error_log  /var/log/nginx-error-sn.log;

Install Contribution Library that enables foreign data wrapper (FDW)

cd /usr/ports/databases/postgresql10-contrib/ && make install clean

Restart or reload nginx to make the changes effective.

====> Compressing man pages (compress-man) ===> Installing for postgresql10-contrib-10.3 ===> Checking if postgresql10-contrib already installed ===> Registering installation for postgresql10-contrib-10.3 Installing postgresql10-contrib-10.3... The PostgreSQL contrib utilities have been installed. Please see /usr/local/share/doc/postgresql/contrib/README for more information. ===> Cleaning for postgresql10-contrib-10.3

Enable the extension in PostgreSQL

CREATE EXTENSION file_fdw; ERROR: could not open extension control file "/usr/local/share/postgresql/extension/file_fdw.control": No such file or directory

Connect to your database with the database super user

psql -U postgres -d databasename psql (10.3) Type "help" for help. database=# CREATE EXTENSION file_fdw; CREATE EXTENSION database=# CREATE SERVER accessfiles FOREIGN DATA WRAPPER file_fdw; ln -sf /var/log/nginx-accessjson_onldat.log /tmp/accesslogjson

CREATE FOREIGN TABLE accesslog (arec varchar(2000)) 
   SERVER accessfiles OPTIONS ( 
     filename '/tmp/accesslogjson');

wnk20160812=# select * from accesslog limit 1;

Change Content

The information on this page is user generated content. The content does not claim to be complete or correct.

Everybody is invited to add or change the data. Just click on this link. No login or email is required. Thanks.