org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction

Question

I am using PostgreSQL server and suddenly I am getting this error. Due to that, I am not able to insert data into any table.

org.postgresql.util.PSQLException: ERROR: cannot execute INSERT in a read-only transaction

I have checked solution online and found that the default_transaction_read_only parameter needs to be set to off but it is off already.

Answer 1

Log on to PostgreSQL and verify if your database is in recovery mode or not with below command :-

• select pg_is_in_recovery();

pg_is_in_recovery() should be false(f)

• If the above value is true, intimate admin to move the DB out of recovery mode.

Further check if default transaction is read-only or read-write with below command :-

• show default_transaction_read_only;

default_transaction_read_only must be "off"

• If the value is not off, run the below command
• psql -U username -d postgres begin; alter database dbname set default_transaction_read_only = off; commit; \q

If issue persists after performing the above fix, please check on the connection setting and make sure to use hostname rather than host IP in place of jdbcUrl