Postgres docker container not listening

[NachoPicchu]

Using the 1.2.0 version of the ansible script on a fresh debian 11 installation, the created postgres docker container does not appear to be listening for connections. The playbook runs through without any errors and results in a hosted lemmy page, but that page simply displays an error like here: #141 (comment)

The Docker Lemmy logs say:

thread 'main' panicked at 'Error connecting to postgres://lemmy:xxxxxxx@postgres:5432/lemmy: could not connect to server: Connection refused
Is the server running on host "postgres" (172.18.0.5) and accepting
TCP/IP connections on port 5432?
', crates/db_schema/src/utils.rs:217:56
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

The Docker Postgres logs say:

PostgreSQL Database directory appears to contain a database; Skipping initialization
2023-10-15 10:34:22.941 GMT [1] LOG:  starting PostgreSQL 15.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit
2023-10-15 10:34:22.942 GMT [1] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-10-15 10:34:22.942 GMT [1] LOG:  could not bind IPv6 address "::1": Address not available
2023-10-15 10:34:22.945 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-10-15 10:34:22.953 GMT [24] LOG:  database system was shut down at 2023-10-15 10:34:22 GMT
2023-10-15 10:34:22.964 GMT [1] LOG:  database system is ready to accept connections
2023-10-15 10:39:23.007 GMT [22] LOG:  checkpoint starting: time
2023-10-15 10:39:23.046 GMT [22] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.032 s, sync=0.002 s, total=0.039 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB

docker ps -a looks like this:

CONTAINER ID   IMAGE                        COMMAND                  CREATED        STATUS                            PORTS                                               NAMES
519fed806c20   nginx                        "/docker-entrypoint.…"   12 hours ago   Up 12 hours                       80/tcp, 0.0.0.0:2045->8536/tcp, :::2045->8536/tcp   domainname_proxy_1
8df9a59e604b   asonix/pictrs:0.4.3          "/sbin/tini -- /usr/…"   12 hours ago   Up 12 hours                       6669/tcp, 8080/tcp                                  domainname_pictrs_1
28d86c12f2ef   postgres:15-alpine           "docker-entrypoint.s…"   12 hours ago   Up 15 minutes                     5432/tcp                                            domainname_postgres_1
5fcf8ea7981d   dessalines/lemmy:0.18.5      "/app/lemmy"             13 hours ago   Restarting (101) 16 seconds ago                                                       domainname_lemmy_1
33a140f107e2   dessalines/lemmy-ui:0.18.5   "docker-entrypoint.s…"   13 hours ago   Up 12 hours                       1234/tcp                                            domainname_lemmy-ui_1
b82a8fceaee6   mwader/postfix-relay         "/root/run"              13 hours ago   Up 12 hours                       25/tcp                                              domainname_postfix_1

Running 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:80              0.0.0.0:*               LISTEN      4536/nginx: master
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      455/sshd: /usr/sbin
tcp        0      0 0.0.0.0:443             0.0.0.0:*               LISTEN      4536/nginx: master
tcp        0      0 0.0.0.0:2045            0.0.0.0:*               LISTEN      4329/docker-proxy
tcp6       0      0 :::80                   :::*                    LISTEN      4536/nginx: master
tcp6       0      0 :::22                   :::*                    LISTEN      455/sshd: /usr/sbin
tcp6       0      0 :::443                  :::*                    LISTEN      4536/nginx: master
tcp6       0      0 :::2045                 :::*                    LISTEN      4337/docker-proxy

Finally, for good measure root@machine_xyz:~# docker network inspect domainname_default:

[
{
"Name": "domainname_default",
"Id": "2207f27c9070eeb474d3b82dd0a68da28c38528018cacb9d9692732e4de93e4f",
"Created": "2023-10-14T17:11:22.661151681-04:00",
"Scope": "local",
"Driver": "bridge",
"EnableIPv6": false,
"IPAM": {
"Driver": "default",
"Options": null,
"Config": [
{
"Subnet": "172.18.0.0/16",
"Gateway": "172.18.0.1"
}
]
},
"Internal": false,
"Attachable": true,
"Ingress": false,
"ConfigFrom": {
"Network": ""
},
"ConfigOnly": false,
"Containers": {
"28d86c12f2ef7dffbd82ee32b508a996128d012792c738a8ae241fb25f694b39": {
"Name": "domainname_postgres_1",
"EndpointID": "d9e5359c4c67ff55ca148e028426569573b25aa30b0b7511df83a2ca3616797a",
"MacAddress": "02:42:ac:12:00:05",
"IPv4Address": "172.18.0.5/16",
"IPv6Address": ""
},
"33a140f107e224b2d96119b72de3c6bfbeea1bfca4565b5fc3bdf559d87b62c1": {
"Name": "domainname_lemmy-ui_1",
"EndpointID": "38d71a70d4ee3ccc6eec6f2406809d76741b12f7d8dcf2afb2efa2c23682f344",
"MacAddress": "02:42:ac:12:00:07",
"IPv4Address": "172.18.0.7/16",
"IPv6Address": ""
},
"519fed806c2024f94a2f9dc49f41819141c3a736670be5593eb339d491f148d2": {
"Name": "domainname_proxy_1",
"EndpointID": "9f6a6dfe82d2e4b6b2b0b9588c170992bcd1940a51d758c13152502179426207",
"MacAddress": "02:42:ac:12:00:04",
"IPv4Address": "172.18.0.4/16",
"IPv6Address": ""
},
"8df9a59e604b0701e84a17d4ac238bc925a4e46a6a447490bcb9851fb08c87b4": {
"Name": "domainname_pictrs_1",
"EndpointID": "31a3c59309e29b677599ee38198eade65d101d307ae8594f3036098c987965ff",
"MacAddress": "02:42:ac:12:00:02",
"IPv4Address": "172.18.0.2/16",
"IPv6Address": ""
},
"b82a8fceaee6099893db10d6402c573a3843182c7e4c46c1b313668a89f70d26": {
"Name": "domainname_postfix_1",
"EndpointID": "29a9c1e397d7351a320b4dee8fda54c0724a7f16dbc9a39c2c9a9970ec5b9b4b",
"MacAddress": "02:42:ac:12:00:03",
"IPv4Address": "172.18.0.3/16",
"IPv6Address": ""
}
},
"Options": {},
"Labels": {
"com.docker.compose.network": "default",
"com.docker.compose.project": "domainname",
"com.docker.compose.version": "1.25.0"
}
}
]

