轉(zhuǎn)自：http://blog.csdn.net/libo2158/article/details/70133380

查看各個(gè)表所占用內(nèi)存

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;

查看當(dāng)前序列

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;

查看某個(gè)表的索引使用情況

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;

表的大小和表中索引個(gè)數(shù)

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;

獲取每個(gè)表的行數(shù)，索引和一些關(guān)于這些索引的信息（比較詳細(xì)）

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;

導(dǎo)出表結(jié)構(gòu)：-s -t

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

導(dǎo)出表結(jié)構(gòu)和內(nèi)容：-t

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

只導(dǎo)出某個(gè)表的內(nèi)容：-a

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

導(dǎo)入

psql -U postgres testdb < tbnode.out

總結(jié)

以上是生活随笔為你收集整理的PG通用(常用)功能的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。