Postgres Guru | Базы данных

🛠️ Настройка репликации с задержкой (Delayed Replication) в PostgreSQL.

Delayed Replication — это механизм, при котором реплика PostgreSQL намеренно отстает от мастера на заданное время. Казалось бы, зачем нам такая репликация вообще нужна?

Но, есть несколько причин когда она может быть полезна:
✅ Защита от человеческих ошибок (DROP TABLE, DELETE без WHERE и т.д.);
✅ Защита от повреждения данных из-за багов в приложении, например;
✅ Атак (например, SQL-инъекций).

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

⚠️ Важно помнить, что отстающая реплика ни в коем случае не заменяет резервное копирование и восстановление на определенный момент времени (PITR). Это скорее дополнительная страховочная сетка, которая может спасти, если с бэкапами что-то пойдет не так.

Настройка задержки репликации производится на сервере реплики либо в файле recovery.conf (PostgreSQL 12+), либо в postgresql.conf с помощью параметра recovery_min_apply_delay.

Варианты формата параметра:
30min — 30 минут;
1h — 1 час;
3600s — 3600 секунд.

Теперь давайте рассмотрим на примере как с помощью отстающей реплики мы можем восстановить случайно удаленные данные.

Предположим, что какой-то пользователь случайно удалил важные данные в какой-то таблице. Отставание реплики у нас настроено на 2 часа (recovery_min_apply_delay=2h).

Так же предположим, что удаление случилось примерно в 14:30 часов по местному времени.

На сервере с отстающей репликой нужно проверить на всякий случай значения пары параметров:

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

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

Теперь попробуем восстановить наши данные.

1️⃣ Останавливаем службу PostgreSQL на сервере реплики:

# systemctl stop postgresql-версия_PG.service

2️⃣ Комментируем или удаляем параметр recovery_min_apply_delay и добавляем параметр recovery_target_time. В параметре recovery_target_time мы должны указать дату и время ДО удаления таблицы.

3️⃣ Стартуем реплику обратно и ждем когда она проиграет записи WAL до заданного времени. Проверить это можно по логам реплики.

4️⃣ Любым удобным способом копируем удаленную таблицу из базы реплики и восстанавливаем ее в основную базу.

5️⃣ Удаляем параметр recovery_target_time из конфигов реплики и рестартуем ее. Ждем когда реплика догонит основной сервер.

6️⃣ Возвращаем параметр recovery_min_apply_delay и опять рестартуем реплику.

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

Главное помнить, что она не заменяет резервное копирование и мониторить отставание, например с помощью вот такого запроса:

SELECT
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS mb_lag,
now() - pg_last_xact_replay_timestamp() AS time_lag
FROM pg_stat_replication;

Так же не забывайте про дополнительное место на дисках основного сервера под накопленные сегменты WAL.

На этом все! До связи!

#pgsettings #pghi

🛠️ Настройка репликации с задержкой (Delayed Replication) в PostgreSQL.

Delayed Replication — это механизм, при котором реплика PostgreSQL намеренно отстает от мастера на заданное время. Казалось бы, зачем нам такая репликация вообще нужна?

Но, есть несколько причин когда она может быть полезна:
✅ Защита от человеческих ошибок (DROP TABLE, DELETE без WHERE и т.д.);
✅ Защита от повреждения данных из-за багов в приложении, например;
✅ Атак (например, SQL-инъекций).

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

⚠️ Важно помнить, что отстающая реплика ни в коем случае не заменяет резервное копирование и восстановление на определенный момент времени (PITR). Это скорее дополнительная страховочная сетка, которая может спасти, если с бэкапами что-то пойдет не так.

Настройка задержки репликации производится на сервере реплики либо в файле recovery.conf (PostgreSQL 12+), либо в postgresql.conf с помощью параметра recovery_min_apply_delay.

Варианты формата параметра:
30min — 30 минут;
1h — 1 час;
3600s — 3600 секунд.

