PostgreSQL: ERROR: cannot execute CREATE DATABASE in a read-only transaction

Question

I installed PostgreSQL and and its GUI, pgAdmin III.
Setup already had a default database postgres.

When I am trying to create a new table or database, it is showing an error:

ERROR: cannot execute CREATE DATABASE in a read-only transaction

I uncommented the line default_transaction_read_only = off in postgresql.conf and executed the command pg_ctl start -D /home/jaison/progresData -Z datanode -l logfile but it is still showing the same error.

please help me to resolve this issue.

Answer 1

You could always double check your problem by issuing a command such as

/usr/bin/pgsql/bin/createdb test

You can frequently get better/clearer error messages and/or other information from the command line than from GUI tools such as pgAdmin III.

But, in order to change a system parameter, you have to reload the postgresql.conf file after making changes. You can do this by either

• rebooting the server or

• reloading the .conf file or

• change on the client side

From here, some parameters always need a reboot and some can be dynamically changed by simply reloading.

I tried to find a clear list of those which required reboot and which required reload, but failed (grateful for input on this!).

For a system reboot,

pgctl restart

To reload, you could also try these commands (from here - or check the documentation page given above).

Option 1: From the command-line shell

su - postgres /path/to/pg_ctl reload

Option 2: Using SQL

SELECT pg_reload_conf();

You can verify that the setting has "taken" from the table pg_settings. Also of interest is this page.

For the default_transaction_read_only parameter, you can also set it directly from the client (see here). Check the links here for how to set all parameters - reboot, reload or client.

test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting 
-------------------------------+---------
 default_transaction_read_only | on
(1 row)

test=# 
test=# `SET default_transaction_read_only = OFF;`
SET
test=# 
test=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_read_only';
             name              | setting 
-------------------------------+---------
 default_transaction_read_only | off
(1 row)

test=# 
test=# SET default_transaction_read_only = ON;
SET