Топ-100
top of page
CerebroSQL

DATABASE node for PostgreSQL

Operating procedure

Node displays information about created databases in PostgreSQL cluster

The current database is marked with a separate icon

Node DATABASE for PostgreSQL
-

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

bottom of page