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 how to save query result to a file in PostgreSQL. We can save query result to txt, csv,.. files.
We have three methods to save/export query results to a file in PostgreSQL:
1) copy command
2) from Linux shell using psql tool.
3) \o method.
Continue reading How to save query result to a file in PostgreSQL
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 repeatedly execute query in PostgreSQL Server. From PostgreSQL 9.3 onwards introduced \watch meta-command, which allows us to automatically re-execute query like watch utility of some Linux environments. Time specified is in seconds only.
We exit query execution by pressing Ctrl+C.
Continue reading How to repeatedly execute query 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