Postgres docker container refusing connection

[parallaxview]

Using the 1.4.0 version of the ansible script on a debian servcer, the created postgres docker container looks like it is working but is not allowing incoming connections. I can psql from inside the container with no issue, and I have listen_addresses = '*' set in my postgresql.conf file. I am stuck as to ideas on this one...

Lemmy Log:

{"log":"Lemmy v0.19.3\n","stream":"stdout","time":"2024-03-22T23:19:12.332203766Z"}
{"log":"thread 'main' panicked at crates/db_schema/src/utils.rs:281:56:\n","stream":"stderr","time":"2024-03-22T23:19:12.334529611Z"}
{"log":"Error connecting to postgres://lemmy:<password>@postgres:5432/lemmy: connection to server at \"postgres\" (172.18.0.3), port 5432 failed: Connection refused\n","stream":"stderr","time":"2024-03-22T23:19:12.334659829Z"}
{"log":"\u0009Is the server running on that host and accepting TCP/IP connections?\n","stream":"stderr","time":"2024-03-22T23:19:12.334673237Z"}

Postgres log:

{"log":"2024-03-22 20:56:23.620 GMT [1] LOG:  listening on IPv4 address \"127.0.0.1\", port 5432\n","stream":"stderr","time":"2024-03-22T20:56:23.620876398Z"}
{"log":"2024-03-22 20:56:23.620 GMT [1] LOG:  could not bind IPv6 address \"::1\": Address not available\n","stream":"stderr","time":"2024-03-22T20:56:23.620989433Z"}
{"log":"2024-03-22 20:56:23.624 GMT [1] LOG:  listening on Unix socket \"/var/run/postgresql/.s.PGSQL.5432\"\n","stream":"stderr","time":"2024-03-22T20:56:23.62497622Z"}
{"log":"2024-03-22 20:56:23.630 GMT [23] LOG:  database system was shut down at 2024-03-22 20:56:23 GMT\n","stream":"stderr","time":"2024-03-22T20:56:23.631122632Z"}
{"log":"2024-03-22 20:56:23.643 GMT [1] LOG:  database system is ready to accept connections\n","stream":"stderr","time":"2024-03-22T20:56:23.643744995Z"}
{"log":"2024-03-22 21:01:23.651 GMT [21] LOG:  checkpoint starting: time\n","stream":"stderr","time":"2024-03-22T21:01:23.652289811Z"}
{"log":"2024-03-22 21:01:23.672 GMT [21] LOG:  checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.007 s, sync=0.002 s, total=0.022 s; sync files=2, longest=0.002 s, average=0.001 s; distance=0 kB, estimate=0 kB\n","stream":"stderr","time":"2024-03-22T21:01:23.672997785Z"}

netstat -tulnp:

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:22              0.0.0.0:*               LISTEN      575/sshd: /usr/sbin 
tcp        0      0 0.0.0.0:16347           0.0.0.0:*               LISTEN      5783/docker-proxy   
tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN      595/nginx: master p 
tcp        0      0 0.0.0.0:80              0.0.0.0:*               LISTEN      595/nginx: master p 
tcp6       0      0 :::22                   :::*                    LISTEN      575/sshd: /usr/sbin 
tcp6       0      0 :::16347                :::*                    LISTEN      5788/docker-proxy   
tcp6       0      0 :::443                  :::*                    LISTEN      595/nginx: master p 
tcp6       0      0 :::80                   :::*                    LISTEN      595/nginx: master p 
udp        0      0 127.0.0.1:323           0.0.0.0:*                           577/chronyd         
udp6       0      0 ::1:323                 :::*                                577/chronyd     

config.hjson:

{
  # for more info about the config, check out the documentation
  # https://join-lemmy.org/docs/en/administration/configuration.html

  database: {
    host: postgres
    password: "{{ postgres_password }}"
  }
  hostname: "{{ domain }}"
  pictrs: {
    url: "http://pictrs:8080/"
    api_key: "{{ postgres_password }}"
  }
  email: {
    smtp_server: "smtp.sendgrid.net:587"
    smtp_login: "apikey"
    smtp_password: "<password>"
    smtp_from_address: "noreply@{{ domain }}"
    tls_type: "none"
  }
}

vars.yml:

postgres_password: "{{ lookup('password', 'inventory/host_vars/{{domain}}/passwords/postgres.psk chars=ascii_letters,digits') }}"

