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?

Solution 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.

Solution 2

This was a bug which is now fixed, Fix will be available in next release.

https://redmine.postgresql.org/issues/3973

If you want to try then you can use Nightly build and check: https://www.postgresql.org/ftp/pgadmin/pgadmin4/snapshots/2019-02-17/

Comments

[Gilles Groven]

But at this stage i am not even connect to the server yet. it when it tries to connect it throws these errors.

[Laurenz Albe]

Can you try with psql? Can you get the PostgreSQL log file?

[Gajendra D Ambi]

My newly created db is in recovery mode and SHOW transaction_read_only; results in true. How can i set recovery off and read only to false

[Laurenz Albe]

@GajendraDAmbi How did you create that database? I cannot think of a way to create a database in recovery mode. But I think you should ask a new question for your problem.

[Gajendra D Ambi]

@LaurenzAlbe manually from pgadmin4 but not I got it resolved, I just had to connect to DB first before executing the commands.