问题描述:PostgresDB 写入8000W条记录后,磁盘用到300G
发现问题过程:
=>\l
查看有哪些数据库(database)
=>\select pg_database_size ('adb');
查看数据库大小
=> select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database; //查看所有数据库的大小
=> select pg_size_pretty(pg_database_size('playboy')); //以KB,MB,GB的方式来查看数据库大小
参考:https://www.cnblogs.com/goozgk/p/8108442.html
查看 TableSpace大小
=> select spcname from pg_tablespace; //查看所有表空间
spcname ------------ pg_default(数据库数据) pg_global(系统数据库信息) (2 rows)
=> select pg_size_pretty(pg_tablespace_size('pg_default')); //查看表空间大小
统计数据库中各表占用磁盘大小
SELECT table_schema || '.' || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size FROM information_schema.tables ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC limit 20;
.统计各数据库占用磁盘大小
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner, CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE 'No Access' END AS SIZE FROM pg_catalog.pg_database d ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END DESC -- nulls first LIMIT 20
=====================
查找数据库文件大小(TableSpce default文件大小)
1)psql查找数据库oid
select oid from pg_database where datname='postgres';
2)posrgres用户下
cat .bash_profile
找到PGDATA目录地址
$PGDATA/base 为 default table space 目录地址
找到oid 所在目录
然后 du -sh * 查看文件大小(目录地址$PGDATA/base/$oid)。
查看数据库大小
select pg_size_pretty(pg_database_size('dbname'));
需要进一步确认数据库文件大小小于实际文件使用量。