PGO 部署与运维

发表于 2022-12-25 分类于数据库

基于 CrunchyData PGO 在 Kubernetes 上部署高可用 PostgreSQL 集群：Operator 安装、集群与 pgBouncer 配置、主备切换，以及初始化、pgBackRest 备份与按时间点恢复、集群克隆等日常运维。

官网

https://github.com/CrunchyData/postgres-operator-examples

https://access.crunchydata.com/documentation/postgres-operator/v5/

一些基本修改

1	cd postgres-operator-examples

指定 namespace 为 postgres-operator

kubectl apply -k kustomize/install/namespace
创建 namespace

apiVersion: v1
kind: Namespace
metadata:
  name: postgres-operator

kubectl apply --server-side -k kustomize/install/default

一般只用修改镜像，没什么大的调整

namespace: postgres-operator

commonLabels:
  app.kubernetes.io/name: pgo
  # The version below should match the version on the PostgresCluster CRD
  app.kubernetes.io/version: 5.3.0

bases:
- ../crd
- ../rbac/cluster
- ../manager

images:
- name: postgres-operator
  newName: imwl/postgres-operator
  newTag: ubi8-5.3.0-0
- name: postgres-operator-upgrade
  newName: imwl/postgres-operator-upgrade
  newTag: ubi8-5.3.0-0

patchesJson6902:
- target: { group: apps, version: v1, kind: Deployment, name: pgo }
  path: selectors.yaml
- target: { group: apps, version: v1, kind: Deployment, name: pgo-upgrade }
  path: selectors.yaml

安装 pg-cluster 高可用

主要修改的文件，按需修改。存储使用 rook-ceph 搭建的 rook-ceph-block-retain

kubectl apply -k kustomize/high-availability

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: imwl
#  annotations:
#    postgres-operator.crunchydata.com/pgbackrest-ip-version: IPv6
spec:
  users:
    - name: postgres
    - name: hive
      databases:
        - hive
    - name: imwl
      databases:
        - imwl
      options: "SUPERUSER"   # 创建超级管理员用户
  service:
    metadata:
      annotations:
        my-annotation: value1
      labels:
        my-label: value2
    type: NodePort
    nodePort: 31432
  image: imwl/crunchy-postgres:ubi8-13.9-2
  postgresVersion: 13
  instances:
    - name: pgha1
      replicas: 2
      resources:
        limits:
          cpu: 8.0
          memory: 16Gi
      dataVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 20Gi
      walVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 10Gi
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: imwl
                  postgres-operator.crunchydata.com/instance-set: pgha1
  backups:
    pgbackrest:
      #manual:     # 一次性备份
      #  repoName: repo1
      #  options:
      #   - --type=full
      global:
        repo1-retention-full: "14"
        repo1-retention-full-type: time
        repo2-path: /pgbackrest/postgres-operator/hippo-multi-repo/repo2
      image: imwl/crunchy-pgbackrest:ubi8-2.41-2
      repos:
      - name: repo1
        schedules:
          full: "0 2 * * 0"
          differential: "0 5 * * 1-6"
        volume:
          volumeClaimSpec:
            storageClassName: "rook-ceph-block-retain"
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 40Gi
      - name: repo2
        schedules:
          full: "0 1 * * 0"
          differential: "0 1 * * 1-6"
        volume:
          volumeClaimSpec:
            storageClassName: "local-path"
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 40Gi

  proxy:
    pgBouncer:
      config:
        global:
          pool_mode: session
          auth_file: /etc/pgbouncer/users.txt
        files:  # 需要动手创建 cm , 添加账号密码
          - configMap:
              name: pgbouncer-users
              items:
                - key: users.txt
                  path: users.txt
      image: imwl/crunchy-pgbouncer:ubi8-1.17-5
      replicas: 2
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 1
            podAffinityTerm:
              topologyKey: kubernetes.io/hostname
              labelSelector:
                matchLabels:
                  postgres-operator.crunchydata.com/cluster: imwl
                  postgres-operator.crunchydata.com/role: pgbouncer

  patroni:
    switchover:   # 允许主备切换，官方不建议启用，只建议手动切换，切换时开启，切换完关闭
      enabled: true
    dynamicConfiguration:
      synchronous_mode: true
      synchronous_mode_strict: true
      postgresql:
        pg_hba:
          - "host all all 0.0.0.0/0 md5"
          # - "hostnossl all all all md5"
        parameters:
          max_connections: 2000
          log_timezone: 'Asia/Shanghai'
          timezone: 'Asia/Shanghai'
          max_parallel_workers: 16
          max_worker_processes: 16
          shared_buffers: 8GB
          work_mem: 64MB

  # 使用 pgadmin
  userInterface:
    pgAdmin:
      image: registry.example.com/test/crunchy-pgadmin4:ubi8-4.30-13
      dataVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 1Gi

  #  开启 export 监控
  monitoring:
    pgmonitor:
      exporter:
        image: registry.example.com/kube-package/crunchy-postgres-exporter:ubi8-5.4.0-0

