Pgwatch2 1.9.0 : msg "WARN main: Could not find PG version info for DB" with monitored replica standby databases

[LudoAugereau]

Hello.

We are using pgwatch2 1.8.5 and are currently evaluating the last version 1.9.0 of Pgwatch2 (v1.9.0#a0c546 (2022-04-25T14:35:52+02:00)) because we are very interested in the improvment of connexions pooling.

But we encountered a lot of errors "WARN main: Could not find PG version info for DB " when pgwatch2 connects on monitored databases that are replicas (e.G in recovery with ps_is_in_recovery()=true).

In the postgresql.log of PostgreSQL instance we see a lot of errors "user=pgwatch2,app=pgwatch2,client=****** ERROR: cannot set transaction read-write mode during recovery"**

So it seems like pgwatch2 1.9.0 doesn't take into account the fact that the monitored DB is in recovery mode (however this should be checked) and tries to open transactions in READ WRITE mode (rejected by the PostgreSQL DB instance of course).

Thanks a lot in advance for your help.

[pashagolub]

Hello,

can you, please, provide us with Docker compose file to reproduce the bug?

Thanks in advance!

[LudoAugereau]

Hello,

Thank you for your feedback and sorry for my late reply.
Can you please telll me where can I find this file ?

Here under is the way I've created and started the docker from the image cybertec/pgwatch2-postgres:1.9.0, with pooling (PW2_CONN_POOLING=on) and a PG database for storing metrics (PW2_PG_METRIC_STORE_CONN_STR) with TimescaleDB.

docker run -d --restart=unless-stopped --name pw2_190_pg_tsdb_pool -p ... -p ... -e PW2_PGHOST=... -e PW2_PGPORT=... -e PW2_PGDATABASE=pgwatch2 -e PW2_PGUSER=pgwatch2 -e PW2_PGPASSWORD=.... -e PW2_DATASTORE=postgres -e PW2_PG_METRIC_STORE_CONN_STR="postgresql://pgwatch2:......@....:..../....." -e PW2_CONN_POOLING=on -e PW2_PG_RETENTION_DAYS=60 cybertec/pgwatch2-postgres:1.9.0

[LudoAugereau]

I have to tell you that since the time I've created this issue, we've understood a litlle bit more what's happening with connections and transactions on monitored databases when POOLING is on.

In the function DBExecReadByDbUniqueName, in case PW2_CONN_POOLING is on, the SQL is executed inside a transaction (call to DBExecInExplicitTX), trying then to open a TX with a "BEGIN READ WRITE" clause, causing errors in postgresql.log of monitored db under recovery mode (replicas). In the other case, (when pooling is off), call to DBExecRead is made (which is OK on any database, whatever it is in recovery or not).

So we have made a FIX here under in the pgwatch2.go of our docker (this "if" is a little bit dummy, but then DBExecRead is called in any case and it works fine (pooling works fine although) !

So why this call of DBExecInExplicitTX when pooiling is on and why reading metric data onto monitored dbs inside transactions ?

Thank you very much for your kindly help.

func DBExecReadByDbUniqueName(dbUnique, metricName string, stmtTimeoutOverride int64, sql string, args ...interface{}) (, error, time.Duration) {
...
sqlToExec := sqlLockTimeout + sqlStmtTimeout + sql // bundle timeouts with actual SQL to reduce round-trip times
//log.Debugf("Executing SQL: %s", sqlToExec)
t1 := time.Now()
if useConnPooling {
//data, err = DBExecInExplicitTX(conn, dbUnique, sqlToExec, args...)
data, err = DBExecRead(conn, dbUnique, sqlToExec, args...)
} else {
data, err = DBExecRead(conn, dbUnique, sqlToExec, args...)
}
...

[pashagolub]

Hello,

would you please test #551.

Thanks in advance!

[LudoAugereau]

Hello,

I have tested your fix and it works well!
So requests are still executed inside transactions when pooling is ON, but TX are open in READ ONLY mode.

Thank you very much for your attention and your work.

Do you know why transactions are opened by pgwatch2 in case of pooling of sessions ?

Have a good day,
Ludovic

[pashagolub]

Cool! Pooling is a complicated story so to fine control what and how is going on pgwatch2 uses transactions in this mode. There are some comments and notices in the commit history about this.

Thanks a lot for your help.

[eshkinkot]

Looks like bug in lib/pq https://github.com/lib/pq/blob/d5affd5073b06f745459768de35356df2e5fd91d/conn_go18.go#L76

	if opts.ReadOnly {
		mode += " READ ONLY"
	} else {
		mode += " READ WRITE"
	}

	tx, err := cn.begin(mode)

There is no default BEGIN, only BEGIN READ WRITE or BEGIN READ ONLY. Default BEGIN is not implemented.