In database management systems like Postgres, different commands and queries are used to perform various tasks. The data kept in the queries’ history is very beneficial for any database administrator. For instance, in different scenarios, there is a need to track back the Postgres server’s query history, such as while investigating a specific task, recovering a certain query if the server crashes, etc. In all these cases, query history serves as the user assistant.

This write-up demonstrates a practical guide on how to check the query history in PostgreSQL.

How to Check Query History in Postgres?

The need for the query history arises when a server crashes, when a failure occurs, for routine task analysis, etc. Postgres allows us to check the backup history:

- Method 1: Using SQL Shell
- Method 2: Using pgAdmin

Method 1: How to Check Query History in Postgres Using SQL Shell?

In SQL Shell, the “\s” retrieves the command's history. The stated command can also be used to save the command's history to a specific file.

Example 1: Getting Query History Using “\s”

Open the terminal, log into “psql”, and execute the following command to see the query history:

\s

The output shows that the “\s” command successfully retrieves the query history.

Example 2: Getting the Last Executed Query

If you want to check only the last executed command, then you must use the “pg_stat_activity” view. For this purpose, firstly, you need to get the “PID” for a specific session:

SELECT pg_backend_pid();

Now use the “pg_stat_activity” view to get the last executed query:

SELECT pid, query, backend_start
FROM pg_stat_activity
WHERE pid = '13999';

The output demonstrates that the “pg_stat_activity” view retrieves the last executed command along with the query’s start time.

Method 2: How to Check Query History in Postgres Using pgAdmin?

Users can check the query history using pgAdmin. It shows the commands’ history with date and time.

Example: Check Query’s History Using pgAdmin

Log into “pgAdmin”, and open the “Query tool”:

Clicking on the “Query Tool” will lead you to the following window:

Select the “Query History” tab to see the commands history:

The output depicts that the pgAmin’s “Query History” tab kept the query history with date and time.

That was all regarding the Postgres query history.

Conclusion

In PostgreSQL, the query history can be checked using SQL Shell or pgAdmin. Execute the “\s” command from SQL Shell (psql) to get query history. To check the query history using pgAdmin, open the “query tool” and navigate to the “Query History” tab. Use the “pg_stat_activity” view to see only the last executed command. This post explained how to see the query history in PostgreSQL using the SQL Shell and pgAdmin.