{"id":63,"date":"2018-10-11T20:19:14","date_gmt":"2018-10-11T18:19:14","guid":{"rendered":"http:\/\/deleforterie.com\/wordpress\/?p=63"},"modified":"2023-01-18T08:53:12","modified_gmt":"2023-01-18T07:53:12","slug":"how-to-add-timestamp-to-postgresql-psql_history-file","status":"publish","type":"post","link":"https:\/\/deleforterie.com\/wordpress\/index.php\/2018\/10\/11\/how-to-add-timestamp-to-postgresql-psql_history-file\/","title":{"rendered":"Postgresql &#8211; How to add timestamp to psql_history file"},"content":{"rendered":"<p>By default <strong>psql<\/strong> write the last 500 commands in the <strong>~\/.psql_history<\/strong> file, if like me you want to have a more precise information of who, when and where psql was used, here is a <strong>psql_history<\/strong> file setup.<\/p>\n<p><!--more--><\/p>\n<p>First, I create a directory to receive the psql_history files<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"shell\">postgres@host:~$ mkdir ~\/.psql_history.d\npostgres@host:~$ chmod 750 ~\/.psql_history.d\n<\/pre>\n<p>And then I create a <strong>~\/.psqlrc<\/strong> file like this<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"shell\">\\set datetag `date +'%F_%H-%M-%S'`\n\\set userlogged `\/usr\/bin\/logname`\n\\set HISTFILE ~\/.psql_history.d\/psql_history- :DBNAME-:userlogged-:datetag\n\\set HISTSIZE 5000\n\\timing\n<\/pre>\n<p>This will produce files like this helping you to investigate who is doing what on your databases<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"shell\">postgres@host:~$ ls -la .psql_history.d\ntotal 16\ndrwxr-x---  2 postgres postgres 4096 oct.  11 20:11 .\ndrwxr-xr-x 12 postgres postgres 4096 oct.  11 20:10 ..\n-rw-------  1 postgres postgres   11 oct.  11 20:11 psql_history-postgres-myuser-2018-10-11_20-11-01\n-rw-------  1 postgres postgres    3 oct.  11 20:11 psql_history-postgres-myuser-2018-10-11_20-11-28\n-rw-------  1 postgres postgres   18 oct.  11 20:13 psql_history-postgres-myuser-2018-10-11_20-13-32\n-rw-------  1 postgres postgres    6 oct.  11 20:14 psql_history-bench-myuser-2018-10-11_20-13-58\n-rw-------  1 postgres postgres   27 oct.  11 20:14 psql_history-poc-myuser-2018-10-11_20-14-08\n\n<\/pre>\n<p>You could set any variable you want or use one of the standard variables explained in the <a href=\"https:\/\/www.postgresql.org\/docs\/10\/static\/app-psql.html#APP-PSQL-VARIABLES\">documentation<\/a>\u00a0like USER, HOST, PORT.<\/p>\n<p>The best should be to have a timestamp on each command inside the history file, but this is not possible for the moment.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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.<\/p>\n","protected":false},"author":2,"featured_media":156,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[9,8],"tags":[],"class_list":["post-63","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","category-sgbd"],"_links":{"self":[{"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/63","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/comments?post=63"}],"version-history":[{"count":7,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/63\/revisions"}],"predecessor-version":[{"id":158,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/posts\/63\/revisions\/158"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/media\/156"}],"wp:attachment":[{"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/media?parent=63"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/categories?post=63"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/deleforterie.com\/wordpress\/index.php\/wp-json\/wp\/v2\/tags?post=63"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}