psql tips: Change the location and filtering of the history file

Anyone who uses PostgreSQL knows of the best client available: psql. This is the client that ships with PostgreSQL. Yes it is a command line client (which turns some people off) but that also means that it is the most efficient at everyday tasks for a DBA. What a lot of people don't know is that psql is rather configurable. Here is an example:

Problem 1: I want my history file in a place other that ~/.psql_history

Problem 2: I want my history file to be per database not global

Solution 1: Edit the .psqlrc file and change the history file settings

\set HISTFILE ~/psql_history/.psql_history

This will put your .psql_history file into the directory psql_history under your home directory.

Solution 2: Edit the .psqlrc file again to:

\set HISTFILE ~/psql_history/.psql_history- :DBNAME

This will not only put all your history files within the psql_history directory, it will separate the history files into a file per database. For example:

postgres@jd-laptop:~$ ls -l psql-history
total 4
-rw------- 1 postgres postgres 33 Oct 24 09:33 psql_history-tut

Where tut is the name of a tutorial database I use for teaching.