这份 manifest 里的四处代价

上面几组参数单看都像是”调优过的”，但它们各自都有代价，值得在抄之前想清楚。

一、synchronous_mode_strict: true 配 1 主 1 备，是拿可用性换零丢失。

synchronous_mode 打开后 Patroni 会维护一个同步备库，事务提交必须等它确认；而 strict 的含义是：没有可用的同步备库时，宁可拒绝所有写入，也不降级成异步。

这里 replicas: 2 意味着 1 主 1 备，唯一那个同步备库就是单点——它挂掉、甚至只是滚动重启，主库都会立刻拒绝所有写事务（客户端表现为 commit 挂住直到超时）。

这不是配错，是一个明确的取舍：

不加 strict：备库挂了自动降级成异步，写入继续，代价是这段时间的提交在主库故障时可能丢；
加了 strict：绝不丢已提交事务，代价是备库不可用等于整库不可写。

想要 strict 又要可用性，就得 replicas: 3（两个候选同步备库）。1 主 1 备加 strict 只适合”丢一条数据的代价远大于停写”的场景，上线前得跟业务方确认过。

二、shared_buffers: 8GB 相对容器 16Gi 的上限偏高。

PostgreSQL 的常规建议是取内存的 25%，因为它还依赖操作系统 page cache 作二级缓存。配到 50% 会让两层缓存重复缓存同一批页面，而留给 page cache、临时文件和下面 work_mem 的空间被压缩。16Gi 的容器给 4GB 比较稳妥。

三、work_mem: 64MB 乘上 max_connections: 2000 是内存放大的隐患。

work_mem 是每个排序或哈希操作的上限，不是每个连接的上限。一个带多个 ORDER BY 加 HASH JOIN 的查询可以同时开好几份：

1 2	最坏情况 ≈ max_connections × 单查询的操作数 × work_mem = 2000 × 3 × 64MB ≈ 384GB

这个数当然不会真的发生，但它说明这两个参数不能各自独立地往上调。稳妥的算法反过来：先按 (可用内存 − shared_buffers) × 0.5 ÷ 预期并发 算出单个 work_mem，少数需要大内存的报表查询在会话里单独 SET work_mem。

四、pool_mode: session 让 pgBouncer 基本失去了连接池的意义。

session 模式的行为是：客户端连上来就独占一个后端连接，直到客户端断开才归还。2000 个客户端连接就对应 2000 个 PostgreSQL 后端进程，复用完全没发生，pgBouncer 在这里退化成了一个 TCP 代理。

真要连接池效果得用 transaction 模式（事务一结束就归还，几十个后端连接能撑几千客户端），代价是不能用会话级特性：

用不了的东西	原因
`SET` / `RESET` 会话级参数	下个事务可能换到别的后端连接上
命名预处理语句 `PREPARE`	同上（较新版本可配 `max_prepared_statements` 缓解）
会话级 advisory lock	锁跟着连接走，换连接就丢
`LISTEN` / `NOTIFY`	需要长期占住同一连接
跨事务的临时表、游标	生命周期绑在会话上

选哪个取决于应用用没用到这些。ORM 的默认配置通常是安全的，但要检查一遍连接池自己的 SET application_name 和 JDBC 的 prepareThreshold。

因为超级用户不能访问 pgbouncer

pgbouncer-users-configmap.yaml

apiVersion: v1
kind: ConfigMap
metadata:
  namespace: postgres-operator
  name: pgbouncer-users
data:
  users.txt: |
    "imwl"  "password"
    "postgres"  "<CHANGE_ME>"
    "hive"   "<CHANGE_ME>"

手动主备切换，当允许时

1	kubectl annotate -n postgres-operator postgrescluster imwl postgres-operator.crunchydata.com/trigger-switchover="$(date)" --overwrite

官方文档里这条命令用的集群名是 hippo，抄的时候容易连着写成 postgrescluster hippo imwl ...，那样 kubectl annotate TYPE NAME1 NAME2 KEY=VAL 会被理解成同时给 hippo 和 imwl 两个资源打注解，本地并没有 hippo 这个集群，命令会以 postgresclusters "hippo" not found 失败。这里只留自己的集群名 imwl。另外注解第二次触发时已经存在，必须带 --overwrite 才能更新时间戳。

