查看指定表大小

  1. david=# select pg_relation_size('test');                
  2.  pg_relation_size 
  3. ------------------
  4.          95748096
  5. (1 row)
  7. david=# select pg_size_pretty(pg_relation_size('test'));
  8.  pg_size_pretty 
  9. ----------------
  10.  91 MB
  11. (1 row)

查看表空间

  1. \db+

查询表空间配置表

  1. select * from t_tablesapce_manager;
  3. - 如果默认表空间满了,将flag置为1(0:可用,1:不可用)

查看表存储路径

  1. select pg_relation_filepath('test');

查看表空间表中有那些表

  1. select a.table_schema || '.' || a.table_name as full_table_name, b.tablespace, pg_size_pretty(pg_total_relation_size('"' || a.table_schema || '"."' || a.table_name || '"')) as table_size from
  2.     information_schema.tables a, 
  3.     pg_tables b 
  4. where a.table_name = b.tablename 
  5. and b.tablespace = 'pgdata'
  6. order by 
  7.     pg_total_relation_size('"' || a.table_schema || '"."' || a.table_name || '"') desc;

将表移动到另外的表空间

  1. alter table public.capture_vehicle_20220313 set tablespace pgdata_01;

查询锁表

select pid, state, usename, query, query_start
from pg_stat_activity
where pid in (
select pid from pg_locks l
join pg_class t on l.relation = t.oid
and t.relkind = ‘r’
) ORDER BY query_start;

干掉锁表的进程

select pg_terminate_backend(22988)

查看默认表空间里面的对象

r = 普通表，i = 索引，S = 序列，v = 视图， c = 复合类型，s = 特殊，t = TOAST表

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a
where a.relkind in (‘r’, ‘i’)
and reltablespace=’0’
order by a.relpages desc;

— 卸载分表
ALTER TABLE capture_person DETACH PARTITION capture_person_20210918;

— 重新挂回分表到主表
ALTER TABLE capture_person ATTACH PARTITION capture_person_20210918 FOR VALUES FROM (‘1631894400000000’) TO (‘1631980800000000’) ;

— 查询_default表中是否有分表的数据
SELECT count(*) FROM capture_img_default WHERE id >= 1632499200000000 AND id < 1632585600000000;

— 迁移_default表中的数据到对应分表
INSERT INTO capture_img_20210925 SELECT * FROM capture_img_default WHERE id >= 1632499200000000 AND id < 1632585600000000;

— 删除_default表中已迁移的数据
DELETE FROM capture_img_default WHERE id >= 1632499200000000 AND id < 1632585600000000;

call proc_add_index_capture_partition_table(‘20220429’,’20220429’);

select func_tool_recovery_partition_table_data(‘20190917’);

/NO LOAD BALANCE/call proc_create_capture_partition_table(-1,-1,-1);