PostgreSQL教程

数据库版本信息

版本函数

获取PostgreSQL版本信息：

SELECT 
    VERSION() AS full_version,                            -- 完整版本信息
    current_setting('server_version') AS server_version,  -- 服务器版本
    current_setting('server_version_num') AS version_number; -- 版本号

版本比较

检查版本兼容性：

SELECT 
    current_setting('server_version_num')::INTEGER >= 130000 AS is_postgres_13_or_later,
    CASE 
        WHEN current_setting('server_version_num')::INTEGER >= 150000 THEN 'PostgreSQL 15+'
        WHEN current_setting('server_version_num')::INTEGER >= 130000 THEN 'PostgreSQL 13-14'
        ELSE 'Older PostgreSQL'
    END AS version_category;

用户和会话信息

用户信息函数

获取当前用户信息：

SELECT 
    CURRENT_USER AS current_user_name,                    -- 当前用户
    SESSION_USER AS session_user_name,                    -- 会话用户
    USER AS user_name,                                    -- 同CURRENT_USER
    CURRENT_ROLE AS current_role_name,                    -- 当前角色
    CURRENT_CATALOG AS current_database;                  -- 当前数据库

会话信息

获取会话相关信息：

SELECT 
    PG_BACKEND_PID() AS backend_process_id,               -- 后端进程ID
    TXID_CURRENT() AS current_transaction_id,             -- 当前事务ID
    PG_MY_TEMP_SCHEMA() AS temp_schema;                   -- 临时模式名

数据库对象信息

表和列信息

获取表和列的元数据：

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'public'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- 获取表大小信息
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC;

索引信息

获取索引相关信息：

SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes 
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC;

系统配置信息

配置参数

查看和设置系统配置：

SELECT 
    name,
    setting,
    unit,
    short_desc
FROM pg_settings 
WHERE name IN ('shared_buffers', 'work_mem', 'maintenance_work_mem', 'effective_cache_size')
ORDER BY name;

-- 查看特定配置参数
SELECT current_setting('timezone') AS timezone_setting;
SELECT current_setting('client_encoding') AS client_encoding;
SELECT current_setting('DateStyle') AS date_style;

设置会话参数

临时修改会话配置：

-- 设置时区
SET timezone = 'Asia/Shanghai';
SHOW timezone;

-- 设置日期格式
SET DateStyle = 'ISO, YMD';
SHOW DateStyle;

-- 设置搜索路径
SET search_path = public, pg_temp;
SHOW search_path;

系统统计信息

活动会话

查看当前数据库活动：

SELECT 
    pid,
    usename,
    application_name,
    client_addr,
    backend_start,
    state,
    query
FROM pg_stat_activity 
WHERE state = 'active';

数据库统计

获取数据库统计信息：

SELECT 
    datname,
    numbackends AS active_connections,
    xact_commit AS transactions_committed,
    xact_rollback AS transactions_rolled_back,
    blks_read AS blocks_read,
    blks_hit AS blocks_hit,
    tup_returned AS tuples_returned,
    tup_fetched AS tuples_fetched,
    tup_inserted AS tuples_inserted,
    tup_updated AS tuples_updated,
    tup_deleted AS tuples_deleted
FROM pg_stat_database 
WHERE datname = current_database();

系统监控函数

锁信息

查看数据库锁信息：

SELECT 
    locktype,
    database,
    relation::regclass AS relation_name,
    page,
    tuple,
    virtualxid,
    transactionid,
    classid::regclass AS class_name,
    objid,
    objsubid,
    virtualtransaction,
    pid,
    mode,
    granted,
    fastpath
FROM pg_locks 
WHERE granted = false; -- 查看未被授予的锁

后台进程

查看后台进程信息：

SELECT 
    pid,
    backend_type,
    application_name,
    state,
    query
FROM pg_stat_activity 
WHERE backend_type != 'client backend';

实际应用场景

健康检查

