Merge pull request '新增共享 PG 的 SQL 级指标与 dev 告警分流' (#173 ) from feat/shared-pg-sql-metrics into main

Reviewed-on: #173
新增主库落在 pve1（HDD）时的回切提示告警
2026-10-01 16:33:05 +00:00 · 2026-10-01 16:30:45 +00:00 · 2026-10-01 16:20:16 +00:00 · 2026-10-01 16:20:16 +00:00 · 2026-10-01 16:20:15 +00:00 · 2026-10-01 16:20:15 +00:00
33 changed files with 2087 additions and 18 deletions
@@ -18,3 +18,13 @@ OCI digest 固定镜像。

 Flux 等待 ExternalSecret 和 Deployment 就绪；任何前置缺失都会使该
 Kustomization 保持 NotReady，而不会回退到明文 Secret。
+
+## 镜像更新
+
+`panxiao81/backstage` 的 CI 在 main push 后只推送 `:latest`。
+`platform/flux-image-automation` 跟踪 `latest` 背后的 digest，由 `flux-bot`
+直接提交到本仓库 main，不经过 PR。`deployment.yaml` 中 image 行的
+`$imagepolicy` 注释是 setter 标记，删除后自动更新会静默停止。
+
+旧 digest 失去 tag 后会被 zot GC 回收（24h），因此不要把 Deployment 手工固定到
+一个已不是 `latest` 的 digest 并长期保留。
@@ -27,7 +27,7 @@ spec:
          type: RuntimeDefault
      containers:
        - name: backstage
-          image: zot.ad.ddupan.top/panxiao81/backstage@sha256:008a3ccf832c9ee061ef03766022789a7539bd9d001a658af2e9f3ff59a9a5cc
+          image: zot.ad.ddupan.top/panxiao81/backstage:latest@sha256:fadd1ac9eaec52eddf105a81f5750878ca1b6fe49e0c6a987c346e501c59b95d # {"$imagepolicy": "flux-system:backstage"}
          imagePullPolicy: IfNotPresent
          env:
            - name: BACKSTAGE_BASE_URL
@@ -1,14 +1,45 @@
-# Hydra 与 OIDC Login/Consent PoC
+# Hydra 与独立 IAM 登录接入

-本目录提供独立 Hydra 签发服务，以及一个薄的 **OIDC 上游适配器**。当前上游配置为
-Authelia；适配器不连接 LDAP，也不管理用户目录。Samba AD、密码和 MFA 继续由现有
-Authelia 链路负责。第一轮只接入人类和 Gitea，不实现 agent 动态授权。
+本目录提供独立 Hydra 签发服务。当前分支将实验性 Hydra 登录入口接至 Spring `iam-login`
+开发实例，由其执行 AD 密码、WebAuthn 和授权确认；不改变 issuer、Gitea 登录源或数据库。
+旧 Go OIDC 适配器继续部署以便回退，Gitea 的直接 Authelia 登录源也保留。
+该配置需经 PR 合并与 Flux 应用后才生效；下面原有 Go/Authelia 说明保留为回退路径资料。

 ```text
-Gitea → Hydra → OIDC Login/Consent → Authelia → Samba AD
-      ← OIDC ← 经验证的上游身份 ← OIDC callback
+Gitea → Hydra → iam-login（Tailscale 开发实例）→ Samba AD + WebAuthn
 ```

+## Spring 开发路径验收
+
+源码版本：[iam-login 5f49a7c](https://git.ddupan.top/panxiao81/iam-login/commit/5f49a7c)。
+开发 UI 是 `https://laptop.tail7e769.ts.net:18082`，已有 Passkey RP ID 保持不变。
+Hydra 回调为 `/oauth2/start`、`/oauth2/consent`、`/oauth2/logout`；默认注销回到 `/signin`。
+浏览器需要 Tailscale 连通性；开发服务暂由本地 JVM 容器运行，并非生产 Native 部署。
+
+开发服务通过 `kubectl -n hydra port-forward --address 127.0.0.1 service/hydra-admin 18445:4445`
+访问私有 Admin API。转发需在 Hydra 滚动更新后重连；验收运行器会循环重建该通道。
+该通道与本地容器是临时验收依赖，必须保持运行；不修改 NetworkPolicy、
+不新增 Admin HTTPRoute。临时运行配置与旧版回退备份仅保存在受限本地运行目录，不提交秘密。
+
+验收前已只读核对目标 Gitea 账号的现有 Hydra 外部关联，并将旧 sub 显式绑定到 AD
+稳定主体；不按邮箱重建关联、不修改 Gitea 账号或仓库权限。客户端管理仅允许有效 MFA 且
+直接属于 AD Domain Admins 的用户；这是验收期明确指定的粗粒度管理组。
+
+从 Gitea `/user/oauth2/hydra` 发起，应进入新密码/Passkey 页，确认授权后返回原账号，核对
+仓库权限。当前 Gitea client 未登记 front/back-channel 或 post-logout 回调，不能声称 Gitea 会话会
+随 IAM 注销。应用的注销协议兼容性需单独验收。旧 `authelia` 登录源始终保留。
+
+开发配置关闭 Boot 默认 LDAP health indicator：它未配置目录连接，不对应同用户 bind 的
+AD 仓储。总健康检查仍验证数据库，真实 AD 认证由本次人类登录验收，不将 readiness 当作
+AD 凭据有效性的证明。
+
+Hydra 的 login/consent URL 是全局配置，本次影响全部经 Hydra 的登录请求（当前接入 Gitea），
+不影响直接走 Authelia 的应用。不要在无法访问 Tailscale 开发实例时合并此切换。
+验收失败时 revert 本次回调变更，恢复旧 Go 的 `/login`、`/consent`；不删除客户端、签名密钥、
+数据库、用户关联或 Passkey。最终 `auth.ddupan.top` 同源部署另开 PR，不在此次切换范围内。
+
+## 原 Go/Authelia 路径与回退资料
+
 目标入口：

 - `https://hydra.ad.ddupan.top`：Hydra 公共 OAuth2/OIDC endpoint。
@@ -12,8 +12,10 @@ urls:
  self:
    issuer: https://hydra.ad.ddupan.top
    public: https://hydra.ad.ddupan.top
-  login: https://hydra-login.ad.ddupan.top/login
-  consent: https://hydra-login.ad.ddupan.top/consent
+  login: https://laptop.tail7e769.ts.net:18082/oauth2/start
+  consent: https://laptop.tail7e769.ts.net:18082/oauth2/consent
+  logout: https://laptop.tail7e769.ts.net:18082/oauth2/logout
+  post_logout_redirect: https://laptop.tail7e769.ts.net:18082/signin
 ttl:
  access_token: 15m
  id_token: 15m
@@ -0,0 +1,16 @@
+apiVersion: kustomize.toolkit.fluxcd.io/v1
+kind: Kustomization
+metadata:
+  name: flux-image-automation
+  namespace: flux-system
+spec:
+  dependsOn:
+    - name: external-secrets
+  interval: 10m
+  path: ./platform/flux-image-automation
+  prune: true
+  sourceRef:
+    kind: GitRepository
+    name: flux-system
+  timeout: 5m
+  wait: true
@@ -8,6 +8,7 @@ resources:
  - apps/external-secrets.yaml
  - apps/gitea.yaml
  - apps/backstage.yaml
+  - apps/flux-image-automation.yaml
  - apps/http-echo.yaml
  - apps/openebs.yaml
  - apps/nats.yaml
@@ -74,6 +74,6 @@ sudo journalctl -u homelab-data-collect.service --since '2 hours ago'

 备份年龄不能证明恢复能力或 WAL 连续性；定期隔离恢复演练仍必要。证书指标读取磁盘文件，
 不证明运行进程已重载该证书。dev 检查不验证外部 DNS/TLS 路径、不执行写入。
-SQL 级 exporter、异地备份及应用迁移不在本次范围。
+SQL 级指标见 [共享 PG](../shared-postgresql/README.md#sql-级指标2026-10-01-主机端上线)；异地备份及应用迁移不在本次范围。

 跨服务状态见 [共享 PG wiki](https://git.ddupan.top/panxiao81/homelab-wiki/src/branch/main/services/shared-postgresql.md)。
@@ -69,7 +69,10 @@ Ayatori 单独使用 `kv/infra/postgresql/ayatori/{prod,dev}`，仅有 username/
 6. `bootstrap-backup.yml` 验证 WAL 与首备成功后启用每日 timer；`proxy.yml` 要求唯一 primary 才发布入口。
 7. DNS 由 `infrastructure/dns/records.yml` 与 Samba `provision-dc.yml --tags dns` 管理。
 8. `controller.yml` 安装 PG 证书每日续签调度，预检通过才启用。
-9. Ayatori 独立控制面就绪后按 [接入说明](ayatori/README.md) 注册；CNPG 迁移另行按应用验收。
+9. `monitoring.yml` 激活 SQL 指标：先由 `site.yml` 写入模板，再执行本 play。它只在配置未生效时
+   向 Patroni 发 HUP、对 pending 成员 `patronictl restart --pending`（primary 原位重启，不 failover，
+   写入短暂中断）或重启 dev；随后在可写实例创建监控角色与扩展并安装 exporter。不需要 Bao。
+10. Ayatori 独立控制面就绪后按 [接入说明](ayatori/README.md) 注册；CNPG 迁移另行按应用验收。

 普通 `site.yml` 不代表所有配置已在线激活；尚未实现通用参数变更的 rolling restart。
 专用 `renew-certificates.yml` 只处理证书，60 天有效期、剩余不足 14 天续签；逐主机检查服务，
@@ -97,10 +100,30 @@ Ayatori 角色，随后停库清理；不覆盖生产 PGDATA。全站恢复及

 现有 VictoriaMetrics/Alertmanager 已采集生产两节点 Patroni 原生指标，规则位于
 `platform/observability/metrics/{scrapes,rules}/shared-postgresql.yaml`。覆盖成员不可采集、无可见主库、
-多主、replica 未流复制及 PG 进程停止。没有部署另一套监控栈；数据库 SQL 级 exporter 仍后置。dev SQL、备份年龄、证书和续签检查的主机采集已于 2026-09-27 部署，
+多主、replica 未流复制及 PG 进程停止。没有部署另一套监控栈。dev SQL、备份年龄、证书和续签检查的主机采集已于 2026-09-27 部署，
 新增规则已随 [PR #166](https://git.ddupan.top/panxiao81/homelab-infra/pulls/166) 合并并由 Flux 接管；
 阈值、验证边界和排障见 [维护监控](../shared-data-monitoring/README.md)。

+### SQL 级指标（2026-10-01 主机端上线）
+
+每个实例在自身主机上运行 [prometheus-community/postgres_exporter](https://github.com/prometheus-community/postgres_exporter)
+（`pg_exporter_version`，安装包与官方 `sha256sums.txt` 同源校验）：prod 为 laptop/pve1 的 `:9187`，
+dev 为 laptop `:9188`，仅监听实例内网地址。exporter 以实例 OS 用户走本地 socket，由
+`local all homelab_monitor peer map=monitor` 与 ident 映射登录到 `homelab_monitor`
+（仅 `pg_monitor`、无密码、`CONNECTION LIMIT 3`）。这只约束 exporter 自己的查询，**不是隔离边界**：
+同一 OS 用户仍可经 peer 以 superuser 登录，exporter 被攻破等同实例属主；systemd
+`InaccessiblePaths` 只让它读不到数据目录、私钥与 pgpass。prod/dev 启用 `pg_stat_statements`。
+
+规则 `platform/observability/metrics/rules/shared-postgresql-sql.yaml`（PrometheusRule）以上游
+`postgres_mixin` 为底改写：采集/SQL 不通、复制回放落后 > 1 MiB（与 failover 阈值一致）、主库复制槽
+保留 > 1 GiB、主库落在 pve1（HDD）超过 15 分钟、WAL 归档失败、XID/MXID 年龄、客户端连接 > 80%、空闲事务 > 10 分钟、事务 > 1 小时、
+死锁、频繁请求 checkpoint。目标带 `env` 标签，Alertmanager 把 `env="dev"` 送到独立 dev 频道。
+
+2026-10-01 验证：隔离集成测试覆盖 peer+ident 登录（主/备/dev）、读取 `pg_stat_statements`、
+监控角色不可写；线上激活时两个 prod 成员按 pending 重启，时间线未变（未切换）；三个端点 `pg_up 1`、
+无采集失败，exporter 各约 8 MiB；重跑 `changed=0`；vmagent Pod 可达三个端点。规则 promtool 场景通过。
+`monitoring.yml --check` 不会显示 HUP、restart 与建角色（均为 command 任务），只能作参考。
+
 2026-09-25 已验证：

 - 真实 prod 主从、dev 初始化；Ayatori 管理账号 verify-full TLS/权限校验；生产稳定入口 SQL 写入。
@@ -19,6 +19,7 @@ pg_profiles:
    user: pgprod
    port: 5432
    api_port: 8008
+    exporter_port: 9187
    dns: pg-prod.ad.ddupan.top
    memory_high: 768M
    memory_max: 1G
@@ -28,6 +29,7 @@ pg_profiles:
    user: pgdev
    port: 5433
    dns: pg-dev.ad.ddupan.top
+    exporter_port: 9188
    memory_high: 256M
    memory_max: 384M
    shared_buffers: 32MB
@@ -38,3 +40,8 @@ pg_replication_addresses: ['192.168.10.127/32', '10.60.0.20/32']
 pg_repo_host: 10.60.0.21
 pg_repo_user: pgbackup
 pg_repo_path: /var/lib/homelab-pgbackrest
+# SQL 指标：exporter 以实例 OS 用户经本地 socket 连接，peer + ident 映射到只读监控角色，
+# 因此无需密码或 Bao；角色仅有 pg_monitor，不复用实例管理账号。
+pg_monitor_user: homelab_monitor
+# 升级只改版本；安装包与同版本官方 sha256sums.txt 一起下载校验，不写死摘要。
+pg_exporter_version: 0.20.1
@@ -0,0 +1,81 @@
+---
+# SQL 指标：激活监控所需的 HBA/ident 与 pg_stat_statements，创建只读监控角色并安装 exporter。
+# 不需要 Bao：模板先由 site.yml 写入；本 play 只做激活与验证。会重启实例（仅在预加载库未生效时），
+# 因此不放进 site.yml。重复执行应为 changed=0。
+- name: 激活实例监控配置
+  hosts: pg_hosts
+  become: true
+  gather_facts: false
+  serial: 1
+  any_errors_fatal: true
+  tasks:
+    - name: 逐实例激活
+      ansible.builtin.include_tasks: tasks/monitoring-activate.yml
+      loop: "{{ pg_instances }}"
+      loop_control:
+        loop_var: pg_instance
+
+- name: 重启 pending 的生产成员以加载 pg_stat_statements
+  hosts: pg-laptop
+  become: true
+  become_user: "{{ pg_profiles.prod.user }}"
+  gather_facts: false
+  vars:
+    pg_patronictl: [/opt/homelab-patroni/bin/patronictl, -c, "{{ pg_config_root }}/prod/patroni.yml"]
+  tasks:
+    # JSON 输出会省略空值字段：没有 pending 的成员不带 "Pending restart" 键。
+    - name: 读取集群成员状态
+      ansible.builtin.command:
+        argv: "{{ pg_patronictl + ['list', '-f', 'json'] }}"
+      register: pg_members
+      changed_when: false
+      check_mode: false
+    # Patroni 在原位重启 primary，不触发 failover；写入会短暂中断，客户端需重试。
+    - name: 仅重启 pending restart 的成员
+      ansible.builtin.command:
+        argv: "{{ pg_patronictl + ['restart', 'pg-prod', '--pending', '--force'] }}"
+      when: pg_members.stdout | from_json | selectattr('Pending restart', 'defined') | selectattr('Pending restart', 'equalto', '*') | list | length > 0
+      changed_when: true
+    - name: 等待两个成员恢复且无 pending
+      ansible.builtin.command:
+        argv: "{{ pg_patronictl + ['list', '-f', 'json'] }}"
+      register: pg_members_after
+      changed_when: false
+      when: not ansible_check_mode
+      retries: 30
+      delay: 4
+      until: >-
+        (pg_members_after.stdout | from_json | length) == 2
+        and (pg_members_after.stdout | from_json | selectattr('Pending restart', 'defined') | selectattr('Pending restart', 'equalto', '*') | list | length) == 0
+        and (pg_members_after.stdout | from_json | selectattr('State', 'in', ['running', 'streaming']) | list | length) == 2
+
+- name: 创建监控角色并安装 exporter
+  hosts: pg_hosts
+  become: true
+  gather_facts: false
+  tasks:
+    - name: 下载 postgres_exporter 并校验同版本官方摘要
+      ansible.builtin.get_url:
+        url: "{{ pg_exporter_base }}/postgres_exporter-{{ pg_exporter_version }}.linux-amd64.tar.gz"
+        dest: /opt/homelab-postgres-exporter.tar.gz
+        checksum: "sha256:{{ pg_exporter_base }}/sha256sums.txt"
+        mode: '0644'
+      vars:
+        pg_exporter_base: https://github.com/prometheus-community/postgres_exporter/releases/download/v{{ pg_exporter_version }}
+      register: pg_exporter_download
+      retries: 5
+      delay: 10
+      until: pg_exporter_download is succeeded
+    - name: 解压 exporter（保留上游许可证）
+      ansible.builtin.unarchive:
+        src: /opt/homelab-postgres-exporter.tar.gz
+        dest: /opt
+        remote_src: true
+        creates: /opt/postgres_exporter-{{ pg_exporter_version }}.linux-amd64/postgres_exporter
+      # check 模式不下载，首次时没有可解压的文件。
+      when: not (ansible_check_mode and pg_exporter_download is changed)
+    - name: 逐实例配置
+      ansible.builtin.include_tasks: tasks/monitoring-exporter.yml
+      loop: "{{ pg_instances }}"
+      loop_control:
+        loop_var: pg_instance
@@ -71,7 +71,7 @@
    owner: root
    group: "{{ pg_profile.user }}"
    mode: '0640'
-  loop: "{{ ['patroni.yml', 'pgbackrest.conf'] if pg_instance == 'prod' else ['postgresql.conf', 'pg_hba.conf'] }}"
+  loop: "{{ ['patroni.yml', 'pgbackrest.conf'] if pg_instance == 'prod' else ['postgresql.conf', 'pg_hba.conf', 'pg_ident.conf'] }}"
  no_log: true
 - name: 安装实例专用服务（不自动启动或重启）
  ansible.builtin.template:
@@ -66,6 +66,8 @@ postgresql:
    unix_socket_directories: {{ pg_socket_dir }}
    unix_socket_permissions: '0700'
    password_encryption: scram-sha-256
+    # postmaster 级参数：变更后需由 monitoring.yml 执行 Patroni pending restart。
+    shared_preload_libraries: pg_stat_statements
    shared_buffers: {{ pg_profile.shared_buffers }}
    work_mem: 4MB
    maintenance_work_mem: 64MB
@@ -76,6 +78,7 @@ postgresql:
    log_statement: none
    log_min_error_statement: panic
  pg_hba:
+    - local all {{ pg_monitor_user }} peer map=monitor
    - local all {{ pg_profile.user }} peer
    - local replication {{ pg_profile.user }} peer
    - local replication replicator scram-sha-256
@@ -88,6 +91,9 @@ postgresql:
 {% endfor %}
    - host all all 0.0.0.0/0 reject
    - host all all ::/0 reject
+  # 只有实例 OS 用户（exporter 运行身份）可映射为监控角色。
+  pg_ident:
+    - monitor {{ pg_profile.user }} {{ pg_monitor_user }}
  remove_data_directory_on_rewind_failure: false
  remove_data_directory_on_diverged_timelines: false
 watchdog:
@@ -1,3 +1,4 @@
+local all {{ pg_monitor_user }} peer map=monitor
 local all {{ pg_profile.user }} peer
 {% for cidr in pg_client_cidrs %}
 hostssl all all {{ cidr }} scram-sha-256
@@ -0,0 +1,2 @@
+# 只有实例 OS 用户（exporter 运行身份）可映射为监控角色。
+monitor {{ pg_profile.user }} {{ pg_monitor_user }}
@@ -3,6 +3,7 @@ port = {{ pg_profile.port }}
 unix_socket_directories = '{{ pg_socket_dir }}'
 unix_socket_permissions = 0700
 hba_file = '{{ pg_config_dir }}/pg_hba.conf'
+ident_file = '{{ pg_config_dir }}/pg_ident.conf'
 password_encryption = 'scram-sha-256'
 shared_buffers = '{{ pg_profile.shared_buffers }}'
 max_connections = {{ pg_profile.max_connections }}
@@ -16,3 +17,5 @@ ssl_ca_file = '{{ pg_config_dir }}/ca.crt'
 archive_mode = off
 log_statement = none
 log_min_error_statement = panic
+# postmaster 级参数：变更后需由 monitoring.yml 重启 dev 实例。
+shared_preload_libraries = 'pg_stat_statements'
@@ -0,0 +1,73 @@
+---
+- name: 设置现有实例路径
+  ansible.builtin.set_fact:
+    pg_profile: "{{ pg_profiles[pg_instance] }}"
+    pg_config_dir: "{{ pg_config_root }}/{{ pg_instance }}"
+    pg_socket_dir: "/run/homelab-postgresql-{{ pg_instance }}"
+- name: 激活前必须存在运行中的受管服务
+  ansible.builtin.command:
+    argv: [systemctl, is-active, "homelab-postgresql-{{ pg_instance }}.service"]
+  changed_when: false
+  check_mode: false
+# 生产：Patroni 只在自身 reload 时把本地配置写成数据目录里的 postgresql.conf/pg_ident.conf，
+# 所以文件视图能说明 Patroni 是否已接收新配置；预加载库是否已生效另看 current_setting。
+# 重启前 pg_file_settings 对该行报 "setting could not be applied"，属预期，不能按 error 过滤。
+# dev：ident_file 是 postmaster 级参数，视图读取的是“当前生效”的那个文件，
+# 所以启动时仍指向旧路径就会读不到映射；两列都为真才算已加载。
+- name: 读取监控配置的激活状态
+  ansible.builtin.command:
+    argv:
+      - "{{ pg_bin_dir }}/psql"
+      - -X
+      - -At
+      - -h
+      - "{{ pg_socket_dir }}"
+      - -p
+      - "{{ pg_profile.port | string }}"
+      - -d
+      - postgres
+      - -c
+      - >-
+        SELECT
+        EXISTS (SELECT 1 FROM pg_ident_file_mappings WHERE map_name = 'monitor' AND error IS NULL)
+        AND EXISTS (SELECT 1 FROM pg_file_settings WHERE name = 'shared_preload_libraries'
+                    AND setting ~ 'pg_stat_statements'),
+        current_setting('shared_preload_libraries') ~ 'pg_stat_statements'
+  become: true
+  become_user: "{{ pg_profile.user }}"
+  changed_when: false
+  check_mode: false
+  register: pg_monitor_state
+- name: 让 Patroni 接收新的本地配置（reload，不重启）
+  ansible.builtin.command:
+    argv: [systemctl, kill, --kill-whom=main, --signal=HUP, homelab-postgresql-prod.service]
+  when: pg_instance == 'prod' and pg_monitor_state.stdout.split('|')[0] != 't'
+  changed_when: true
+- name: 等待 Patroni 写出新配置
+  ansible.builtin.command:
+    argv: "{{ pg_monitor_state.cmd }}"
+  become: true
+  become_user: "{{ pg_profile.user }}"
+  when: pg_instance == 'prod' and pg_monitor_state.stdout.split('|')[0] != 't' and not ansible_check_mode
+  changed_when: false
+  register: pg_monitor_reloaded
+  retries: 10
+  delay: 3
+  until: pg_monitor_reloaded.stdout.split('|')[0] == 't'
+# dev 没有 Patroni；预加载库与 ident_file 都是 postmaster 级参数，只能重启。
+- name: 重启 dev 以加载监控配置
+  ansible.builtin.systemd_service:
+    name: homelab-postgresql-dev.service
+    state: restarted
+  when: pg_instance == 'dev' and pg_monitor_state.stdout != 't|t'
+- name: 检查 dev 重启后监控配置已生效
+  ansible.builtin.command:
+    argv: "{{ pg_monitor_state.cmd }}"
+  become: true
+  become_user: "{{ pg_profile.user }}"
+  when: pg_instance == 'dev' and not ansible_check_mode
+  changed_when: false
+  register: pg_monitor_dev
+  retries: 10
+  delay: 3
+  until: pg_monitor_dev.rc == 0 and pg_monitor_dev.stdout == 't|t'
@@ -0,0 +1,65 @@
+---
+- name: 设置现有实例路径
+  ansible.builtin.set_fact:
+    pg_profile: "{{ pg_profiles[pg_instance] }}"
+    pg_config_dir: "{{ pg_config_root }}/{{ pg_instance }}"
+    pg_parent_dir: "{{ pg_data_root }}/{{ pg_instance }}"
+    pg_socket_dir: "/run/homelab-postgresql-{{ pg_instance }}"
+    pg_psql: ["{{ pg_bin_dir }}/psql", -X, -At, -v, ON_ERROR_STOP=1, -h, "/run/homelab-postgresql-{{ pg_instance }}",
+              -p, "{{ pg_profiles[pg_instance].port | string }}", -d, postgres]
+# 角色与扩展只在可写实例上创建，standby 经复制获得。
+- name: 检查监控角色与扩展是否符合声明
+  ansible.builtin.command:
+    argv: "{{ pg_psql + ['-c', pg_monitor_check] }}"
+  vars:
+    pg_monitor_check: >-
+      SELECT pg_is_in_recovery(),
+      COALESCE((SELECT rolcanlogin AND NOT rolsuper AND NOT rolcreaterole AND NOT rolcreatedb
+                AND NOT rolreplication AND NOT rolbypassrls AND rolconnlimit = 3 AND rolpassword IS NULL
+                AND pg_has_role(oid, 'pg_monitor', 'MEMBER')
+                FROM pg_authid WHERE rolname = '{{ pg_monitor_user }}'), false)
+      AND EXISTS (SELECT 1 FROM pg_extension WHERE extname = 'pg_stat_statements')
+  become: true
+  become_user: "{{ pg_profile.user }}"
+  changed_when: false
+  check_mode: false
+  register: pg_monitor_role
+- name: 创建或修正监控角色（无密码，仅能经本地 ident 映射登录）
+  ansible.builtin.command:
+    argv: "{{ pg_psql }}"
+    stdin: |
+      SELECT 'CREATE ROLE {{ pg_monitor_user }}'
+      WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = '{{ pg_monitor_user }}') \gexec
+      ALTER ROLE {{ pg_monitor_user }} LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS
+        CONNECTION LIMIT 3 PASSWORD NULL;
+      GRANT pg_monitor TO {{ pg_monitor_user }};
+      CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
+  become: true
+  become_user: "{{ pg_profile.user }}"
+  when: pg_monitor_role.stdout == 'f|f'
+  changed_when: true
+- name: 安装实例 exporter 服务
+  ansible.builtin.template:
+    src: postgres-exporter.service.j2
+    dest: "/etc/systemd/system/homelab-postgresql-{{ pg_instance }}-exporter.service"
+    mode: '0644'
+  register: pg_exporter_unit
+- name: 启用 exporter
+  ansible.builtin.systemd_service:
+    name: "homelab-postgresql-{{ pg_instance }}-exporter.service"
+    daemon_reload: "{{ pg_exporter_unit is changed }}"
+    enabled: true
+    state: "{{ 'restarted' if pg_exporter_unit is changed else 'started' }}"
+  # check 模式下 unit 文件尚未写入，systemd 找不到服务。
+  when: not (ansible_check_mode and pg_exporter_unit is changed)
+# 同时证明 HBA/ident 已加载、角色可登录、exporter 能查询。
+- name: 检查 exporter 已连上数据库
+  ansible.builtin.uri:
+    url: "http://{{ pg_address }}:{{ pg_profile.exporter_port }}/metrics"
+    return_content: true
+  register: pg_exporter_metrics
+  changed_when: false
+  when: not ansible_check_mode
+  retries: 10
+  delay: 3
+  until: pg_exporter_metrics.status == 200 and pg_exporter_metrics.content is search('(?m)^pg_up 1$')
@@ -0,0 +1,27 @@
+[Unit]
+Description=PostgreSQL {{ pg_instance }} SQL metrics
+After=homelab-postgresql-{{ pg_instance }}.service
+[Service]
+# 本地 socket 上 TLS 无意义，sslmode=disable 只是避免驱动默认尝试 TLS。
+# 以实例 OS 用户运行才能进入 0700 的 socket 目录；peer + ident 把它映射成只读监控角色。
+# 监控角色只约束 exporter 自己发出的查询；这不是隔离边界——同一 OS 用户仍可经 peer 以
+# superuser 登录，exporter 被攻破即等同实例属主。InaccessiblePaths 只是不让它直接读写
+# 数据目录、私钥与 pgpass。暴露面因此仅限内网监听地址。
+User={{ pg_profile.user }}
+Group={{ pg_profile.user }}
+Environment="DATA_SOURCE_NAME=host={{ pg_socket_dir }} port={{ pg_profile.port }} user={{ pg_monitor_user }} dbname=postgres sslmode=disable application_name=postgres_exporter"
+ExecStart=/opt/postgres_exporter-{{ pg_exporter_version }}.linux-amd64/postgres_exporter \
+  --web.listen-address={{ pg_address }}:{{ pg_profile.exporter_port }} \
+  --collector.database_wraparound --collector.long_running_transactions \
+  --collector.stat_checkpointer --collector.stat_wal_receiver --collector.stat_statements
+Restart=on-failure
+RestartSec=5
+InaccessiblePaths={{ pg_parent_dir }} {{ pg_config_dir }}
+NoNewPrivileges=true
+ProtectSystem=strict
+ProtectHome=true
+PrivateTmp=true
+PrivateDevices=true
+MemoryMax=64M
+[Install]
+WantedBy=multi-user.target
@@ -88,9 +88,9 @@ def main():
                       pg_etcd_endpoints=[f'{h["etcd_address"]}:23379' for h in hosts.values()],
                       pg_config_dir='/node/config',pg_parent_dir='/node',pg_data_dir='/node/data',pg_socket_dir='/node/socket',
                       pg_bin_dir='/usr/lib/postgresql/18/bin',pg_replication_addresses=['127.0.0.0/8'],pg_client_cidrs=['127.0.0.0/8'],
-                       pg_repo_remote=False,pg_repo_path='/repo')
+                       pg_repo_remote=False,pg_repo_path='/repo',pg_monitor_user='homelab_monitor')
                values[name]=v
-                for template in (['postgresql.conf','pg_hba.conf'] if name=='dev' else ['patroni.yml','pgbackrest.conf']):
+                for template in (['postgresql.conf','pg_hba.conf','pg_ident.conf'] if name=='dev' else ['patroni.yml','pgbackrest.conf']):
                    (cfg/template).write_text(env.get_template(template+'.j2').render(**v))
                for folder in ['socket','backup-lock','backup-spool']:(d/folder).mkdir()
            (pg/'repo').mkdir();(pg/'restore').mkdir();(pg/'pitr').mkdir()
@@ -131,6 +131,20 @@ def main():
            initial_backup=json.loads(backrest('p1','--output=json','info').stdout)[0]['backup'][-1]['label']
            recovery_target=sql('p1','SELECT clock_timestamp()::text')
            print('PASS: separate dev, TLS verify-full, non-superuser Ayatori native privileges, initial backup',flush=True)
+            # 与 monitoring.yml 相同的声明：无密码，只能由实例 OS 用户经 peer + ident 映射登录。
+            monitor_role="CREATE ROLE homelab_monitor LOGIN CONNECTION LIMIT 3 PASSWORD NULL; GRANT pg_monitor TO homelab_monitor; CREATE EXTENSION pg_stat_statements;"
+            sql('p1',monitor_role);sql('dev',monitor_role)
+            def monitor(name,query):
+                return run(['docker','exec','-i',prefix+'-'+name,'psql','-X','-v','ON_ERROR_STOP=1','-At','-h','/node/socket','-p','25432','-U','homelab_monitor','postgres'],input=query).stdout.strip()
+            for name in ['p1','p2','dev']:
+                wait_for(lambda: monitor(name,"SELECT current_setting('shared_preload_libraries')||(SELECT count(*)>=0 FROM pg_stat_statements)")=='pg_stat_statementstrue')
+            for name in ['p1','dev']:
+                try:
+                    monitor(name,'CREATE TABLE public.monitor_write(id int)')
+                    raise AssertionError(f'monitor role could write on {name}')
+                except subprocess.CalledProcessError:
+                    pass
+            print('PASS: monitor role logs in via peer+ident on primary/standby/dev, reads pg_stat_statements, cannot write',flush=True)
            proxycfg=w/'proxy.cfg'
            # 文件父目录仍由调用者拥有，新增代理配置不触碰 PG 文件。
            proxycfg.write_text(env.get_template('haproxy.cfg.j2').render(pg_proxy_bind='127.0.2.10:26432',pg_proxy_members=[dict(name=n,address=values[n]['pg_address'],port=25432,api_port=28008) for n in ['p1','p2']]))
@@ -0,0 +1,26 @@
+# CI pushes only :latest from main; follow the digest behind it. Old digests
+# lose their tag and are collected by zot GC, so the deployment must keep
+# tracking the current one.
+apiVersion: image.toolkit.fluxcd.io/v1
+kind: ImageRepository
+metadata:
+  name: backstage
+  namespace: flux-system
+spec:
+  image: zot.ad.ddupan.top/panxiao81/backstage
+  interval: 1m
+---
+apiVersion: image.toolkit.fluxcd.io/v1
+kind: ImagePolicy
+metadata:
+  name: backstage
+  namespace: flux-system
+spec:
+  imageRepositoryRef:
+    name: backstage
+  filterTags:
+    pattern: '^latest$'
+  policy:
+    alphabetical: {}
+  digestReflectionPolicy: Always
+  interval: 1m
@@ -0,0 +1,19 @@
+# Gitea token of the private `flux-bot` user: collaborator with write on
+# homelab-infra only, token scoped to write:repository. Stored in OpenBao as
+# username/password because that is the basic-auth Secret shape Flux reads.
+apiVersion: external-secrets.io/v1
+kind: ExternalSecret
+metadata:
+  name: homelab-infra-write
+  namespace: flux-system
+spec:
+  refreshInterval: 1h
+  secretStoreRef:
+    kind: ClusterSecretStore
+    name: openbao
+  target:
+    creationPolicy: Owner
+    name: homelab-infra-write
+  dataFrom:
+    - extract:
+        key: k8s/flux-image-automation
@@ -0,0 +1,15 @@
+# Write-capable source used only by ImageUpdateAutomation. The flux-system
+# GitRepository stays anonymous and read-only.
+apiVersion: source.toolkit.fluxcd.io/v1
+kind: GitRepository
+metadata:
+  name: homelab-infra-write
+  namespace: flux-system
+spec:
+  interval: 10m
+  ref:
+    branch: main
+  secretRef:
+    name: homelab-infra-write
+  timeout: 60s
+  url: http://gitea-http.gitea.svc.cluster.local:3000/panxiao81/homelab-infra.git
@@ -0,0 +1,28 @@
+# Commits digest changes straight to main (no PR): dev images are expected to
+# deploy as soon as CI pushes them. Scoped to apps/backstage so a stray setter
+# elsewhere cannot be rewritten.
+apiVersion: image.toolkit.fluxcd.io/v1
+kind: ImageUpdateAutomation
+metadata:
+  name: homelab-infra
+  namespace: flux-system
+spec:
+  interval: 1m
+  sourceRef:
+    kind: GitRepository
+    name: homelab-infra-write
+  git:
+    checkout:
+      ref:
+        branch: main
+    commit:
+      author:
+        name: flux-bot
+        email: [email protected]
+      messageTemplate: |
+        chore(image): update {{ range .Changed.Changes }}{{ .OldValue }} -> {{ .NewValue }} {{ end }}
+    push:
+      branch: main
+  update:
+    path: ./apps/backstage
+    strategy: Setters
@@ -0,0 +1,7 @@
+apiVersion: kustomize.config.k8s.io/v1beta1
+kind: Kustomization
+resources:
+  - external-secret.yaml
+  - gitrepository.yaml
+  - imageupdateautomation.yaml
+  - backstage.yaml
@@ -25,6 +25,8 @@ resources:
  - scrapes/shared-etcd.yaml
  - rules/shared-postgresql.yaml
  - scrapes/shared-postgresql.yaml
+  - rules/shared-postgresql-sql.yaml
+  - scrapes/shared-postgresql-sql.yaml
  - scrapes/platform-controllers.yaml
  - rules/platform-controllers.yaml
  - scrapes/cnpg.yaml
@@ -58,6 +58,7 @@ spec:
        - alert: SharedPostgresDevUnavailable
          expr: homelab_data_dev_sql_ready{job="shared-data-maintenance"} == 0
          for: 3m
-          labels: {severity: warning, service: shared-postgresql}
+          # env 供 Alertmanager 把 dev 分流到独立 channel。
+          labels: {severity: warning, service: shared-postgresql, env: dev}
          annotations:
            summary: 共享 PG 开发实例本地 SQL 不可用或处于只读状态
@@ -0,0 +1,118 @@
+# 以 postgres_exporter 上游 postgres_mixin 为底按本环境改写：实例停止、多主、角色变化已由
+# Patroni 规则（shared-postgresql.yaml）覆盖；QPS/缓存命中率/回滚数不是故障信号，未采用；
+# 上游 PostgresHasReplicationSlotUsed 会被 Patroni 在 standby 保留的非活跃槽持续触发，未采用。
+# 每条告警都带抓取目标的 env 标签，Alertmanager 按它把 dev 分流到独立 channel。
+apiVersion: monitoring.coreos.com/v1
+kind: PrometheusRule
+metadata:
+  name: shared-postgresql-sql
+  namespace: monitoring
+spec:
+  groups:
+    - name: shared-postgresql-sql
+      rules:
+        - alert: SharedPostgresSqlExporterDown
+          expr: up{job="shared-postgresql-sql"} == 0
+          for: 3m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 的 SQL exporter 无法采集'
+        # 进程停止已有 Patroni critical；这里覆盖进程在但 SQL 不通（HBA、连接耗尽、dev 实例）。
+        - alert: SharedPostgresSqlUnreachable
+          expr: pg_up{job="shared-postgresql-sql"} == 0
+          for: 2m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 无法以监控角色执行 SQL'
+        # 正常 primary 在 laptop ZFS SSD；pve1 的数据盘是 HDD。自动切换后主库安全落到 pve1
+        # 不会触发任何“无主/多主”规则（2026-09-30 的切换整整一天无人察觉），所以单独提示回切。
+        # 15m 留给计划内切换演练。
+        - alert: SharedPostgresPrimaryOnStandbyHost
+          expr: pg_replication_is_replica{job="shared-postgresql-sql", env="prod", member="pg-pve1"} == 0
+          for: 15m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: 共享 PG 生产主库运行在 pg-pve1（HDD），请确认切换原因并计划回切 laptop
+        - alert: SharedPostgresSqlCollectorFailing
+          expr: pg_scrape_collector_success{job="shared-postgresql-sql"} == 0 or pg_exporter_last_scrape_error{job="shared-postgresql-sql"} == 1
+          for: 10m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 部分 SQL 指标采集失败 {{ $labels.collector }}'
+        # 与 Patroni maximum_lag_on_failover（1 MiB）一致：持续超过即该 standby 不再是合格的切换候选。
+        - alert: SharedPostgresReplicationLagging
+          expr: pg_stat_replication_pg_wal_lsn_diff{job="shared-postgresql-sql"} > 1048576
+          for: 5m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} 副本 {{ $labels.application_name }} 回放落后超过 1 MiB，已不满足自动切换条件'
+        # 只看可写成员：standby 上 Patroni 维护的槽副本不反映真实保留压力。
+        # 1 GiB 是 max_slot_wal_keep_size（2GB）的一半，留出处理时间。
+        - alert: SharedPostgresReplicationSlotRetention
+          expr: >-
+            pg_replication_slots_pg_wal_lsn_diff{job="shared-postgresql-sql"} > 1073741824
+            and on(instance) pg_replication_is_replica{job="shared-postgresql-sql"} == 0
+          for: 10m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} 复制槽 {{ $labels.slot_name }} 保留 WAL 超过 1 GiB'
+        - alert: SharedPostgresWalArchiveFailing
+          expr: increase(pg_stat_archiver_failed_count{job="shared-postgresql-sql"}[15m]) > 0
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} WAL 归档失败，PITR 链可能中断'
+        # XID/MXID 上限约 2^31；超过一半说明 freeze 跟不上，超过 75% 已接近强制停写；critical 时不重复 warning。
+        - alert: SharedPostgresWraparoundRisk
+          expr: >-
+            (max without(datname) (pg_database_wraparound_age_datfrozenxid_seconds{job="shared-postgresql-sql"}) > 1e9
+            or max without(datname) (pg_database_wraparound_age_datminmxid_seconds{job="shared-postgresql-sql"}) > 1e9)
+            unless (max without(datname) (pg_database_wraparound_age_datfrozenxid_seconds{job="shared-postgresql-sql"}) > 1.6e9
+            or max without(datname) (pg_database_wraparound_age_datminmxid_seconds{job="shared-postgresql-sql"}) > 1.6e9)
+          for: 10m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} XID/MXID 年龄超过 10 亿'
+        - alert: SharedPostgresWraparoundImminent
+          expr: >-
+            max without(datname) (pg_database_wraparound_age_datfrozenxid_seconds{job="shared-postgresql-sql"}) > 1.6e9
+            or max without(datname) (pg_database_wraparound_age_datminmxid_seconds{job="shared-postgresql-sql"}) > 1.6e9
+          for: 5m
+          labels: {severity: critical, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} XID/MXID 接近回卷停写，需立即 VACUUM FREEZE'
+        - alert: SharedPostgresConnectionsHigh
+          expr: >-
+            sum without(datname, state, usename, application_name, backend_type, wait_event_type, wait_event)
+              (pg_stat_activity_count{job="shared-postgresql-sql", backend_type="client backend"})
+            / pg_settings_max_connections{job="shared-postgresql-sql"} > 0.8
+          for: 5m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 客户端连接超过 max_connections 的 80%'
+        # 长事务阻止 vacuum 回收并持有锁；idle in transaction 通常是应用忘记提交。
+        - alert: SharedPostgresIdleInTransaction
+          expr: >-
+            max without(datname, state, usename, application_name, backend_type, wait_event_type, wait_event)
+              (pg_stat_activity_max_tx_duration{job="shared-postgresql-sql", state=~"idle in transaction.*"}) > 600
+          for: 1m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 有事务空闲未提交超过 10 分钟'
+        - alert: SharedPostgresLongTransaction
+          expr: pg_long_running_transactions_oldest_timestamp_seconds{job="shared-postgresql-sql"} > 3600
+          for: 1m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 最老事务已运行超过 1 小时'
+        - alert: SharedPostgresDeadlocks
+          expr: sum without(datname) (increase(pg_stat_database_deadlocks{job="shared-postgresql-sql"}[15m])) > 0
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 15 分钟内出现死锁'
+        # 每日 pgBackRest full 会请求一次 checkpoint；每小时多于 4 次通常意味着 max_wal_size 偏小。
+        - alert: SharedPostgresCheckpointsRequested
+          expr: increase(pg_stat_checkpointer_num_requested_total{job="shared-postgresql-sql"}[1h]) > 4
+          for: 15m
+          labels: {severity: warning, service: shared-postgresql}
+          annotations:
+            summary: '共享 PG {{ $labels.env }} {{ $labels.member }} 频繁请求 checkpoint，max_wal_size 可能偏小'
@@ -0,0 +1,18 @@
+# postgres_exporter 跑在数据库主机上，按实例一个端口；env 标签供 Alertmanager 分流 dev。
+apiVersion: operator.victoriametrics.com/v1beta1
+kind: VMStaticScrape
+metadata:
+  name: shared-postgresql-sql
+  namespace: monitoring
+spec:
+  jobName: shared-postgresql-sql
+  targetEndpoints:
+    - targets: ['192.168.10.127:9187']
+      labels: {cluster: homelab-pg-prod, member: pg-laptop, env: prod}
+      interval: 30s
+    - targets: ['10.60.0.20:9187']
+      labels: {cluster: homelab-pg-prod, member: pg-pve1, env: prod}
+      interval: 30s
+    - targets: ['192.168.10.127:9188']
+      labels: {cluster: homelab-pg-dev, member: pg-laptop, env: dev}
+      interval: 30s
@@ -10,7 +10,7 @@ import sys
 import yaml

 groups = []
