1) ************************* Users ***************************
-- List of users with roles assigned:
SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN
CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN
CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN
CAST('create database' AS pg_catalog.text)
ELSE
CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
select * from pg_roles where rolname='database_user'; ---- rolcanlogin : True/False
SELECT usename, datname, state FROM pg_stat_activity;
SELECT usename,datname,state,client_addr,xact_start,query_start, backend_start, state_change,query FROM pg_stat_activity
where usename not like 'rds%';
-----------------------------------------------
2) *************************** Table *******************************
SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN
CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN
CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN
CAST('create database' AS pg_catalog.text)
ELSE
CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
select * from pg_roles where rolname='database_user'; ---- rolcanlogin : True/False
SELECT usename, datname, state FROM pg_stat_activity;
SELECT usename,datname,state,client_addr,xact_start,query_start, backend_start, state_change,query FROM pg_stat_activity
where usename not like 'rds%';
-----------------------------------------------
2) *************************** Table *******************************
---- Table size:
SELECT pg_size_pretty( pg_total_relation_size('tablename') );
SELECT pg_size_pretty( pg_total_relation_size('tablename') );
SELECT count(*) FROM pg_tables WHERE tableowner = 'stg_user' and schemaname like 'stg%';
SELECT schemaname,tablename,tableowner FROM pg_tables WHERE tableowner = 'stg_user' and schemaname like 'stg%' order by tablename;
SELECT schemaname,count(*) FROM pg_tables WHERE tableowner = 'stg_user' and schemaname like 'stg%' group by schemaname ;
3) ****************************** Performance queries ******************
long running query:
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query,
state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
another query with Idle session.
It is. And no, idle queries aren’t considered LRQ at all. When state is idle, the query shows information for the last executed query.
For the purpose of the article -identify LRQ- the query should be modified to the following:
SELECT
pid,
user,
pg_stat_activity.query_start,
now() - pg_stat_activity.query_start AS query_time,
query,
state,
wait_event_type,
wait_event
FROM pg_stat_activity
WHERE state != 'idle' and (now() - pg_stat_activity.query_start) > interval '5 minu
How to check which query is taking long time in PostgreSQL?
Using log_min_duration_statement parameter: To use the log_min_duration_statement parameter to trace slow queries in PostgreSQL.
If you configure log_min_duration_statement in postgresql. conf as 3500,
PostgreSQL will categorize queries lasting more than 3.5 seconds as slow queries and record them in the log file.
4 ********************** Kill/Delete/blocking/locks sessions *************************
In order to cancel these long running queries you should execute:
SELECT pg_cancel_backend(__pid__);
It may take a few seconds to stop the query entirely using the pg_cancel_backend command.
If the you find the process is stuck you can kill it by running:
SELECT pg_terminate_backend(__pid__);
pg_locks: Information on current locks held within the database by open transactions, with one row per lockable object.
pg_blocking_pids(): A function that can find the process IDs (PIDs) of sessions that are blocking the PostgreSQL server process of a supplied PID.
pg_cancel_backend(): Function that cancels the currently running query by sending a SIGINT to a process ID.
pg_terminate_backend(): Terminate a backend process completely (the query and usually the connection) on the database (uses SIGTERM instead of SIGINT).
-------
show the blocked processes on your database along with the actual query that is blocking them is:
SELECT
activity.pid,
activity.usename,
activity.query,
blocking.pid AS blocking_id,
blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));
granted: True if lock is held, false if the process is waiting.
locktype: The type of object that is locked – (see here) relation, page, object, tranasctionid, userlock etc.
------
Joining the pg_lock view to pg_stat_activity for the query and pg_class for the table names can be useful to gather more context as to what is locking on your database at any point in time
select
relname as relation_name,
query,
pg_locks.*
from pg_locks
join pg_class on pg_locks.relation = pg_class.oid
join pg_stat_activity on pg_locks.pid = pg_stat_activity.pid;
5************************ Cron Jobs *******************************
Pg cron: setup and
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html
show all; --- to see all parameter values.
select * from cron.job_run_details order by start_time desc;
select * from cron.job;
SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'cron.%' ORDER BY name;
*****************************************************************************************************************************************************************************