-- 创建数据库健康检查函数
CREATE OR REPLACE FUNCTION database_health_check()
RETURNS TABLE(
    check_name TEXT,
    check_result TEXT,
    check_status TEXT
) AS $$
BEGIN
    RETURN QUERY
    -- 检查连接数
    SELECT 
        'Active Connections' AS check_name,
        COUNT(*)::TEXT AS check_result,
        CASE 
            WHEN COUNT(*) > 100 THEN 'WARNING'
            ELSE 'OK'
        END AS check_status
    FROM pg_stat_activity 
    WHERE state = 'active'
    
    UNION ALL
    
    -- 检查锁等待
    SELECT 
        'Lock Waits' AS check_name,
        COUNT(*)::TEXT AS check_result,
        CASE 
            WHEN COUNT(*) > 0 THEN 'WARNING'
            ELSE 'OK'
        END AS check_status
    FROM pg_locks 
    WHERE granted = false
    
    UNION ALL
    
    -- 检查表大小
    SELECT 
        'Largest Table' AS check_name,
        pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS check_result,
        CASE 
            WHEN pg_relation_size(schemaname||'.'||tablename) > 1073741824 THEN 'WARNING' -- 1GB
            ELSE 'OK'
        END AS check_status
    FROM pg_tables 
    WHERE schemaname = 'public'
    ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 执行健康检查
SELECT * FROM database_health_check();

性能监控

-- 创建性能监控函数
CREATE OR REPLACE FUNCTION performance_monitoring(duration INTERVAL DEFAULT '1 hour')
RETURNS TABLE(
    metric_name TEXT,
    current_value NUMERIC,
    threshold NUMERIC,
    status TEXT
) AS $$
BEGIN
    RETURN QUERY
    -- 缓存命中率
    SELECT 
        'Cache Hit Ratio' AS metric_name,
        ROUND((blks_hit::NUMERIC / NULLIF(blks_hit + blks_read, 0)) * 100, 2) AS current_value,
        95.0 AS threshold,
        CASE 
            WHEN (blks_hit::NUMERIC / NULLIF(blks_hit + blks_read, 0)) * 100 >= 95 THEN 'OK'
            ELSE 'WARNING'
        END AS status
    FROM pg_stat_database 
    WHERE datname = current_database()
    
    UNION ALL
    
    -- 死锁率
    SELECT 
        'Deadlock Rate' AS metric_name,
        deadlocks::NUMERIC AS current_value,
        0.0 AS threshold,
        CASE 
            WHEN deadlocks > 0 THEN 'WARNING'
            ELSE 'OK'
        END AS status
    FROM pg_stat_database 
    WHERE datname = current_database()
    
    UNION ALL
    
    -- 慢查询
    SELECT 
        'Slow Queries' AS metric_name,
        COUNT(*)::NUMERIC AS current_value,
        10.0 AS threshold,
        CASE 
            WHEN COUNT(*) > 10 THEN 'WARNING'
            ELSE 'OK'
        END AS status
    FROM pg_stat_statements 
    WHERE mean_time > 1000 -- 超过1秒的查询
    AND calls > 10;
END;
$$ LANGUAGE plpgsql;

-- 执行性能监控
SELECT * FROM performance_monitoring();

容量规划

-- 创建容量规划函数
CREATE OR REPLACE FUNCTION capacity_planning()
RETURNS TABLE(
    resource_type TEXT,
    current_usage TEXT,
    projected_growth TEXT,
    recommendation TEXT
) AS $$
BEGIN
    RETURN QUERY
    -- 数据库大小
    SELECT 
        'Database Size' AS resource_type,
        pg_size_pretty(pg_database_size(current_database())) AS current_usage,
        'N/A' AS projected_growth,
        CASE 
            WHEN pg_database_size(current_database()) > 10737418240 THEN 'Consider archiving old data' -- 10GB
            ELSE 'No immediate action needed'
        END AS recommendation
    
    UNION ALL
    
    -- 连接数
    SELECT 
        'Active Connections' AS resource_type,
        COUNT(*)::TEXT AS current_usage,
        'N/A' AS projected_growth,
        CASE 
            WHEN COUNT(*) > 80 THEN 'Consider increasing max_connections'
            ELSE 'Connection usage is healthy'
        END AS recommendation
    FROM pg_stat_activity 
    WHERE state = 'active'
    
    UNION ALL
    
    -- 表增长
    SELECT 
        'Table Growth' AS resource_type,
        tablename AS current_usage,
        'N/A' AS projected_growth,
        CASE 
            WHEN pg_relation_size(schemaname||'.'||tablename) > 536870912 THEN 'Consider partitioning large tables' -- 512MB
            ELSE 'Table size is manageable'
        END AS recommendation
    FROM pg_tables 
    WHERE schemaname = 'public'
    ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC
    LIMIT 1;
