When working with ysqlsh, the command history feature is extremely useful. It allows you to quickly recall previous queries using the up-arrow key, making iterative testing and debugging much faster.
Normally, ysqlsh stores command history in a local history file and reloads it automatically when a new session starts. This means commands entered in one session should still be available when you reconnect.
For example:
$ bin/ysqlsh
yugabyte=# SELECT 'remember this command';
?column?
-----------------------
remember this command
(1 row)
yugabyte=# \q
Start a new session:
$ bin/ysqlsh
yugabyte=#
Press the up arrow, and the previous command should appear:
SELECT 'remember this command';
However, some users have recently noticed that ysqlsh history no longer persists between sessions. The up-arrow still works within the same session, but previously executed commands disappear once ysqlsh exits.
This behavior was traced to a libedit issue, and a fix has already landed in the YugabyteDB master branch. The fix will appear in an upcoming YugabyteDB release.
In the meantime, there is a simple workaround that restores the history file header and ensures ysqlsh retains unlimited command history.
ð TL;DR
Run this command once from your shell:
hist="${PSQL_HISTORY:-$HOME/.psql_history}"; rc="${PSQLRC:-$HOME/.psqlrc}"; [ -e "$hist" ] || : >"$hist"; head -n1 "$hist" | grep -qxF '_HiStOrY_V2_' || { t=$(mktemp) && { printf '_HiStOrY_V2_\n'; cat "$hist"; } >"$t" && mv "$t" "$hist"; }; t=$(mktemp) && { grep -vE '^[[:space:]]*\\set[[:space:]]+HISTSIZE([[:space:]]+|$)' "$rc" 2>/dev/null; printf '\\set HISTSIZE -1\n'; } >"$t" && mv "$t" "$rc"This command:
- â Restores the missing history file header
- â Ensures the history file exists
- â Sets unlimited history retention for
ysqlsh
You do not need to add this to .bash_profile… it is a one-time repair step.
Where ysqlsh Stores History
According to the YugabyteDB documentation, ysqlsh stores command history in the file:
~/.psql_history
Each time ysqlsh starts, it loads commands from this file so they can be recalled using the up arrow.
ysqlsh also reads optional startup configuration from:
~/.psqlrc
This file allows you to configure things like prompt behavior, formatting options, and history retention settings.
If the history file becomes corrupted or loses its expected header, history persistence can stop working across sessions.
The One-Line Workaround
Run this command once in your shell:
hist="${PSQL_HISTORY:-$HOME/.psql_history}"; rc="${PSQLRC:-$HOME/.psqlrc}"; [ -e "$hist" ] || : >"$hist"; head -n1 "$hist" | grep -qxF '_HiStOrY_V2_' || { t=$(mktemp) && { printf '_HiStOrY_V2_\n'; cat "$hist"; } >"$t" && mv "$t" "$hist"; }; t=$(mktemp) && { grep -vE '^[[:space:]]*\\set[[:space:]]+HISTSIZE([[:space:]]+|$)' "$rc" 2>/dev/null; printf '\\set HISTSIZE -1\n'; } >"$t" && mv "$t" "$rc"
This command safely repairs the history file and updates .psqlrc so that ysqlsh retains an unlimited number of commands.
Breaking Down the Command
Although the command looks intimidating at first, it performs a series of straightforward steps.
Step 1: Determine the correct history file
hist="${PSQL_HISTORY:-$HOME/.psql_history}"
ysqlsh supports overriding the history file using the PSQL_HISTORY environment variable.
If that variable is not set, the default location is:
~/.psql_history
Step 2: Determine the startup configuration file
rc="${PSQLRC:-$HOME/.psqlrc}"
Similarly, the startup configuration file can be overridden using PSQLRC.
Otherwise the default is:
~/.psqlrc
Step 3: Create the history file if it doesn’t exist
[ -e "$hist" ] || : >"$hist"
If the history file is missing, this creates an empty file so history can be written to it.
Step 4: Restore the required history header
head -n1 "$hist" | grep -qxF '_HiStOrY_V2_'
ysqlsh expects the history file to begin with a special marker:
_HiStOrY_V2_
If that marker is missing, the command:
1. creates a temporary file
2. inserts the header
3. copies the existing history
4. replaces the original file
This restores the expected format.
Step 5: Remove old HISTSIZE settings
The command then filters out any existing HISTSIZE configuration from .psqlrc to avoid duplicates.
Step 6: Set unlimited history retention
Finally, it adds the line:
\set HISTSIZE -1
to .psqlrc.
This tells ysqlsh to retain unlimited command history.
Why You May See âmv: overwrite ⊠?â
Some users may see this prompt:
mv: overwrite '/root/.psqlrc'?
This happens because many systems define:
alias mv='mv -i'
The -i flag causes mv to prompt before overwriting files.
This behavior is normal and simply confirms the updated .psqlrc should replace the existing one.
Verifying the Fix
After running the workaround, test history persistence:
$ bin/ysqlsh
yugabyte=# SELECT 'history test';
yugabyte=# \q
Reconnect:
$ bin/ysqlsh
Press the up arrow and the previous command should reappear.
Special thanks to Kai Franz, Software Engineer at Yugabyte, for:
- â Identifying and fixing the underlying libedit history issue
- â Providing the workaround command that restores ysqlsh history persistence
The fix has already landed in the YugabyteDB master branch and will appear in an upcoming release.
Final Thoughts
If your ysqlsh command history disappears between sessions, the issue is usually caused by a missing history header or misconfigured history retention.
Until the upstream libedit fix appears in an upcoming YugabyteDB release, the one-line workaround above provides a quick and safe way to restore normal history behavior.
Have Fun!