Title:
Tag1 for Path:	a single word in lower characters
Tag2 for Path:
List of alternative single tags:	one or many words in lower characters, separated by comma or space
Text:	<p>We want to load access.log to our PostgreSQL database for further analysis. This is how we proceed.</p> <h3>Configure nginx to produce access.log in json format</h3> <p>In the nginx.conf we need to define the log format for the json access logfile.</p> <pre> 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" }'; </pre> <p>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.</p> <pre> # 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; </pre> <h3>Install Contribution Library that enables foreign data wrapper (FDW)</h3> <code> cd /usr/ports/databases/postgresql10-contrib/ && make install clean </code> <p>Restart or reload nginx to make the changes effective.</p> <samp> ====> 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 </samp> <h3>Enable the extension in PostgreSQL</h3> <samp> CREATE EXTENSION file_fdw; ERROR: could not open extension control file "/usr/local/share/postgresql/extension/file_fdw.control": No such file or directory </samp> <p>Connect to your database with the database super user</p> <code> psql -U postgres -d databasename </code> <samp> psql (10.3) Type "help" for help. database=# <kbd>CREATE EXTENSION file_fdw;</kbd> CREATE EXTENSION database=# <kbd>CREATE SERVER accessfiles FOREIGN DATA WRAPPER file_fdw;</kbd> </samp> <code> ln -sf /var/log/nginx-accessjson_onldat.log /tmp/accesslogjson </code> <pre> CREATE FOREIGN TABLE accesslog (arec varchar(2000)) SERVER accessfiles OPTIONS ( filename '/tmp/accesslogjson'); </pre> <samp> wnk20160812=# select * from accesslog limit 1; </samp>
URL:
Operation:	Delete Update Insert
Template Prompt