Error with custom docker-compose: "Can't reach database server"

[william-roy]

I'm trying to add Umami to my existing docker-compose setup, and it starts up just fine, but upon trying to login, can't seem to connect to the database (a digital ocean postgres database cluster). I've seeded the database using the sql/schema.postgresql.sql file, which worked just fine—I can connect through pgadmin and verify this. I can also verify the database is connectable:

Here's the relevant part of the docker-compose.yml (omitting private info):

And here's the error (again, omitting private info):

umami     | PrismaClientInitializationError2 [PrismaClientInitializationError]: Can't reach database server at `<host>`:`25060`
umami     |
umami     | Please make sure your database server is running at `<host>`:`25060`.
umami     |     at cb (/app/node_modules/@prisma/client/runtime/index.js:35107:17)
umami     |     at processTicksAndRejections (internal/process/task_queues.js:97:5) {
umami     |   clientVersion: '2.23.0',
umami     |   errorCode: undefined
umami     | }

I've seen other closed issues where there were problems with the connection string, but i've triple-checked it and can say for sure it is correct and valid. I also have another service in the same docker-compose file connecting to another database in the same cluster just fine, so I don't think it's an issue of permissions, firewalls, outbound connections, etc.

[william-roy]

I managed to solve this by creating a connection pool for Umami and connecting to that. This tutorial mentions that:

PostgreSQL was built to have persistent connections. The Next.js API routes that Umami runs on, however, are not able to share database connections across requests. To support the short-lived connections that will be made from the API route and prevent errors, you will create a connection pool for your cluster.

I didn't see anything about this mentioned in Umami's docs, but it may be worth adding a note somewhere if there isn't one already.

[mikecao]

@william-roy Thanks for the info. The text about postgresql seems odd to me though. I am running postgresql myself and don't have any connection issues, and I don't use pooling. Maybe it's something specific with docker.

[william-roy]

It might be something specific to digitalocean's database clusters? Maybe @okezieuc can clue us in.

[microlancer]

I'm having the same issue, and maybe it's related to docker-entrypoint-initdb? I am not using digitalocean. I see this in the logs:

