PostgreSQL – View Operation Timestamps for Long Running Operations in PSQL

postgresqlpsql

When I do long running queries, I tend to use tmux and a psql console. It will not show me any output of when some operation was started or completed.
How can I get the psql console to show some timestamps?

Clarification:
I'm hoping for a non-scripting solution, just in the psql console. (Or does nobody use that console?) I'm maybe naive but still trying to do most of the database stuff using the database itself and its tools (using procedures, etc.)

Best Answer

There are various options for retrieving timing information while using psql.

The \timing command.
In scripts you can use

\echo `date`

to insert the date in the output.

You can have the date in prompt strings using eg

\set PROMPT1 = '%`date` %n@%m:%/%R%# '

For both 2 and 3 you can supply arguments to date to get the date in the format you want.

If using psql non-interactively, you can pipe the output through ts (from moreutils) or through awk '{ print strftime("%Y-%m-%d %H:%M:%S"), $0; fflush(); }'(GNU's version of awk is needed) to prepend a timestamp to each line of output.

Related Solutions

PostgreSQL – Problem with LISTEN-NOTIFY Mechanism

This is a psql issue. psql is waiting for you to enter something on its command line. It only checks for notifications from the server when it has a reason to contact the server, and you haven't given it a reason.

They could have implemented it differently, so that it uses a select loop to wait on either the keyboard or the server, whichever becomes interesting first. But that would make things much more complicated and it is not the way it was done.

This is the same reason that, if you bring down the server, psql doesn't notice until you try to do something that would try to talk to the server.

Note that the same issue will exist with clients/libraries other than psql, and different libraries solve (or fail to solve) it in different ways.

PostgreSQL psql – Using Relative Path for \copy File

I think there's no way to indicate to \copy that the file is relative to the location of the script, but you may use \cd to change the current directory before \copy.

\cd interpolates variables so the directory can be passed on the command line with -v:

psql -vscriptdir="c:\path\to\script" -f c:\path\to\script\script1.sql

Before including anything, say there's this command in the top-level script:

\cd :scriptdir

(except if the -f argument is simply a file without a path: in this case it's not necessary since the current directory is already set where the script is)

Then every inclusion of a script in a subdirectory should be:

\cd dir2
\i script2.sql
\cd ..

(as opposed to \ir dir2/script2.sql)

When script2.sql will invoke \copy with a file relative to it, the data file will be found since it's in the current directory.

That should work recursively, assuming scripts in subdirectories also follow that convention, and that they don't do an unrelated \cd that would interfere with the \cd .. waiting to happen on the way back to the top.

Best Answer

Related Solutions

PostgreSQL – Problem with LISTEN-NOTIFY Mechanism

PostgreSQL psql – Using Relative Path for \copy File

Related Question