How to time each command within a bash script (Running a Series of psql statements)

user_3507127 0
Viewed 109

I currently have a bash script that runs a bunch of psql scripts. These psql scripts look like:

psql -a -f "filename.sql"
psql -a -f "filename2.sql"
etc.

So each psql is executing PostgreSQL stored in different files. I am running a series of them in a bash script, and it would be helpful for me to know how long each file is taking.

A hacky way I thought of doing this is to just print date before and after each one (i.e.)

date
psql -a -f "filename.sql"
date

psql -a -f "filename2.sql"
date

Is there a smarter way to do this?

psql postgresql sql bash
2 Answers

Use the time command. See the man page

time psql -a -f "filename1.sql"
... # et cetera

It runs your command and has time output, such as

real    0m33.961s
user    0m0.340s
sys     0m0.940s
user_64381680

For more resolution, change your sql scripts like this, which will time the individual sql statements inside the scripts:

\timing on
-- some sql statements that you want to benchmark
\timing off

If you simply add \timing on at the top of the sql script, all statements in the script will be benchmarked.

From psql manual:

\timing [ on | off ]
With a parameter, turns displaying of how long each SQL statement takes on or off. Without a parameter, toggles the display between on and off. The display is in milliseconds; intervals longer than 1 second are also shown in minutes:seconds format, with hours and days fields added if needed.

user_9676210
Related