When working with PostgreSQL 9.1 on Ubuntu Server 12.04, you might encounter transactions being forced into read-only mode unexpectedly. This typically manifests when trying to execute DDL commands like:

ERROR: cannot execute CREATE ROLE in a read-only transaction
ERROR: cannot execute CREATE TABLE in a read-only transaction

The most common scenarios that trigger this behavior include:

• Database replication settings (especially in hot standby configurations)
• Explicit transaction blocks with READ ONLY declaration
• Incorrect recovery.conf settings
• Permissions issues with the replication user

First, verify if your database is actually in recovery mode:

SELECT pg_is_in_recovery();

If this returns true, your server is operating as a standby. To check the current transaction mode:

SHOW transaction_read_only;

For PostgreSQL 9.1 specifically, follow these steps:

1. Check recovery.conf (located in your data directory):

# If this exists, comment it out
standby_mode = 'on'

2. Restart PostgreSQL:

sudo service postgresql restart

3. Promote the standby server if needed:

touch /var/lib/postgresql/9.1/main/trigger_file

If you're intentionally using replication, consider these alternatives:

-- On master server:
ALTER SYSTEM SET hot_standby = off;
SELECT pg_reload_conf();

-- Or for temporary writes on standby:
SET LOCAL transaction_read_only = off;
BEGIN;
-- Your write operations here
COMMIT;

To prevent future occurrences, modify these settings in postgresql.conf:

hot_standby = off
default_transaction_read_only = off

Remember to reload the configuration after changes:

SELECT pg_reload_conf();

---

When you encounter errors like ERROR: cannot execute CREATE ROLE in a read-only transaction or ERROR: cannot execute CREATE TABLE in a read-only transaction, it means your PostgreSQL database is operating in read-only mode. This typically happens due to several configuration or replication scenarios.

• Hot standby replication configuration
• Database recovery mode
• Incorrect default_transaction_read_only setting
• Filesystem permissions issues

First, verify if your database is actually in read-only mode:

SELECT pg_is_in_recovery();

If this returns true, your database is in recovery or standby mode.

If you're using streaming replication, the standby server is read-only by design. To make changes:

# Promote standby to master
pg_ctl promote -D /path/to/data/directory

# Or temporarily allow writes on standby (PostgreSQL 9.6+)
ALTER SYSTEM SET hot_standby_feedback = on;
ALTER SYSTEM SET hot_standby = off;
SELECT pg_reload_conf();

Verify your default transaction mode:

SHOW default_transaction_read_only;

To change it:

ALTER SYSTEM SET default_transaction_read_only = off;
SELECT pg_reload_conf();

Ensure PostgreSQL has write permissions to its data directory:

sudo chown -R postgres:postgres /var/lib/postgresql/
sudo chmod -R 0700 /var/lib/postgresql/9.1/main

If your database crashed and is in recovery:

# Check recovery status
SELECT pg_is_in_recovery();

# If recovery.conf exists (PostgreSQL 9.x), remove or rename it:
sudo mv /var/lib/postgresql/9.1/main/recovery.conf /var/lib/postgresql/9.1/main/recovery.conf.bak

# Restart PostgreSQL
sudo service postgresql restart

For replication setups, consider these configurations in postgresql.conf:

hot_standby = on
hot_standby_feedback = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s