Creating umami_db_1 ... done                                                                                                                                                                                                    
Creating umami_umami_1 ... done                                                                                                                                                                                                 
Attaching to umami_db_1, umami_umami_1                                                                                                                                                                                          
db_1     | The files belonging to this database system will be owned by user "postgres".
db_1     | This user must also own the server process.
db_1     |                                                                                         
db_1     | The database cluster will be initialized with locale "en_US.utf8".            
db_1     | The default database encoding has accordingly been set to "UTF8".                       
db_1     | The default text search configuration will be set to "english".
db_1     |                                                                                                
db_1     | Data page checksums are disabled.                                                       
db_1     |                                                                                                                      
db_1     | fixing permissions on existing directory /var/lib/postgresql/data ... ok
db_1     | creating subdirectories ... ok                                                                     
db_1     | selecting dynamic shared memory implementation ... posix                                       
db_1     | selecting default max_connections ... 100                                            
db_1     | selecting default shared_buffers ... 128MB                                             
db_1     | selecting default time zone ... UTC                                                            
db_1     | creating configuration files ... ok                                  
db_1     | running bootstrap script ... ok                                                        
db_1     | performing post-bootstrap initialization ... sh: locale: not found                                                                        
db_1     | 2021-07-25 01:21:21.209 UTC [29] WARNING:  no usable system locales were found                
db_1     | ok                                                                                                       
umami_1  | yarn run v1.22.4                                                                               
db_1     | syncing data to disk ... initdb: warning: enabling "trust" authentication for local connections             
db_1     | You can change this by editing pg_hba.conf or using the option -A, or
db_1     | --auth-local and --auth-host, the next time you run initdb.                                               
db_1     | ok             
db_1     |                                                                                                                                                                         
db_1     |           
db_1     | Success. You can now start the database server using:                                                     
db_1     |                                                                                                          
db_1     |     pg_ctl -D /var/lib/postgresql/data -l logfile start                              
db_1     |                                                                                                             
umami_1  | $ next start  
db_1     | waiting for server to start....2021-07-25 01:21:21.661 UTC [34] LOG:  starting PostgreSQL 12.7 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit
db_1     | 2021-07-25 01:21:21.662 UTC [34] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1     | 2021-07-25 01:21:21.674 UTC [35] LOG:  database system was shut down at 2021-07-25 01:21:21 UTC           
db_1     | 2021-07-25 01:21:21.681 UTC [34] LOG:  database system is ready to accept connections  
db_1     |  done
db_1     | server started
db_1     | CREATE DATABASE
db_1     |
db_1     |
db_1     | /usr/local/bin/docker-entrypoint.sh: running /docker-entrypoint-initdb.d/schema.postgresql.sql
db_1     | psql:/docker-entrypoint-initdb.d/schema.postgresql.sql:1: NOTICE:  table "event" does not exist, skipping
db_1     | DROP TABLE
db_1     | psql:/docker-entrypoint-initdb.d/schema.postgresql.sql:2: NOTICE:  table "pageview" does not exist, skipping
db_1     | DROP TABLE
db_1     | psql:/docker-entrypoint-initdb.d/schema.postgresql.sql:3: NOTICE:  table "session" doesnot exist, skipping
db_1     | DROP TABLE
db_1     | psql:/docker-entrypoint-initdb.d/schema.postgresql.sql:4: NOTICE:  table "website" doesnot exist, skipping
db_1     | DROP TABLE
db_1     | psql:/docker-entrypoint-initdb.d/schema.postgresql.sql:5: NOTICE:  table "account" doesnot exist, skipping
db_1     | DROP TABLE
db_1     | CREATE TABLE
db_1     | CREATE TABLE
db_1     | CREATE TABLE
db_1     | CREATE TABLE
db_1     | CREATE TABLE
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | CREATE INDEX
db_1     | INSERT 0 1
db_1     |
db_1     |
db_1     | waiting for server to shut down....2021-07-25 01:21:21.868 UTC [34] LOG:  received fastshutdown request
db_1     | 2021-07-25 01:21:21.869 UTC [34] LOG:  aborting any active transactions
db_1     | 2021-07-25 01:21:21.870 UTC [34] LOG:  background worker "logical replication launcher"(PID 41) exited with exit code 1
db_1     | 2021-07-25 01:21:21.876 UTC [36] LOG:  shutting down
db_1     | 2021-07-25 01:21:21.885 UTC [34] LOG:  database system is shut down
umami_1  | ready - started server on 0.0.0.0:3000, url: http://localhost:3000
db_1     |  done
db_1     | server stopped
db_1     |
db_1     | PostgreSQL init process complete; ready for start up.
db_1     |
db_1     | 2021-07-25 01:21:21.980 UTC [1] LOG:  starting PostgreSQL 12.7 on x86_64-pc-linux-musl,compiled by gcc (Alpine 10.3.1_git20210424) 10.3.1 20210424, 64-bit
db_1     | 2021-07-25 01:21:21.981 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1     | 2021-07-25 01:21:21.981 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1     | 2021-07-25 01:21:21.982 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1     | 2021-07-25 01:21:21.992 UTC [50] LOG:  database system was shut down at 2021-07-25 01:21:21 UTC
db_1     | 2021-07-25 01:21:21.995 UTC [1] LOG:  database system is ready to accept connections
umami_1  | PrismaClientInitializationError2 [PrismaClientInitializationError]: Can't reach database server at `db`:`5432`
umami_1  |
umami_1  | Please make sure your database server is running at `db`:`5432`.
umami_1  |     at cb (/app/node_modules/@prisma/client/runtime/index.js:33902:17)
umami_1  |     at processTicksAndRejections (internal/process/task_queues.js:97:5) {
umami_1  |   clientVersion: '2.26.0',
umami_1  |   errorCode: undefined
umami_1  | }
umami_1  | PrismaClientInitializationError2 [PrismaClientInitializationError]: Can't reach database server at `db`:`5432`
umami_1  |
umami_1  | Please make sure your database server is running at `db`:`5432`.
umami_1  |     at cb (/app/node_modules/@prisma/client/runtime/index.js:33902:17)
umami_1  |     at processTicksAndRejections (internal/process/task_queues.js:97:5) {
umami_1  |   clientVersion: '2.26.0',
umami_1  |   errorCode: undefined
umami_1  | }

It definitely seems like a docker or container networking issue. I can ping between umami_db_1 and umami_umami_1 just fine. But when I try to establish a TCP connection, it gets blocked or hangs. For example, from the db -> web:

/ # ping umami
PING umami (192.168.80.4): 56 data bytes
64 bytes from 192.168.80.4: seq=0 ttl=64 time=0.119 ms
64 bytes from 192.168.80.4: seq=1 ttl=64 time=0.126 ms
^C
--- umami ping statistics ---
2 packets transmitted, 2 packets received, 0% packet loss
round-trip min/avg/max = 0.119/0.122/0.126 ms
/ # nc -vz umami 3000
^Cpunt!
/ # nc -vz 192.168.80.4 3000
^Cpunt!

I just tested on another machine (Ubuntu 18.04) and it worked fine. I now suspect it's something like docker's networking state being messed up and requiring maybe a reset like described in https://stackoverflow.com/a/20431030/95560 but I haven't tested that because I don't want to disturb the other containers. The machine where it isn't working is a Debian 9 server running docker version 19.03.9 and the machine where it works is 20.10.7.

@william-roy what version of docker are you using?

[microlancer]

I narrowed down the issue to my host firewall. For example, if I disable my firewall with the command ufw disable and then try the same nc command it connects properly. Apparently this ufw-docker incompatibility was well-known since 2014 and leading to high-profile security incidents. I fixed the issue by following the steps for reconfiguring both docker and ufw as shown @ https://stackoverflow.com/a/46266757/95560 Everything is working fine now, and I can login to umami successfully.