How to Show Last Queries Executed in PostgreSQL

PostgreSQL is a popular database used by many organizations around the world. It is used in many production systems. Often database administrators need to view recent queries run on their databases. In this article, we will learn how to show last queries executed in PostgreSQL.

How to Show Last Queries Executed in PostgreSQL

By default, the server log is disabled in many PostgreSQL systems. Log into PostgreSQL and run the following command to turn on server log.

log_statement = all

The above command will log every database call. But please avoid using it on production server since it will produce huge logs due to the volume of queries on production server. The log_statement variable can be assigned values such as all, none (off), ddl, mod. Accordingly, it will enable/disable logging or selectively log only specific queries.

Please note, if you change the log_statement parameter, you need to reload or restart the database server.

Your server log file will be located at Installation_folder/data/pg_log folder and can be viewed using any editor such as vim, nano, etc. Here is an example to view the latest entries in PostgreSQL log file using tail command.

$ tail -f /usr/local/var/log/postgres.log

By default, the format of log file is the same as the output you normally see on stderr (terminal) when you run SQL queries. If you want to customize it as say, CSV, then you need to add the following setting in postgresql.conf file which contains log settings.

log_destination = 'stderr,csvlog'   
logging_collector = on

Alternatively, you can also use pgAdmin to view PostgreSQL log files, in Tools->Server Status. But this feature may not be available in PgAdmin4+. But remember that this PgAdmin log file is different from the above mentioned log file of PostgreSQL database server.

In this article, we have learnt how to show last queries in PostgreSQL.

Also read:

How to Read from Stdin in Python
How to Download Large Files in Python
How to Remove All Occurrences of Value in Python List
How to Move File in Python
How to Reset Auto Increment in MySQL