Error cannot execute drop database in a read only transaction

Normally the most plausible reasons for this kind of error are :

• trying create statements on a read-only replica (the entire instance is read-only).

• <username> has default_transaction_read_only set to ON

• the database has default_transaction_read_only set to ON

The script mentioned has in its first lines:

CREATE DATABASE exercises;
c exercises
CREATE SCHEMA cd;

and you report that the error happens with CREATE SCHEMA at line 6, not before.

That means that the CREATE DATABASE does work, when run by <username>.
And it wouldn’t work if any of the reasons above was 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 database postgres, the one that the invocation of psql connects to, through an ALTER DATABASE statement that supersedes the configuration file.

That would be why CREATE DATABASE works, but then as soon as it connects to a different database with c, the default_transaction_read_only setting of the session would flip to ON.

But of course that would be a pretty weird and unusual configuration.

Reached out 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 terminal dropdb exercises and ran script again psql -U <username> -f clubdata.sql -d postgres -x -q

I had same issue for Postgre Update statement

SQL Error: 0, SQLState: 25006 ERROR: cannot execute UPDATE in a read-only transaction

Verified Database access by running below query and it will return either true or false

SELECT pg_is_in_recovery()

true -> Database has only Read Access

false -> Database has full Access

if returns true then check with DBA team for the full access and also try for ping in command prompt and ensure the connectivity.

ping <database hostname or dns>

Also verify if you have primary and standby node for the database

I was having getting cannot execute CREATE TABLE in a read-only transaction, cannot execute DELETE TABLE in a read-only transaction and others.

They all followed a cannot execute INSERT in a read-only transaction. It was like the connection had switched itself over to read-only in the middle of my batch processing.

Turns out, I was running out of storage! Write access was disabled when the database could no longer write anything. I am using Postgres on Azure. I don’t know if the same effect would happen if I was on a dedicated server.

Дисклеймер

Я — разработчик. Я пишу код, с базой данных взаимодействую лишь как пользователь. Я ни в коем случае не претендую на должность системного администратора и, тем более, dba. Но…

Так вышло, что мне нужно было организовать резервное копирование postgresql базы данных. Никаких облаков — держи SSH и сделай, чтобы все работало и не просило денег. Что мы делаем в таких случаях? Правильно, пихаем pgdump в cron, каждый день бэкапим все в архив и если совсем разошлись — отправляем этот архив куда-нибудь подальше.

В этот раз сложность состояла в том, что по планам база должна была расти примерно на +- 100 МБ в день. Разумеется, уже через пару недель желание бэкапить все pgdump’ом отпадет. Тут на помощь приходят инкрементальные бэкапы.

Интересно? Добро пожаловать под кат.

Инкрементальный бэкап — разновидность резервной копии, когда копируются не все файлы источника, а только новые и измененные с момента создания предыдущей копии.

Как и любой разработчик, СОВЕРШЕННО не желающий (на тот момент) разбираться в тонкостях postgres я хотел найти зеленую кнопку. Ну, знаете, как в AWS, DigitalOcean: нажал одну кнопку — получил репликацию, нажал вторую — настроил бэкапы, третью — все откатил на пару часов назад. Кнопки и красивого GUIшного инструмента я не нашел. Если вы знаете такой (бесплатный или дешевый) — напишите об этом в комментариях.

Погуглив я нашел два инструмента pgbarman и pgbackrest. С первым у меня просто не задалось (очень скудная документация, пытался все поднять по старинным мануалам), а вот у второго документация оказалась на уровне, но и не без изъяна. Чтобы упростить работу тем, кто столкнется с подобной задачей и была написана данная статья.

Дочитав данную статью вы научитесь делать инкрементальные бекапы, сохранять их на удаленный сервер (репозиторий с бэкапами) и восстанавливать их в случае утери данных или иных проблем на основном сервере.

Подготовка

Для воспроизведения мануала вам понадобятся два VPS. Первый будет хранилищем (репозиторием, на котором будут лежат бэкапы), а второй, собственно, сам сервер с postgres (в моем случае 11 версия postgres).

Подразумевается, что на сервере с postgres у вас есть root, sudo пользователь, пользователь postgres и сам postgres установлен (пользователь postgres создается автоматически при установке postgresql), а на сервере-репозитории есть root и sudo пользователь (в мануале будет использоваться имя пользователя pgbackrest).

