I have local installation of 9.1 database with few tables which had cca. 300 mio records and the database grew to about 20 GB. Afterwards I issued
delete from command to delete all records from it (I should have used
truncate, but I didn't know that). So I did full vacuum on my db to reclaim disk space, but it just doesn't help. My problem looks identical to this one, but there is no solution provided. I have already checked this thread and documentation on "recovering disk space", but still can't find a solution. I use this code to get size of all tables
SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND C.relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 15;
Totalling less than 1GB. However
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size FROM pg_database
still shows about 20 GB.
Any advice much appreciated.