Docker image - ERROR: Could not connect to Postgres

[tomskopek]

I'm seeing ERROR: Could not connect to Postgres, and am not seeing any Preset Metric Configs.

I'm trying to set up on a digital ocean droplet and connect to monitor a remote postgres db. I've installed pgwatch via the docker command.

docker run -d --restart=unless-stopped --name pw2 \
  -p 3000:3000 -p 8080:8080 -p 127.0.0.1:5432:5432 \
  -e PW2_TESTDB=true \
  cybertec/pgwatch2-postgres:latest

I'm probably doing something stupid. I'm not well versed with docker. Any ideas what it could be or how to debug the connection?

Note: I did not apt install postgres on the droplet since I understand that the docker image does that. Is that wrong?

Thanks in advance!

My full steps (I believe most are unrelated but including them all):

1. Create a droplet on digitalocean
2. Follow the initial server setup guide to create a sudo non-root user
3. Follow the docker install guide
4. Follow the installing nginx guide
5. Configure nginx to proxy_pass / to 8080 (temporarily to set up connection)
6. Follow the certbot steps to enable SSL
7. Allow https traffic through ufw firewall
8. Point my log.mydomain.com to droplet IP
9. Run the pgwatch2 docker container
10. Navigate to log.mydomain.com

Details

$ docker ps -a
CONTAINER ID   IMAGE                               COMMAND                  CREATED          STATUS          PORTS                                                                                                                                NAMES
2b1b2dd63073   cybertec/pgwatch2-postgres:latest   "/pgwatch2/docker-la…"   14 minutes ago   Up 14 minutes   0.0.0.0:3000->3000/tcp, :::3000->3000/tcp, 8081/tcp, 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp, 127.0.0.1:5432->5432/tcp, 9187/tcp   pw2

