PostgreSQL 9.6环境下PgPool版本选择及启动故障排查求助

1. Which PgPool-II Version Should I Choose? #

Since you're running PostgreSQL 9.6, go with pgpool-II-96—here's why:

• This package is explicitly built to match PostgreSQL 9.6's version, ensuring full compatibility with the protocol, features, and extension support specific to PG 9.6. The official PgPool docs call this out for a good reason: version-aligned packages minimize compatibility headaches.
• The postgresql-pgpool-II package is often a generic or distro-maintained version that might not be tightly coupled to PG 9.6. While it could work, you risk encountering subtle mismatches (like protocol differences or missing version-specific patches) that are avoided with the version-specific pgpool-II-96.

2. Newbie-Friendly PgPool-II Deployment Tutorial #

I get it, the official docs can feel overwhelming. Here's a simplified step-by-step to get a basic single-node PgPool setup working with your PG 9.6 instance:

• Pre-check: Ensure your PostgreSQL 9.6 instance is running and accessible locally. Run systemctl status postgresql-9.6 to confirm.
• Install the right package:

  yum install pgpool-II-96 pgpool-II-96-extensions
  

• Basic configuration (edit /etc/pgpool-II-96/pgpool.conf):
  • Set listen_addresses = '*' to allow connections from your local machine (adjust for remote access later if needed).
  • Configure your backend PostgreSQL node:

    backend_hostname0 = '127.0.0.1'  # Replace with your PG node IP if remote
    backend_port0 = 5432             # Default PG port
    backend_weight0 = 1              # Traffic weight for this node
    backend_flag0 = 'ALLOW_TO_FAILOVER'
    

• Setup authentication (edit /etc/pgpool-II-96/pool_hba.conf):
  Add a rule to allow local connections (similar to PostgreSQL's pg_hba.conf):

  host  all  all  127.0.0.1/32  trust
  

  For remote connections, adjust this to use md5 or scram-sha-256 (match your PG's authentication method).
• Test the connection:

  psql -h 127.0.0.1 -p 9999 -U postgres
  

  If you can connect, PgPool is working!
• Start and enable the service:

  systemctl start pgpool-II-96
  systemctl enable pgpool-II-96
  

3. Fixing "FATAL: unable to read data from DB node 0" #

That error means PgPool can't communicate with your PostgreSQL node 0. Here are the most common fixes:

• Check if PostgreSQL is running: If PG is down, start it with systemctl start postgresql-9.6.
• Verify backend configuration: Double-check backend_hostname0 and backend_port0 in pgpool.conf—make sure they match your PG instance's IP and port. Also, ensure PG's postgresql.conf has listen_addresses set to allow connections from PgPool's server (e.g., listen_addresses = '*' for testing).
• Fix authentication issues: PgPool needs permission to connect to PG. Ensure:
  • Your PG's pg_hba.conf allows connections from PgPool's IP (e.g., host all all 127.0.0.1/32 trust for local access).
  • The user PgPool uses (default is postgres) has the right to connect to PG.
• Test network connectivity: Run telnet <PG_NODE_IP> 5432 from the PgPool server. If it fails, check firewalls (iptables, firewalld) to ensure port 5432 is open between PgPool and PG.

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