pg 解决锁表问题

SELECT pid, relname, transactionid, mode, granted 
FROM pg_locks
 JOIN  pg_stat_user_tables ON pg_locks.relation = pg_stat_user_tables.relid


select * from pg_stat_activity where datname='数据库名' and state='active'

查看锁
 SELECT 
   *
FROM 
    pg_stat_activity
WHERE
    wait_event_type = 'Lock';
 
 select pg_terminate_backend(3674725)

查看数据库内存开销

 SELECT datname,
       pg_size_pretty(pg_database_size(datname)) as size
FROM pg_stat_database;

查看表的大小

   SELECT pg_relation_size('talbeName');

查看表的状态

     select * from pg_stat_user_tables where relname='talbeName';

查看表的真实大小

    select pg_size_pretty(pg_relation_size('talbeName'));

排查内存

free -h -s 5

张表垃圾切片太多了,可以进行切片手动回收

vacuum talbeName

切换账号

sudo /bin/su - root
ssh登录其他服务： ssh -i /xx/id_ras username@host
-i 后面跟着密钥地址