ERROR: cannot execute UPDATE in a read-only transaction in IntelliJ IDEA with PostgreSQL

Question

I'm connecting to the PostgreSQL database using IntelliJ IDEA DB Navigator plugin. Connections is established, and I can do selects on different tables. But when I try to execute an update statement from the console, I get the following error:

Error executing UPDATE statement. ERROR: cannot execute UPDATE in a read-only transaction - Connection: *: 47ms 

Is there any way to figure out how to fix the error or how to check why the transaction in the read-only mode?

Versions: IntelliJ IDEA 2020.3.2 (Ultimate Edition) VM: OpenJDK 64-Bit Server VM by JetBrains s.r.o. DB Navigator version 3.2

Answer 1

In my case, I ended up connecting to the read-only slave replica instead of the master database. We used multiple-host connection string in the form of

postgresql://host1:port1,host2:port2,host3:port3/

where one of the hosts was a slave replica. Without setting targetServerType=master the JDBC connected to the read-only replica.

After checking that the instance is indeed in recovery mode, or read-only, I changed the connection string. Adding targetServerType=master (or targetServerType=primary) settings resolved the issue:

jdbc:postgresql://hostmaster.com:5432,hostsecond.com:5432/dbName?&targetServerType=master