Where is psql client history kept? (~/.psql_history non-existent!)
PostgresqlPsqlPostgresql Problem Overview
A password is visible in my psql prompt (by pushing up arrow to view previous inputted commands). So I would like to delete that entry from the psql client.
Other resources(1, 2) claim that psql client history is kept in is a ~/.psql_history file, however this file is simply not there. (it's not in .bash_history either)
So there must be another file where the history of psql prompt is kept, any ideas where?
I log in to my prompt like this
sudo -u postgres psql my_db
Note: I am working on Ubuntu 12.04
Postgresql Solutions
Solution 1 - Postgresql
When you use:
sudo -u postgres psql my_db
you run psql as the (Linux) user postgres, therefor the .psql_history file is in the home directory of the postgres user (e.g. /home/postgres/.psql_history), not in the home directory of the user you originally logged in with.
In some installations, the location of the postgres user's home directory is /var/lib/postgresql. You can check this by running:
grep postgres /etc/passwd | cut -d ':' -f 6
Solution 2 - Postgresql
The filename is given by an internal variable named HISTFILE.
Technically it doesn't have to be ~/.psql_history.
Check the ~/.psqlrc file for an alternate setting. For example it might be:
> \set HISTFILE ~/.psql_history- :DBNAME
as mentioned in the manual for a per-database history file.
Solution 3 - Postgresql
I have a server here (Amazon Linux AMI, Centos like) that has no postgres home folder. Still it remembers the history of psql. Using the locate command I found the history file located in /var/lib/pgsql93/.psql_history. On another machine it's in /var/lib/pgsql/. After editing this file, removing all relevant lines, the history was gone.
When the locate command doesn't return results, use the command updatedb to create the database for the locate command. You cannot run these commands as user postgres btw.
Solution 4 - Postgresql
You need to modify /etc/passwd. Put the location of your pgsql folder on postgres line and try : grep postgres /etc/passwd | cut -d ':' -f 6
Solution 5 - Postgresql
Some other answers have hinted at this and mentioned how to look up where the .psql_history file is kept in the case that your postgres user doesn't have a home directory (ie. you don't have /home/postgres/), but they haven't mentioned what the default location is.
To save time looking at the directory references, your .psql_history file (and .bash_history file for the postgres user) can be found at var/lib/postgresql/
Also note that if you are using ls to check this directory to include the -a flag as they are still hidden files.