Postgre SQL 部分表信息查询常用sql

查所有表的大小

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

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

更新序列

SELECT
    setval(
        'table_name_id_seq',
        (
            SELECT
                MAX (ID)
            FROM
                table_name_id
        )
    );

重置数据库索引

REINDEX INDEX tbl_cust_id_idx;

查看连接信息

select * from pg_stat_activity;

杀死所有idle进程

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE current_query='<IDLE>'