-for name in ("kubernetes-health", "host-health", "monitoring-delivery", "platform-controllers", "data-services", "openbao", "shared-data-maintenance"):
+for name in ("kubernetes-health", "host-health", "monitoring-delivery", "platform-controllers", "data-services", "openbao", "shared-data-maintenance", "shared-postgresql-sql"):
    path = pathlib.Path(sys.argv[1]) / f"{name}.yaml"
    groups.extend(yaml.safe_load(path.read_text())["spec"]["groups"])
 pathlib.Path(sys.argv[2]).write_text(yaml.safe_dump({"groups": groups}, allow_unicode=True))
@@ -125,6 +125,7 @@ tests:
        instance: host:9109
        severity: warning
        service: shared-postgresql
+        env: dev
      exp_annotations:
        summary: 共享 PG 开发实例本地 SQL 不可用或处于只读状态
 - name: dev 恢复后解除
@@ -0,0 +1,165 @@
+rule_files:
+- rules.yaml
+evaluation_interval: 1m
+tests:
+- name: dev SQL 不通时告警带 env=dev 以便分流
+  interval: 1m
+  input_series:
+  - series: pg_up{job="shared-postgresql-sql",instance="h:9188",cluster="homelab-pg-dev",member="pg-laptop",env="dev"}
+    values: '0x5'
+  alert_rule_test:
+  - eval_time: 5m
+    alertname: SharedPostgresSqlUnreachable
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'h:9188', cluster: homelab-pg-dev, member: pg-laptop, env: dev, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG dev pg-laptop 无法以监控角色执行 SQL
+- name: 回放落后持续超过 1 MiB 告警
+  interval: 1m
+  input_series:
+  - series: pg_stat_replication_pg_wal_lsn_diff{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",application_name="pg-laptop"}
+    values: '2097152x10'
+  alert_rule_test:
+  - eval_time: 8m
+    alertname: SharedPostgresReplicationLagging
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', member: pg-pve1, env: prod, application_name: pg-laptop, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod 副本 pg-laptop 回放落后超过 1 MiB，已不满足自动切换条件
+- name: 短暂落后不告警
+  interval: 1m
+  input_series:
+  - series: pg_stat_replication_pg_wal_lsn_diff{job="shared-postgresql-sql",instance="p:9187",env="prod",application_name="pg-laptop"}
+    values: '2097152x2 0x8'
+  alert_rule_test:
+  - eval_time: 8m
+    alertname: SharedPostgresReplicationLagging
+    exp_alerts: []
+- name: 复制槽积压只在主库告警
+  interval: 1m
+  input_series:
+  - series: pg_replication_slots_pg_wal_lsn_diff{job="shared-postgresql-sql",instance="p:9187",env="prod",slot_name="pg_laptop"}
+    values: '2147483648x15'
+  - series: pg_replication_is_replica{job="shared-postgresql-sql",instance="p:9187",env="prod"}
+    values: '0x15'
+  - series: pg_replication_slots_pg_wal_lsn_diff{job="shared-postgresql-sql",instance="r:9187",env="prod",slot_name="pg_pve1"}
+    values: '2147483648x15'
+  - series: pg_replication_is_replica{job="shared-postgresql-sql",instance="r:9187",env="prod"}
+    values: '1x15'
+  alert_rule_test:
+  - eval_time: 12m
+    alertname: SharedPostgresReplicationSlotRetention
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', env: prod, slot_name: pg_laptop, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod 复制槽 pg_laptop 保留 WAL 超过 1 GiB
+- name: 回卷 critical 时不重复 warning
+  interval: 1m
+  input_series:
+  - series: pg_database_wraparound_age_datfrozenxid_seconds{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app"}
+    values: '1700000000x15'
+  - series: pg_database_wraparound_age_datminmxid_seconds{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app"}
+    values: '1200000000x15'
+  alert_rule_test:
+  - eval_time: 12m
+    alertname: SharedPostgresWraparoundRisk
+    exp_alerts: []
+  - eval_time: 12m
+    alertname: SharedPostgresWraparoundImminent
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', member: pg-pve1, env: prod, severity: critical, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod pg-pve1 XID/MXID 接近回卷停写，需立即 VACUUM FREEZE
+- name: 回卷 warning
+  interval: 1m
+  input_series:
+  - series: pg_database_wraparound_age_datfrozenxid_seconds{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app"}
+    values: '1200000000x15'
+  alert_rule_test:
+  - eval_time: 12m
+    alertname: SharedPostgresWraparoundRisk
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', member: pg-pve1, env: prod, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod pg-pve1 XID/MXID 年龄超过 10 亿
+- name: 只计客户端连接
+  interval: 1m
+  input_series:
+  - series: pg_stat_activity_count{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app",state="active",usename="a",application_name="x",backend_type="client backend",wait_event_type="",wait_event=""}
+    values: '50x10'
+  - series: pg_stat_activity_count{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app",state="idle",usename="b",application_name="y",backend_type="client backend",wait_event_type="",wait_event=""}
+    values: '20x10'
+  - series: pg_stat_activity_count{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="",state="",usename="",application_name="",backend_type="autovacuum worker",wait_event_type="",wait_event=""}
+    values: '30x10'
+  - series: pg_settings_max_connections{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod"}
+    values: '80x10'
+  alert_rule_test:
+  - eval_time: 8m
+    alertname: SharedPostgresConnectionsHigh
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', member: pg-pve1, env: prod, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod pg-pve1 客户端连接超过 max_connections 的 80%
+- name: 后台进程不计入连接使用率
+  interval: 1m
+  input_series:
+  - series: pg_stat_activity_count{job="shared-postgresql-sql",instance="p:9187",env="prod",datname="app",state="active",usename="a",application_name="x",backend_type="client backend",wait_event_type="",wait_event=""}
+    values: '40x10'
+  - series: pg_stat_activity_count{job="shared-postgresql-sql",instance="p:9187",env="prod",datname="",state="",usename="",application_name="",backend_type="autovacuum worker",wait_event_type="",wait_event=""}
+    values: '40x10'
+  - series: pg_settings_max_connections{job="shared-postgresql-sql",instance="p:9187",env="prod"}
+    values: '80x10'
+  alert_rule_test:
+  - eval_time: 8m
+    alertname: SharedPostgresConnectionsHigh
+    exp_alerts: []
+- name: 死锁计数增长告警
+  interval: 1m
+  input_series:
+  - series: pg_stat_database_deadlocks{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app"}
+    values: '0 0 0 1 1 1'
+  alert_rule_test:
+  - eval_time: 5m
+    alertname: SharedPostgresDeadlocks
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', member: pg-pve1, env: prod, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod pg-pve1 15 分钟内出现死锁
+- name: 空闲事务超过 10 分钟
+  interval: 1m
+  input_series:
+  - series: pg_stat_activity_max_tx_duration{job="shared-postgresql-sql",instance="p:9187",member="pg-pve1",env="prod",datname="app",state="idle in transaction",usename="a",application_name="x",backend_type="client backend",wait_event_type="Client",wait_event="ClientRead"}
+    values: '700x5'
+  alert_rule_test:
+  - eval_time: 3m
+    alertname: SharedPostgresIdleInTransaction
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: 'p:9187', member: pg-pve1, env: prod, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG prod pg-pve1 有事务空闲未提交超过 10 分钟
+- name: 主库落在 pve1 持续 15 分钟提示回切
+  interval: 1m
+  input_series:
+  - series: pg_replication_is_replica{job="shared-postgresql-sql",instance="10.60.0.20:9187",cluster="homelab-pg-prod",member="pg-pve1",env="prod"}
+    values: '0x20'
+  - series: pg_replication_is_replica{job="shared-postgresql-sql",instance="192.168.10.127:9187",cluster="homelab-pg-prod",member="pg-laptop",env="prod"}
+    values: '1x20'
+  alert_rule_test:
+  - eval_time: 10m
+    alertname: SharedPostgresPrimaryOnStandbyHost
+    exp_alerts: []
+  - eval_time: 16m
+    alertname: SharedPostgresPrimaryOnStandbyHost
+    exp_alerts:
+    - exp_labels: {job: shared-postgresql-sql, instance: '10.60.0.20:9187', cluster: homelab-pg-prod, member: pg-pve1, env: prod, severity: warning, service: shared-postgresql}
+      exp_annotations:
+        summary: 共享 PG 生产主库运行在 pg-pve1（HDD），请确认切换原因并计划回切 laptop
+- name: 主库在 laptop 不提示
+  interval: 1m
+  input_series:
+  - series: pg_replication_is_replica{job="shared-postgresql-sql",instance="10.60.0.20:9187",member="pg-pve1",env="prod"}
+    values: '1x20'
+  alert_rule_test:
+  - eval_time: 16m
+    alertname: SharedPostgresPrimaryOnStandbyHost
+    exp_alerts: []
@@ -16,6 +16,10 @@ spec:
      group_interval: 5m
      repeat_interval: 4h
      routes:
+        # dev 必须排第一且不 continue：否则 dev 告警会被后面的 severity 路由送进生产频道。
+        - receiver: telegram-dev
+          matchers:
+            - env="dev"
        - receiver: telegram
          matchers:
            - severity="critical"
@@ -37,6 +41,11 @@ spec:
          - bot_token_file: /etc/vm/secrets/alertmanager-telegram/telegram_bot_token
            chat_id: -1003956377923
            send_resolved: true
+      - name: telegram-dev
+        telegram_configs:
+          - bot_token_file: /etc/vm/secrets/alertmanager-telegram/telegram_bot_token
+            chat_id: -1003651477106
+            send_resolved: true
  resources:
    requests:
      cpu: 25m
Author	SHA1	Message	Date
panxiao81	2c7dd224cf	Merge pull request '新增共享 PG 的 SQL 级指标与 dev 告警分流' (#173 ) from feat/shared-pg-sql-metrics into main yaml / yaml (push) Successful in 2m44s Details ansible / collection-test (push) Successful in 4m51s Details ansible / lint (push) Successful in 4m53s Details Reviewed-on: #173	2026-10-01 16:33:05 +00:00
panxiao81andClaude Opus 5.5	943d31bf08	新增主库落在 pve1（HDD）时的回切提示告警 yaml / yaml (pull_request) Successful in 2m25s Details ansible / lint (pull_request) Successful in 4m3s Details ansible / collection-test (pull_request) Successful in 4m12s Details 自动切换后主库安全落到 pve1 不会触发无主/多主规则；2026-09-30 的切换一天无人察觉。 Co-Authored-By: Claude Opus 5.5 <[email protected]>	2026-10-01 16:30:45 +00:00
panxiao81andClaude Opus 5.5	954bb39a23	docs: 记录共享 PG SQL 级指标的部署与验证 ansible / collection-test (pull_request) Successful in 9m52s Details yaml / yaml (pull_request) Failing after 10m40s Details ansible / lint (pull_request) Successful in 12m38s Details Co-Authored-By: Claude Opus 5.5 <[email protected]>	2026-10-01 16:20:16 +00:00
panxiao81andClaude Opus 5.5	549f82810e	修正 Patroni 已接收配置的判断：重启前预加载库行带 error 属预期 Co-Authored-By: Claude Opus 5.5 <[email protected]>	2026-10-01 16:20:16 +00:00
panxiao81andClaude Opus 5.5	567b42ed13	修正 monitoring.yml 的 check 模式与 Patroni pending 判断只读探测在 check 模式下照常执行；依赖真实执行结果的等待与验证在 check 模式跳过。 patronictl JSON 会省略空值字段，pending 判断先检查键是否存在。 Co-Authored-By: Claude Opus 5.5 <[email protected]>	2026-10-01 16:20:15 +00:00
panxiao81andClaude Opus 5.5	0cd4a67e12	新增共享 PG 的 SQL 级指标与 dev 告警分流 - 数据库主机上按实例运行 prometheus-community/postgres_exporter 0.20.1，以实例 OS 用户走本地 socket，经 peer + ident 映射到仅有 pg_monitor 的 homelab_monitor 角色；无密码、不经 Bao。版本与官方 sha256sums.txt 同源。 - prod/dev 启用 pg_stat_statements；monitoring.yml 仅在未生效时 reload/重启（prod 走 Patroni pending restart），重复执行 changed=0。 - 告警以上游 postgres_mixin 为底改写为 PrometheusRule，阈值对齐 Patroni failover lag 与 max_slot_wal_keep_size；抓取目标带 env 标签。 - Alertmanager 新增 env="dev" 路由到独立 dev 频道，dev SQL 检查告警补 env。 Co-Authored-By: Claude Opus 5.5 <[email protected]>	2026-10-01 16:20:15 +00:00
flux-bot	cd9d461950	chore(image): update zot.ad.ddupan.top/panxiao81/backstage:latest@sha256:bbc8bacf373ae9009047d3bd00b885995efdd6d365452206d4ed5cf364912668 -> zot.ad.ddupan.top/panxiao81/backstage:latest@sha256:fadd1ac9eaec52eddf105a81f5750878ca1b6fe49e0c6a987c346e501c59b95d yaml / yaml (push) Successful in 18s Details	2026-10-01 16:16:46 +00:00
flux-bot	a26e5bf214	chore(image): update zot.ad.ddupan.top/panxiao81/backstage:latest@sha256:008a3ccf832c9ee061ef03766022789a7539bd9d001a658af2e9f3ff59a9a5cc -> zot.ad.ddupan.top/panxiao81/backstage:latest@sha256:bbc8bacf373ae9009047d3bd00b885995efdd6d365452206d4ed5cf364912668 yaml / yaml (push) Successful in 28s Details	2026-10-01 16:07:46 +00:00
panxiao81	186a0cb582	Merge pull request 'Backstage 通过 Flux image automation 自动部署 latest' (#172 ) from feat/backstage-image-automation into main yaml / yaml (push) Successful in 30s Details	2026-10-01 16:06:35 +00:00
panxiao81andClaude Opus 5.5	7f5bef829f	feat: deploy backstage latest via Flux image automation yaml / yaml (pull_request) Successful in 23s Details Install image-reflector and image-automation controllers, track the digest behind backstage:latest, and let flux-bot commit digest updates to main. Co-Authored-By: Claude Opus 5.5 <[email protected]>	2026-10-01 16:05:13 +00:00
panxiao81	53e2854993	Merge pull request '将 Hydra 实验登录接至 Spring IAM 开发实例' (#168 ) from feat/iam-login-hydra-acceptance into main yaml / yaml (push) Successful in 20s Details Reviewed-on: #168	2026-10-01 14:40:20 +00:00
panxiao81	61674df541	将 Hydra 实验登录回调接至 Spring IAM 开发实例 yaml / yaml (pull_request) Successful in 26s Details	2026-09-29 15:45:21 +00:00