创建完后，修改账号密码集群名-pguser-用户名,测试全部密码 password

kubectl patch secret -n postgres-operator imwl-pguser-hive -p '{"stringData":{"password":"password","verifier":""}}'

kubectl patch secret -n postgres-operator imwl-pguser-imwl -p '{"stringData":{"password":"password","verifier":""}}'

kubectl patch secret -n postgres-operator imwl-pguser-postgres -p '{"stringData":{"password":"password","verifier":""}}'

数据库连接方式

ssh 中转后 imwl-ha.postgres-operator.svc.cluster.local:5432 imwl/password
ip:31432

更多信息参考官网和github

初始化数据库

默认的需要创建 configmap, 但 configmap 大小有限制，手动初始化数据库

将 sql 文件放入 /mnt/imwl/initdb /mnt/imwl 是共享目录

apiVersion: batch/v1
kind: Job
metadata:
  name: init-db
  namespace: postgres-operator
spec:
  parallelism: 1
  template:
    spec:
      containers:
      - image: registry.example.com/test/crunchy-postgres:ubi8-13.9-2
        name: init-db
        command: ['sh', '-c', 'for sql in `ls /tmp/*.sql`;do psql -h $PGHOST -p $PGPORT -U $PGUSER -d postgres -v ON_ERROR_STOP=1 -f $sql --quiet;done']
        volumeMounts:
        - mountPath: /tmp
          name: init-db
        env:
        - name: PGPASSWORD
          valueFrom:
            secretKeyRef:
              name: test-pguser-postgres
              key: password
        - name: PGHOST
          valueFrom:
             secretKeyRef:
               name: test-pguser-postgres
               key: host
        - name: PGPORT
          valueFrom:
            secretKeyRef:
              name: test-pguser-postgres
              key: port
        - name: PGUSER
          valueFrom:
             secretKeyRef:
               name: test-pguser-postgres
               key: user
      restartPolicy: OnFailure
      volumes:
      - name: init-db
        hostPath:
          path: /mnt/imwl/initdb
          type: Directory
  backoffLimit: 5

备份

前文配置了定时备份

做一次备份

1	kubectl annotate -n postgres-operator postgrescluster imwl postgres-operator.crunchydata.com/pgbackrest-backup="$(date)"

重新备份 --overwrite

1	kubectl annotate -n postgres-operator postgrescluster imwl --overwrite postgres-operator.crunchydata.com/pgbackrest-backup="$(date)"

恢复

新建一个 new-imwl 的集群

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: new-imwl
  namespace: postgres-operator
spec:  # 从 imwl 集群克隆
  dataSource:
    postgresCluster:
      clusterName: imwl
      repoName: repo1
#      options:  # 按时间点恢复
#      - --type=time
#      - --target="2022-06-09 14:15:11-04"
  image: registry.example.com/test/crunchy-postgres:ubi8-13.9-2
  postgresVersion: 13
  instances:
    - dataVolumeClaimSpec:
        storageClassName: "rook-ceph-block-retain"
        accessModes:
        - "ReadWriteOnce"
        resources:
          requests:
            storage: 20Gi
  backups:
    pgbackrest:
      image: registry.example.com/test/crunchy-pgbackrest:ubi8-2.41-2
      repos:
      - name: repo1
        volume:
          volumeClaimSpec:
            storageClassName: "rook-ceph-block-retain"
            accessModes:
            - "ReadWriteOnce"
            resources:
              requests:
                storage: 40Gi

查验, 数据正常

[root@imwl-01 high-availability]# kubectl get pvc -n postgres-operator  |grep new-imwl
new-imwl-00-5879-pgdata     Bound    pvc-f61d731c-524e-4f74-b0ef-0f73be5fa4de   20Gi       RWO            rook-ceph-block-retain   10m
new-imwl-repo1              Bound    pvc-d3ad6b64-7ff0-4b47-b461-4a609de34b4e   40Gi       RWO            rook-ceph-block-retain   9m33s
[root@imwl-01 high-availability]# kubectl get pv  |grep new-imwl
pvc-d3ad6b64-7ff0-4b47-b461-4a609de34b4e   40Gi       RWO            Retain           Bound      postgres-operator/new-imwl-repo1              rook-ceph-block-retain            9m43s
pvc-f61d731c-524e-4f74-b0ef-0f73be5fa4de   20Gi       RWO            Retain           Bound      postgres-operator/new-imwl-00-5879-pgdata     rook-ceph-block-retain            10m
[root@imwl-01 high-availability]# kubectl get svc -n postgres-operator  |grep new-imwl
new-imwl-ha          ClusterIP   10.68.52.59     <none>        5432/TCP         9m53s
new-imwl-ha-config   ClusterIP   None            <none>        <none>           9m53s
new-imwl-pods        ClusterIP   None            <none>        <none>           10m
new-imwl-primary     ClusterIP   None            <none>        5432/TCP         9m53s
new-imwl-replicas    ClusterIP   10.68.132.189   <none>        5432/TCP         9m53s

