Hi All,
I am facing an error while running some process on primary/master server.
cannot execute DROP SCHEMA in a read-only transaction.
cannot execute DROP EXTENSION in a read-only transaction.
I see that there are a lot of INSERT, UPDATE operation performed successfully on primary.
Any reason wy such error occued on primary node?
Thanks in advance.
Hi ram1992,
Could you please check in postgresql.conf file default_transaction_read_only is set to "on" for cluster. But as per your update that this shouldn't be the case.
Can you check wether the user,database which you have connected has read_only access.
Below are the example.
I have created a user repuser in my local machine and I have provided the read_only access to the repuser by below command.
postgres=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
repuser | Replication | {} |
postgres=# alter user repuser set default_transaction_read_only =on;
ALTER ROLE
Provided the readonly access to the "repuser", Connected the postgres database with "repuser" and tried to drop the scema and extension and I received the same error.
[postgres@primary data]$ psql -p 5432 -U repuser -d postgres
psql (10.6)
Type "help" for help.
postgres=> drop schema public ;
ERROR: cannot execute DROP SCHEMA in a read-only transaction
postgres=> drop extension dblink ;
ERROR: cannot execute DROP EXTENSION in a read-only transaction
postgres=> alter database prod set default_transaction_read_only =on;
ERROR: cannot execute ALTER DATABASE in a read-only transaction
postgres=> \q
[postgres@primary data]$ psql -p 5432 -U postgres
psql (10.6)
Type "help" for help.
Database readonly access.
Provided the readonly access to the "prod" database and tried to drop the scema and extension and I received the same error.
postgres=# alter database prod set default_transaction_read_only =on;
ALTER DATABASE
postgres=# \c prod
You are now connected to database "prod" as user "postgres".
prod=# drop schema public ;
ERROR: cannot execute DROP SCHEMA in a read-only transaction
prod=# drop extension dblink ;
ERROR: cannot execute DROP EXTENSION in a read-only transaction
This could be one of the reasons that your seeing the read-only transaction error on master server.
Hope this helps you.
Hi kapil,
From logs I can't find any statement excuted as 'ALTER USER / DATABASE'.
Is there any view or sysytem table to verify the user's previlege.
Thanks
Hi ram1992,
For user you can refer veiw called pg_user and for database their is no view.
Please find the below output for your reference.
edb=# select * from pg_user;
-[ RECORD 1 ]-----+-----------------------------------
usename | enterprisedb
usesysid | 10
usecreatedb | t
usesuper | t
userepl | t
usebypassrls | f
passwd | ********
valuntil |
useaccountstatus | 0
uselockdate |
usepasswordexpire |
useconfig |
-[ RECORD 2 ]-----+-----------------------------------
usename | repuser
usesysid | 16554
usecreatedb | f
usesuper | f
userepl | f
usebypassrls | f
passwd | ********
valuntil |
useaccountstatus | 0
uselockdate |
usepasswordexpire |
useconfig | {default_transaction_read_only=on}
Hi Ram,
Any failover or switchover happend in few days/time before?.
Canu you please share pg_pool log also and send the session level changes.till not yet restarted..i hope you what am asking!!!
Thanks
Diny.
Hi Diny,
These are new server which was set up with streaming replication. We don't use pgpool, it's just two servers in sync via replication and also useconfig filed of that user is empty.
Thanks
Hi Ram,
Please send your user description like your current user enable in Read Only Transaction parameter.[default_transaction_read_only=on if ti on plese off]
otherwise
we want the below details:-
1.your user descriptions
2.user grants and privileges
Hi Dinesh,
Sorry for the delay respone, the parameter "default_transaction_read_only" has been set to "off" across the instance, also the value is not changed for any user or has been set at session/DB level.
Thanks,
Hi ram1992,
Possible root cause have been shared on the thread regarding the read-only transaction error messages. However if your still seeing the read-only error messages on master cluster, please share the postgres logs to investigate further.
Hi Kapil,
Sorry for late response, I do see the following error message in log and the replication set up gets terminating.
LOG : invalid resource manager ID
Any idea of what may be the root cause.
Thanks