PG通用(常用)功能

• 查看各个表所占用内存

  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