PostgreSQL Logging – How to Log psql Time and Statements to a Text File

loggingpsql

I am currently comparing performance of PostgreSQL with several other SQL systems. I am aware of the \timing option to turn on timing queries. However, I would very much like to automate the process of copying the statements executed and the query speed below it. I imagine there is a simple way to log this?

Let's say I run:

CREATE TABLE t1 AS
SELECT itemID, prodCategory
FROM products
WHERE prodCategory = "footwear"

I want to automatically save into a text file:

CREATE TABLE t1 AS
SELECT itemID, prodCategory
FROM products
WHERE prodCategory = "footwear"

SELECT 7790
Time: 10.884 ms

If OS Specifications are needed, I am using MacOS.

Best Answer

No, it's not possible currently..

  1. Look at PrintTiming, it is using printf. While the stuff that prints to the log file, is using fprintf. My assumption is that the patch that provides this would be a couple of lines.
  2. You would have to have the ability to suppress the outputting of the result on --log-file, which we don't currently have. And in your case you want the summary logged SELECT 7790 but not the actual data.

.. maybe in the future.

Related Question