PostgreSQL log cleaner

postgresqllogcleaner.pl

#!/usr/bin/perl -s
# postgresqllogcleaner.pl
# PostgreSQL log cleaner
# 
# Usage:
#   postgresqllogcleaner.pl -i=/path/to/postgresql.log -o=/path/to/output.log
#
# Short description:
# This script used for cleaning postgresql log to be valid SQL
#
# Long description:
# PostgreSQL log contain some extra text like LOG statement, LOG duration, LOG connection, etc
# and every query log not ended with semicolon, which is invalid if you have multiple query,
# this script should cleaned up for you
#
# Background:
# We want to upgrade our production engine to PotsgreSQL 9 from 8, but we want to test if all query working fine
# so we use our log to test if any error occured (you can use psql cli with -f option to import log file)
#
# Tested on:
#   PostgreSQL 8.3.9
#   Perl v5.10.1
#
# Benchmark:
#   A huge 6,1G log to become 3,7G cleaned on approximate 15 minutes
#
# Azhari Harahap <azhari@harahap.us>
#

# Arguments (input and output file)
if (!defined $i) {
    print "Error: Missing input file\n";
    exit;
}

if (!defined $o) {
    print "Error: Missing output file\n";
    exit;
}

$parts = '';
open(i) or die("Error: Could not open input file\n");
open(my $out, '>', $o) or die("Error: Could not open output file\n");

while (<i>)
{
    # trim line
    $_ =~ s/^\s+|\s+$//g; 

    # use "LOG: statement" as limiter
    if($_ =~ /(.*)statement:(.*)/gis)
    {
        # parts not empty (query has completed) and reset parts
        if($parts ne '')
        {
            print $out $parts.";\n";
            $parts = '';
        }

        # query on log statement (e.g. "LOG: statement SELECT ...")
        if($2 ne '')
        {
            $parts = '';
            $parts .= $2;
        }
    }
    elsif($_ !~ /LOG/gis)
    {
        chomp($_);
        $_ =~ s/\r//g;
        $parts .= ' '.$_;
    }
}