Postgresql – How to add timestamp to psql_history file

By default psql write the last 500 commands in the ~/.psql_history file, if like me you want to have a more precise information of who, when and where psql was used, here is a psql_history file setup.

First, I create a directory to receive the psql_history files

postgres@host:~$ mkdir ~/.psql_history.d
postgres@host:~$ chmod 750 ~/.psql_history.d

And then I create a ~/.psqlrc file like this

\set datetag `date +'%F_%H-%M-%S'`
\set userlogged `/usr/bin/logname`
\set HISTFILE ~/.psql_history.d/psql_history- :DBNAME-:userlogged-:datetag
\set HISTSIZE 5000
\timing

This will produce files like this helping you to investigate who is doing what on your databases

postgres@host:~$ ls -la .psql_history.d
total 16
drwxr-x---  2 postgres postgres 4096 oct.  11 20:11 .
drwxr-xr-x 12 postgres postgres 4096 oct.  11 20:10 ..
-rw-------  1 postgres postgres   11 oct.  11 20:11 psql_history-postgres-myuser-2018-10-11_20-11-01
-rw-------  1 postgres postgres    3 oct.  11 20:11 psql_history-postgres-myuser-2018-10-11_20-11-28
-rw-------  1 postgres postgres   18 oct.  11 20:13 psql_history-postgres-myuser-2018-10-11_20-13-32
-rw-------  1 postgres postgres    6 oct.  11 20:14 psql_history-bench-myuser-2018-10-11_20-13-58
-rw-------  1 postgres postgres   27 oct.  11 20:14 psql_history-poc-myuser-2018-10-11_20-14-08

You could set any variable you want or use one of the standard variables explained in the documentation like USER, HOST, PORT.

The best should be to have a timestamp on each command inside the history file, but this is not possible for the moment.