Sling Academy
Home/PostgreSQL/See Postgres command history with psql

See Postgres command history with psql

Last updated: January 05, 2024

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.

Next Article: PostgreSQL: Common psql commands you should never forget

Previous Article: PostgreSQL: How to check live and dead rows in tables

Series: PostgreSQL Tutorials: From Basic to Advanced

PostgreSQL

You May Also Like

  • PostgreSQL with TimescaleDB: Querying Time-Series Data with SQL
  • PostgreSQL Full-Text Search with Boolean Operators
  • Filtering Stop Words in PostgreSQL Full-Text Search
  • PostgreSQL command-line cheat sheet
  • How to Perform Efficient Rolling Aggregations with TimescaleDB
  • PostgreSQL with TimescaleDB: Migrating from Traditional Relational Models
  • Best Practices for Maintaining PostgreSQL and TimescaleDB Databases
  • PostgreSQL with TimescaleDB: Building a High-Performance Analytics Engine
  • Integrating PostgreSQL and TimescaleDB with Machine Learning Models
  • PostgreSQL with TimescaleDB: Implementing Temporal Data Analysis
  • Combining PostgreSQL, TimescaleDB, and Airflow for Data Workflows
  • PostgreSQL with TimescaleDB: Visualizing Real-Time Data with Superset
  • Using PostgreSQL with TimescaleDB for Energy Consumption Analysis
  • PostgreSQL with TimescaleDB: How to Query Massive Datasets Efficiently
  • Best Practices for Writing Time-Series Queries in PostgreSQL with TimescaleDB
  • PostgreSQL with TimescaleDB: Implementing Batch Data Processing
  • Using PostgreSQL with TimescaleDB for Network Traffic Analysis
  • PostgreSQL with TimescaleDB: Troubleshooting Common Performance Issues
  • Building an IoT Data Pipeline with PostgreSQL and TimescaleDB