PG通用(常用)功能

概述转自：http://blog.csdn.net/libo2158/article/details/70133380 查看各个表所占用内存 SELECT table_schema || ‘.’ || table_name AS table_full_name, pg_size_pretty(pg_total_relation_size(‘”’ || table_schema || ‘”.”’ ||

转自：http://blog.csdn.net/libo2158/article/details/70133380

查看各个表所占用内存

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 setval(‘scm_inout_daily_acount_ID_seq’,max(ID)) from scm_inout_daily_acount;

查看当前序列

select nextval(‘scm_inout_daily_acount_ID_seq’); select currval(‘scm_inout_daily_acount_ID_seq’);

查看所有表的索引的使用情况

select relname,indexrelname,IDx_scan,IDx_tup_read,IDx_tup_fetch from pg_stat_user_indexes order by IDx_scan asc,IDx_tup_read asc,IDx_tup_fetch asc;

查看某个表的索引使用情况

select relname,IDx_tup_fetch from pg_stat_user_indexes where relname = table_name order by IDx_scan asc,IDx_tup_fetch asc;

表的大小和表中索引个数

SELECT t.tablename,indexname,c.reltuples AS num_rows,pg_size_pretty(pg_relation_size(quote_IDent(t.tablename)::text)) AS table_size,pg_size_pretty(pg_relation_size(quote_IDent(indexrelname)::text)) AS index_size,CASE WHEN indisunique THEN ‘Y’ ELSE ‘N’ END AS UNIQUE,IDx_scan AS number_of_scans,IDx_tup_read AS tuples_read,IDx_tup_fetch AS tuples_fetched FROM pg_tables t left OUTER JOIN pg_class c ON t.tablename=c.relname left OUTER JOIN ( SELECT c.relname AS ctablename,ipg.relname AS indexname,x.indnatts AS number_of_columns,IDx_tup_fetch,indisunique FROM pg_index x JOIN pg_class c ON c.oID = x.indrelID JOIN pg_class ipg ON ipg.oID = x.indexrelID JOIN pg_stat_all_indexes psai ON x.indexrelID = psai.indexrelID ) AS foo ON t.tablename = foo.ctablename WHERE t.schemaname=’public’ ORDER BY 1,2;

获取每个表的行数，索引和一些关于这些索引的信息（比较详细）

SELECT pg_class.relname,pg_size_pretty(pg_class.reltuples::BIGINT) AS rows_in_bytes,pg_class.reltuples AS num_rows,COUNT(indexname) AS number_of_indexes,CASE WHEN x.is_unique = 1 THEN ‘Y’ ELSE ‘N’ END AS UNIQUE,SUM(CASE WHEN number_of_columns = 1 THEN 1 ELSE 0 END) AS single_column,SUM(CASE WHEN number_of_columns IS NulL THEN 0 WHEN number_of_columns = 1 THEN 0 ELSE 1 END) AS multi_column FROM pg_namespace left OUTER JOIN pg_class ON pg_namespace.oID = pg_class.relnamespace left OUTER JOIN (SELECT indrelID,MAX(CAST(indisunique AS INTEGER)) AS is_unique FROM pg_index GROUP BY indrelID) x ON pg_class.oID = x.indrelID left OUTER JOIN ( SELECT c.relname AS ctablename,x.indnatts AS number_of_columns FROM pg_index x JOIN pg_class c ON c.oID = x.indrelID JOIN pg_class ipg ON ipg.oID = x.indexrelID ) AS foo ON pg_class.relname = foo.ctablename WHERE pg_namespace.nspname=’public’ AND pg_class.relkind = ‘r’ GROUP BY pg_class.relname,pg_class.reltuples,x.is_unique ORDER BY 2;

导出表结构：-s -t

pg_dump -s -t xxxx.tbtest testdb > tbnode.out

导出表结构和内容：-t

pg_dump -h mDW -t xxxx.tbtest testdb > tbnode.sql

只导出某个表的内容：-a

pg_dump -h mDW -t xxxx.tbtest -a testdb > tbnode.sql

导入

psql -U postgres testdb < tbnode.out

总结

以上是内存溢出为你收集整理的PG通用(常用)功能全部内容，希望文章能够帮你解决PG通用(常用)功能所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。