-
查看各个表所占用内存
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