ERROR: cannot execute SELECT in a read-only transaction when connecting to DB

Question

When trying to connect to my Amazon PostgreSQL DB, I get the above error. With pgAdmin, I get "error saving properties".

I don't see why to connect to a server, I would do any write actions?

Answer 1

There are several reasons why you can get this error:

1. The PostgreSQL cluster is in recovery (or is a streaming replication standby). You can find out if that is the case by running

   SELECT pg_is_in_recovery();
   

2. The parameter default_transaction_read_only is set to on. Diagnose with

   SHOW default_transaction_read_only;
   

3. The current transaction has been started with

   START TRANSACTION READ ONLY;
   

   You can find out if that is the case using the undocumented parameter

   SHOW transaction_read_only;
   

If you understand that, but still wonder why you are getting this error, since you are not aware that you attempted any data modifications, it would mean that the application that you use to connect tries to modify something (but pgAdmin shouldn't do that).

In that case, look into the log file to find out what statement causes the error.