1

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.

7
  • I usually use pcregrep -M (multiline). The devil is understanding regexp, and having \n characters in the regexp too. Try to define a regexp that gets all lines until finding the next date or until a line not starting with blanks/tabs – Rui F Ribeiro Feb 3 '17 at 11:13
  • I am with something in hands that requires more attention, will try to come back later on. – Rui F Ribeiro Feb 3 '17 at 11:16
  • Hum another problem, they are not tabs, it just spaces. The default PostgreSQL logs are so hard to handle, i am considering to change the log_format. I think they need to create a directive that forces multi lines queries to go to a single line. Dont you agree? – Luciano Andress Martini Feb 3 '17 at 11:19
  • Having it in multiples lines make it visually easier on the eyes, having it in one line if possible, makes it easier to grep. At the end of day, it depends on your preferences, and the size of your queries. If I am not wrong, there is a character limit for logs, I remember vaguely something about that. – Rui F Ribeiro Feb 3 '17 at 12:51
  • 1
    (I did not even noticed we were jumping between the question and answer. Glad the solution worked. I am adding something to the answer for you) – Rui F Ribeiro Feb 3 '17 at 12:54
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.

9
  • 1
    I am trying lets see what happen. Is running for a long time. – Luciano Andress Martini Feb 3 '17 at 12:42
  • And no, it dit not work, returned nothing =[ – Luciano Andress Martini Feb 3 '17 at 12:43
  • Can you paste a few lines of the log somewhere? It might have to do with formatting...I am changing something. – Rui F Ribeiro Feb 3 '17 at 12:44
  • Try it now as it is. – Rui F Ribeiro Feb 3 '17 at 12:44
  • Ok it is processing. – Luciano Andress Martini Feb 3 '17 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.