Hi DD
By default Postgresql does not collect this level of detail information to tell you which database has a high load at X point in time.
You can infer which database has this high load without increasing logging
Select * from pg_stat_database this dumps total inserts, update, scans etc...
To track which database has the highest load dump this to a table
select * into table compare_db_stat from pg_stat_database
after another high load event occurs compare this table to the current pg_stat_database results.
To identify which tables are being hit use pg_stat_all_tables
select * from pg_stat_all_tables order by n_tup_ins desc, n_tup_upd desc
then dump the results to a table with this command
Select * into table Stats_Compare from pg_stat_all_tables
after the high load has occurred compare the two tables to see specifically what values have changed
The draw back with this approach pg_stats_all_tables only shows the results for the current database, each database must be done independently
The other option is alter the log settings to record the SQL statements, wait for event to happen, then review the logs.
The big draw with this approach is the log files get BIG FAST..
Additional resources to review.
Dear Members!
We have more than 200 databases in a server.
The PGAdmin's dashboard shows me 4500 inserts periodically.
I want to know which database(s) causes this.
Do you know any query which can show me the inserts per databases?
And I don't know it works as TPS query? So I need to make differents between measured values in two time point?
select * from (
SELECT current_timestamp, datname, sum(xact_commit+xact_rollback) db FROM pg_stat_database
group by current_timestamp, datname
) t order by db desc
Thank you for any advance!
Best regards
DD