Multiple Hosts in connection string are not handled properly (POSTGRES_SEEDS accepts only single host?)

dhiaayachi commented 2 weeks ago

Expected Behavior

According to https://github.com/jackc/pgx/pull/545 pgx can accept multiple Postgres hosts in the format like 2.2.2.2:1,127.0.0.1,4.2.4.2 I assume that temporal passes this to a driver via POSTGRES_SEEDS env variable

Actual Behavior

From the logs I see that temporal sees string like host1,host2 as a single PG host "host1,host2" so fails to connect

temporal              | 2024-04-10T12:13:31.205Z    ERROR   Unable to connect to SQL database.  {"error": "dial tcp: lookup temporal-pg-host1,temporal-pg-host2: no such host", "logging-call-at": "handler.go:52"}

Steps to Reproduce the Problem

Get some Postgres cluster in master/replica mode consists of temporal-pg-host1 and temporal-pg-host2 nodes
Modify docker-compose-postgres.yml, comment out postgres container section and set POSTGRES_SEEDS=temporal-pg-host1,temporal-pg-host2
Run docker compose -f docker-compose-postgres.yml up

Specifications

Version: Actual docker composer manifest from the official Github repo
Platform: MacOS, Docker Compose version v2.26.1-desktop.1

dhiaayachi commented 2 days ago

Thanks for reporting the issue! It looks like you are experiencing an issue where Temporal is failing to connect to a multi-host PostgreSQL cluster. Based on the logs, Temporal is treating the comma-separated list of hosts as a single host name.

We are working on addressing this issue. In the meantime, you can work around it by using environment variables to specify the individual hosts. For example, you can set the following environment variables:

POSTGRES_HOST1=temporal-pg-host1
POSTGRES_HOST2=temporal-pg-host2
POSTGRES_PORT=5432

Then, update your docker-compose-postgres.yml file to use these environment variables:

version: "3.8"

services:
  postgresql:
    image: postgres:15
    environment:
      - POSTGRES_HOST=${POSTGRES_HOST1}
      - POSTGRES_HOST2=${POSTGRES_HOST2}
      - POSTGRES_PORT=${POSTGRES_PORT}
      # ... other environment variables

This should allow Temporal to connect to the PostgreSQL cluster correctly.

Let me know if this works for you, or if you have any other questions.

dhiaayachi commented 2 days ago

Thank you for reporting this issue.

The issue is that the Temporal server interprets a comma-separated string of hosts as a single host and fails to connect to it. This behavior can be fixed by splitting the host string into an array before passing it to the server.

To work around this issue, you can modify the docker-compose-postgres.yml file to set the POSTGRES_SEEDS environment variable to an array of hosts instead of a comma-separated string:

version: "3.7"

services:
  postgresql:
    image: postgres:14
    # Comment out this section and use environment variable instead
    # volumes:
    #   - postgresql-data:/var/lib/postgresql/data
    environment:
      - POSTGRES_USER=temporal
      - POSTGRES_PASSWORD=temporal
      - POSTGRES_DB=temporal
      # Set POSTGRES_SEEDS to an array of hosts
      - POSTGRES_SEEDS=["temporal-pg-host1", "temporal-pg-host2"]
    ports:
      - "5432:5432"
    networks:
      - temporal-network
    restart: unless-stopped
    # Use an environment variable for the hostname
    hostname: ${POSTGRES_HOSTNAME}

  temporal:
    # ...
    depends_on:
      - postgresql
    environment:
      # ...
      - POSTGRES_HOSTNAME=temporal-pg-host1 # Or any other host in the array
    # ...

networks:
  temporal-network:

volumes:
  postgresql-data:

This will pass an array of hosts to the Temporal server, which will then be able to connect to the correct PostgreSQL cluster.

This is a workaround, and we are working on a fix for this issue. We will update the documentation when the fix is available.

dhiaayachi commented 2 days ago

Thanks for reporting the issue. We've identified this as a known issue and are working on resolving it.

Here are some possible workarounds:

Use a single Postgres host for your Temporal Service and rely on the database's replication mechanism for high availability.

The current workaround is to set up Postgres in master/replica mode and use only the master as the connection for Temporal.

Let us know if you have any further questions.

dhiaayachi / temporal