7

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.

4

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
  • I am extremly sorry..But I was not able to check the post due to some reasons. But this post seems to be very useful. thank you very much – jaison Dec 15 '15 at 13:11
  • @jaison - if you think that the answer is correct, please mark it as such. This will help those looking for answers to the same or similar questions. – Vérace Oct 12 '18 at 11:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.