Parsing apache logs using PostgreSQL

Question

This O'Reilly article gives an example of a PostgreSQL statement that parses an Apache log line:

 INSERT INTO http_log(log_date,ip_addr,record)
     SELECT CAST(substr(record,strpos(record,'[')+1,20) AS date),
            CAST(substr(record,0,strpos(record,' ')) AS cidr),
            record
 FROM tmp_apache;

Obviously this only extracts the IP and timestamp fields. Is there a canonical statement for extracting all fields from a typical combined log format record? If there isn't, I will write one and I promise to post the result here!

Answer 1

See this blog post for information on logging to Postgres.

Answer 2

OK, here is my solution:

insert into accesslog
select m[1], m[2], m[3],
    (to_char(to_timestamp(m[4], 'DD/Mon/YYYY:HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS ')
        || split_part(m[4], ' ',2))::timestamp with time zone,
     m[5], m[6]::smallint, (case m[7] when '-' then '0' else m[7] end)::integer, m[8], m[9] from (
    select regexp_matches(record,
 E'(.*) (.*) (.*) \\[(.*)\\] "(.*)" (\\d+) (.*) "(.*)" "(.*)"')
 as m from tmp_apache) s;

It takes raw log lines from the table tmp_apache and extracts the fields (using the regexp) into an array.