Postgres Db Size Command

Postgresql find total disk space used by a database

SELECT pg_database_size('geekdb')

or

SELECT pg_size_pretty(pg_database_size('geekdb'))

http://www.thegeekstuff.com/2009/05/15-advanced-postgresql-commands-with-examples/

postgresql list and order tables by size

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2

This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

List of all object size functions in the manual.

Postgres Database size and Table size sum do not match up

The issue appears to be connected to the dirty shutdown.

The solution is posted on dba stackexchange: How to reclaim space taken by an index that partially built and was terminated by a power outage

Should you suffer the same issue, check it for solution.

How can i get the size of every db on my PostgreSQL server

Make use of system information functions:

https://www.postgresql.org/docs/current/functions-admin.html

For database sizes:

select datname, pg_size_pretty(pg_database_size(datname)) from pg_database ;
        datname         | pg_size_pretty 
------------------------+----------------
 my_test_db             | 7865 kB
 production             | 64 MB
 no_public_sch_template | 7857 kB
 test                   | 11 MB
 test_gz                | 9681 kB
 sch_test               | 7857 kB
 template0              | 7713 kB
 task_manager           | 9681 kB
 template1              | 7865 kB
 aquaculture            | 15 MB
 track_stocks           | 9425 kB
 track_stocks_test      | 9529 kB
 postgres               | 8273 kB

From pg_database you can get the database name(datname) for building a script to iterate over for the tables.

---

Related Topics