-- 使用超級用戶連接后執(zhí)行
CREATE EXTENSION adminpack;

-- 驗證安裝
SELECT * FROM pg_extension WHERE extname = 'adminpack';

\df pg_file.*
\df pg_log.*

-- 讀取服務(wù)器上的文件內(nèi)容
SELECT pg_read_file('postgresql.conf', 0, 1000);  -- 讀取前1000字節(jié)

-- 讀取整個文件
SELECT pg_read_file('postgresql.conf');

-- 寫入內(nèi)容到服務(wù)器文件
SELECT pg_write_file('test.txt', 'This is test content', false);

-- 追加內(nèi)容到文件
SELECT pg_write_file('test.txt', E'\nAdditional content', true);

-- 列出目錄內(nèi)容
SELECT pg_ls_dir('.');

SELECT pg_ls_logdir();

-- 讀取最新的日志文件內(nèi)容
SELECT pg_read_file(pg_ls_logdir() ORDER BY name DESC LIMIT 1);

SELECT pg_switch_xlog();  -- 9.6及更早版本
SELECT pg_switch_wal();   -- 10.0及以后版本

SELECT pg_reload_conf();

-- 撤銷public模式的默認(rèn)權(quán)限
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- 僅限特定角色使用adminpack函數(shù)
GRANT EXECUTE ON FUNCTION pg_read_file(text) TO admin_role;
GRANT EXECUTE ON FUNCTION pg_write_file(text, text, boolean) TO admin_role;

-- 創(chuàng)建審計表
CREATE TABLE adminpack_audit (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    function_name TEXT NOT NULL,
    parameters TEXT,
    executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 創(chuàng)建審計觸發(fā)器函數(shù)
CREATE OR REPLACE FUNCTION audit_adminpack_usage()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO adminpack_audit(username, function_name, parameters)
    VALUES (current_user, TG_OP, TG_ARGV[0]);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- 為關(guān)鍵函數(shù)創(chuàng)建觸發(fā)器(需要PostgreSQL 9.3+)
CREATE TRIGGER trg_audit_file_read
AFTER EXECUTE ON FUNCTION pg_read_file(text)
FOR EACH STATEMENT
EXECUTE FUNCTION audit_adminpack_usage();

-- 備份postgresql.conf
SELECT pg_write_file(
    'conf_backup/postgresql.conf.' || to_char(CURRENT_TIMESTAMP, 'YYYYMMDD_HH24MISS'),
    pg_read_file('postgresql.conf'),
    false
);

-- 查找錯誤日志
WITH log_files AS (
    SELECT name FROM pg_ls_logdir() WHERE name LIKE '%.log'
)
SELECT name, 
       COUNT(*) FILTER (WHERE line LIKE '%ERROR%') AS error_count,
       COUNT(*) FILTER (WHERE line LIKE '%WARNING%') AS warning_count
FROM log_files,
     LATERAL (SELECT pg_read_file('log/' || name) AS content) AS c,
     LATERAL unnest(string_to_array(content, E'\n')) AS line
GROUP BY name;

-- 批量重命名日志文件
DO $$
DECLARE
    f record;
BEGIN
    FOR f IN SELECT name FROM pg_ls_logdir() WHERE name LIKE '%.log' AND name NOT LIKE '%.bak'
    LOOP
        PERFORM pg_write_file(
            'log/' || f.name || '.bak',
            pg_read_file('log/' || f.name),
            false
        );
        PERFORM pg_file_unlink('log/' || f.name);
    END LOOP;
END $$;

-- 創(chuàng)建自動備份任務(wù)
CREATE OR REPLACE FUNCTION backup_config_files()
RETURNS VOID AS $$
BEGIN
    PERFORM pg_write_file(
        'conf_backup/hba.conf.' || to_char(CURRENT_TIMESTAMP, 'YYYYMMDD'),
        pg_read_file('pg_hba.conf'),
        false
    );
    PERFORM pg_write_file(
        'conf_backup/postgresql.conf.' || to_char(CURRENT_TIMESTAMP, 'YYYYMMDD'),
        pg_read_file('postgresql.conf'),
        false
    );
END;
$$ LANGUAGE plpgsql;

-- 日志輪轉(zhuǎn)腳本示例
DO $$
DECLARE
    log_file text;
BEGIN
    FOR log_file IN SELECT name FROM pg_ls_logdir() WHERE name ~ '^postgresql-\d{4}-\d{2}-\d{2}_'
    LOOP
        IF log_file < to_char(CURRENT_DATE - interval '30 days', '"postgresql-"YYYY-MM-DD_') THEN
            PERFORM pg_file_unlink('log/' || log_file);
        END IF;
    END LOOP;
END $$;