Error cannot execute insert in a read only transaction

Skip to content

ERROR:  cannot execute CREATE DATABASE in a read-only transaction
Error: cannot execute DROP DATABASE in a read-only transaction
ERROR:  cannot execute CREATE TABLE in a read-only transaction
Error: cannot execute DROP TABLE in a read-only transaction
cannot execute INSERT in a read-only transaction

/var/lib/postgresql/13/main

sudo systemctl restart postgresql

Typically when discussing having “read-only” connections to a PostgreSQL database, it is in the context of connecting to a replica.

There are a variety of methods available to route connections with known read-only queries (i.e. queries with SELECT statements…that are not calling VOLATILE functions that modify data). This includes connection proxy software like Pgpool-II or framework mechanisms such as Django’s database router.

However, there are situations where you might need to force read-only connections to your primary (read-write) Postgres instance. Some examples include putting your application into a degraded state to perform a database move or upgrade, or allowing an administrator to inspect a system that may be accumulating write-ahead logs that track all changes to the system.

PostgreSQL has a configuration parameter call default_transaction_read_only. Setting default_transaction_read_only globally to on forces all connections to disallow writes to the database. default_transaction_read_only is a reloadable parameter, so you do not need to restart your Postgres instance to use it.

Here is a quick example of how default_transaction_read_only works. First, ensure your system does not have default_transaction_read_only set:

postgres=# SHOW default_transaction_read_only ;

 default_transaction_read_only
-------------------------------
 off
(1 row)

postgres=# CREATE TABLE IF NOT EXISTS abc (id int); INSERT INTO abc VALUES (1) RETURNING id;
CREATE TABLE

 id
----
  1
(1 row)

INSERT 0 1

This works as expected: we’re able to create a table and insert data. Now let’s put the system into default_transaction_read_only mode (note that I am running this on PostgreSQL 14)

ALTER SYSTEM SET default_transaction_read_only TO on;
SELECT pg_reload_conf();
SHOW default_transaction_read_only;

Ensure that default_transaction_read_only is enabled:

postgres=# SHOW default_transaction_read_only;

 default_transaction_read_only
-------------------------------
 on
(1 row)

Now verify that writes are disallowed:

postgres=# INSERT INTO abc VALUES (2) RETURNING id;
ERROR:  cannot execute INSERT in a read-only transaction

Excellent!

Note that default_transaction_read_only is not a panacea: there are some caveats that you should be aware of.

First, default_transaction_read_only can be overriden in a session, even if the value is set database-wide. For example:

postgres=# SHOW default_transaction_read_only ;
 default_transaction_read_only
-------------------------------
 on
(1 row)

postgres=# SET default_transaction_read_only TO off;
SET

postgres=# INSERT INTO abc VALUES (2) RETURNING id;

 id
----
  2
(1 row)

INSERT 0 1

Second, when utilizing default_transaction_read_only with an application, you must also ensure your app can be configured to send only read queries to the database, ensuring a smooth user experience.

That said, if you have a situation where you need to put a PostgreSQL primary instance into a “read-only” mode temporarily, you can use default_transaction_read_only to prevent write transactions.

Существует несколько типов методов реализации, которые заблокированы.

1. Жесткий замок, напрямую переключите базу данных в режим восстановления, абсолютно не имеют операции записи.

2, Soft Lock, установите значение default_transaction_read_only on, транзакция по умолчанию используется транзакция только для чтения. Пользователи могут быть взломаны, если вы используете The Begion Transaction Read Write.

1 экземпляр блокировки

Жесткий замок

1, настройте Recovery.conf

recovery_target_timeline = 'latest'  
standby_mode = on  

2, перезапустите базу данных

pg_ctl restart -m fast  

3, жесткий замок, не может треснуть

postgres=# select pg_is_in_recovery();  
 pg_is_in_recovery   
-------------------  
 t  
(1 row)  
  
postgres=# insert into t1 values (1);  
ERROR:  cannot execute INSERT in a read-only transaction  
  
postgres=# begin transaction read write;  
ERROR:  cannot set transaction read-write mode during recovery  

Мягкий замок

1, установить default_transaction_read_only

postgres=# alter system set default_transaction_read_only=on;  
ALTER SYSTEM  

2, конфигурация перегрузки

postgres=# select pg_reload_conf();  
 pg_reload_conf   
----------------  
 t  
(1 row)  

3. Все сеансы автоматически входят в режим транзакции по умолчанию только для чтения.

До перезагрузки

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 off  
(1 row)  

После перезагрузки

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 on  
(1 row)  
  
postgres=# insert into t1 values (1);  
ERROR:  cannot execute INSERT in a read-only transaction  

4, мягкий замок может быть сломан

postgres=# begin transaction read write;  
BEGIN  
postgres=# insert into t1 values (1);  
INSERT 0 1  
postgres=# end;  
COMMIT  

2 разблокирован пример

Жесткий смысл замок

1, переименовать Recovery.conf на Recovery.done

cd $PGDATA  
  
mv recovery.conf recovery.done  

2, перезапустите базу данных

pg_ctl restart -m fast  

Мягкая блокировка

1, установить default_transaction_read_only

postgres=# alter system set default_transaction_read_only=off;  
ALTER SYSTEM  

2, конфигурация перегрузки

postgres=# select pg_reload_conf();  
 pg_reload_conf   
----------------  
 t  
(1 row)  

3. Все сеансы автоматически входят в режим транзакции по умолчанию только для чтения.

До перезагрузки

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 on  
(1 row)  

После перезагрузки

postgres=# show default_transaction_read_only ;  
 default_transaction_read_only   
-------------------------------  
 off  
(1 row)  
  
Пишите восстановление  
postgres=# insert into t1 values (1);  
INSERT 0 1