[NachoPicchu]

The reason for the postgres container not listening was that my customPostgresql.conf did not contain the line
listen_addresses = '*'
However, lemmy logs still show errors related to the DB:

thread 'main' panicked at 'Error connecting to postgres://lemmy:xxxxxxxxxx@postgres:5432/lemmy: FATAL:  no pg_hba.conf entry for host "172.18.0.5", user "lemmy", database "lemmy", no encryption
', crates/db_schema/src/utils.rs:217:56
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

Which is mirrored by the postgres log:

PostgreSQL Database directory appears to contain a database; Skipping initialization

2023-10-15 11:20:57.046 GMT [1] LOG:  starting PostgreSQL 15.4 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit
2023-10-15 11:20:57.047 GMT [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-10-15 11:20:57.047 GMT [1] LOG:  listening on IPv6 address "::", port 5432
2023-10-15 11:20:57.050 GMT [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-10-15 11:20:57.057 GMT [24] LOG:  database system was shut down at 2023-10-15 11:20:56 GMT
2023-10-15 11:20:57.066 GMT [1] LOG:  database system is ready to accept connections
2023-10-15 11:21:01.331 GMT [28] FATAL:  no pg_hba.conf entry for host "172.18.0.5", user "lemmy", database "lemmy", no encryption
2023-10-15 11:22:01.736 GMT [30] FATAL:  no pg_hba.conf entry for host "172.18.0.5", user "lemmy", database "lemmy", no encryption
2023-10-15 11:23:02.185 GMT [32] FATAL:  no pg_hba.conf entry for host "172.18.0.5", user "lemmy", database "lemmy", no encryption
2023-10-15 11:24:02.597 GMT [34] FATAL:  no pg_hba.conf entry for host "172.18.0.5", user "lemmy", database "lemmy", no encryption
2023-10-15 11:25:02.991 GMT [36] FATAL:  no pg_hba.conf entry for host "172.18.0.5", user "lemmy", database "lemmy", no encryption

My current customPostgresql.conf looks like this:

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

max_connections = 200
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 512MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 5242kB
huge_pages = off
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 4
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
max_parallel_maintenance_workers = 2
listen_addresses = '*'

I have not touched the vars.yml file. The relevant section still reads:

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

The pg_hba.conf file inside the container (referenced in the lemmy and postgres error logs) looks like this:

postgres:/# cat ./var/lib/postgresql/data/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local         DATABASE  USER  METHOD  [OPTIONS]
# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl     DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostgssenc    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnogssenc  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type:
# - "local" is a Unix-domain socket
# - "host" is a TCP/IP socket (encrypted or not)
# - "hostssl" is a TCP/IP socket that is SSL-encrypted
# - "hostnossl" is a TCP/IP socket that is not SSL-encrypted
# - "hostgssenc" is a TCP/IP socket that is GSSAPI-encrypted
# - "hostnogssenc" is a TCP/IP socket that is not GSSAPI-encrypted
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# "gss", "sspi", "ident", "peer", "pam", "ldap", "radius" or "cert".
# Note that "password" sends passwords in clear text; "md5" or
# "scram-sha-256" are preferred since they send encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the server receives a
# SIGHUP signal.  If you edit the file on a running system, you have to
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
# or execute "SELECT pg_reload_conf()".
#
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.

# CAUTION: Configuring the system for local "trust" authentication
# allows any local user to connect as any PostgreSQL user, including
# the database superuser.  If you do not trust all your local users,
# use another authentication method.


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

Running the ansible uninstall script, stopping and removing all docker containers and then installing again has not helped here.

[NachoPicchu]

Solution:

The issue was that my customPostgresql.conf file did not contain the line
listen_addresses = '*'
Everything else I've encountered is downstream from that. (I have resolved it by removing all docker containers and images and removing the install directory with the volumes and config files, running the uninstall.yml and installing again)

[codyro]

Looks like you got this figured out. This should be fixed in 1.2.1 (#183)