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
-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.