PG通用(常用)功能
来源:互联网 发布:我的世界联机出现java 编辑:程序博客网 时间:2024/05/25 19:56
转自: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, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = table_name order by idx_scan asc, idx_tup_read 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_scan, idx_tup_read, idx_tup_fetch, indexrelname, 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, ipg.relname AS indexname, 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
阅读全文
0 0
- PG通用(常用)功能
- PG通用(常用)功能
- PHP常用(通用)功能
- PG常用SQL
- PG常用管理命令
- JS常用(通用)功能(插件)
- PG的ALTER常用操作
- PG常用的几个查询(1)
- PG常用的几个查询(1)
- 关于postgreSQL(PG)的search_path参数功能及效果
- 通用框架功能列表
- 通用功能点
- 通用功能 | General Function
- iOS通用库--常用功能一之与时间和日历相关的封装
- iOS通用库--常用功能二之下拉选择框的封装
- 火绒内核注入dll方式win7-win10通用x64下不触发PG
- markdown 常用通用语法
- SWT 通用多功能搜索功能
- 解决在visualstudio2012调用exiv2库遇到的问题
- 求1+2+3+...+n
- Android图表库MPAndroidChart(九)——神神秘秘的散点图
- Hadoop入门之命令参考
- Gradle入门系列(5):创建多项目构建
- PG通用(常用)功能
- HTTP与HTTPS的区别
- Mac最强虚拟机软件Parallels Desktop xx版13.2.0
- 【笔记】PHP- array_push() 还有JSON
- FAQ宝典之Rancher Server
- AndroidStudio 工程添加NDK支持
- 使用Jacob批量转换word为txt、pdf、xps、html、xml等文档
- Bitmap的使用
- MySQL事务隔离级别详解