Geek Answers Handbook

ERROR: unable to execute CREATE TABLE in read-only transaction

I am trying to configure pgexercises data on my local machine. When I run: psql -U <username> -f clubdata.sql -d postgres -x I get the error: psql:clubdata.sql:6: ERROR: cannot execute CREATE SCHEMA in a read-only transaction .

Why did this create a read-only database on my local machine? Can i change this?

+7
postgresql
source share
2 answers

They got to pgexercises.com and they were able to help me.

I ran these commands (separately):

 psql -U <username> -d postgres begin; set transaction read write; alter database exercises set default_transaction_read_only = off; commit; \q

Then I dropped the database from the dropdb exercises terminal and ran the script psql -U <username> -f clubdata.sql -d postgres -x -q again

+6
source share

Typically, the most likely causes of this kind of error are:

  • An attempt to create statements in a read-only replica (the entire instance is read-only).

  • <username> has default_transaction_read_only set to ON

  • database default_transaction_read_only set to ON

The specified script has in its first lines:

 CREATE DATABASE exercises; \c exercises CREATE SCHEMA cd;

, and you report that the error occurs with CREATE SCHEMA on line 6, and not earlier.

This means that CREATE DATABASE works when <username> is executed. And this will not work if any of the above reasons were directly applicable.

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 postgres database, the one that calls psql, through the ALTER DATABASE statement, which replaces the configuration file .

This is why CREATE DATABASE works, but then as soon as it connects to another database with \c , the session default_transaction_read_only parameter will turn to ON .

But, of course, it would be a rather strange and unusual configuration.

+6
source share

More articles:


All Articles