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 || ‘”.”’ || 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