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 $$
declare
pg_cursor_dbsize cursor for select datname from pg_database;
dbname pg_database.datname%type;
size varchar(50);
begin
open pg_cursor_dbsize;
loop
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 ;
end;
$$ 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.

3 comments:

Srikrishnan's Blog for open source said...

thala,
good utility

dataanalytics said...

thanks for ur comment krish..

Unknown 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;