tl;dr; SELECT pg_database.datname, pg_database_size(pg_database.datname), pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database ORDER by 2 DESC;
I recently had to transfer all my postgres data for my local databases on the laptop. Although I, unfortunately, can't remember what it was before I deleted a bunch of databases, now after some clean up, all my PostgreSQL databases weighs 12GB.
To find out the size of all your database, start psql like this:
$ psql postgres
Then run this:
SELECT pg_database.datname,
pg_database_size(pg_database.datname),
pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER by 2 DESC;
Here's what it looked like on my laptop:
postgres=# SELECT pg_database.datname,
postgres-# pg_database_size(pg_database.datname),
postgres-# pg_size_pretty(pg_database_size(pg_database.datname))
postgres-# FROM pg_database ORDER by 2 DESC;
datname | pg_database_size | pg_size_pretty
--------------------------+------------------+----------------
songsearch | 10689224876 | 10194 MB
air_mozilla_org | 355639812 | 339 MB
kl | 239297028 | 228 MB
kl2 | 239256068 | 228 MB
peterbecom | 191914500 | 183 MB
kintobench | 125968556 | 120 MB
airmozilla | 41640452 | 40 MB
socorro_webapp | 32530948 | 31 MB
tecken | 26706092 | 25 MB
dailycookie | 12935684 | 12 MB
autocompeter | 12313092 | 12 MB
socorro_integration_test | 11428356 | 11 MB
breakpad | 11313668 | 11 MB
test_peterbecom | 9298436 | 9081 kB
battleshits | 9028100 | 8817 kB
thuawood2 | 8716804 | 8513 kB
thuawood | 8667652 | 8465 kB
fastestdb | 8012292 | 7825 kB
premailer | 7676420 | 7497 kB
crontabber | 7586308 | 7409 kB
postgres | 7536812 | 7360 kB
crontabber_exampleapp | 7488004 | 7313 kB
whatsdeployed | 7414276 | 7241 kB
socorro_test | 7315972 | 7145 kB
template1 | 7307780 | 7137 kB
template0 | 7143940 | 6977 kB
(26 rows)
Comments