END;
$$ LANGUAGE plpgsql;

-- 执行容量规划
SELECT * FROM capacity_planning();

安全相关函数

用户权限检查

SELECT 
    rolname AS role_name,
    rolsuper AS is_superuser,
    rolcreatedb AS can_create_db,
    rolcreaterole AS can_create_role,
    rolcanlogin AS can_login
FROM pg_roles 
ORDER BY rolname;

-- 检查当前用户权限
SELECT 
    has_database_privilege(current_user, current_database(), 'CONNECT') AS can_connect,
    has_database_privilege(current_user, current_database(), 'CREATE') AS can_create,
    has_table_privilege(current_user, 'users', 'SELECT') AS can_select_users,
    has_table_privilege(current_user, 'users', 'INSERT') AS can_insert_users;

性能优化

系统视图优化

创建常用的系统信息视图：

-- 创建表大小视图
CREATE OR REPLACE VIEW table_sizes AS
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_relation_size(schemaname||'.'||tablename) AS size_bytes
FROM pg_tables 
WHERE schemaname = 'public'
ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC;

-- 创建索引使用统计视图
CREATE OR REPLACE VIEW index_usage_stats AS
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan AS index_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- 创建数据库报告函数
CREATE OR REPLACE FUNCTION generate_database_report()
RETURNS TABLE(
    section TEXT,
    item TEXT,
    value TEXT,
    recommendation TEXT
) AS $$
BEGIN
    RETURN QUERY
    -- 数据库基本信息
    SELECT 
        'Database Info' AS section,
        'Version' AS item,
        VERSION() AS value,
        'N/A' AS recommendation
    
    UNION ALL
    
    SELECT 
        'Database Info' AS section,
        'Current Database' AS item,
        current_database() AS value,
        'N/A' AS recommendation
    
    UNION ALL
    
    SELECT 
        'Database Info' AS section,
        'Current User' AS item,
        CURRENT_USER AS value,
        'N/A' AS recommendation
    
    UNION ALL
    
    -- 连接信息
    SELECT 
        'Connections' AS section,
        'Active Connections' AS item,
        COUNT(*)::TEXT AS value,
        CASE 
            WHEN COUNT(*) > 80 THEN 'Consider increasing max_connections'
            ELSE 'Connection usage is healthy'
        END AS recommendation
    FROM pg_stat_activity 
    WHERE state = 'active'
    
    UNION ALL
    
    -- 性能指标
    SELECT 
        'Performance' AS section,
        'Cache Hit Ratio' AS item,
        ROUND((blks_hit::NUMERIC / NULLIF(blks_hit + blks_read, 0)) * 100, 2)::TEXT || '%' AS value,
        CASE 
            WHEN (blks_hit::NUMERIC / NULLIF(blks_hit + blks_read, 0)) * 100 >= 95 THEN 'Good'
            ELSE 'Consider increasing shared_buffers'
        END AS recommendation
    FROM pg_stat_database 
    WHERE datname = current_database()
    
    UNION ALL
    
    -- 存储信息
    SELECT 
        'Storage' AS section,
        'Database Size' AS item,
        pg_size_pretty(pg_database_size(current_database())) AS value,
        CASE 
            WHEN pg_database_size(current_database()) > 10737418240 THEN 'Consider archiving old data'
            ELSE 'Storage usage is healthy'
        END AS recommendation;
END;
$$ LANGUAGE plpgsql;

-- 生成数据库报告
SELECT * FROM generate_database_report() ORDER BY section, item;