[root@imwl-01 high-availability]# kubectl exec -it -n postgres-operator  new-imwl-00-5879-0 -- sh
Defaulted container "database" out of: database, replication-cert-copy, pgbackrest, pgbackrest-config, postgres-startup (init), nss-wrapper-init (init)
sh-4.4$ export PGPASSWORD="<CHANGE_ME>"
sh-4.4$ psql -h 10.68.52.59 -p 5432 -U postgres
psql (13.9)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 test       | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | test=CTc/postgres
 hive      | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | hive=CTc/postgres
 imwl      | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres+
           |          |          |             |             | imwl=CTc/postgres
 postgres  | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =Tc/postgres         +
           |          |          |             |             | postgres=CTc/postgres
 template0 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(6 rows)

只还原数据，不新建集群, 改原有配置，只用加下面几行

spec:
  backups:
    pgbackrest:
      restore:
        enabled: true
        repoName: repo1
        options:
        - --type=time
        - --target="2022-06-09 14:15:11-04"
      #  - --db-include=hive  # 只还原单个数据库

执行

kubectl annotate -n postgres-operator postgrescluster imwl --overwrite postgres-operator.crunchydata.com/pgbackrest-restore=id1

一些基本操作

# 重启
kubectl patch postgrescluster/imwl -n postgres-operator --type merge   --patch '{"spec":{"metadata":{"annotations":{"restarted":"'"$(date)"'"}}}}'

# 关闭
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"shutdown": true}}'

# 开机
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"shutdown": false}}'

# 暂停
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"paused": true}}'

# 恢复
kubectl patch postgrescluster/imwl -n postgres-operator --type merge  --patch '{"spec":{"paused": false}}'

查看 master 数据库 pod

1	kubectl -n postgres-operator get pods --selector=postgres-operator.crunchydata.com/role=master -o jsonpath='{.items[*].metadata.labels.postgres-operator\.crunchydata\.com/instance}'

查看 replica

1	kubectl -n postgres-operator get pods --selector=postgres-operator.crunchydata.com/role=replica -o jsonpath='{.items[*].metadata.labels.postgres-operator\.crunchydata\.com/instance}'

手动主备切换

1	kubectl annotate -n postgres-operator postgrescluster imwl postgres-operator.crunchydata.com/trigger-switchover="$(date)" --overwrite

注意资源名只写自己的集群 imwl。官方文档的示例集群叫 hippo，跟着抄成 postgrescluster hippo imwl ... 的话，kubectl annotate TYPE NAME1 NAME2 KEY=VAL 这个语法会被解析成同时给 hippo 和 imwl 两个资源打注解，而本地没有 hippo，命令直接以 postgresclusters "hippo" not found 失败。--overwrite 是因为注解在第一次切换后就已经存在，不加的话更新不了时间戳。

额外备份sql

#!/bin/bash

# docker run --name some-postgres -e POSTGRES_PASSWORD=<CHANGE_ME> -v /opt:/opt -d postgres:13.9

PSQL_HOST="imwl-ha.postgres-operator.svc.cluster.local"
PSQL_PORT="5432"
PSQL_USER="postgres"
PSQL_PASS='<CHANGE_ME>'
BACKUP_DIRECTORY="/opt/backups/postgresql/imwl"
DEFAULT_DATE="$(date +%Y%m%d)"
BACKUP_LOGDIR="/opt/backups/logs"
REMOTE_BACKUP_DIRECTORY="/opt/backups/postgresql/imwl"
REMOTE_HOST="172.20.19.57"
REMOTE_HOST_PASS="<CHANGE_ME>"
REMOTE_HOST2="172.20.19.59"
REMOTE_HOST_PASS2="<CHANGE_ME>"

logger(){
    [ ! -d "${BACKUP_LOGDIR}" ] && mkdir -p ${BACKUP_LOGDIR}
    local logfile="${BACKUP_LOGDIR}/pgbackup-${DEFAULT_DATE}.log"
    local logtype=$1; local msg=$2
    datetime=`date +'%F %H:%M:%S'`
    format1="${datetime}  [line: `caller 0 | awk '{print$1}'`]"
    format2="${logtype}: ${msg}"
    logformat="$format1  $format2"
    echo -e "${logformat}" | tee -a $logfile
}

