前言
近期在项目在做环境搭建,需要将原有的数据也需要镜像一份数据,为了方便检查一下数据同步的是否正确,需要验证每张表的条数和真实的使用磁盘大小
验证条数
-
查询出pg_class表中的reltuples就是表的记录数:
SELECT relname AS TABLE_NAME, reltuples AS rowCounts FROM pg_class WHERE relkind = 'r' ORDER BY rowCounts DESC
这样查出来的有一个问题,就是会把系统表的数据也查出来,这显然不是我想要的。怎么去掉系统表。
可以查询Schema下的每张表的记录数
SELECT relname AS TABLE_NAME, reltuples AS rowCounts FROM pg_class WHERE relkind = 'r' AND relnamespace = ( SELECT oid FROM pg_namespace WHERE nspname = 'public' ) ORDER BY rowCounts DESC;
磁盘大小
-
整体使用磁盘大小
select pg_size_pretty(pg_database_size('查询的数据库名'));
-
每张表的使用大小
SELECT table_schema || '.' || TABLE_NAME AS table_full_name, 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