#postgres

SELECT usename FROM pg_catalog.pg_user;

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
GRANT CONNECT ON DATABASE test TO readonly_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES FOR USER readonly_user GRANT SELECT, USAGE ON SEQUENCES TO readonly_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly_user;
ALTER DEFAULT PRIVILEGES FOR ROLE readonly_user GRANT EXECUTE ON FUNCTIONS TO readonly_user;

ALTER ROLE postgres WITH PASSWORD 'password1';

create role rep nosuperuser replication login connection limit 32 encrypted password '123456';

create database repdb;

grant all privileges on database repdb to rep;

-- 重载配置
SELECT pg_reload_conf();

SELECT pg_create_restore_point('20220221'); -- 创建还原点

SELECT pg_size_pretty(pg_database_size('your_database_name')) AS size;

SELECT datname AS database_name,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

SELECT pg_size_pretty(pg_relation_size('your_table_name')) AS table_size;

SELECT pg_size_pretty(pg_total_relation_size('your_table_name')) AS total_size;

SELECT relname AS table_name,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 删除主表和分区表
-- DROP TABLE public.action_test CASCADE;
-- action_test 表
CREATE TABLE IF NOT EXISTS public.action_test (
    create_time TIMESTAMPTZ DEFAULT now() NOT NULL,
    kafka_offset BIGINT,
    kafka_create_time TIMESTAMPTZ NOT NULL,
    kafka_partition SMALLINT,
    kafka_topic VARCHAR(255),
    -- 你要求的所有字段 (varchar)
    imsi VARCHAR(255),
    imei VARCHAR(255),
    imei1 VARCHAR(255),
    imei2 VARCHAR(255),
    hsman VARCHAR(255),
    hstype VARCHAR(255),
    appId VARCHAR(255),
    appType VARCHAR(255),
    event VARCHAR(255),
    message JSONB
) PARTITION BY RANGE (kafka_create_time);

CREATE OR REPLACE FUNCTION public.create_daily_action_test_partition() RETURNS void LANGUAGE plpgsql AS $$
DECLARE d date;
start_date date;
end_date date;
partition_table text;
BEGIN -- 循环今天、明天（用 SELECT UNION ALL 构造两行）
FOR d IN
SELECT current_date
UNION ALL
SELECT current_date + 1 LOOP start_date := d;
end_date := d + INTERVAL '1 day';
partition_table := 'action_test_' || to_char(start_date, 'YYYY_MM_DD');
-- 判断是否存在
IF EXISTS (
    SELECT 1
    FROM pg_class
    WHERE relname = partition_table
) THEN RAISE NOTICE 'Partition "%" already exists, skip.',
partition_table;
ELSE -- 创建分区表
EXECUTE format(
    'CREATE TABLE public.%I PARTITION OF public.action_test
                 FOR VALUES FROM (%L) TO (%L);',
    partition_table,
    start_date::text,
    end_date::text
);
RAISE NOTICE 'Created partition: %',
partition_table;
END IF;
-- 创建索引（全用 IF NOT EXISTS）
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (imsi);',
    'idx_' || partition_table || '_imsi',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (imei);',
    'idx_' || partition_table || '_imei',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (imei1);',
    'idx_' || partition_table || '_imei1',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (imei2);',
    'idx_' || partition_table || '_imei2',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (hsman);',
    'idx_' || partition_table || '_hsman',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (hstype);',
    'idx_' || partition_table || '_hstype',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (appId);',
    'idx_' || partition_table || '_appid',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (appType);',
    'idx_' || partition_table || '_apptype',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I (event);',
    'idx_' || partition_table || '_event',
    partition_table
);
EXECUTE format(
    'CREATE INDEX IF NOT EXISTS %I ON public.%I USING GIN (message jsonb_path_ops);',
    'idx_' || partition_table || '_message_gin',
    partition_table
);
RAISE NOTICE 'Indexes created for partition: %',
partition_table;
END LOOP;
END;
$$;
-- 执行函数创建当前日期的分区
select public.create_daily_action_test_partition();

SELECT cron.schedule_in_database(
        'daily_create_action_test_partition',
        '5 0 * * *',
        $$
        SELECT public.create_daily_action_test_partition();
$$,
'action'
);

docker run -i --rm -e PGPASSWORD=password \
    postgres:9.6.8 \
    psql -U postgres -p 5432 -h 127.0.0.1 -d ops_list -tA -c "SELECT ops_name FROM public.ops_list WHERE date = '2025-11-25' LIMIT 1;" | tail -1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

CREATE DATABASE test WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

ALTER DATABASE test OWNER TO postgres;

\connect test

SELECT string_agg(yaml_text, '')
FROM (
    SELECT
        '- targets: ["' || local_host || ':9100"]' || E'\n' ||
        '  labels:' || E'\n' ||
        '    saltid: '   || id || E'\n' ||
        '    job_name: ' || id || E'\n' ||
        '    instance: ' || id || E'\n' ||
        '    comment: "' || COALESCE(note, '') || '"' || E'\n\n' AS yaml_text
    FROM "server"
    WHERE id LIKE 'lmb-cp%'
    ORDER BY id
) AS t;

SELECT
    datname AS database_name,
    blks_read AS disk_read_blocks, -- 从磁盘读取的次数
    blks_hit AS cache_hit_blocks,  -- 从内存命中的次数
    -- 计算命中率，越低代表 I/O 压力越大
    round(blks_hit::numeric / (blks_hit + blks_read + 0.001) * 100, 2) AS cache_hit_ratio,
    temp_files AS temp_file_count, -- 产生的临时文件数
    temp_bytes / 1024 / 1024 AS temp_mb -- 临时文件大小
FROM pg_stat_database
WHERE datname NOT LIKE 'template%'
ORDER BY blks_read DESC;