由 John Doe 三月 14, 2023
摘要：PostgreSQL 支持数据类型json和jsonb，而 nginx 支持 JSON 格式的日志输出。我们可以使用 PostgreSQL 轻松实现对 nginx 访问日志的分析。
目录
配置 nginx 使用 JSON 格式输出日志
修改 nginx 配置文件/etc/nginx/nginx.conf，配置访问日志的输出格式，以 json 形式输出：
##
# Logging Settings
##
map $time_iso8601 $date {
~([^T]+) $1;
}
map $time_iso8601 $time {
~\T([0-9:]+)[\+\-] $1;
}
map $time_iso8601 $timezone {
~([\+\-][0-9:]+)$ $1;
}
map $msec $millisec {
~\.([0-9]+)$ $1;
}
log_format json_logger escape=json
'{'
'"time":"$date $time.$millisec$timezone",'
'"client_addr":"$remote_addr",'
'"host":"$http_host",'
'"path":"$request_uri",'
'"request":"$request",'
'"status":"$status",'
'"content_type":"$sent_http_content_type",'
'"body_bytes_sent":"$body_bytes_sent",'
'"http_referrer":"$http_referer",'
'"user_agent":"$http_user_agent"'
'}';
access_log /var/log/nginx/access.log json_logger;
error_log /var/log/nginx/error.log;
重新启动 nginx 服务，加载新的配置：
# systemctl restart nginx
导入 JSON 格式的日志文件
使用psql连接到数据库：
$ psql -U postgres -d postgres
首先，创建一个临时表，您将在其中导入数据。
CREATE TEMPORARY TABLE json_logs (log jsonb);
创建表后，让我们导入数据。
我们将使用psql的 COPY 命令将本地文件加载到 PostgreSQL 服务器并写入临时表中：
\COPY json_logs (log) FROM '/var/log/nginx/access.log';
这会将每个 JSON 对象导入到临时表中的一行中，现在您可以像这样轻松查询数据：
SELECT log->>'time' AS time, log->>'path' AS path
FROM json_logs;
time | path
-------------------------------+--------
2023-03-14 14:47:07.832+08:00 | /
2023-03-14 14:47:37.893+08:00 | /zh-cn
...
将 JSON 格式的日志内容转换成表数据
现在我们已经将 JSON 格式的日志内容存放到了数据库，但它们都在 JSONB 类型的列中，我们可能希望将其导入到适当的表中以保证类型和结构，这是非常推荐的。您可以使用 INSERT INTO .. SELECT 语句可以轻松执行此操作，下面是一个示例（您可以使用现有表来执行此操作）：
CREATE TABLE nginx_logs (
time timestamptz,
client_addr varchar,
host varchar,
path varchar,
request varchar,
status smallint,
content_type varchar,
body_bytes_sent integer,
http_referrer varchar,
user_agent varchar);
INSERT INTO nginx_logs
SELECT (log->>'time')::timestamptz,
log->>'client_addr',
log->>'host',
log->>'path',
log->>'request',
(log->>'status')::smallint,
log->>'content_type',
(log->>'body_bytes_sent')::integer,
log->>'http_referrer',
log->>'user_agent'
FROM json_logs;
这会将所有记录插入到表nginx_logs中，当运行它时，如果存在类型不匹配，PostgreSQL 将发出错误，这对于确保数据完整性非常有帮助。请注意，如果要在目标表中插入大量记录、索引和触发器会大大增加插入数据所需的时间，您可以在插入时暂时禁用它们以显着提高速度。
查询分析访问日志
现在我们已经将 JSON 格式的日志内容转换成了数据表，接下来我们就可以利用 PostgreSQL 强大的查询分析能力，对访问日志进行分析。比如我们想查询每小时内的访问量，可以这样：
SELECT date_trunc('hour', time) AS hour,
count(time) AS access
FROM nginx_logs
WHERE content_type LIKE 'text/html;%'
GROUP BY hour
ORDER BY hour;