How do I grep a multi-line postgresql log?

Question

If I need to search for some command in the system logs, this happens:

$cat /var/log/postgresql/postgresql-9.1-main.log | grep 'UPDATE limit'
8833 2017-02-01 12:31:51 BRST whoami@172.X.X.20 anotherdb LOG:  comando: UPDATE limit
5067 2017-02-02 17:38:27 BRST whoami@172.X.X.20 thisdb LOG:  comando: UPDATE limit

But I need the full command, I am trying a lot of commands like pcggrep, agrep, grep, but I am not having any sucess.

The expected result need to be like this (note the number of lines on the query may vary):

8833 2017-02-01 12:31:51 BRST whoami@172.X.X.20 anotherdb LOG:  comando: UPDATE limit
                        SET xxx = xxx
                        FROM xxx
                          JOIN xxx ON xxx = xxx AND xxx = xxx
                          JOIN xxx ON xxx = xxx AND xxx = '012017'
                        WHERE xxx = xxx and xxx = 13
5067 2017-02-02 17:38:27 BRST whoami@172.X.X.20 thisdb LOG:  comando: UPDATE limit
                        SET xxx = xxx
                        FROM xxx
                          JOIN xxxx ON xxx = xxx AND xxx = xxx

I tried pcggrep and agrep, but they are expecting something like a start pattern or end pattern, but I think I don't have a end pattern, this is the default postgresql configuration in portuguese, but looking by eye, you can see when a new line of the query start by this number 8833 or 5067.

Answer 1

For using pcregreptry this:

pcregrep -iM "^[0-9]+.*UPDATE limit.*\n(^[^0-9].*\n){1,}" /var/log/postgresql/postgresql-9.1-main.log

-i is for case insensitivity;
-M is for multiline;

You can change the string "UPDATE limit" for other type of queries you need to search.

Basically, the regexp is: find all the lines that start with one or more digits, followed by a string, then the query, followed by a string until the end of line, then followed by 1 or more lines (){1,} that do not start with digits [^0-9].

Here is a link about extended regular expressions.