PostgreSQL配置pg_cron每月重置users表nbr_requests列失败求助

针对你用pg_cron实现每月初重置users表nbr_requests字段为0但任务失效并报"connection failed"的问题，可按以下步骤排查解决：

1. 验证pg_cron的加载与安装状态 #

• 执行以下命令确认shared_preload_libraries已正确加载pg_cron：

  SHOW shared_preload_libraries;
  

  确保输出结果包含pg_cron，若未包含，需检查postgresql.conf配置并重启PostgreSQL服务。
• 确认cron.database_name配置的数据库与目标库一致：

  SHOW cron.database_name;
  

  需确保结果为nv_api_db。
• 检查pg_cron扩展是否已在目标数据库中创建：

  SELECT * FROM pg_extension WHERE extname = 'pg_cron';
  

  若查询无结果，需切换到nv_api_db数据库，执行CREATE EXTENSION pg_cron;完成安装。

2. 排查连接失败的核心原因 #

• 检查pg_hba.conf的规则顺序：PostgreSQL按配置文件的顺序匹配连接规则，你添加的127.0.0.1/32 trust规则必须放在0.0.0.0/0 md5规则之前，否则本地连接会优先匹配md5验证规则导致失败。调整顺序后，执行SELECT pg_reload_conf();或重启服务使配置生效。
• 查看PostgreSQL日志定位具体错误：日志默认存储在PostgreSQL数据目录的pg_log文件夹下，搜索包含cron的日志条目，可获取连接失败的具体原因（如用户权限、数据库名称错误等）。

3. 确认定时任务配置正确性 #

• 检查定时任务是否已成功创建：

  SELECT * FROM cron.job;
  

  确认任务的schedule字段为0 0 1 * *，command字段为UPDATE users SET nbr_requests = 0，且database字段为nv_api_db。
• 验证时区配置：执行SHOW timezone;确认数据库时区与cron.timezone = 'CET'一致，避免因时区偏差导致任务执行时间错误。
• 手动测试SQL语句：在nv_api_db数据库中直接执行UPDATE users SET nbr_requests = 0;，确认语句无语法错误且当前用户具备users表的更新权限。

4. 权限校验 #

• 确保创建定时任务时使用的是超级用户身份，pg_cron要求超级用户权限创建任务。
• 确认PostgreSQL系统用户（如postgres）具备本地连接nv_api_db的权限，pg_cron默认以该系统用户身份发起数据库连接。

内容的提问来源于stack exchange，提问作者Emman BM Boïka