$ docker logs --follow pw2
......+.+........+......+....+..+...+......+.......+.....+.......+..+.+.....+.......+...+...+.................+....+.........+..+.......+...+...+....................+.+.....+.+..+.......+...+...+..+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.....+..............+.+...+.....+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*.............+...+...+....+...........................+......+.....+...+.......+..................+...........+...+.+......+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
.....+......+...+..+.........+.+.....+....+..+...............+.......+..+...+...+.......+...+..+......+.......+..+............+......+...+.......+........+......+.+........+....+...+..+...+.........+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*...............+..+.......+.........+..+...+.+...+...+.....+...+.......+...+............+...+......+........+.+......+........+.+..+....+...+..+....+.....+.........+.+..+....+......+.....+...+.+...........+...+....+.....+..........+..+.........+.+...+........+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*............+............+...+............+......+..+.+...........+...+............+.+.....+...+......+...+...+.......+..+....+.........+............+.....+....+............+............+...+..+......................+.....+......+.......+...+...+.....+.......+..+.+......+.........+......+......+.......................+..........+.....+.+......+.....+....+...+..+.............+........+.......+..+......+...................+.....+...+............+...............+......+....+......+.....+..........+....................+....+...+...+..+.......+.....................+...+..+...+.+..............+.+......+.....+.........+.+..............+.............+.....+.+............+..+.......+.....+.+..+..................+....+...........+...+....+...............+...........+.+...........+..........+..............+...+............+..........+......+........+..........+......+...+......+.....+....+.....+......+..........+.................+....+........+...+...+............+.........+.+...+..+....+.........+.....+...............+....+..+...............+..........+...+...+..+................+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-----
CREATE ROLE
GRANT ROLE
CREATE DATABASE
REVOKE
GRANT
CREATE DATABASE
CREATE DATABASE
CREATE SCHEMA
SET
ALTER DATABASE
SET
CREATE TABLE
CREATE TABLE
CREATE INDEX
ALTER TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 16
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 17
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE SCHEMA
GRANT
psql:/pgwatch2/sql/metric_store/00_schema_base.sql:17: WARNING:  NB! Enabling asynchronous commit for pgwatch2 role - revert if possible data loss on crash is not acceptable!
DO
SET
CREATE TABLE
COMMENT
CREATE INDEX
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
CREATE FUNCTION
CREATE TRIGGER
CREATE FUNCTION
GRANT
RESET
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE FUNCTION
GRANT
CREATE SCHEMA
CREATE EXTENSION
SET
CREATE TABLE
COMMENT
CREATE INDEX
CREATE INDEX
CREATE TABLE
COMMENT
CREATE INDEX
RESET
INSERT 0 1
CREATE FUNCTION
GRANT
BEGIN
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
COMMIT
BEGIN
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_stat_statements/9.4/metric.sql:55: NOTICE:  ALTER FUNCTION get_stat_statements() SET search_path TO public, pgwatch2
DO
COMMIT
CREATE FUNCTION
GRANT
COMMENT
CREATE FUNCTION
GRANT
COMMENT
BEGIN
CREATE EXTENSION
CREATE FUNCTION
psql:/pgwatch2/metrics/00_helpers/get_table_bloat_approx/9.5/metric.sql:47: NOTICE:  ALTER FUNCTION get_table_bloat_approx() SET search_path TO public, pgwatch2
DO
COMMIT
BEGIN
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_table_bloat_approx_sql/12/metric.sql:153: NOTICE:  ALTER FUNCTION get_table_bloat_approx_sql() SET search_path TO public, pgwatch2
DO
COMMIT
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_psutil_cpu/9.1/metric.sql:5: NOTICE:  extension "plpython3u" already exists, skipping
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_psutil_mem/9.1/metric.sql:2: NOTICE:  extension "plpython3u" already exists, skipping
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_psutil_disk/9.1/metric.sql:2: NOTICE:  extension "plpython3u" already exists, skipping
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
psql:/pgwatch2/metrics/00_helpers/get_psutil_disk_io_total/9.1/metric.sql:2: NOTICE:  extension "plpython3u" already exists, skipping
CREATE EXTENSION
CREATE FUNCTION
GRANT
COMMENT
CREATE EXTENSION
INSERT 0 1
2023-06-22 03:20:54,881 INFO Set uid to user 0 succeeded
2023-06-22 03:20:54,885 INFO RPC interface 'supervisor' initialized
2023-06-22 03:20:54,886 CRIT Server 'unix_http_server' running without any HTTP authentication checking
2023-06-22 03:20:54,886 INFO supervisord started with pid 1
2023-06-22 03:20:55,889 INFO spawned: 'supervisord_bootstrap' with pid 122
supervisorctl start postgres ...
2023-06-22 03:20:55,897 INFO success: supervisord_bootstrap entered RUNNING state, process has stayed up for > than 0 seconds (startsecs)
2023-06-22 03:20:56,063 INFO spawned: 'postgres' with pid 124
2023-06-22 03:20:56,064 INFO success: postgres entered RUNNING state, process has stayed up for > than 0 seconds (startsecs)
postgres: started
sleep 5
supervisorctl start grafana ...
2023-06-22 03:21:01,321 INFO spawned: 'grafana' with pid 135
2023-06-22 03:21:01,322 INFO success: grafana entered RUNNING state, process has stayed up for > than 0 seconds (startsecs)
grafana: started
sleep 5
supervisorctl start grafana_dashboard_setup ...
2023-06-22 03:21:06,590 INFO spawned: 'grafana_dashboard_setup' with pid 147
2023-06-22 03:21:06,591 INFO success: grafana_dashboard_setup entered RUNNING state, process has stayed up for > than 0 seconds (startsecs)
grafana_dashboard_setup: started
sleep 5
supervisorctl start pgwatch2 ...
2023-06-22 03:21:11,821 INFO spawned: 'pgwatch2' with pid 155
2023-06-22 03:21:12,824 INFO success: pgwatch2 entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
pgwatch2: started
sleep 5
2023-06-22 03:21:16,743 INFO exited: grafana_dashboard_setup (exit status 0; expected)
supervisorctl start webpy ...
2023-06-22 03:21:18,045 INFO spawned: 'webpy' with pid 374
2023-06-22 03:21:19,751 INFO success: webpy entered RUNNING state, process has stayed up for > than 1 seconds (startsecs)
webpy: started
sleep 5
2023-06-22 03:21:24,787 INFO exited: supervisord_bootstrap (exit status 0; expected)

