Measuring timing over many commands in psql

Maybe it would be easiest to run a psql in the background, with it set to execute stdin, and connect its stdin to a named pipe. Then you can continually push data into that pipe, and finally push "end; \quit". Something like:

#!/bin/sh

psql_pipe=/tmp/psql$$
mkfifo -m 600 $psql_pipe
psql < $psql_pipe &
exec 3>$psql_pipe
psql_pid=$!

echo "> Started psql (pid=$psql_pid) reading from $psql_pipe"
trap '
  kill $psql_pid
  rm -f $psql_pipe
' EXIT

echo "begin;" >&3

echo "select now();" >&3

sleep 2

echo "select now();" >&3

sleep 2

echo "end; \quit" >&3

wait $psql_pid

Note that you can't simply do echo "sql" >$psql_pipe since the EOF would be transmitted to psql, which would then exit early-- the shell script has to keep its fd open.

You could use the shell command time

postgres@db:$ time psql db -c 'SELECT 1'
 ?column?
----------
        1
(1 row)


real    0m0.108s
user    0m0.040s
sys     0m0.032s

Or, to combine a meta command with an SQL command, you could pipe a string to psql:

postgres@db:~/script$ echo '\timing \\ SELECT 1;' | LANG=C psql
Timing is on.
 ?column?
----------
        1
(1 row)

Time: 0.000 ms

Per documentation:

The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.