I'm facing a DWH on postgresql with no DBA (I'm not one) so I turn to you with hope to figure this issue/s. Long story short, when I check the disk usage per tablespace, I get different distribution than when I run du on the linux machine. I have 3 tablespaces I use: dwh, ssd, and archive. And 2 more pg_default and pg_global that as I understand come with the installation. This is the output from pg_tablespace:
spcname | spcowner | spclocation | spcacl | spcoptions
------------+----------+------------------------+--------+------------
pg_default | 10 | | |
pg_global | 10 | | |
ssd | 16385 | /ssd/rt/data | |
dwh | 16385 | /ssd/rt/dwh | |
archive | 16385 | /archive/postgres/data | |
When I run this query:
SELECT tablespace,
sum(pg_total_relation_size((schemaname || '.' || tablename)::regclass))/1024/1024/1024 as total_usage_gb
FROM pg_tables
group by 1
which suppose to output the usage per tablespace I get the following:
tablespace | total_usage_gb
------------+------------------------
| 252.6884689331054688
archive | 2268.1800308227539063
dwh | 9.9236679077148438
pg_global | 0.00161743164062500000
(4 rows)
When I run du -sh on the folders that the tablespaces use I get the following:
1.1T /ssd/rt/data
7.8G /ssd/rt/dwh
1.5T /archive/postgres/data
697M /var/lib/pgsql/9.1/data/base
1.7M /var/lib/pgsql/9.1/data/global
A bit more info before I ask my questions: When I run show default_tablespace I get the following:
default_tablespace -------------------- (1 row)
When I run show temp_tablespaces I get the following
temp_tablespaces ------------------ (1 row)
I'm using postgresql 9.1
My questions are:
When the tablespace is null, what happen? Because there are no row from the DB query that lists the "ssd" tablespace, there is one row with tablespace null that has 253GB. I don't think it went to pg_global or pg_default because those folders has less than 1GB occupied.
Where is the 1.1TB that the du -sh shows on folder /ssd/rt/data which suppose to be tablespace "ssd"
The du -sh list 1.5TB in archive folder but my query returns 2.2TB in the archive tablespace.
I'm sorry in advance if this is a trivial issue, I tried looking at it in the documentation but couldn't find much.
-- UPDATE --
I found the pg_database system_catalog. In there, there is a column that holds the default tablespace which in my case is the one named, "ssd".
I still couldn't solve the discrepancy between pg_total_relation_size and du command.
-- END OF UPDATE --
pg_tables
is only for the database you're connected to. Could you show all the databases withselect oid, datname, pg_database_size(datname) from pg_database
? – Daniel Vérité May 14 at 13:02