Shareware software
DATABASE node for PostgreSQL
Operating procedure
Node displays information about created databases in PostgreSQL cluster
The current database is marked with a separate icon
SELECT d.oid,
d.datname AS "Name",
r.rolname AS "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) AS "Encoding",
t.spcname AS "Tablespace",
pg_catalog.shobj_description(d.oid, 'pg_database') AS "Description",
v.datname as "vacuum",
case when current_database() = d.datname
then 1
else 0
end "current"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_roles r
ON d.datdba = r.oid
JOIN pg_catalog.pg_tablespace t
ON d.dattablespace = t.oid
LEFT JOIN pg_catalog.pg_stat_progress_vacuum v
ON v.datname=d.datname
order by 1
For each database on the cluster, the following information is displayed:
Database owner
Base encoding
Default tablespace
Comment to base
STATISTICS - database usage statistics
select d.datname,
d.numbackends,
d.xact_commit,
d.xact_rollback,
d.blks_read,
d.blks_hit,
d.tup_returned,
d.tup_fetched,
d.tup_inserted,
d.tup_updated,
d.tup_deleted,
d.conflicts,
d.temp_files,
d.temp_bytes,
d.deadlocks,
d.blk_read_time,
d.blk_write_time,
d.stats_reset
from pg_catalog.pg_stat_database d
where d.datname = $$DBNAME
VACUUM PROGRESS - state of processes running vacuum
select (select relname
from pg_catalog.pg_class
where oid = v.relid) as "Table",
v.phase,
v.heap_blks_total,
v.heap_blks_scanned,
v.heap_blks_vacuumed,
v.index_vacuum_count,
v.max_dead_tuples,
v.num_dead_tuples
from pg_catalog.pg_stat_progress_vacuum v
where v.datname = $$DBNAME