> For AI agents: the complete documentation index is available at https://www.tteam.icu/llms.txt, the full documentation bundle is available at https://www.tteam.icu/llms-full.txt. # postgres ## 单机部署 ### 启动 **pg9.6** ```sh file="./postgres/setup9.6.sh" #!/bin/sh port=5432 password=password pg_version=9.6.8 data_dir=data-${port} container_name=pg-${port} mkdir -p ${data_dir}/ init-db-${port}/ # 新部署的机器这段需要注释掉 # cat << EOF > data-${port}/recovery.conf # standby_mode = on # 这个说明这台机器为从库 # primary_conninfo = 'application_name=standby01 host=192.168.xx.xx port=5432 user=postgres password=password' # 主库地址 # recovery_target_timeline = 'latest' # 这个说明这个流复制同步到最新的数据 # EOF # mv -f ${data_dir}/recovery.conf ${data_dir}/recovery.conf.bak chown -R 999:999 ${data_dir}/ docker stop ${container_name} docker rm ${container_name} docker run -d --name ${container_name} \ --restart=always \ --network=host \ -e POSTGRES_PASSWORD=${password} \ -v /etc/localtime:/etc/localtime:ro \ -v /etc/timezone:/etc/timezone:ro \ -v $(pwd)/${data_dir}:/var/lib/postgresql/data \ -v $(pwd)/init-db-${port}/:/docker-entrypoint-initdb.d/ \ muen/postgres:${pg_version} \ -c listen_addresses='*' \ # 允许所有网络地址连接 -c dynamic_shared_memory_type=posix \ # 使用 POSIX 共享内存 -c log_timezone='Asia/Shanghai' \ # 设置日志时区为 Asia/Shanghai -c datestyle='iso, mdy' \ # 设置日期格式为 iso, mdy -c timezone='Asia/Shanghai' \ # 设置时区为 Asia/Shanghai -c lc_messages='en_US.utf8' \ # 设置消息本地化为 en_US.utf8 -c lc_monetary='en_US.utf8' \ # 设置货币本地化为 en_US.utf8 -c lc_numeric='en_US.utf8' \ # 设置数字本地化为 en_US.utf8 -c lc_time='en_US.utf8' \ # 设置时间本地化为 en_US.utf8 -c default_text_search_config='pg_catalog.english' \ # 设置默认文本搜索配置为 pg_catalog.english -c logging_collector=on \ # 开启日志收集器 -c log_filename='postgresql-%Y-%m-%d.log' \ # 设置日志文件名格式为 postgresql-年-月-日.log -c log_statement=all \ # 记录所有 SQL 语句 -c log_min_duration_statement=1000 \ # 仅记录执行时间超过 1000 毫秒的 SQL 语句 -c shared_buffers=128MB \ # 分配 128MB 共享缓冲区 -c temp_buffers=8MB \ # 分配 8MB 临时缓冲区 -c work_mem=4MB \ # 每个工作线程分配 4MB 内存 -c maintenance_work_mem=64MB \ # 维护操作最多使用 64MB 内存 -c port=${port} \ # 监听端口号为 5432 -c wal_level=logical \ # 设置 WAL 日志级别为 logical -c max_wal_senders=4 \ # 允许的最大 WAL 发送者数量为 4 -c max_wal_size=1GB \ # 设置最大 WAL 大小为 1GB -c wal_sender_timeout=60s \ # 设置 WAL 发送者超时为 60 秒 -c max_connections=2000 \ # 允许的最大连接数为 2000 -c hot_standby=off \ # 关闭热备模式 -c max_standby_streaming_delay=30s \ # 最大备用流延迟为 30 秒 -c wal_receiver_status_interval=10s \ # 设置 WAL 接收者状态检查间隔为 10 秒 -c hot_standby_feedback=on \ # 开启热备反馈 -c synchronous_standby_names='standby01' \ # 同步备用服务器名称为 standby01 -c synchronous_commit=off \ # 关闭同步提交 -c commit_delay=0 \ # 提交延迟为 0 -c commit_siblings=5 \ # 提交兄弟数量为 5 -c archive_mode=on \ # 开启归档模式 -c archive_command='DATE=`date +%Y%m%d`;DIR="/var/lib/postgresql/data/pg_xlog_backup/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f' \ # 设置归档命令 -c pgaudit.log_client=on \ # 开启客户端审计日志 # 添加复制权限 hba_file=${data_dir}/pg_hba.conf while [ ! -f ${hba_file} ];do echo "等待初始化成功" && sleep 3 done if [ -f ${hba_file} ] && [ $(cat ${hba_file} |egrep "^host"|grep replication|grep -c postgres) -eq 0 ];then # 重新加载配置 sleep 3 echo "host replication postgres all md5" >> ${hba_file} && docker exec -i ${container_name} su -c 'psql -c "select pg_reload_conf();"' postgres fi ``` **pg14.6** ```sh file="./postgres/setup14.6.sh" #!/bin/sh port=5432 password="password" pg_version=14.6-alpine mkdir -p data-${port}/pgdata init-db/ chown -R 1000:1000 data-${port}/pgdata docker stop pg-${port} docker rm pg-${port} docker run -d --name pg-${port} \ --restart=always \ --network=host \ -e POSTGRES_PASSWORD=${password} \ -e PGDATA=/var/lib/postgresql/data/pgdata \ -v /etc/localtime:/etc/localtime:ro \ -v /etc/timezone:/etc/timezone:ro \ -v $(pwd)/data-${port}:/var/lib/postgresql/data \ -v $(pwd)/init-db-${port}/:/docker-entrypoint-initdb.d/ \ postgres:${pg_version} \ -c listen_addresses='*' \ -c dynamic_shared_memory_type=posix \ -c log_timezone='Asia/Shanghai' \ -c datestyle='iso, mdy' \ -c timezone='Asia/Shanghai' \ -c lc_messages='en_US.utf8' \ -c lc_monetary='en_US.utf8' \ -c lc_numeric='en_US.utf8' \ -c lc_time='en_US.utf8' \ -c default_text_search_config='pg_catalog.english' \ -c logging_collector=on \ -c log_filename='postgresql-%Y-%m-%d.log' \ -c shared_buffers=128MB \ -c temp_buffers=8MB \ -c work_mem=4MB \ -c maintenance_work_mem=64MB \ -c port=${port} \ -c wal_level=logical \ -c max_wal_senders=4 \ -c max_wal_size=1GB \ -c wal_sender_timeout=60s \ -c max_connections=2000 \ -c hot_standby=off \ -c max_standby_streaming_delay=30s \ -c wal_receiver_status_interval=10s \ -c hot_standby_feedback=on \ -c synchronous_standby_names='standby01' \ -c synchronous_commit=off \ -c commit_delay=0 \ -c commit_siblings=5 \ -c archive_mode=off \ -c archive_command='DATE=`date +%Y%m%d`;DIR="/var/lib/postgresql/data/pg_xlog_backup/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f' \ ``` **pg17** ```sh file="./postgres/setup17.sh" #!/bin/sh port=5432 password=password image="muen/postgres:17" data_dir=data-${port} init_db_dir=init-db-${port} container_name=pg-${port} mkdir -p ${data_dir}/ ${init_db_dir}/ # PG17 已不再使用 recovery.conf，这里保留你的兼容逻辑 if [ -f ${data_dir}/recovery.conf ];then mv -f ${data_dir}/recovery.conf ${data_dir}/recovery.conf.bak fi chown -R 999:999 ${data_dir}/ docker stop ${container_name} docker rm ${container_name} docker run -d --name ${container_name} \ --restart=always \ --net host \ -e POSTGRES_PASSWORD=${password} \ -v /etc/localtime:/etc/localtime:ro \ -v /etc/timezone:/etc/timezone:ro \ -v $(pwd)/${data_dir}:/var/lib/postgresql/data \ -v $(pwd)/${init_db_dir}/:/docker-entrypoint-initdb.d/ \ ${image} \ -c listen_addresses='*' \ -c port=${port} \ -c dynamic_shared_memory_type=posix \ -c log_timezone='Asia/Shanghai' \ -c datestyle='iso, mdy' \ -c timezone='Asia/Shanghai' \ -c lc_messages='en_US.utf8' \ -c lc_monetary='en_US.utf8' \ -c lc_numeric='en_US.utf8' \ -c lc_time='en_US.utf8' \ -c default_text_search_config='pg_catalog.english' \ -c logging_collector=on \ -c log_destination='csvlog' \ -c log_directory='pg_log' \ -c log_rotation_size=0 \ -c log_truncate_on_rotation=off \ -c log_filename='postgresql-%Y-%m-%d.log' \ -c log_statement=ddl \ -c log_min_duration_statement=1000 \ -c shared_buffers=2GB \ -c effective_cache_size=6GB \ -c maintenance_work_mem=512MB \ -c work_mem=16MB \ -c max_connections=300 \ -c wal_level=logical \ -c max_wal_senders=10 \ -c max_worker_processes=8 \ -c max_parallel_workers=8 \ -c max_parallel_workers_per_gather=4 \ -c wal_buffers=64MB \ -c min_wal_size=1GB \ -c max_wal_size=4GB \ -c wal_sender_timeout=60s \ -c checkpoint_timeout=15min \ -c checkpoint_completion_target=0.9 \ -c hot_standby=off \ -c max_standby_streaming_delay=30s \ -c wal_receiver_status_interval=10s \ -c hot_standby_feedback=on \ -c synchronous_standby_names='standby01' \ -c synchronous_commit=off \ -c commit_delay=0 \ -c commit_siblings=5 \ -c archive_mode=on \ -c archive_command='DATE=`date +%%Y%%m%%d`;DIR="/var/lib/postgresql/data/pg_wal_backup/$DATE";(test -d $DIR || mkdir -p $DIR) && cp %p $DIR/%f' \ -c shared_preload_libraries='pgaudit,pg_cron' \ -c pgaudit.log='ddl,role' \ -c pgaudit.log_catalog=off \ -c cron.database_name='postgres' # 添加复制权限 hba_file=${data_dir}/pg_hba.conf while [ ! -f ${hba_file} ]; do echo "等待初始化成功" && sleep 3 done if [ -f ${hba_file} ] && \ [ $(grep -E "^host" ${hba_file} | grep replication | grep -c postgres) -eq 0 ]; then sleep 5 echo "host replication postgres all md5" >> ${hba_file} docker exec -i ${container_name} su -c \ 'psql -p "'$port'" -c "select pg_reload_conf();"' postgres fi ``` ## 集群部署 ### 在主节点中备份数据 ```bash docker run -i --net host --rm -e PGPASSWORD=password -v $(pwd)/cache/postgresql_data/:/tmp/postgresql_data/ muen/postgres:9.6.8 \ pg_basebackup -v --format t --gzip --progress --write-recovery-conf --xlog --pgdata=/tmp/postgresql_data --host xx.xx.xx.xx --port 5432 --username postgres ``` ### 启动从节点 需要修改 `synchronous_commit`、`synchronous_standby_names`、`hot_standby` 配置。 ## 用户操作 ### 查询用户 ```sql SELECT usename FROM pg_catalog.pg_user; ``` ### 只读用户 创建一个用户名为`readonly_user`的密码为`password`的只读`test`库的用户 ```sql 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; ``` :::tip 如果是在创建只读用户后新建的表，只读用户默认没有该表的查询权限，需要单独授权: ```sql GRANT SELECT ON table TO readonly_user; ``` ::: ### 修改密码 ```sql ALTER ROLE postgres WITH PASSWORD 'password1'; ``` ## 创建基础备份前准备 ### 创建用户 ```sql create role rep nosuperuser replication login connection limit 32 encrypted password '123456'; ``` ### 创建用户库 ```sql create database repdb; ``` ### 用户与用户库关联 ```sql grant all privileges on database repdb to rep; ``` ### 修改 pg\_hba.conf 添加权限 ```bash host replication rep 0.0.0.0/0 md5 ``` ```sql -- 重载配置 SELECT pg_reload_conf(); ``` ## 创建基础备份 ### 创建备份目录 ```bash mkdir -p backdb && cd backdb ``` ### 包含wal日志的备份 ```bash docker run -i --net host --rm -e PGPASSWORD=password -v $(pwd)/.cache/postgresql_data/:/tmp/postgresql_data/ muen/postgres:9.6.8 \ pg_basebackup -v --format t --gzip --progress --write-recovery-conf --xlog --pgdata=/tmp/postgresql_data --host xx.xx.xx.xx --port 5432 --username postgres ``` :::tip 如果数据库操作频繁，可以将启动参数中的 `wal_keep_segments` 增大，例如设置为 `2048`。 ::: ## 恢复 ### 把xlog恢复 ```bash # 从旧库复制到新库 cp -R ../data-5433/pg_xlog/* pg_xlog/ cp -R ../data-5433/pg_xlog_backup/* pg_xlog_backup/ # 从每天的备份中恢复 tar -zxvf 10.0.18.2_5433.tar.gz ``` ### 修改 recovery.conf ```bash restore_command = 'cp -r /var/lib/postgresql/data/pg_xlog_backup/20240123/* /var/lib/postgresql/data/pg_xlog/' recovery_target_time ='2024-01-23 20:00:38.042165+08' ``` ### 另外的方式 #### 设置还原点的 xid(还原顺序由前往后，区间 xid\[1,607]) ```bash recovery_target_xid = '607' #recovery_target = 'immediate' ``` 在恢复过程中，可以通过 `recovery.conf` 文件指定恢复参数: \*\*归档恢复设置: \*\* - `restore_command`: 用于获取已归档的 XLOG 日志文件的命令 - `archive_cleanup_command`: 清除不再需要的 XLOG 日志文件的命令 - `recovery_end_command`: 归档恢复结束后执行的命令 \*\*恢复目标设置(默认恢复到 WAL 日志末尾): \*\* - `recovery_target = 'immediate'`: 从在线备份恢复时，恢复到备份结束的点 - `recovery_target_name (string)`: 指定已命名的恢复点(由 `pg_create_restore_point()` 创建) - `recovery_target_time (timestamp)`: 指定恢复到的时间戳 - `recovery_target_xid (string)`: 指定恢复到的事务 ID - `recovery_target_inclusive (boolean)`: 是否在恢复目标之后停止(true)或之前停止(false)，默认为 true - `recovery_target_timeline (string)`: 指定恢复到的时间线 - `recovery_target_action (enum)`: 达到恢复目标时的动作，包括 pause(暂停)、promote(接受连接)、shutdown(停止服务器)，默认为 pause ### recovery\_target\_name 使用 该参数用于还原到指定备份点: ```sql SELECT pg_create_restore_point('20220221'); -- 创建还原点 ``` ```bash recovery_target_name='20220221' -- 添加还原点配置 ``` ## pg12以上备份与恢复 ### 备份 ```bash docker run -i --net host --rm -e PGPASSWORD=password -v $(pwd)/postgresql_data/:/tmp/postgresql_data/ postgres:14.6-alpine \ pg_basebackup -v --format t --gzip --progress --write-recovery-conf -X none --pgdata=/tmp/postgresql_data --host 192.168.1.1 --port 5432 --username postgres ``` ### 恢复 解压后删除 `standby.signal` 文件，并清空 `postgresql.auto.conf` 文件。 :::tip 如果一直报错"主检查点记录无效"，可以尝试使用 `pg_resetwal` 命令，该命令会按照数据库原有数据重新生成 WAL 日志，但会丢失当前 WAL 日志中的部分数据: ```shell docker run -it --net host --privileged=true --rm --entrypoint=/bin/bash -e PGPASSWORD="password" -v $(pwd)/data-5432:/var/lib/postgresql/data postgres:14.6 su - postgres /usr/lib/postgresql/14/bin/pg_resetwal -f -D /var/lib/postgresql/data/pgdata/ ``` ::: ## 数据迁移 ### 查询pg结果导出csv ```bash docker run -i --rm -e PGPASSWORD=password \ -v ./:/tmp/ \ postgres:9.6.8 \ psql -U postgres -p 5432 -h 192.168.1.1 -d vsadmin -c "\COPY (SELECT * FROM public.test) TO '/tmp/test.csv' With CSV DELIMITER ','" ``` :::tip - `HEADER`: 增加一行字段名 - `QUOTE '\"'`: 字段包裹双引号 ::: ### 导入csv ```bash docker run -i --rm -e PGPASSWORD=password \ --network=host \ -v ./:/tmp/ \ postgres:9.6.8 \ psql -U postgres -p 5432 -h 192.168.1.1 -d test -c "\COPY public.test FROM '/tmp/merged.csv' WITH CSV DELIMITER ','" ``` ### 执行sql文件 ```bash docker run -i --net host --rm -e PGPASSWORD=password -v ./:/tmp muen/postgres:9.6.8 \ --network=host \ psql -h xx.xx.xx.xx -p 5432 -U postgres -d mydatabase -f /tmp/init.sql ``` :::details 定时导出csv ```bash #!/bin/bash # 获取当天日期 today=$(date +%Y-%m-%d) # 获取昨天日期 yesterday=$(date -d "yesterday" +%Y-%m-%d) workdir=$(pwd) mkdir -p ${workdir}/op_cron/sql/${today}/ mkdir -p ${workdir}/op_cron/data/${today}/ cat << EOF > ${workdir}/op_cron/sql/${today}/test.sql select * from public.test where date >= '${yesterday}' and date < '${today}'; EOF docker run -i --rm -e PGPASSWORD=password \ --network=host \ -v ${workdir}/op_cron/:/tmp \ -w /tmp \ muen/postgres:17 \ psql -U postgres -p 5432 -h 192.168.1.1 -d action \ -A -F ',' --no-align \ -f /tmp/sql/${today}/test.sql > ${workdir}/op_cron/data/${today}/${yesterday}_test.csv ``` ::: ## 小型库的迁移 ```sh file="./postgres/backup_small.sh" title="backup_small.sh" #!/bin/bash source_host=192.168.1.1 source_port=5432 source_user=postgres source_password=password image="postgres:9.6.8" database_name_list=" test1 test2 " for database_name in ${database_name_list}; do docker run --rm --name pgbackup-${source_host}-${source_port} \ --net host \ -v /etc/timezone:/etc/timezone:ro \ -v /etc/localtime:/etc/localtime:ro \ -e PGPASSWORD=${source_password} ${image} \ pg_dump -d ${database_name} -h ${source_host} -p ${source_port} -U ${source_user} -F p | gzip > ${database_name}.sql.gz done # 导入 docker exec -it pg-5432 psql -U postgres -d test1 -f /tmp/test1.sql ``` ## 查看占用大小 ### 查看数据库的总大小 ```sql SELECT pg_size_pretty(pg_database_size('your_database_name')) AS size; ``` ### 查看所有数据库的大小 ```sql SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC; ``` ### 查看特定表的大小 ```sql SELECT pg_size_pretty(pg_relation_size('your_table_name')) AS table_size; ``` ### 查看表及其索引的总大小 ```sql SELECT pg_size_pretty(pg_total_relation_size('your_table_name')) AS total_size; ``` ### 查看数据库中所有表的大小 ```sql 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; ``` ## 作为埋点库 :::tip 用到了定时任务和自动建分区表, 需要使用pg17 ::: ```sql title="postgres库运行" CREATE EXTENSION IF NOT EXISTS pg_cron; ``` ```sql file="./postgres/action/action.sql" title="埋点库运行" -- 删除主表和分区表 -- 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(); ``` ```sql title="postgres库运行" SELECT cron.schedule_in_database( 'daily_create_action_test_partition', '5 0 * * *', $$ SELECT public.create_daily_action_test_partition(); $$, 'action' ); ``` ## 技巧 ### 命令行查询单字段 ```sql 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 ``` ### 初始化sql 在`docker`映射阶段把`sql`文件映射到`/docker-entrypoint-initdb.d`中 ```text -v $(pwd)/init-db/:/docker-entrypoint-initdb.d/ ``` 然后在sql文件中最前面加上以下sql就可以完成建库的时候初始化sql 以下是`test`库的初始化 ```sql 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 ``` ### 拼接字符串 ```sql 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; ``` ### 性能排查 ```sql 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; ```