pgwatch uses inactive session

[michael-zhasil-pfx]

Describe the bug
In some scenarios, there is a loss of connection between database and pgwatch.
In that time in both pgwatch and database logs there is a log variant on FATAL: password authentication failed for user "pgwatch"
While investigating I've checked DNS, IPs, password etc.
I've fond idle pgwatch connection in db and removed it but the problem still remained.
pgwatch restart fixed the issue at least for a while

To Reproduce
I am unable to tell how exactly the error occurs

Expected behavior
A clear and concise description of what you expected to happen.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• Version pgwatch: 3.5.0
• Version db: 15, 16, 17

Additional context
pgwatch seems to try to use and old connection from it's memmory, that does not exists in the database no more.
Creating new connection fixes the issue

[pashagolub]

Thanks. This is how pgxpool works. If there was a loss of connection, then we need to reconnect. We will do it when the new measurements are fetched. So this is a expected workflow. Every connection has a max connection idle timeout set to 15sec. So every idle connection will be either reestablished, or closed.

I'm not sure why we see exactly this message though. Do you use pgbouncer?

[michael-zhasil-pfx]

No we are not using pgbouncer, also the problem was there for hours before I did a manual restart

[michael-zhasil-pfx]

I looks like it cannot handle end of connection on database restart. I've restarted bunch of databases, and the connections did not recover until pgwatch restart

[pashagolub]

thanks, that's reaaly weird. I will try to reproduce

[michael-zhasil-pfx]

I did some digging.
For at least last three hours reaper monitoredSource.Connect returns OK, with version in format:
Connect OK. Version: PostgreSQL 16.8 (Ubuntu 16.8-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
https://github.com/cybertec-postgresql/pgwatch/blob/master/internal/reaper/reaper.go#L95
The very next log is server error: FATAL: password authentication failed for user

[michael-zhasil-pfx]

I found start of today.
Pgwatch survived by it's logs some database restart and few disconnects then
around 9:16, there is server error: FATAL: the database system is shutting down (SQLSTATE 57P03) in pgwatch pod logs
at 9:32:26.373 we have till 9:32:28.625 connection refursed
after that there is nothing with connection, all metrics are failing except for Connect OK, and instance_up, after that it wait's till restart

[pashagolub]

Hi, I had no chance to reproduce this issue locally. But I'm keeping my eye on it. So if you have anything to add, please, be my guest.

[michael-zhasil-pfx]

Thanks for your time, unfortunately I do not have much of a new information, except for that is happening repeatedly on some instances. From what I gathered it happens when pgwatch monitores db for some time, then there is some longer loss of connection like db restart. After that pgwatch can use the instance_up - i.e. Connection check, but any other metric fails.

[michael-zhasil-pfx]

I've redacted actual names and conn strings but this is logs from two of the failed instances from this evening
`2025-07-07 22:17:37.235
2025-07-07 20:17:37.234 [INFO] [source:database] [metric:instance_up] [rows:1] [cache:false] measurements fetched (reaper/reaper.go:518 reaper.(*Reaper).FetchMetric)

2025-07-07 22:17:37.234
2025-07-07 20:17:37.234 [DEBUG] [source:database] [metric:instance_up] [error:failed to connect to user=pgwatch database=database: 10.68.41.127:5432 (connectionString): server error: FATAL: password authentication failed for user "pgwatch" (SQLSTATE 28P01)] [database:instance_up] failed to fetch metrics. marking instance as not up (reaper/reaper.go:503 reaper.(*Reaper).FetchMetric)`

This log corrects my previous statement instance_up seems without debug OK, but also fails.

On the other hand
2025-07-07 22:09:44.954 2025-07-07 20:09:44.953 [ERROR] [source:testdb] [metric:stat_activity_realtime] [error:failed to connect to user=pgwatch database=testdb`: 10.68.40.165:5432 (testdb.con.str): server error: FATAL: password authentication failed for user "pgwatch" (SQLSTATE 28P01)] failed to fetch metrics (reaper/reaper.go:509 reaper.(*Reaper).FetchMetric)

2025-07-07 22:09:47.254
2025-07-07 20:09:47.254 [ERROR] [source:testdb] [metric:psutil_mem] [error:failed to connect to user=pgwatch database=testdb: 10.68.40.165:5432 (testdb.con.str): server error: FATAL: password authentication failed for user "pgwatch" (SQLSTATE 28P01)] failed to fetch metrics (reaper/reaper.go:509 reaper.(*Reaper).FetchMetric)

2025-07-07 22:09:52.563
2025-07-07 20:09:52.563 [INFO] [source:testdb] [recovery:false] Connect OK. Version: PostgreSQL 17.4 (Ubuntu 17.4-1.pgdg22.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit (reaper/reaper.go:104 reaper.(*Reaper).Reap)

2025-07-07 22:10:00.006
2025-07-07 20:10:00.006 [ERROR] [source:testdb] [metric:stat_activity] [error:failed to connect to user=pgwatch database=testdb: 10.68.40.165:5432 (testdb.con.str): server error: FATAL: password authentication failed for user "pgwatch" (SQLSTATE 28P01)] failed to fetch metrics (reaper/reaper.go:509 reaper.(*Reaper).FetchMetric)

2025-07-07 22:10:03.597
2025-07-07 20:10:03.597 [ERROR] [source:testdb] [metric:psutil_cpu] [error:failed to connect to user=pgwatch database=testdb: 10.68.40.165:5432 (testdb.con.str): server error: FATAL: password authentication failed for user "pgwatch" (SQLSTATE 28P01)] failed to fetch metrics (reaper/reaper.go:509 reaper.(*Reaper).FetchMetric)`

Connect seems OK, prints correct version and so on, but any other metric fails until pgwatch is restarted

[michael-zhasil-pfx]

Hi,
I found out how to replicate the behavior locally.

1. Start with passwordA in both config and db.
2. Change password in db to passwordB
   2.1. I've set pool_max_conn_lifetime_jitter and waited till there is password error, but it might be optional
3. Set passwordB in config

In this case There is Connect OK from reaper.go FetchRuntimeInfo but password error from actual metrics