Having issues connecting to my postgres service locally on host

Nomad
1

I’m having difficulty connecting to my postgres service from a client on the host

$ psql -h localhost
psql: could not connect to server: Connection refused
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

Here is the job config

job "postgres" {
  datacenters = ["main1"]

  group "postgres" {
    # Keep attempting to restart the same allocation on the same node if it fails
    restart {
      attempts = 1
      delay = "1s"
      mode = "delay"
      interval = "5s"
    }

    network {
      mode = "bridge"

      port "postgres" {
        # So we can connect with third-party clients
        static = 5432

        to = 5432
      }
    }

    service {
      name = "postgres"
      port = "5432"

      connect {
        sidecar_service {}
      }
    }

    task "postgres" {
      driver = "docker"

      env {
        PGDATA = "/var/lib/postgresql/data/pgdata"
      }

      config {
        image = "postgres:11.15"
        ports = ["postgres"]
        volumes = [
          "/mnt/pgdata:/var/lib/postgresql/data/pgdata",
        ]

        # Set /dev/shm to 1 GB (in bytes). Needed for getting around "could not resize shared memory" errors
        shm_size = 1073741824
      }

      template {
        data = <<-EOF
          {{ with secret "secrets/postgres" }}
          # Note that $POSTGRES_USER is the superuser while default user is a separate non-superuser strictly for
          # interacting with $POSTGRES_DB. Our backend should not connect to $POSTGRES_DB as a superuser just so that we
          # can still use $POSTGRES_USER to connect to $POSTGRES_DB as a superuser in case we are having issues with the
          # database (since the database reserves a few connections just for superusers)
          POSTGRES_DB="{{ .Data.data.database }}"
          POSTGRES_USER="{{ .Data.data.super_username }}"
          POSTGRES_PASSWORD="{{ .Data.data.super_password }}"
          POSTGRES_DEFAULT_USER="{{ .Data.data.default_username }}"
          POSTGRES_DEFAULT_PASSWORD="{{ .Data.data.default_password }}"

          # Specify default database and user to connect as when running `psql`
          PGDATABASE="{{ .Data.data.database }}"
          PGUSER="{{ .Data.data.super_username }}"
          {{ end }}
        EOF
        destination = "secrets/postgres.env"
        env = true
      }
    }
  }
}

Within the container, running with netstat -tlpn4 returns

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      -

Is there anything else I’m missing here or what else can I check to be the culprit? What’s odd is I have this exact same job running in my staging environment and I’m able to connect to it just fine, but not in production. Thanks.

2

Well, obviously you should check the Postgres log.

3

Hi @axsuul,

The postgres job and task are using bridge network mode. This means the postgres application will be running within a network namespace and therefore won’t be bound to localhost on the client. Instead it is bound to 0.0.0.0 within the network namespace.

It seems you have a defined service block which is integrated into Consul. I would therefore check the Consul UI or API to understand exactly which IP address and port is being advertised for the Postgres endpoint. It could also be possible to run the netstat command on the client to help understand this or look within iptables for the Nomad CNI chain.

Thanks,
jrasell and the Nomad team

4

Note: nothing in the Postgres logs

Thanks for your response. In the Consul UI, my postgres service instance is showing

image
image1818×500 104 KB

Is this what you’re referring to? I have tried doing

psql -h 10.128.0.66

on the host with no success.

5

That’s good, if PG logs look good now you know it’s likely a networking problem… Perhaps install socat and try socat tcp-listen:5432,reuseaddr,fork tcp-connect:10.128.0.66:5432, then psql -h localhost?

6

Thanks for your help everyone. I decided to attack it from a different angle and instead use Tailscale to tunnel directly into Consul Connect services with GitHub - markpash/tailscale-sidecar: A TCP proxy used to expose services onto a tailscale network without root. Ideal for container environments.

It’s much more secure and I don’t have to bind a static port anymore :+1: