Ошибка нет доступа к последовательности postgresql

ОШИБКА: отказано в разрешении для последовательности towns_id_seq с использованием Postgres

Я новичок в postgres (и вообще в информационных системах баз данных). Я запустил следующий скрипт SQL в моей базе данных:

Когда, как пользователь www, пытаюсь:

Я получаю следующую ошибку:

Я понимаю, что проблема заключается в серийном типе. Вот почему я даю права на выбор, вставку и удаление для * _id_seq на www. Но это не решает мою проблему. Чего мне не хватает?

Начиная с PostgreSQL 8.2 вы должны использовать:

GRANT USAGE — для последовательностей эта привилегия позволяет использовать функции currval и nextval.

Также, как указано @epic_fil в комментариях, вы можете предоставить разрешения для всех последовательностей в схеме с помощью:

Поскольку у @Phil есть комментарий, который вызывает много положительных отзывов, которые могут быть не замечены, я использую его синтаксис, чтобы добавить ответ, который предоставит пользователю разрешения для всех последовательностей в схеме (при условии, что ваша схема по умолчанию является общедоступной). )

@Tom_Gerken, @epic_fil и @kupson вполне корректны в своих утверждениях, чтобы дать разрешения для работы с существующими последовательностями. Однако пользователь НЕ получит права доступа к последовательностям, созданным в будущем. Для этого вам нужно объединить оператор GRANT с оператором ALTER DEFAULT PRIVILEGES, например, так:

Конечно, это работает только на PostgreSQL 9+.

Это добавит к существующим привилегиям по умолчанию, а не перезапишет их, поэтому вполне безопасно в этом отношении.

Выполните следующую команду в postgres.

войти в postgres:

CREATE SEQUENCE public.cities_id_seq INCREMENT 1
MINVALUE 0
MAXVALUE 1
START 1 CACHE 1; ALTER TABLE public.cities_id_seq ВЛАДЕЛЕЦ pgowner;

pgowner будет вашим пользователем базы данных.

Источник

ОШИБКА: отказано в разрешении для последовательности cities_id_seq с использованием Postgres

Я новичок в postgres (и вообще в информационных системах баз данных). Я запустил в своей базе данных следующий sql-скрипт:

Когда пользователь www пытается:

Я получаю следующее сообщение об ошибке:

Я понимаю, что проблема в серийном типе. Вот почему я предоставляю права выбора, вставки и удаления для * _id_seq на www. Но это не решает мою проблему. Что мне не хватает?

задан 17 фев ’12, 04:02

Предоставление вставки / удаления в последовательности для меня не имеет смысла. Я удивлен, что это вообще работает. — a_horse_with_no_name

5 ответы

Начиная с PostgreSQL 8.2 вы должны использовать:

GRANT USAGE — для последовательностей эта привилегия позволяет использовать функции currval и nextval.

Также, как указано @epic_fil в комментариях, вы можете предоставить разрешения для всех последовательностей в схеме с помощью:

Внимание: Не забудьте выбрать базу данных ( c ) перед выполнением команд предоставления привилегий

ответ дан 26 апр.

К вашему сведению, синтаксис «. НА ВСЕХ ПОСЛЕДОВАТЕЛЬНОСТЯХ В СХЕМЕ имя_схемы» также поддерживается. — epic_fil

Интересный. Я сделал ГРАНТ всем на столе, в котором находится моя последовательность, но, похоже, это не покрывает последовательность. Это тоже очень похоже на ОС. — Киннард Хоккенхалл

Как это на самом деле? Когда я когда-либо захочу разрешить пользователю вставлять данные в таблицу, но НЕ хочу позволять им использовать тот факт, что один из столбцов автоматически увеличивается? — Бретт Видмайер

IS SELECT необходимый? Не должен USAGE крышка что нужно? — Tᴀʀᴇǫ Mᴀʜᴍᴏᴏᴅ

@BrettWidmeier Совершенно верно. Меня просто ошеломляет, как подобное терпят разработчики. Это похоже на то, как будто люди хотят бродить по Интернету и читать бездонные потоки StackOverflow, пытаясь исправить то, что должно было работать прямо из коробки. — Милосмнс

Поскольку у @Phil есть комментарий, который получает много голосов, которые могут остаться незамеченными, я использую его синтаксис, чтобы добавить ответ, который предоставит пользователю разрешения для всех последовательностей в схеме (при условии, что ваша схема является общедоступной по умолчанию. )

ответ дан 24 мар ’15, в 21:03

Обратите внимание, что это работает только в PostgreSQL 9.0 и новее, чтобы добиться того же в 8, вы можете сделать что-то вроде: SELECT ‘GRANT USAGE, SELECT ON’ || quote_ident (schemaname) || ‘.’ || quote_ident (relname) || ‘TO www;’ ОТ pg_statio_all_sequences ГДЕ schemaname = ‘public’; — Том Геркен 2 дня назад — Том Геркен

Этот обходной путь версии 8 спас жизнь. Сначала я изо всех сил пытался понять, как его использовать. Мне пришлось использовать этот запрос для создания списка новых запросов, которые я затем должен был отправить. Есть ли способ сделать это за один удар? Также я вставил «ПОСЛЕДОВАТЕЛЬНОСТИ» после «ВКЛ» — Пол Гардинер

@Tom_Gerken, @epic_fil и @kupson совершенно правы в своих заявлениях о предоставлении разрешений на работу с существующими последовательностями. Однако пользователь НЕ получит прав доступа к последовательностям, созданным в будущем. Для этого вы должны объединить оператор GRANT с оператором ALTER DEFAULT PRIVILEGES, например:

Конечно, это работает только в PostgreSQL 9+.

Это добавит к существующим привилегиям по умолчанию, а не перезапишет их, поэтому в этом отношении вполне безопасно.

Источник

ОШИБКА: разрешение отклонено для последовательности city_id_seq с помощью Postgres

Я новичок в postgres (и в информационных системах базы данных). Я выполнил следующую sql script в моей базе данных:

Когда, как пользователь www, пытается:

Я получаю следующую ошибку:

Я понимаю, что проблема связана с серийным типом. Поэтому я предоставляю возможность выбора, вставки и удаления прав для * _id_seq на www. Но это не устраняет мою проблему. Что мне не хватает?

ОТВЕТЫ

Ответ 1

Начиная с PostgreSQL 8.2 вы должны использовать:

ИСПОЛЬЗОВАНИЕ GRANT — для последовательностей эта привилегия позволяет использовать функции currval и nextval.

Также, как указано в комментариях @epic_fil, вы можете предоставить разрешения для всех последовательностей в схеме с помощью:

Ответ 2

Так как @Phil имеет комментарий, получающий много upvotes, которые могут не заметить, я использую его синтаксис для добавления ответа, который предоставит пользователю права на все последовательности в схеме (если ваша схема по умолчанию ‘общественность’)

Ответ 3

@Tom_Gerken, @epic_fil и @kupson вполне корректны с их инструкциями, чтобы предоставить разрешения для работы с существующими последовательностями. Однако пользователь НЕ получит права доступа к последовательностям, созданным в будущем. Для этого вам необходимо объединить инструкцию GRANT с инструкцией ALTER DEFAULT PRIVILEGES, например:

Это, конечно, работает только на PostgreSQL 9+.

Это добавит к существующим привилегиям по умолчанию, а не перезаписывает их, поэтому вполне безопасно в этом отношении.

Ответ 4

Выполните следующую команду в postgres.

войдите в postgres:

СОЗДАТЬ ПОСЛЕДОВАТЕЛЬНОСТЬ public.cities_id_seq INCREMENT 1
MINVALUE 0
MAXVALUE 1
НАЧАТЬ 1 КЭШ 1; ALTER TABLE public.cities_id_seq ВЛАДЕЛЕЦ pgowner;

pgowner будет вашим пользователем базы данных.

Источник

Начиная с 10 версии, перенести данные с одной базы PostgreSQL на другую несложно, с обновлением, без обновления — неважно. Об этом немало сказано и сказанное сводится к следующему: на мастере, 10 версии и выше, устанавливаем параметр конфигурации wal_level=»logical» . В pg_hba.conf добавляем такую строку:

Затем рестартуем на мастере postgres и выполняем на реплике из-под пользователя postgres:

Теперь подключаемся на мастере пользователем postgres к базе db_name и создаём публикацию:

а на реплике создаём подписку:

По завершении репликации переключаем приложение или балансировщик на новую базу.

Теперь вы знаете постгрес (и с какой стороны доить слонеску) и можете идти устраиваться ДБА.
Для любознательных есть пара небольших деталей под катом.

Задачи, решаемые логической репликацией

Для чего может быть полезна логическая репликация, написано в документации:

• Передача подписчикам инкрементальных изменений в одной базе данных или подмножестве базы данных, когда они происходят.
• Срабатывание триггеров для отдельных изменений, когда их получает подписчик.
• Объединение нескольких баз данных в одну (например, для целей анализа).
• Репликация между разными основными версиями PostgreSQL.
• Репликация между экземплярами PostgreSQL на разных платформах (например, с Linux на Windows).
• Предоставление доступа к реплицированным данным другим группам пользователей.
• Разделение подмножества базы данных между несколькими базами данных.

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

Это тот момент, когда можно сказать — «А помните, мы хотели уменьшить объём базы за счёт выравнивания? Давайте сейчас столбцы и перераспределим!». Также на стороне реплики возможно, например, провести перераспределение данных из одного столбца типа JSON в несколько других столбцов, или даже таблиц, либо наоборот, после чего заполнять уже доработанную и протестированную базу, при необходимости обрабатывая данные напильником триггерами. Можно какие-то поля отправить в TOAST, а какие-то наоборот — достать. В некоторых пределах можно поменять типы значений в столбцах. Также причиной выбора является возможность провести практически бесшовное обновление, одновременно с котором допустимо некоторое изменение схемы данных, а при некотором усердии — кардинальное перекраивание схемы данных. В общем, к списку добавляется один пункт:

• Трансформация схемы данных, в определённых пределах, практически без перерыва в обслуживании.

Но, как и любой другой инструмент, логическая репликация имеет, помимо преимуществ, ещё и ограничения и недостатки. Знать их не помешает, ведь приведённый над катом пример будет работать без проблем только в сферической учебной БД.

Документация и примечания к выпускам

Надо помнить, что поведение СУБД в различных мажорных версиях может заметно разниться. Поэтому перед обновлением, да и вообще, важно ознакомиться с документацией и списком изменений (10, 11, 12, 13) и определить, какие из них могут изменить поведение вашей БД.

Здесь не будет рассказываться о создании публикаций и подписок, всё это есть в документации. Статья носит обзорный характер и не служит заменой документации.

Примечание

В статье приводится много примеров для воспроизведения которых лучше использовать psql , так как pgcli работает немного по-другому и перетащенный туда скрипт работает с ущербом для наглядности. Также создайте базу test , а в ней схему ts :

Не все скрипты можно перетаскивать как есть, в некоторых придётся поменять IP-адреса.
Приведённые примеры кода ни в коем случае не выполняйте на экземплярах СУБД, которые кем-либо используются. Некоторые примеры приведены для демонстрации падения СУБД и могут принести немало неприятностей. Лучше всего создать пару ВМ и экспериментировать на них.

Обновление до последней корректирующей (минорной) версии

Первое, что нужно запланировать и сделать при подготовке к использованию логической репликации — обновиться на последнюю минорную версию, особенно если в создаваемых публикациях планируется использовать предложение FOR ALL TABLES .

Почему стоит обновиться? Например, поэтому:

1. В версиях 10.8 и 11.3 был исправлен баг с обработкой изменений, вносящихся во временные и нежурналируемые таблицы. Данные в таких таблицах в логической репликации не участвуют, поэтому им не требуется настройка репликационных идентификаторов, но, при попытке обновить в таких таблицах данные, сервер выдавал сообщение об ошибке: ERROR: cannot update table «logical_replication_test» because it does not have a replica identity and publishes updates и отменял транзакцию. Хорошего в этом мало, поэтому, если ваше приложение использует временные или нежурналируемые таблицы, то обновление обязательно;
2. В версиях 10.11 и 11.6 был устранён вывод ошибки в случае, когда состав столбцов идентификации на мастере и на реплике различался. Правда и репликация изменения или удаления строк в таком случае прекращается;
3. В версиях 10.12, 11.7, 12.2 был устранено несколько багов, которые приводили к невозможности значительно изменять схему таблиц на реплике по сравнению со схемой таблиц на мастере. Например, на реплике нельзя было создавать дополнительные столбцы с функцией в качестве значения по умолчанию («… clmname numeric DEFAULT random() . «).
4. В версиях 10.16, 11.11, 12.6 13.2 устранили утечки памяти в процессах walsender при передаче новых снимков для логического декодирования

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

Создание ролей и строк аутентификации в pg_hba.conf

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

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

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

Создать роль можно такой командой:

В pg_hba.conf на мастере нужно добавить две записи: одну для локального подключения, другую для подключения с реплики. Предпочтительно указывать точные адреса реплик — лучше сто записей в pg_hba, чем одна дыра в безопасности.

Обратите внимание что, несмотря на то что логическая репликация основана в значительной мере на потоковой, указывается не специальная запись replication , а имя базы или all — указание на все базы. Если указать replication , то создать подписку на реплике не получится из-за ошибки аутентификации.

После этого перезагружаем настройки из-под суперпользователя:

Репликационные идентификаторы

Весь процесс логической репликации в принципе строится на идее репликационных идентификаторов. Поэтому дальнейшая подготовка состоит в проверке наличия во всех реплицируемых таблицах либо первичного ключа, либо индекса, соответствующего некоторым минимальным требованиям и задействованного в REPLICA IDENTITY USING INDEX , либо назначении REPLICA IDENTITY FULL . То есть проверка наличия в таблицах репликационных идентификаторов. Они нужны для однозначной идентификации изменяемых или удаляемых строк при репликации команд UPDATE и DELETE и передаются на реплику в специальном поле для каждой записи.

Репликационные идентификаторы можно не настраивать, или даже отключить, если планируется реплицировать только команды INSERT . Главное не забыть правильно создать публикацию — исключить из неё команды UPDATE и DELETE . Но если вам на реплике нужны актуальные данные из активно изменяющихся таблиц, а первичные ключи или уникальные NOT NULL индексы в таблицах отсутствуют, то репликационные идентификаторы придётся настраивать с нуля. Не выполнив это условие, можно добиться того, что UPDATE и DELETE будут приводить к отмене транзакций на мастере, малоприятный факт на рабочей базе.

Что может выступать в качестве репликационного идентификатора

Как понятно из названия репликационных идентификаторов — они должны идентифицировать строки таблиц. Если в вашей таблице есть поле или комбинация полей, которая никогда не повторяется и поэтому позволяет идентифицировать строку, её следует либо объявить первичным ключом, либо создать по ней уникальный индекс.

Если столбцов пригодных к роли репликационных идентификаторов нет, что очень странно, то придётся их создавать. Каждое значение в таком столбце должно быть уникальным. В качестве источника уникальности могут выступать как естественные, так и искусственные ключи и их комбинации — зависит от архитектуры базы данных. При этом неважно что именно использовать в качестве естественных ключей, главное, чтоб они выполняли роль однозначного идентификатора. В качестве искусственных ключей используются, как правило, различные последовательности и типы UUID.

Последовательности

Использовать последовательности при создании репликационных идентификаторов можно двумя с половиной способами: ручное указание вызова функции получения следующего значения последовательности, например nextval ; назначение столбцу последовательного псевдотипа serial ; использование столбцов идентификации в соответствии со стандартом SQL. Вполне рабочим вариантом может быть отсутствие значения по умолчанию, ведь можно возложить эту обязанность на приложение, но столбец должен быть NOT NULL .

SEQUENCE и serial

Наиболее гибким и мощным является использование различных SEQUENCE . В таком случае, после создания последовательности, значения по умолчанию для столбцов необходимо прописывать самостоятельно. При необходимости назначить таблицу-владельца последовательности также придётся поработать руками.

Тип serial это синтаксический сахар для обычного способа создания последовательностей, этакий шаблон. Всё что нужно сделать, это назначить столбцу тип smallserial/serial/bigserial.

Использование типа serial менее гибко, но его использование позволяет избавиться от необходимости создавать последовательности вручную. Также такой последовательности автоматически назначается свойство OWNED BY . Это указание на столбец таблицы, при создании которого была создана последовательность.

Такую последовательность в дальнейшем нельзя удалить, не удалив это указание. И здесь кроется опасность — удалив такую последовательность с указанием ключевого слова CASCADE можно устроить локальный армагеддон. В привязанном к удалённой последовательности столбце останется включенным свойство NOT NULL , а вот свойство DEFAULT обнулится.

У полей serial есть ещё одна неприятная особенность — пользователь без права на использование автоматически созданной последовательности, и с правами на INSERT в таблицу, практически вставку выполнять не сможет, если только не укажет значение поля serial вручную. Если не укажет, то получит ошибку доступа к соответствующей последовательности. В принципе это не проблема, нужно не забывать давать права на использование последовательности вместе с правами на вставку в таблицу.

Несколько таблиц на одной последовательности
Если нужно подключить к одной последовательности несколько таблиц, — делать это нужно самостоятельно. Для этого задаётся получение nextval(нужная_последовательность) в свойстве DEFAULT интересующего вас столбца. Воспользоваться можно и той последовательностью, что была создана с использованием serial — никто не мешает вручную прописать её для других таблиц, разве что потом будут некоторые проблемы с удалением самой первой таблицы: нужно будет поменять или удалить ссылку на таблицу-владельца такой последовательности: ALTER SEQUENCE name_of_your_seq OWNED BY NONE .

Пришедшие из стандарта SQL cтолбцы идентификации задаются либо при создании таблицы, либо ими могут стать имеющиеся столбцы, либо можно добавить такие столбцы отдельно.

Последовательность, созданную для столбца идентификации, в отличие от первых полутора вариантов, не стоит использовать в других таблицах. В дальнейшем это помешает удалить исходную таблицу, а если удалить её с предложением CASCADE , то свойство DEFAULT у таблиц использовавших эту последовательность обнулится. При этом свойство NOT NULL никуда не денется.

В результате появится шанс наблюдать на мастере орды null value in column «i» violates not-null constraint . С последовательностями, созданными с помощью serial тоже такое бывает, но для них это исправимо — поменяйте принадлежность последовательности либо на нужный столбец нужной таблицы, либо сделайте её «бесхозяйной». С последовательностями столбцов идентификации это не работает.

Первичными ключами столбцы идентификации автоматически не становятся, это просто синтаксис назначения столбцу особых свойств, несколько отличающихся от обычных последовательностей. В частности, при типе serial вы можете спокойно проводить вставку любых произвольных значений в ключевые поля, за исключением имеющихся конечно (если на столбце включен PRIMARY KEY ).

Понятно, что это приведёт к тому, что однажды последовательность выдаст вставленные ранее произвольные значения и получившая их транзакция прервётся с ошибкой duplicate key . Использование столбцов идентификации позволит не беспокоиться о таком развитии событий — в столбцы идентификации, созданные с ключом ALWAYS , вставить произвольное число не так просто, нужно использовать специальную форму команды INSERT . При создании таких столбцов поддерживаются те же параметры, что и при создании обычной последовательности.

Universally Unique IDentifiers

Если назначить столбцу тип UUID, то значение для такого столбца не будет генерироваться автоматически. Для получения нового значения UUID необходимо пользоваться одним из двух дополнительных модулей: uuid-ossp или pgcrypto

В отличие от последовательностей UUID имеет длину не 16/32/64 бита, а 128 бит, — что нужно учитывать при расчётах нагрузки на сетевую подсистему. Зато у него есть то преимущество, что UUID генерирует такие строки, содержимое которых не повторяется в распределённых системах.

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

В простейшем случае получать значения UUID можно через расширение pg_crypto . В нём есть только одна функция получения UUID:

Модуль uuid-ossp , в отличие от pg_crypto предоставляет больше возможностей по части выбора типа UUID. Если PostgreSQL установлен из пакетов, то можно сразу устанавливать расширение, только его имя обязательно нужно заключить в двойные кавычки, так как оно содержит дефис. Если собираете PostgreSQL из исходников, то нужно воспользоваться ключом —with-uuid=ossp (работает в Debian, как в RHEL — не знаю). Для этого, в дополнение к уже установленному постгресу, понадобится поставить несколько пакетов:

Первичные ключи и уникальные индексы

Определившись с источниками уникальности нужно указать какой столбец, или какие столбцы, надлежит использовать как репликационные идентификаторы. По умолчанию ими являются первичные ключи. Они, как и уникальные индексы, позволяют добавлять на стороне реплики произвольное число столбцов, не опасаясь приостановки репликации при операциях UPDATE и DELETE .

Главное, чтобы у добавленных столбцов не было назначено свойство NOT NULL с отсутствующим значением по умолчанию — при начальной репликации и репликации команды INSERT возникнет ошибка на стороне реплики, устранять которую возможно только на стороне реплики. На стороне мастера тут уже ничего не поделаешь, разве что удалить слот репликации — чтоб журнал предзаписи не переполнялся, и мастер не создавал каждые пять секунд процесс декодирования. Также можно запретить доступ через pg_hba.conf или на балансировщике, затем исправить неполадки на реплике и снова разрешить доступ.

Перед началом репликации таблицы первичные ключи должны быть настроены с обоих сторон, а имеющиеся в реплицируемой таблице столбцы, даже не являющиеся репликационными идентификаторами, должны присутствовать и в реплицированной таблице. То есть на мастере нельзя удалять столбцы, если они есть на реплике.

При непосредственном создании первичных ключей таблица блокируется на запись блокировкой SHARE ROW EXCLUSIVE . Поэтому предпочтительно использовать предварительное создание уникального индекса в режиме CONCURRENTLY и затем уже привязку его в качестве первичного ключа. Да — это ресурсоёмкая операция, зато доступность БД на запись не страдает.

Обратите внимание на то, что если вы создаёте первичный ключ на столбце, не имеющем свойства NOT NULL , то такое свойство будет создано автоматически, но после после удаления ограничения автоматически не удалится.

Наравне с первичными ключами можно использовать уникальные индексы, но их использование в качестве идентификатора репликации необходимо указывать явно с помощью предложения . REPLICA IDENTITY USING INDEX name_of_index . и с ними нужно быть поосторожнее. В отличие от первичных ключей столбцы, на которых строится индекс, нужно самостоятельно снабдить ограничением NOT NULL .

Требования к индексам перечислены в документации: индекс должен быть уникальным, не частичным, не отложенным и включать только столбцы, помеченные NOT NULL. Состав столбцов, по которым построен индекс на мастере, должен совпадать с их составом на реплике. Если в нужных таблицах в реплицируемой схеме нет первичных ключей — можно на мастере создать уникальный индекс, назначить его репликационным идентификатором, а после переноса схемы на реплику — создать там идентичный по составу и порядку столбцов первичный ключ. Можно и наоборот.

REPLICA IDENTITY FULL

В отличие от первичных ключей и индексов при REPLICA IDENTITY FULL идентификатором служит вся строка. В этом случае в журнал предзаписи попадает вся старая строка и по протоколу репликации также передаётся вся старая строка. Не передаются только значения полей TOAST — если изменения их не коснулись. Исходя из этого можно представить, насколько разрастается объём хранимых и передаваемых данных.

Поэтому от применения REPLICA IDENTITY FULL нужно максимально воздерживаться. Мало того — ошибки в его использовании могут привести к необходимости рестарта репликации. Когда используется REPLICA IDENTITY FULL , то состав столбцов в таблице на мастере и реплике преимущественно должен совпадать (порядок столбцов значения не имеет), иначе изменение и удаление данных реплицироваться не будет и последствия будут различаться в зависимости от того, где есть лишние столбцы — на мастере или на реплике.

Если лишние столбцы будут на мастере и публикация будет создана для команд UPDATE и DELETE — репликация приостановится до тех пор, пока на реплике будут отсутствовать нужные столбцы.

Вставка данных и начальная синхронизация не так строго ограничены по части состава столбцов — на реплике могут быть дополнительные столбцы, и они даже могут содержать какие-то данные. Но репликация будет идти благополучно для всех команд только если добавленные на реплике столбцы не содержат данных. Это связано с тем, что поля со значением NULL в идентификации строк не участвуют.

Однако пользы от такого способа добавления столбцов немного — при обычном добавлении столбцов, без данных, перезаписи таблицы не происходит, поэтому добавлять пустые столбцы можно и на рабочей базе.

Если же нужно менять данные на реплике, но на мастере нет возможности создать первичный ключ — его можно создать на реплике, тогда репликация всех команд будет происходить благополучно, хоть в дополнительных столбцах реплики и будут данные. Это работает, потому что в сообщениях протокола логической репликации, при REPLICA IDENTITY FULL на мастере, в качестве идентификатора отправляется вся строка старых данных и процесс применения сообщений выбирает из него значение того поля, которое на реплике является полем первичного ключа.

REPLICA IDENTITY FULL удобно использовать для трансляции небольших, редко изменяемых таблиц или для таблиц с небольшим размером строк — им не нужны индексы на мастере и данные очень быстро пишутся и отправляются. На реплике же можно и индексы строить, и первичные ключи создавать — вполне удобно. Но для обновляемых данных в масштабных таблицах применять его очень опрометчиво.

Включить для таблицы этот идентификатор крайне просто:

Если на мастере REPLICA IDENTITY FULL задана, то на реплике её наличие роли не играет — но только при условии, что состав столбцов таблицы на реплике идентичен таковому на мастере. Иначе на реплике будут применяться только команды INSERT , а если у вас 11 версия мастера — то и TRUNCATE . Команды UPDATE и DELETE будут применяться только если дополнительные столбцы в изменяемой/удаляемой строке будут равны NULL.

Если нужно реплицировать исторические данные или провести только начальную синхронизацию — можно вообще убрать идентификаторы репликации:

это снизит нагрузку на журнал предзаписи, декодирование и сеть. Но нужно обязательно создавать публикацию только для команд INSERT , иначе обновление и удаление в этой таблице работать перестанут, независимо от того — есть подписка или нет.

Состав и порядок столбцов

Вне зависимости от выбранных репликационных идентификаторов — порядок столбцов, даже ключевых — значения практически не имеет. Начальная синхронизация и репликация команд INSERT проходят без проблем, независимо от настройки репликационных идентификаторов — идентификаторы строк в этом случае не передаются.

Если же бездумно добавлять столбцы к таблице с той или другой стороны, то, например, при REPLICA IDENTITY FULL , придётся перезапускать репликацию для такой таблицы. Причём, в зависимости от способа прицеливания в ногу, можно либо добиться неконсистентности на реплике, либо ещё и раздуть на мастере журнал предзаписи до невероятных размеров. Что, в одном случае, не даст возможности восстановить согласованность данных в проблемной таблице на реплике и приведёт к необходимости перезаливки данных. Во втором случае такая возможность останется (условно) и реализуется автоматически — после выявления и устранения причины такой ситуации, однако целостность данных после этого всё равно останется под сомнением.

С первичными ключами и индексами ситуация значительно лучше. Хоть на реплике и нельзя удалять столбцы если такие есть на мастере, зато появляется возможность добавлять новые. И не просто добавлять, а различными способами заполнять их данными, без опаски получить остановку репликации или неконсистентность.

Склад грабель горизонтального хранения

Предупреждён — значит вооружен. Ниже приведены несколько самых распространённых сообщений об ошибках и просто общих рассуждений. Лучше прочитать эти сообщения здесь, чем на рабочих серверах — читаем и вооружаемся. Если у вас есть что-нибудь интересное на эту тему — расскажите в комментариях.

Если на мастере репликационные идентификаторы не заложены в бюджет

По умолчанию публикация создаётся для команд INSERT , UPDATE и DELETE (и TRUNCATE , начиная с 11 версии). При этом проверки идентификаторов репликации в целевых таблицах не происходит, от этого может получиться так, что они будут не у всех таблиц. Мало того — после создания публикации допускается сброс или удаление репликационного идентификатора:

Первое — сброс на значение по умолчанию, то есть на использование первичного ключа таблицы и если он есть — жить можно. Второе — отключение идентификаторов на таблице. Но если нет первичного ключа или идентификаторов вообще, то, при попытке выполнить на мастере обновление или удаление строк, будут получены соответствующие ошибки:

Так будет в случае, когда публикация не создана исключительно для команд INSERT и, для одиннадцатой версии — TRUNCATE . Вариантов исправления такой ситуации два — изменить подписку на publish=’insert’ или добавить репликационные идентификаторы.

. not find row.. .

Пример сообщений которые можно увидеть когда:

• на реплике добавили столбец и его значение не NULL ;
• на реплике по какой-либо причине строки отсутствуют, а на мастере они есть;
• на реплике были изменены строки;
• на реплике нет строки с переданным значением репликационного идентификатора

Если на реплике сложилась одна из приведённых ситуаций, то, при обычном значении параметра log_min_messages=warning , этих сообщений в логах реплики не появится. От того и о пропавших данных можно узнать, когда будет уже очень поздно. А можно и не узнать. С точки зрения СУБД в таком поведении нет ничего предосудительного, возможно таков был план. Но если это не был план, то для приложения это катастрофа, так что тут нужно быть предусмотрительным и внимательным.

Наблюдать эти сообщения в логах возможно при log_min_messages=debug1 . Однако так увеличится объём логов, но консистентности данных не прибавится — реплика, получив сообщение об изменениях, не смогла найти изменяемую/удаляемую строку и благополучно выкинула сообщение на свалку истории, а второго шанса ей не предоставится. Мастер не будет уведомлен об этом и в свои логи писать ничего не станет. Следует учесть, что в этом режиме в лог будет записываться строка подключения, вместе с именем пользователя и его паролем — сомнительное преимущество использования такого уровня сообщений журнала.

В данном случае можно радоваться хотя бы тому, что на мастере такое поведение почти никак не скажется. В этом случае реплика получает сообщения репликации, затем, не применив их, отчитывается мастеру, что всё хорошо и журнал предзаписи не разрастается. Конечно, после такого инцидента придётся выправлять сложившееся положение дел:

1. На мастере исключать таблицу из публикации;
2. На реплике обновлять подписку и вычищать таблицу;
3. Привести состав столбцов к единому виду;
4. Включать на мастере таблицу в публикацию;
5. Обновлять подписку на реплике и ждать перезаливки данных.

А всего нужно было — не добавлять столбцы в таблицу на стороне реплики если используется REPLICA IDENTITY FULL или не изменять, бездумно, данные на реплике.

В отличие от UPDATE и DELETE , операция INSERT и начальная синхронизация будут нормально обрабатываться, даже если на реплике есть столбцы отсутствующие на мастере.

Лишний столбец на мастере

Если же случилось так, что кто-то удосужился создать лишний столбец на мастере, то на реплике начнёт появляться множество таких сообщений:

А на мастере множество таких:

То есть вставка, изменение и удаление строк не принимается репликой, ведь на стороне реплики нет столбцов для поступивших данных. О сложившейся ситуации мастер «информируется». В этом случае на реплике постоянно создаётся и уничтожается новый процесс репликации для сбойной подписки.

В результате на мастере начнёт раздувать журнал предзаписи независимо от типа операции. Так можно довести и до останова сервера, не заметив вовремя разбухание журнала. Удаление столбца на стороне мастера не спасёт — логическое декодирование будет использовать записи из журнала предзаписи, а там добавленный столбец есть и никуда не денется. Также не спасёт и удаление таблицы из публикации — это связано с тем, что публикацией может пользоваться несколько подписок и такое лекарство окажется опаснее болезни, потому и возможность такую не реализовали.

Лично я считаю, что для публикаций с одним подписчиком не помешает добавить такую возможность — но пока что разработчики так не думают. В общем имеется три варианта:

Добавить на реплику недостающий столбец и надеяться, что на обоих серверах их содержимое совпадает (x6c6f6c), иначе все операции UPDATE и DELETE по несовпадающим строкам пропадут и оставят после себя уже знакомые записи в логе » . not find row for . » — это относится к REPLICA IDENTITY FULL .

Отсутствие в добавленном столбце данных, имеющихся на мастере, приведет к тому, что пробка, конечно, рассосётся — только вот накопившиеся операции INSERT данных попадут в таблицу на реплике, а UPDATE и DELETE — нет. Потому что идентификатором строки будет вся строка, а в одном из столбцов данные не совпадают.

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

Дубликаты значений в столбцах репликационных идентификаторов или «Раньше думать надо было!»

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

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

Совсем другое дело, когда неправильной является строка, пришедшая с мастера. В этом случае всё равно придётся либо удалять строку на реплике, либо назначать ей заведомо большой идентификатор; ждать завершения загрузки с мастера отставших транзакций; заменять пришедшую с мастера строку старой строкой с реплики.

Ситуация в обоих случаях усугубляется тем, что лишних строк может быть очень много. Настолько, что новые транзакции будут выполняться быстрее, чем будут разбираться конфликтные ситуации. И всё время, пока расследуется инцидент, размер журнала предзаписи будет разрастаться, а пользователи реплики не будут иметь возможности получать актуальные данные.

Если предполагается использовать логическую репликацию не для обновления, а для обмена данными между различными базами — следует очень внимательно проработать вопрос об идентификации строк. Так как для идентификации, обычно, используются последовательности, то можно посоветовать пробежаться по слайдам доклада CTO Stickeroid Ai, Камиля Исламова о способах применения последовательностей в PostgreSQL и документацию по последовательностям, затем перепроектировать их в сбойной системе баз данных.

Столбец NOT NULL без DEFAULT на реплике

Для любых столбцов на реплике не должно быть установлено свойство NOT NULL без указания значения по умолчанию. Особенно это касается тех столбцов, которые есть на мастере, потому что на реплике для них значение DEFAULT подставляться не будет — как пришел NULL , так и будет записываться.

Если в дополнительные NOT NULL столбцы значение по умолчанию не поставить, то репликация приостановится с приведённой выше ошибкой. Как только удалите такой столбец, зададите ему значение по умолчанию или удалите ограничение NOT NULL — репликация возобновится без потери данных.

Если в реплицируемой таблице на стороне мастера изначально есть значения NULL — сначала необходимо заполнить такие поля, либо удалить ограничение NOT NULL на реплике.

При появлении такой ошибки в начале репликации можно узнать из логов название проблемной таблицы, но не схему в которой она расположена:

Если же начальная синхронизация давно закончилась и репликация идёт полным ходом, то в логах записи о таблице не будет — придётся искать вручную.

На мастере есть первичный ключ, на реплике он отсутствует

Ошибка наблюдается при репликации команд UPDATE и DELETE , если в таблице на реплике отсутствует первичный ключ, который есть на мастере. Повторяется до тех пор, пока на реплике не будет создан соответствующий первичный ключ. Репликация при этом приостанавливается и продолжается после устранения причины ошибки. Состав столбцов на обоих серверах либо идентичен, либо на реплике могут быть дополнительные столбцы; порядок столбцов может различаться.

На мастере и реплике первичные ключи или индексы построены на разных столбцах

Ошибка появляется при репликации команд UPDATE и DELETE , если в таблице на реплике первичный ключ (индекс) построен не на том столбце, на котором построен первичный ключ (индекс) на мастере. Повторяется до тех пор, пока на реплике не будет удалён неправильный и не будет создан правильный первичный ключ (индекс).

Репликация при этом приостанавливается и продолжается после устранения причины ошибки. На реплике могут быть дополнительные столбцы, порядок столбцов может различаться.

Фантомного индекса боль

Ошибка очень интересная и наблюдается на мастере при выполнении команд UPDATE и DELETE , если в таблице на мастере в качестве репликационного идентификатора использовался уникальный индекс, который потом удалили, например потому, что создали взамен первичный ключ, или просто так удалили (всяко быват, уж мы их ругам-ругам, ничо не помогат).

Ошибку эту можно получить только на мастере и только на таблицах, включенных в публикации, поддерживающие UPDATE и DELETE . Вообще эта ошибка означает именно отсутствие любого репликационного идентификатора, но в данном случае всё немного интереснее — идентификатором остаётся индекс, который удалили.

Чтоб исправить ошибку нужно вернуть REPLICA IDENTITY DEFAULT , перенацелив тем самым поиcк идентификатора туда, где он есть — на первичный ключ. Также можно воссоздать индекс и снова перенацелиться на него, так как oid у него будет уже другой, и система не будет его видеть.

Если же нет времени строить индексы и первичные ключи — включаем REPLICA IDENTITY FULL , а уж затем восстанавливаем индексы и так далее. В рабочих системах начинать изменения нужно с реплики, иначе вылезут другие ошибки.

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

Непреобразуемые типы

Ошибка наблюдается на любом этапе репликации, если в таблице на реплике есть хотя бы один столбец, в тип которого нельзя преобразовать полученный с мастера тип данных. Повторяется до тех пор, пока на реплике не будет исправлена таблица. Репликация при этом приостанавливается и продолжается после устранения причины ошибки.

И здесь снова неполная информация о том, в какой таблице произошла ошибка. То есть при начальной синхронизации в строке с контекстом есть информация о таблице, но не о схеме. Но, если уже после начальной синхронизации столбец был изменён, то информация будет полноценной.

И такое может пригодиться, если что-то натворили непонятное

Общие замечания

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

Выполнять создание индексов желательно до переноса схемы данных, с тем, чтоб они перенеслись на реплику в составе схемы БД, ну или не забыть создать их и на реплике. К тому же на реплике возможно сразу создать первичный ключ с аналогичным составом столбцов ещё до начала репликации.

Использовать REPLICA IDENTITY FULL стоит только если строки в таблице незначительного размера и редко изменяются. При использовании такого типа идентификатора желательно воздержаться от изменения структуры принимающей таблицы.

Если после переноса схемы вам необходимо поменять в ней некоторые таблицы, то возможно проверять безопасность изменений в новой базе используя специально восстановленную резервную копию мастера как источник данных и устанавливая log_min_messages=debug1 для проверки правильности работы репликации по части UPDATE и DELETE . Изменение этого параметра не требует перезапуска сервера, поэтому возможно его переключать в любое время, например на время проверки внесенных изменений.

После начала репликации на мастере нельзя добавлять столбцы, а если добавлять, то начинать надо с реплики.

Новые столбцы с NOT NULL на реплике хороши только если в комплекте идёт DEFAULT .

Ошибки могут случаться, но в большинстве случаев они некритичные и легко устраняются, только нужно с умом подойти к выбору репликационных идентификаторов. Если речь идёт не об обновлении, а о более сложных схемах применения логической репликации, то к проектированию системы в целом нужно подойти очень ответственно, иначе можно отправить всё в нокдаун или потерять данные.

И на сладкое: можно добиться того, что системные каталоги на реплике сохранят информацию о подписках, которые, как казалось, были удалены. Тут может помочь только реинициализация кластера БД. В таком случае команда dRs показывает наличие подписки, а SELECT * FROM pg_subscription; — нет. При этом на реплике все необходимые для обслуживания процессы запускаются, но ничего полезного не делают, кроме множества ошибок в логах (не можем подключиться, слота нет. ), даже при специально повторно созданных объектах и слотах. То есть было утеряно некоторое количество внутренней информации о подписке. За месяц экспериментов такое положение дел было достигнуто только два раза, и оба раза это произошло после отправки хоста тестовых виртуалок в ждущий или спящий режим. Хоть и получалось так не всегда — не делайте так. Но случиться такое с хост-системой всё-таки может, так что про такую вероятность нужно знать.

Источник

Hi,

I guess this is more a postgresql question than releated to postgrest, but anyway:

I created a table in schema public named user. Then I created a view in schema «1» named users AS a simple select * from public.user.

I can GET and DELETE entries from the above view. but when I want to insert a new row I get the error as mentioned in the title:

the grants look as followed:
GRANT ALL ON TABLE public.user TO superuser;
GRANT ALL ON TABLE «1».users TO superuser;
GRANT ALL ON TABLE «1».users TO myuser;

GRANT myuser TO superuser;

Notice: user «myuser» is the user who gets a token from postgrest (that is, he is not the anonymous user).

Do I have to explicitly set a GRANT on the user_id_seq sequence as well? Because nothing is mentioned in the docs of postgrest and I wondered if it is my fault that this does not work.

Я создал таблицу clients и пользователя manager. Я пытаюсь вызвать процедуру с таким телом:

1

INSERT INTO clients(fio_client, location_client, reg_date_client) VALUES(fio_cl, location_cl, reg_date);

Выдаётся следующая ошибка:

42501: нет доступа к последовательности clients_id_client_seq

Manager имеет все те же права, что и postgres (при этом для postgres всё работает). Права я вытаскиваю следующей командой:

1
2
3
4

SELECT 
 * 
FROM information_schema.role_table_grants 
WHERE grantee='manager' AND TABLE_NAME='clients';

Отличие есть в столбце is_grantable. У postgres везде YES, а у manager везде NO. Я так понимаю, что причина в этом. Что это вообще такое и как это исправить?