Docker Swarm上PostgreSQL容器运行多日后持续损坏求助

我在Docker Swarm集群中部署了单副本PostgreSQL容器，通过节点标签约束仅在特定节点运行，供集群内其他服务访问。但容器运行3-10天后总会出现数据库损坏，该问题已困扰数月。

典型报错日志 #

PostgreSQL Database directory appears to contain a database; Skipping initialization
2024-02-04 02:44:42.774 UTC [1] LOG:  starting PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2024-02-04 02:44:42.774 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2024-02-04 02:44:42.774 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2024-02-04 02:44:42.779 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2024-02-04 02:44:42.786 UTC [30] LOG:  database system was shut down at 2024-02-03 12:13:41 UTC
2024-02-04 02:44:42.786 UTC [30] LOG:  invalid record length at 31/39646860: expected at least 24, got 0
2024-02-04 02:44:42.786 UTC [30] LOG:  invalid checkpoint record
2024-02-04 02:44:42.786 UTC [30] PANIC:  could not locate a valid checkpoint record
2024-02-04 02:44:42.923 UTC [1] LOG:  startup process (PID 30) was terminated by signal 6: Aborted
2024-02-04 02:44:42.924 UTC [1] LOG:  aborting startup due to startup process failure
2024-02-04 02:44:42.925 UTC [1] LOG:  database system is shut down

此外还出现过postmaster.pid锁仍处于活跃状态的报错。

已排查内容 #

• 排除磁盘问题：使用DigitalOcean虚拟节点，已迁移至新虚拟机管理程序，问题依旧；节点总容量155GB，可用96GB，空间充足。
• 版本与重置测试：从PostgreSQL 15升级到16，完全清空数据库目录后重新部署，问题仍存在。
• 故障模拟测试：尝试在数据写入时强制重启服务器、关闭节点、将服务副本数缩为0等操作，PostgreSQL均能正常恢复，无法稳定复现问题。

Docker Swarm配置 #

version: "3.9"
services:
  postgres:
    image: postgres:16.1
    ports:
      - 5432:5432 # Not *usually* on here
    environment:
      POSTGRES_PASSWORD: "<password>"
    networks:
      - swarm
    deploy:
      placement:
        constraints:
          - node.labels.has_database==true # Only one node has this
      replicas: 1
    volumes:
      - /home/postgresql-data:/var/lib/postgresql/data
networks:
  swarm:
    driver: overlay
    external: true
    attachable: true

PostgreSQL配置（postgresql.conf非注释内容） #

listen_addresses = '*'
max_connections = 300                   # (change requires restart)
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_timezone = 'Etc/UTC'
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'en_US.utf8'              # locale for system error message
lc_monetary = 'en_US.utf8'              # locale for monetary formatting
lc_numeric = 'en_US.utf8'               # locale for number formatting
lc_time = 'en_US.utf8'                  # locale for time formatting
default_text_search_config = 'pg_catalog.english'

排查建议 #

• 校验Swarm服务重启机制：添加PostgreSQL健康检查，避免Swarm因误判健康状态强制重启容器：

  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U postgres"]
    interval: 10s
    timeout: 5s
    retries: 5
  

• 检查宿主机OOM日志：查看宿主机/var/log/syslog或dmesg日志，确认PostgreSQL是否因内存不足被内核OOM Killer强制杀死。
• 优化PostgreSQL数据安全配置：调整WAL与刷盘参数，增强数据持久化可靠性：

  wal_sync_method = fsync
  full_page_writes = on
  wal_writer_delay = 10ms
  max_wal_size = 4GB
  

• 修改Docker挂载方式：将宿主机绑定挂载改为Docker命名卷，减少宿主机文件系统层面的潜在问题：

  volumes:
    - postgres-data:/var/lib/postgresql/data
  volumes:
    postgres-data:
  

• 添加容器关闭超时：在Swarm服务配置中设置stop_grace_period: 30s，给PostgreSQL足够时间完成刷盘和正常关闭。
• 监控后台写入指标：通过pg_stat_bgwriter视图监控检查点、WAL刷盘频率，排查是否存在异常延迟。

内容的提问来源于stack exchange，提问作者user1910744