setting timing on not getting time in log

Question

I have made the sql query like:

\timing on
\echo
truncate table raw.decrement_m7_temp;

copy raw.decrement_m7_temp FROM PROGRAM 'zcat /path/filename' with(format csv,delimiter ',',header);

delete from raw.decrement_m7_temp where decrement_dt ilike '%dt%';
delete from raw.decrement_m7_temp where msisdn !~ E'^[0-9]+$' OR char_length(msisdn)>12;

I want to track the how much these query is taking time in log. I am running the following code:

psql -U postgres -d databasename -a -f /path/fimename -L /path/abc.log

While running this query its display the time on black Unix screen but not track the time in log file. How it is possible to track the time in log file?

Answer 1

\timing is a psql command - that is, it acts on the client side. If you want to know the timing of your queries on the server side (that is, written to the logs), do

SET log_min_duration_statement TO 0;  

This will then result in a log entry for each and every statement. You can disable this by setting it to -1. To find the timings, you can grep the server log:

grep duration server.log

The issue with psql's -L option is that

-L filename

--log-file=filename

Write all query output into file filename, in addition to the normal output destination.

That is, anything that is not a query output (and not a header, I'd add) won't be redirected into the log file. You can either use a shell redirect to write everything into the log:

psql [...] > abc.log

Or, if you want to see the output on screen, too, use tee:

psql [...] | tee abc.log