Чтобы у вас было меньше проблем при воспроизведении инструкции — курсивом я прописываю где, каким пользователем и с какими правами я исполнял команду во время написания и проверки статьи.

Установка pgbackrest

Репозиторий (пользователь pgbackrest):

1. Скачиваем архив с pgbackrest и переносим его содержимое в папку /build:

sudo mkdir /build
sudo wget -q -O - 
       https://github.com/pgbackrest/pgbackrest/archive/release/2.18.tar.gz | 
       sudo tar zx -C /build

2. Устанавливаем необходимые для сборки зависимости:

sudo apt-get update
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev 
       libpq-dev

3. Собираем pgbackrest:

cd /build/pgbackrest-release-2.18/src && sudo ./configure
sudo make -s -C /build/pgbackrest-release-2.18/src

4. Копируем исполняемый файл в директорию /usr/bin:

sudo cp /build/pgbackrest-release-2.18/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest

5. Pgbackrest требует наличие perl. Устанавливаем:

sudo apt-get install perl

6. Создаем директории для логов, даем им определенные права:

sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf

7. Проверяем:

pgbackrest version

Postgres сервер (sudo пользователь или root):

Процесс установки pgbackrest на сервере с postgres аналогичен процессу установки на репозитории (да, pgbackrest должен стоять на обоих серверах), но в 6-ом пункте вторую и последнюю команды:

sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf

заменяем на:

sudo chown postgres:postgres /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.conf

Настройка взаимодействия между серверами через passwordless SSH

Для того, чтобы pgbackrest корректно работал, необходимо настроить взаимодействие между postgres сервером и репозиторием по файлу-ключу.

Репозиторий (пользователь pgbackrest):

Создаем пару ключей:

mkdir -m 750 /home/pgbackrest/.ssh
ssh-keygen -f /home/pgbackrest/.ssh/id_rsa 
       -t rsa -b 4096 -N ""

Внимание! Указанные выше команды выполняем без sudo.

Postgres сервер (sudo пользователь или root):

Создаем пару ключей:

sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa 
       -t rsa -b 4096 -N ""

Репозиторий (sudo пользователь):

Копируем публичный ключ postgres сервера на сервер-репозиторий:

(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && 
       echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && 
       sudo ssh [email protected]<postgres_server_ip> cat /var/lib/postgresql/.ssh/id_rsa.pub) | 
       sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keys

На данном шаге попросит пароль от root пользователя. Вводить нужно именно пароль root пользователя postgres сервера!

Postgres сервер (sudo пользователь):

Копируем публичный ключ репозитория на сервер с postgres:

(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && 
       echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && 
       sudo ssh [email protected]<repository_server_ip> cat /home/pgbackrest/.ssh/id_rsa.pub) | 
       sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys

На данном шаге попросит пароль от root пользователя. Вводить нужно именно пароль root пользователя репозитория!

Проверяем:

Репозиторий (root пользователь, для чистоты эксперимента):

sudo -u pgbackrest ssh [email protected]<postgres_server_ip>

Postgres сервер (root пользователь, для чистоты эксперимента):

sudo -u postgres ssh [email protected]<repository_server_ip>

Убеждаемся, что без проблем получаем доступ.

Настройка postgres сервера

Postgres сервер (sudo пользователь или root):

1. Разрешим «стучаться» на postgres сервер с внешних ip. Для этого отредактируем файл postgresql.conf (находится в папке /etc/postgresql/11/main), добавив в него строчку:

listen_addresses = '*'

Если такая строка уже есть — либо раскомментируйте ее, либо установите значение параметра как ‘*’.

В файле pg_hba.conf (так же находится в папке /etc/postgresql/11/main) добавляем следующие строчки:

hostssl  all  all  0.0.0.0/0  md5
host  all  all  0.0.0.0/0  md5

где:

hostssl/host - подключаемся через SSL (или нет)
all - разрешаем подключение ко всем базам
all - имя пользователя, которому разрешаем подключение (всем)
0.0.0.0/0 - маска сети с которой можно подключаться
md5 - способ шифрования пароля

2. Внесем необходимые настройки в postgresql.conf (он находится в папке /etc/postgresql/11/main) для работы pgbackrest:

archive_command = 'pgbackrest --stanza=main archive-push %p' # Где main - название кластера. При установке postgres автоматически создает кластер main.
archive_mode = on
max_wal_senders = 3
wal_level = replica