# You can set any pict-rs environmental variables here. They will populate the templates/docker-compose.yml file.
# https://git.asonix.dog/asonix/pict-rs
pictrs_env_vars:
  - PICTRS__SERVER__API_KEY: "{{ postgres_password }}"
  - PICTRS__MEDIA__VIDEO__VIDEO_CODEC: vp9
  - PICTRS__MEDIA__ANIMATION__MAX_WIDTH: 256
  - PICTRS__MEDIA__ANIMATION__MAX_HEIGHT: 256
  - PICTRS__MEDIA__ANIMATION__MAX_AREA: 65536
  - PICTRS__MEDIA__ANIMATION__MAX_FRAME_COUNT: 400
  - PICTRS_OPENTELEMETRY_URL: http://otel:4137
  - RUST_LOG: debug
  - RUST_BACKTRACE: full
#  - PICTRS__STORE__TYPE: object_storage
#  - PICTRS__STORE__ENDPOINT: '<S3 endpoint>'
#  - PICTRS__STORE__BUCKET_NAME: '<bucket name>'
#  - PICTRS__STORE__REGION: '<region>'
#  - PICTRS__STORE__USE_PATH_STYLE: false
#  - PICTRS__STORE__ACCESS_KEY: '<access key>'
#  - PICTRS__STORE__SECRET_KEY: '<secret key>'

postgres_env_vars:
  - POSTGRES_USER: lemmy
  - POSTGRES_PASSWORD: "{{ postgres_password }}"
  - POSTGRES_DB: lemmy

lemmy_env_vars:
  - RUST_LOG: warn

lemmyui_env_vars:
  - LEMMY_UI_LEMMY_INTERNAL_HOST: lemmy:8536
  - LEMMY_UI_LEMMY_EXTERNAL_HOST: "{{ domain }}"
  - LEMMY_UI_HTTPS: true

postfix_env_vars:
  - POSTFIX_myhostname: "{{ domain }}"

[ticoombs]

Check your customPostgres.conf file (the only one you didnt post) as the postgres logs show that it's only listening internally on the container.

127.0.0.1\", port 5432

You currently need to make sure that your listen addresses exists in that file.

# Listen beyond localhost
listen_addresses = '*'

[parallaxview]

Yup - it is set:

# - Connection Settings -

listen_addresses = '*'
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

EDIT- I was looking at the postgresql.conf not custom... didn't know it needed to be added there...

Added at the end but same issue persists.

customPostgresql.conf:

# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 1 GB
# CPUs num: 1
# Connections num: 200
# Data Storage: ssd

max_connections = 200
shared_buffers = 256MB
effective_cache_size = 768MB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.9
wal_buffers = 7864kB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 655kB
min_wal_size = 1GB
max_wal_size = 4GB
listen_addresses = '*'

[ticoombs]

What does your docker-compose.yml file look like?

    volumes:
      - ./volumes/postgres:/var/lib/postgresql/data:Z
      - ./customPostgresql.conf:/etc/postgresql.conf
    command: 'postgres -c config_file=/etc/postgresql.conf'

Also, did you docker compose down postgres && docker compose up -d postgres after updating customPostgresql.conf?

[parallaxview]

docker-compose.yml:

  postgres:
    image: docker.io/postgres:15-alpine
    hostname: postgres
    environment:
{% if postgres_env_vars is defined and postgres_env_vars|length > 0 %}
{% for item in postgres_env_vars %}
{% for key, value in item.items() %}
      - {{ key }}={{ value }}
{% endfor %}
{% endfor %}
{% endif %}
    volumes:
      - ./volumes/postgres:/var/lib/postgresql/data:Z
      - ./customPostgresql.conf:/etc/postgresql.conf
    restart: always
    command: postgres -c config_file=/etc/postgresql.conf
    shm_size: 1g
    logging: *default-logging

I re-deployed via the ansible commands.

[ticoombs]

Yep that looks all fine.

I re-deployed via the ansible commands

Unfortunately that does not mean postgres re-read the configuration. What does your postgres logs show now? When setup correctly, it should show:

LOG: listening on IPv4 address "0.0.0.0", port 5432

[parallaxview]

Well that seems to be the problem:

LOG: listening on IPv4 address "127.0.0.1", port 5432

I blew away the container and then re-deployed which fixed it! Thanks much for the help!