See Postgres command history with psql

Introduction

Working with PostgreSQL databases often involves repeating certain queries or examining previous operations. The ‘psql’ tool provides a convenient way to track and review your SQL command history, which can significantly streamline your workflow.

Accessing Command History

The simplest way to access your command history in ‘psql’ is by using the Readline history feature which ‘psql’ is built upon. You can navigate through your past commands by pressing the Up and Down keys on your keyboard. This lets you scroll through commands executed during the current session.

\s

This command will display your current session’s history. However, if you want to see all the commands that have been executed in the past, you can use the following command:

\s ~/.psql_history

This will output the content of your ‘.psql_history’ file, which contains the command history across sessions.

Filtering Command History

If you’re looking for a specific command or set of commands, you can combine ‘grep’ with your history command to search through it:

\s | grep 'SELECT'

This will output every ‘SELECT’ query in your history.

Time-stamping Commands

To better keep track of when certain commands were executed, you can alter the ‘.psqlrc’ configuration file to add time-stamps to your history:

\set HISTFILE ~/.psql_history- :DBNAME:\du\set HISTCONTROL ignoredups\set HISTTIMEFORMAT "%F %T %z "

This set of commands updates the history file to include database-specific history, ignores duplicate entries, and adds a timestamp to each command.

Advanced History Commands

PostgreSQL’s ‘psql’ offers even more advanced options for command history management:

> SELECT
< \g

If you hit Enter after ‘<‘ or ‘>’, ‘psql’ will display the previous or next command containing ‘SELECT’.

\s filename

Save your history to a specific file for later review or sharing.

\i filename

You can run all commands saved in a previous session by importing the file.

Programmatic Access to History

While working on scripts, you may want to access command history in a programmatic way. Using ‘psql’ in non-interactive mode along with shell commands can achieve this:

psql -h localhost -U user -d dbname -c "\s" > history_output.txt

This example saves the history to a file named ‘history_output.txt’ through shell redirection.

Customizing ‘psql’ History

You can customize how ‘psql’ stores and retrieves history through configurations in the ‘.psqlrc’ file. This can help manage long histories, prevent certain commands from being stored, or create session-specific histories.

The following setting limits the number of commands stored:

\set HISTSIZE 500

An example to prevent storing any ‘DROP DATABASE’ commands might look like this:

\unset HISTFILE

Secure Your History

Because command histories can contain sensitive information, it’s important to ensure that ‘.psql_history’ is properly secured:

chmod 600 ~/.psql_history

This changes the file permissions so that only your user can read and write to the history file.

Conclusion

Mastering your command history in ‘psql’ can make database interactions faster, easier, and more efficient. Whether you’re a novice or an experienced DBA, leveraging history features will enhance your PostgreSQL experience. Always remember to manage your history with care to keep sensitive information secure.