Menu
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?
478
A read-only transaction or query is a transaction which does not modify any data.
In mysql we can check whether the database is in read-only mode by triggering the below query. SELECT schemaname||'. '||tablename FROM pg_tables WHERE has_table_privilege ( 'postgres', schemaname||'. '||tablename, 'select' ) AND schemaname NOT IN ( 'pg_catalog','information_schema');
When a PostgreSQL server process starts up and discovers a recovery. conf file in the data directory, it starts up in a special mode called “recovery mode”. When in recovery mode, client connections are refused.
This error typically occurs when clustered databases such as Amazon Aurora are in recovery or have recently had a failover. The parameter default_transaction_read_only is set to on:
Normally the most plausible reasons for this kind of error are : trying create statements on a read-only replica (the entire instance is read-only). and you report that the error happens with CREATE SCHEMA at line 6, not before. That means that the CREATE DATABASE does work, when run by <username> .
One possibility that would technically explain this would be that default_transaction_read_only would be ON in the postgresql.conf file, and set to OFF for the database postgres, the one that the invocation of psql connects to, through an ALTER DATABASE statement that supersedes the configuration file.
There are several reasons why you can get this error:
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();
The parameter default_transaction_read_only is set to on. Diagnose with
SHOW default_transaction_read_only;
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.
66
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/
3
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