Теперь давайте рассмотрим на примере как с помощью отстающей реплики мы можем восстановить случайно удаленные данные.

Предположим, что какой-то пользователь случайно удалил важные данные в какой-то таблице. Отставание реплики у нас настроено на 2 часа (recovery_min_apply_delay=2h).

Так же предположим, что удаление случилось примерно в 14:30 часов по местному времени.

На сервере с отстающей репликой нужно проверить на всякий случай значения пары параметров:

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

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

Теперь попробуем восстановить наши данные.

1️⃣ Останавливаем службу PostgreSQL на сервере реплики:

# systemctl stop postgresql-версия_PG.service

2️⃣ Комментируем или удаляем параметр recovery_min_apply_delay и добавляем параметр recovery_target_time. В параметре recovery_target_time мы должны указать дату и время ДО удаления таблицы.

3️⃣ Стартуем реплику обратно и ждем когда она проиграет записи WAL до заданного времени. Проверить это можно по логам реплики.

4️⃣ Любым удобным способом копируем удаленную таблицу из базы реплики и восстанавливаем ее в основную базу.

5️⃣ Удаляем параметр recovery_target_time из конфигов реплики и рестартуем ее. Ждем когда реплика догонит основной сервер.

6️⃣ Возвращаем параметр recovery_min_apply_delay и опять рестартуем реплику.

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

Главное помнить, что она не заменяет резервное копирование и мониторить отставание, например с помощью вот такого запроса:

SELECT
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS mb_lag,
now() - pg_last_xact_replay_timestamp() AS time_lag
FROM pg_stat_replication;

Так же не забывайте про дополнительное место на дисках основного сервера под накопление сегменты WAL.

На этом все! До связи!

#pgsettings #pghi

🛠️ Настройка репликации с задержкой (Delayed Replication) в PostgreSQL.

Delayed Replication — это механизм, при котором реплика PostgreSQL намеренно отстает от мастера на заданное время. Казалось бы, зачем нам такая репликация вообще нужна?

Но, есть несколько причин когда она может быть полезна:
✅ Защита от человеческих ошибок (DROP TABLE, DELETE без WHERE и т.д.);
✅ Защита от повреждения данных из-за багов в приложении, например;
✅ Атак (например, SQL-инъекций).

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

⚠️ Важно помнить, что отстающая реплика ни в коем случае не заменяет резервное копирование и восстановление на определенный момент времени (PITR). Это скорее дополнительная страховочная сетка, которая может спасти, если с бэкапами что-то пойдет не так.

Настройка задержки репликации производится на сервере реплики либо в файле recovery.conf (PostgreSQL 12+), либо в postgresql.conf с помощью параметра recovery_min_apply_delay.

Варианты формата параметра:
30min — 30 минут;
1h — 1 час;
3600s — 3600 секунд.

Теперь давайте рассмотрим на примере как с помощью отстающей реплики мы можем восстановить случайно удаленные данные.

Предположим, что какой-то пользователь случайно удалил важные данные в какой-то таблице. Отставание реплики у нас настроено на 2 часа (recovery_min_apply_delay=2h).

Так же предположим, что удаление случилось примерно в 14:30 часов по местному времени.

На сервере с отстающей репликой нужно проверить на всякий случай значения пары параметров:

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

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

Теперь попробуем восстановить наши данные.

1️⃣ Останавливаем службу PostgreSQL на сервере реплики:

# systemctl stop postgresql-версия_PG.service

2️⃣ Комментируем или удаляем параметр recovery_min_apply_delay и добавляем параметр recovery_target_time. В параметре recovery_target_time мы должны указать дату и время ДО удаления таблицы.

3️⃣ Стартуем реплику обратно и ждем когда она проиграет записи WAL до заданного времени. Проверить это можно по логам реплики.

4️⃣ Любым удобным способом копируем удаленную таблицу из базы реплики и восстанавливаем ее в основную базу.

5️⃣ Удаляем параметр recovery_target_time из конфигов реплики и рестартуем ее. Ждем когда реплика догонит основной сервер.

6️⃣ Возвращаем параметр recovery_min_apply_delay и опять рестартуем реплику.

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

Главное помнить, что она не заменяет резервное копирование и мониторить отставание, например с помощью вот такого запроса:

SELECT
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_lag,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) / 1024 / 1024 AS mb_lag,
now() - pg_last_xact_replay_timestamp() AS time_lag
FROM pg_stat_replication;

Так же не забывайте про дополнительное место на дисках основного сервера под накопленные сегменты WAL.

На этом все! До связи!

#pgsettings #pghi

Столкнулись с падением производительности базы данных?
Не делайте резких движений: вы можете ухудшить ситуацию.

Сначала нужно верно диагностировать причину проблемы.
Возможно вы неправильно выбрали индексы, а быть может дело вообще в самой архитектуре БД – вариантов масса!

На открытом вебинаре «Как ускорить работу и повысить надёжность PostgreSQL»
вы узнаете:
🎯как обеспечить высокую производительность и отказоустойчивость базы данных
🎯как вовремя выявить деградацию производительности с помощью диагностики

Вебинар проведёт Дмитрий Золотов, Kotlin-разработчик в «Яндексе».

Приглашаем технических руководителей, админов БД, девопсов и разработчиков.

Все участники получат в подарок видеоурок «Безопасность в PostgreSQL: защита данных, управление доступом и аудит» и скидку 7% на любой курс OTUS.

6 мая, 19:00 МСК
Бесплатно
Записаться - https://otus.pw/A74C/

Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963. erid: 2W5zFJSbeQQ

🤖 Работа с JSONPath в PostgreSQL.

Как нам всем известно, наша PostgreSQL поддерживает мощные функции для работы с JSON, включая JSONPath — язык запросов, вдохновлённый XPath (для XML) и JavaScript. Начиная с версии 12, PostgreSQL позволяет использовать JSONPath для эффективного извлечения и обработки данных в JSON и JSONB.

В этой заметке мы рассмотрим:
✅ Основы синтаксиса JSONPath;
✅ Как применять его в SQL-запросах;
✅ Практические примеры для работы с JSONB.

JSONPath — это язык запросов, который позволяет:
📌 Находить элементы в JSON по прямому пути, без полного разбора JSON;
📌 Фильтровать данные с помощью условий;
📌 Выполнять арифметические и строковые операции.

Пример простого JSONPath-выражения.

Предположим, что у нас есть вот такой JSON с информацией по книгам:

{
"store": {
"book": [
{"title": "The Catcher in the Rye", "price": 15.99},
{"title": "Clean Code", "price": 42.50}
]
}
}

Запрос

$.store.book[0].title

вернёт:

The Catcher in the Rye

Т. е. здесь мы нашли название книги по номеру ее индекса. Помним, что в JSON в отличии от массивов PostgreSQL, нумерация элементов массива начинается с 0.

Основные операторы JSONPath.

Мы не можем использовать операторы JSONPath напрямую в SQL запросах. Но мы можем это делать с помощью специальных функций JSONPath.

Рассмотрим основные операторы JSONPath, в скобках будут указаны примеры использования:

$ ( `$.key`) - корневой элемент;

`.*` или `[*]` ( `$.store.book[*].title`) - все элементы массива;

[N] ( `$.store.book[0]`) - элемент массива по индексу;

?() (`$.book[?(@.price > 20)]`) - фильтрация по условию;

`..` (`$..book`) - рекурсивный поиск;

`-`, `+`, `*`, `/` (`$.sum(@.prices)` - арифметические операции.

Использование JSONPath в PostgreSQL.

Теперь давайте посмотрим как этим все пользоваться в PostgreSQL с помощью устроенных функций.

Извлечение данных (`jsonb_path_query`):

SELECT jsonb_path_query(
'{"user": {"name": "Alice", "age": 25}}',
'$.user.name'
) AS username;

Результат:

Alice

Фильтрация массивов (`jsonb_path_query_array`):

SELECT jsonb_path_query_array(
'{"products": [{"id": 1, "price": 99}, {"id": 2, "price": 200}]}',
'$.products[*] ? (@.price > 100)'
) AS expensive_items;

Результат:

[{"id": 2, "price": 200}]


Проверка существования (`jsonb_path_exists`):

SELECT *
FROM orders
WHERE jsonb_path_exists(order_data, '$.items[*] ? (@.price > 100)');

Находит заказы с товарами дороже 100.

Расширенные примеры.

Поиск по шаблону (`like_regex`):

SELECT jsonb_path_query(
'{"users": [{"email": "[email protected]"}, {"email": "[email protected]"}]}',
'$.users[*] ? (@.email like_regex "example\.com$")'
) AS matching_users;

Результат:

{"email": "[email protected]"}


Агрегация (`min`, `max`, `avg`):

SELECT jsonb_path_query(
'{"prices": [10, 20, 30]}',
'$.avg($.prices[*])'
) AS average_price;

Результат:

20

Когда использовать JSONPath?
✅ Глубокий поиск во вложенных JSON-структурах;
✅ Динамическая фильтрация без разбора JSON в коде;
✅ Обновление JSON через jsonb_set с указанием пути.

Ограничения:
❌ Не все функции стандарта IEC SQL 2016 реализованы;
❌ Некоторые операции (например, сложные JOIN) лучше выполнять в SQL.

Как видите, наша любимая СУБД позволяет нам очень эффективно извлекать данные из JSON объектов с помощью JSONPath по прямому пути без полного разбора JSON. Так что, пользуемся! 😊

На этом все! До связи!

#pgjson

Столкнулись с падением производительности базы данных?
Не делайте резких движений: вы можете ухудшить ситуацию.

Сначала нужно верно диагностировать причину проблемы.
Возможно вы неправильно выбрали индексы, а быть может дело вообще в самой архитектуре БД – вариантов масса!

На открытом вебинаре «Как ускорить работу и повысить надёжность PostgreSQL»
вы узнаете:
🎯как обеспечить высокую производительность и отказоустойчивость базы данных
🎯как вовремя выявить деградацию производительности с помощью диагностики

Вебинар проведёт Дмитрий Золотов, Kotlin-разработчик в «Яндексе».

Приглашаем технических руководителей, админов БД, девопсов и разработчиков.

Все участники получат в подарок видеоурок «Безопасность в PostgreSQL: защита данных, управление доступом и аудит» и скидку 7% на любой курс OTUS.

6 мая, 19:00 МСК
Бесплатно
Записаться - https://otus.pw/TmRi/

Реклама. ООО "ОТУС ОНЛАЙН-ОБРАЗОВАНИЕ". ИНН 9705100963. erid: 2W5zFGGLvdW

🔬 Temporal Tables (Переодические таблицы) в PostgreSQL.

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

PostgreSQL не имеет встроенной поддержки стандарта SQL:2011 Temporal Tables, но их можно реализовать с помощью:
✅ Триггеров (ручная реализация);
✅ Расширения temporal_tables (более удобный способ).

Зачем нам вообще нужны Temporal Tables?

📌 Аудит изменений: кто и когда изменил данные;
📌 Восстановление данных: откат к предыдущей версии;
📌 Анализ истории: сравнение состояний на разные даты.

Примеры использования:
✅ Финансовые транзакции (история баланса);
✅ Медицинские записи (история диагнозов);
✅ Юридические документы (версионирование).

Реализация через триггеры (ручной способ).

Создадим таблицу employees и её историческую копию employees_history:

Основная таблица:

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC,
valid_from TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
valid_to TIMESTAMPTZ DEFAULT 'infinity'
);

Таблица для истории изменений (ее создаём копированием основной):

CREATE TABLE employees_history (LIKE employees);

Триггер для записи изменений в историю:

CREATE OR REPLACE FUNCTION save_employee_history()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
INSERT INTO employees_history
SELECT OLD.*;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Привязываем триггер к основной таблице:

CREATE TRIGGER track_employee_changes
BEFORE UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION save_employee_history();

Как это работает:
✅ При операциях UPDATE или DELETE старая версия строки сохраняется в employees_history;
✅ Поля valid_from и valid_to показывают период актуальности данных.

Можно упростить задачу с помощью расширения temporal_tables.

Официальный GitHub расширения:

➡️ https://github.com/arkhipov/temporal_tables

Установить расширение можно либо с помощью клиента PGNX, либо собрать из исходников. Подробности смотрим в документации.

После установки расширения, подключаемся к нужной базе и вводим команду:

CREATE EXTENSION IF NOT EXISTS temporal_tables;

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

ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

Эта колонка (sys_period) должна иметь одинаковое наименование и тип данных как в основной так и в периодической таблице.

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

CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'employees_history', true);

Последний параметр true включает транзакционный учет периодических записей.

Ограничения и подводные камни:

❌ Производительность: триггеры добавляют нагрузку;
❌ Хранение: история может занимать много места (можно партиционировать, или предусмотреть политику удаления старых данных);
❌ Удаление данных: если удалить запись из основной таблицы, её история останется.

На этом все! До связи и спокойных длинных выходных! 😉

#queries #pgext

🔬 Temporal Tables (Переодические таблицы) в PostgreSQL.

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

PostgreSQL не имеет встроенной поддержки стандарта SQL:2011 Temporal Tables, но их можно реализовать с помощью:
✅ Триггеров (ручная реализация);
✅ Расширения temporal_tables (более удобный способ).

Зачем нам вообще нужны Temporal Tables?

📌 Аудит изменений: кто и когда изменил данные;
📌 Восстановление данных: откат к предыдущей версии;
📌 Анализ истории: сравнение состояний на разные даты.

Примеры использования:
✅ Финансовые транзакции (история баланса);
✅ Медицинские записи (история диагнозов);
✅ Юридические документы (версионирование).

Реализация через триггеры (ручной способ).

Создадим таблицу employees и её историческую копию employees_history:

Основная таблица:

CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
salary NUMERIC,
valid_from TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
valid_to TIMESTAMPTZ DEFAULT 'infinity'
);

Таблица для истории изменений (ее создаём копие основной):

CREATE TABLE employees_history (LIKE employees);

Триггер для записи изменений в историю:

CREATE OR REPLACE FUNCTION save_employee_history()
RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
INSERT INTO employees_history
SELECT OLD.*;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Привязываем триггер к основной таблице:

CREATE TRIGGER track_employee_changes
BEFORE UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE FUNCTION save_employee_history();

Как это работает:
✅ При операциях UPDATE или DELETE старая версия строки сохраняется в employees_history;
✅ Поля valid_from и valid_to показывают период актуальности данных.

Можно упростить задачу с помощью расширения temporal_tables.

Официальный GitHub расширения:

➡️ https://github.com/arkhipov/temporal_tables

Установить расширение можно либо с помощью клиента PGNX, либо собрать из исходников. Подробности смотрим в документации.

После установки расширения, подключаемся к нужной базе и вводим команду:

CREATE EXTENSION IF NOT EXISTS temporal_tables;

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

ALTER TABLE employees ADD COLUMN sys_period tstzrange NOT NULL;

Эта колонка (sys_period) должна иметь одинаковое наименование и тип данных как в основной так и в периодической таблице.

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

CREATE TRIGGER versioning_trigger
BEFORE INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'employees_history', true);

Последний параметр true включает транзакционный учет периодических записей.

Ограничения и подводные камни:

❌ Производительность: триггеры добавляют нагрузку;
❌ Хранение: история может занимать много места (можно партиционировать, или предусмотреть политику удаления старых данных);
❌ Удаление данных: если удалить запись из основной таблицы, её история останется.

На это все! До связи и спокойных длинных выходных! 😉

#queries #pgext