In this article, we will see how to get list of sessions on PostgreSQL database. We will see all sessions in PostgreSQL and sessions specific to the database. We can get session by running queries against pg_stat_activity.
Syntax to get list of sessions on PostgreSQL database: Continue reading How to get list of sessions on PostgreSQL database
In this article, we will see difference between pg_cancel_backend and pg_terminate_backend in PostgreSQL.
pg_cancel_backend(pid) is a milder version og pg_terminate_backend(pid).
The difference between pg_cancel_backend and pg_terminate_backend in PostgreSQL is that Continue reading Difference between pg_cancel_backend and pg_terminate_backend in PostrgreSQL
In this article, we are going to see What is the difference between Vacuum and Vacuum Full.
Vacuum
Plain VACUUM simply reclaims space and makes it available for re-use.
This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained.
However, extra space is not returned to the operating system (in most cases); it’s just kept available for re-use within the same table.
VACUUM FULL
VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system.
This form is much slower and requires an exclusive lock on each table while it is being processed.
In this article, we will see how to find which queries are running PostgreSQL. We can find the present running queries by querying against the system view pg_stat_activity.
To find, we must be logged in as a superuser or as the same database user we want to check.
We have to set parameter track_activities=on in the postgresql.conf file or using the following SQL statement.
set track_activities=on
Continue reading How to find which queries are running PostgreSQL
As administrator, From time to time we need to investigate if there is any query running indefinitely on PostgreSQL. These long running queries may impact database performance and probably they are stuck on some background process.
1. Following queries will return currently running top 10 queries and longest running queries in the front.
Continue reading How to find long running queries in PostgreSQL
In this article, we will see how to check whether a user is connected or not to PostgreSQL server. We can get this information from the system view pg_stat_activity.
select datname from pg_stat_activity where usename='james';
Continue reading How to check whether a user is connected to PostgreSQL or not
In this tutorial, we find certain user connected to PostgreSQL server or not by querying pg_stat_activity system view.
If below query returns any results means, then user is connected to PostgreSQL Server database.
Check whether PostgreSQL User is connected or not:
select usename,datname from pg_stat_activity where usename='anil';
Continue reading Check whether PostgreSQL User is connected or not