1.PostgreSQL日常运维管理

-- 检查PostgreSQL数据库的运行状态
SELECT pg_is_in_recovery();
 
-- 查看数据库大小
SELECT pg_size_pretty(pg_database_size('数据库名'));
 
-- 查看所有表的大小
SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) AS size
FROM
    information_schema.tables
ORDER BY
    pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) DESC;
 
-- 查看特定表的索引大小
SELECT
    indexname,
    pg_size_pretty(pg_relation_size(indexname)) as index_size
FROM
    (
        SELECT
            indexname = '表名' || '__' || indexrelname
        FROM
            pg_index,
            pg_class c
        WHERE
            c.relname ~ ('^' || '表名' || '__')
            AND pg_index.indexrelid = c.oid
            AND c.relkind = 'i'
    ) as subquery;
 
-- 查看特定表的索引和表的总大小
SELECT
    pg_size_pretty(pg_total_relation_size('表名')) as total_size;
 
-- 查看特定表的记录数
SELECT
    reltuples
FROM
    pg_class
WHERE
    relname = '表名';
 
-- 查看特定表的索引数
SELECT
    count(*)
FROM
    pg_index
WHERE
    indrelid = (SELECT oid FROM pg_class WHERE relname = '表名')
    AND indisprimary = false;

以上SQL语句提供了一些基本的运维操作，包括检查数据库的恢复状态、查看数据库大小、查看所有表的大小、查看特定表的索引大小、查看特定表的索引和表的总大小、查看特定表的记录数以及查看特定表的索引数。这些操作可以帮助数据库管理员进行日常的运维管理。