Monday, August 9, 2010

Display size of all database in postgresql

pg_database_size(dbname) function used to return size of given database in byte format.we can use pg_size_pretty() function to convert byte into human readable format. i.e.

SELECT pg_size_pretty(pg_database_size('dbname'))

now if you would like display the size of all available database in postgres server

create or replace function pg_function_alldb_size() returns void as $$
pg_cursor_dbsize cursor for select datname from pg_database;
dbname pg_database.datname%type;
size varchar(50);
open pg_cursor_dbsize;
fetch next from pg_cursor_dbsize into dbname;
exit when not found;
select * into size from pg_size_pretty(pg_database_size(dbname));
raise notice 'db name is % and size is % ',dbname,size;
end loop;
return ;
$$ language plpgsql

  • pg_cursor_dbsize is cursor which stores the all database name from pg_database table
  • pg_size_pretty(pg_database_size(dbname)) it returns size of the database to the size variable.
  • raise notice - used to display the output.


blog for open source - Krish said...

good utility

solaimurugan.v said...

thanks for ur comment krish..

suya huang said...

very good function! I happened to see another way to query the size:
SELECT pg_database.datname,
pg_size_pretty(pg_database_size(pg_database.datname)) AS size
FROM pg_database;