3. Внесем необходимые настройки в файл конфигурации pgbackrest (/etc/pgbackrest/pgbackrest.conf):

[main]
pg1-path=/var/lib/postgresql/11/main

[global]
log-level-file=detail
repo1-host=<repository_server_ip>

4. Перезагрузим postgresql:

sudo service postgresql restart

Настройка сервера-репозитория

Репозиторий (pgbackrest пользователь):

Внесем необходимые настройки в файл конфигурации pgbackrest
(/etc/pgbackrest/pgbackrest.conf):

[main]
pg1-host=<postgres_server_ip>
pg1-path=/var/lib/postgresql/11/main

[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2 # Параметр, указывающий сколько хранить полных бэкапов. Т.е. если у вас есть два полных бэкапа и вы создаете третий, то самый старый бэкап будет удален. Можно произносить как "хранить не более двух бэкапов" - по аналогии с ротациями логов. Спасибо @Aytuar за исправление ошибки.
start-fast=y # Начинает резервное копирование немедленно, прочитать про этот параметр можно тут https://postgrespro.ru/docs/postgrespro/9.5/continuous-archiving

Создание хранилища

Репозиторий (pgbackrest пользователь):

Создаем новое хранилище для кластера main:

sudo mkdir -m 770 /var/lib/pgbackrest
sudo chown -R pgbackrest /var/lib/pgbackrest/
sudo -u pgbackrest pgbackrest --stanza=main stanza-create

Проверка

Postgres сервер (sudo пользователь или root):

Проверяем на postgres сервере:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info check

Репозиторий (pgbackrest пользователь):

Проверяем на сервере-репозитории:

sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info check

Убеждаемся, что в выводе видим строку «check command end: completed successfully».

Устали? Переходим к самому интересному.

Делаем бэкап

Репозиторий (pgbackrest пользователь):

1. Выполняем резервное копирование:

sudo -u pgbackrest pgbackrest --stanza=main backup

2. Убеждаемся, что бэкап был создан:

ls /var/lib/pgbackrest/backup/main/

Pgbackrest создаст первый полный бэкап. При желании вы можете запустить команду бэкапа повторно и убедиться, что система создаст инкрементальный бэкап.

Если вы хотите повторно сделать полный бэкап, то укажите дополнительный флаг:

sudo -u pgbackrest pgbackrest --stanza=main --type=full backup

Если вы хотите подробный вывод в консоль, то также укажите:

sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backup

Восстанавливаем бэкап

Postgres сервер (sudo пользователь или root):

1. Останавливаем работающий кластер:

sudo pg_ctlcluster 11 main stop

2. Восстанавливаемся из бэкапа:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta --recovery-option=recovery_target=immediate restore

Чтобы восстановить базу в состояние последнего ПОЛНОГО бэкапа используйте команду без указания recovery_target:

sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta restore

Важно! После восстановления может оказаться так, что база зависнет в режиме восстановления (будут ошибки в духе ERROR: cannot execute DROP DATABASE in a read-only transaction). Честно говоря, я еще не понял, с чем это связано. Решается следующим образом (нужно будет малость подождать после исполнения команды):

sudo -u postgres psql -c "select pg_wal_replay_resume()"

На самом деле, есть возможность восстановить конкретный бэкап по его имени. Здесь я лишь укажу ссылку на описание данной фичи в документации. Разработчики советуют использовать данный параметр с осторожностью и объясняют почему. От себя могу добавить, что я его использовал. Если очень нужно — убедитесь, что после восстановления база вышла из recovery mode (select pg_is_in_recovery() должен показать «f») и на всякий случай сделайте полный бэкап после восстановления.

3. Запускаем кластер:

sudo pg_ctlcluster 11 main start

После восстановления бэкапа нам необходимо выполнить повторный бэкап:

Репозиторий (pgbackrest пользователь):

sudo pgbackrest --stanza=main backup

На этом все. В заключение хочу напомнить, что я ни в коем случае не пытаюсь строить из себя senior dba и при малейшей возможности буду использовать облака. В настоящее время сам начинаю изучать различные темы вроде резервного копирования, репликаций, мониторинга и т.п. и о результатах пишу небольшие отчеты, дабы сделать небольшой вклад в сообщество и оставить для себя небольшие шпаргалки.