[pashagolub]

Hey.

The very first question is: where do you see ERROR: Could not connect to Postgres?

Checked your logs. Everything seems pretty normal. Any firewall involved?

[tomskopek]

Thanks for the quick reply!
Seeing ERROR: Could not connect to Postgres on the 8080 configuration GUI page - there is a screenshot in the original post, and again highlighted here:

There is a firewall on the droplet, yes

$ sudo ufw status

Status: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
Nginx Full                 ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
Nginx Full (v6)            ALLOW       Anywhere (v6)

I just now tried disabling the firewall, and recreating the pgwatch2 docker container, but I'm still getting the same ERROR: Could not connect to Postgres

[tomskopek]

Another two things to highlight, which might help diagnose the issue:

The Preset metrics config is empty. From my understanding this should be populated?

And another thing that might help:

Entering into the docker container and trying to connect with psql gives:

$ docker exec -it pw2 bash
root@de0cb2aab9ae:/# psql postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist

[tomskopek]

I'm now able to see the pgwatch2 tables by using psql as the pgwatch2 user inside the docker container.

So - could it be an issue with:

• port 8080 not able to communicate with port 5432?
• configuration incorrect for the pgwatch2 postgres db?

These are my two ideas at the moment but not sure how to proceed. I'd be grateful for any guidance.

$ docker exec -it pw2 bash
root@de0cb2aab9ae:/# su postgres
postgres@de0cb2aab9ae:/$ psql
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

postgres=# \dt
Did not find any relations.
postgres=# \du
                                     List of roles
 Role name |                         Attributes                         |  Member of
-----------+------------------------------------------------------------+--------------
 pgwatch2  |                                                            | {pg_monitor}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=#
\q
postgres@de0cb2aab9ae:/$ su pgwatch2
su: user pgwatch2 does not exist or the user entry does not contain all the required fields
postgres@de0cb2aab9ae:/$ psql -U pgwatch2
psql (15.3 (Ubuntu 15.3-1.pgdg22.04+1))
Type "help" for help.

pgwatch2=> \dt
               List of relations
  Schema  |       Name       | Type  |  Owner
----------+------------------+-------+----------
 pgwatch2 | metric           | table | pgwatch2
 pgwatch2 | metric_attribute | table | pgwatch2
 pgwatch2 | monitored_db     | table | pgwatch2
 pgwatch2 | preset_config    | table | pgwatch2
 pgwatch2 | schema_version   | table | pgwatch2
(5 rows)

[pashagolub]

In all these examples you are using Unix-socket to connect which works fine locally. Try to add explicit -h localhost or -h <public ip> to the command line

[tomskopek]

Sorry for the spam - I'm trying to share as much info as possible to help diagnose my issue:

I tried connecting to the droplet pgwatch2 db from my local machine.

I've tried all steps twice, starting the docker container both with the -p 127.0.0.1:5432:5432 option and without it.

Reminder that I disabled my firewall, so I don't think this can be the issue anymore:

$ sudo ufw status
Status: inactive

[pashagolub]

Would you please try the latest official one 1.10, not the latest (which is built against master):

docker run -d --restart=unless-stopped --name pw2 \
  -p 3000:3000 -p 8080:8080 -p 127.0.0.1:5432:5432 \
  -e PW2_TESTDB=true \
  cybertec/pgwatch2-postgres:1.10

That might be a bug in the dev version.

[tomskopek]

Bingo!

1.10 works for me. Thanks! 😄

[pashagolub]

What is good for you is bad for us. :) That means we have a bug. You're more than welcome!