backup_database(){
    local databases=(
        hive
        test
    )

    if [ ! -d "${BACKUP_DIRECTORY}" ];then
        mkdir -p ${BACKUP_DIRECTORY}
    fi

    for db in ${databases[@]}
    do
        postgres_dump $db
    done

    logger INFO "Databases backed up to ${BACKUP_DIRECTORY} directory"
}

postgres_dump(){
    local database=$1
    logger INFO "Dumping database ${database} ..."

    export PGPASSWORD=${PSQL_PASS}
    docker exec -i -e PGPASSWORD=$PSQL_PASS some-postgres  /usr/bin/pg_dump -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database} |gzip > ${BACKUP_DIRECTORY}/${DEFAULT_DATE}-${database}.sql.gz

    # docker exec -i -e PGPASSWORD=$PSQL_PASS some-postgres  /usr/bin/pg_dumpall -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database} |gzip > ${BACKUP_DIRECTORY}/${DEFAULT_DATE}-full.sql.gz

    #  docker run --rm -i -e PGPASSWORD=$PSQL_PASS  postgres:13.9 /usr/bin/pg_dumpall  -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database} |gzip > ${BACKUP_DIRECTORY}/${DEFAULT_DATE}-full.sql.gz

    if [ ${PIPESTATUS[0]} -ne 0 -o ${PIPESTATUS[1]} -ne 0 ];then
        logger ERROR "Failed to dump database ${database}!"
        exit 1
    fi

    logger INFO "Database ${database} dump complete"
}

backup_database


#远程备份，保存3天
sshpass -p $REMOTE_HOST_PASS scp $BACKUP_DIRECTORY/$DEFAULT_DATE* root@$REMOTE_HOST:$REMOTE_BACKUP_DIRECTORY
logger INFO "Databases backed up to remote directory: ${REMOTE_BACKUP_DIRECTORY}"

sshpass -p $REMOTE_HOST_PASS2 scp $BACKUP_DIRECTORY/$DEFAULT_DATE* root@$REMOTE_HOST2:$REMOTE_BACKUP_DIRECTORY
logger INFO "Databases backed up to remote directory: ${REMOTE_BACKUP_DIRECTORY}"


#检查当前备份文件数量是否大于9，若是，删除3天之前的备份文件
file_num=`ls -l $BACKUP_DIRECTORY |grep "^-"|wc -l`
if [ $file_num -gt 9 ];then
  find $BACKUP_DIRECTORY -mtime +2 -exec rm -rfv {} \;
  logger INFO "Delete backup files three days ago"
fi

恢复

1	psql -f xxxx.sql -h ${PSQL_HOST} -p ${PSQL_PORT} -U ${PSQL_USER} ${database}

拿文件恢复
不推荐

[root@k8s-60 pg13]#  docker run --rm -it --name postgres  -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=<CHANGE_ME> -v /root/pg13:/var/lib/postgresql/data  -v /etc/localtime:/etc/localtime -p 15432:5432 postgres:13.9  su - postgres -c '/usr/lib/postgresql/13/bin/pg_resetwal -f  /var/lib/postgresql/data'
Write-ahead log reset

[root@k8s-60 pg13]#  docker run -d --name postgres --restart=always  -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=<CHANGE_ME> -v /root/pg13:/var/lib/postgresql/data  -v /etc/localtime:/etc/localtime -p 15432:5432 postgres:13.9
dccddceec900708489f10e4c9462d0e62d0d4340aabb5804360253f523cc994b

推荐
恢复基础备份，然后 wal日志数据库恢复参考

https://blog.csdn.net/zimu312500/article/details/124283358

关闭空闲连接

select * from pg_stat_activity;

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE state = 'idle' AND pid <> pg_backend_pid()
  AND state_change < now() - interval '10 min';

网上常见的 WHERE current_query='<IDLE>' 配 pg_terminate_backend(procpid) 是 9.1 及更早的写法：PG 9.2 就把 procpid、current_query 分别改名成了 pid、query，'<IDLE>' 这种表示法同期换成了独立的 state 列。这个集群是 PG 13，照老写法会直接报 column "procpid" does not exist。

上面顺手加的两个条件也建议保留：pid <> pg_backend_pid() 避免把当前会话自己杀掉，state_change 那一行则是只清理真正闲了 10 分钟以上的连接，免得把刚刚空下来、马上就要复用的连接打断。