Ответы пользователя по тегу PostgreSQL
  • Postgres синхронная репликация?

    Melkij
    @Melkij
    PostgreSQL DBA
    Момент первый: какой synchronous_standby_names?
    Момент второй:
    репликация через настройку synchronous_commit = on

    https://www.postgresql.org/docs/current/runtime-co...
    When set to on, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and flushed it to disk.

    When set to remote_apply, commits will wait until replies from the current synchronous standby(s) indicate they have received the commit record of the transaction and applied it, so that it has become visible to queries on the standby(s).

    Разницу чувствуете?

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

    Очень необычный workload. Ни разу такого не встречал.
    Мне любопытно, что вы такое делаете, что вам неважна латентность записи, у вас тяжёлая пишущая нагрузка (не путать с объёмом пишущей нагрузки - репликам это всё писать придётся так же, но ещё и читать), очень много крайне критичного к лагу асинхронной репликации чтения.
    Ответ написан
  • О чем говорит результат запрса? Или какой вывод можно сделать?

    Melkij
    @Melkij
    PostgreSQL DBA
    О чем говорит state = active - запрос до сих пор не завершился

    О том что запрос выполняется.

    О чем говорит wait = lock и wait_event = transaction_id?

    Ожидаем результат другой транзакции.

    которая обновляет некоторые данные

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

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

    Melkij
    @Melkij
    PostgreSQL DBA
    \dt+ показывает pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
    pg_total_relation_size - это pg_table_size + размер индексов таблицы.

    Все они считаются здесь: https://github.com/postgres/postgres/blob/REL_11_S...
    Итого вы не посчитали размер индексов на toast. Как правило он там один

    слои файлов (main, fsm, vm)

    Это relation forks
    Ответ написан
  • Как работает кластеризация mysql или другой БД кластер?

    Melkij
    @Melkij
    PostgreSQL DBA
    Такой запрос будет очень медленный

    Почему? Ну если только table - это не таблица, а развесистый view.
    Если таблица - читаем потоком данные удобным для субд способом (сортировки не запрошено - субд выберет удобный для себя порядок чтения) и шлём запросившему. Простая штука, и если производительности не хватает здесь - то это вопрос не к базе и её масштабированию, а к приложению: зачем вообще понадобился там этот не детерминированный миллион строк из таблицы.

    Кластер - это слово очень общее.
    Мастер-реплика cold standby - кластер
    Мастер-реплика hot standby - кластер
    Мастер-мастер - кластер
    N шардов - кластер
    Гетерогенная система с двухфазным коммитом - всё равно кластер

    то что делает кластер

    Неправильный задаёте вопрос.
    Сначала определяете зачем вам головная боль, с какой целью вы хотите добавить второй сервер СУБД и почему вам недостаточно одного - затем смотрите в каком направлении вам необходимо для решения этой задачи двигаться.
    Ответ написан
  • Ошибка при использовании Sqlalchemy + Heroku Postgresql DB?

    Melkij
    @Melkij
    PostgreSQL DBA
    Стена ненужного кода и текста там где достаточно лишь переводчика.
    there is no unique constraint matching given keys for referenced table "users"

    Переведите.
    Посмотрите на свои foreign key
    Найдите что СУБД возмущается конечно имея полные на то основания
    Ответ написан
  • Почему запрос с IN в СУБД PostgreSQL выполняется значительно медленнее аналогичного запроса с OR?

    Melkij
    @Melkij
    PostgreSQL DBA
    ORDER BY ID

    хм.

    Покажите explain (analyze,buffers) запросов. И покажите планы запросов с "ORDER BY ID + 0". И ещё
    select * from pg_stats where tablename = 'table' and attname = 'column'


    Я предположу что у вас вовсе не in медленный, а or ломает оценку планировщика, но в этом конкретном случае это оказывается неожиданным благом.
    Ответ написан
  • Сколько нужно создавать слотов для 10 реплик при потоковой репликации?

    Melkij
    @Melkij
    PostgreSQL DBA
    Зависит от схемы расположения реплик.

    Каждое соединение репликации использует один из допустимых max_wal_senders хоста к которому подключается. Если вы настраиваете слоты репликации - то вдобавок вам нужно соответствующее число max_replication_slots на этом хосте.
    pg_basebackup в свою очередь использует до двух коннектов max_wal_senders и до одного max_replication_slots (зависит от параметров запуска и версии)

    Так если вы будете копировать все 10 реплик одновременно с помощью basebackup с одной мастер-базы - вам понадобится 20 max_wal_senders и 10 max_replication_slots. После запуска реплик будет использовано 10 max_wal_senders и от 0 до 10 max_replication_slots - смотря как настроите. Для каждой реплики отдельный слот репликации если вы хотите использовать слоты репликации.
    Но это если настраивать репликацию без каскада.
    Коннекты репликации считаются не по кластеру, а подключённые к конкретному инстансу. В случае каскада у вас легко может быть схема, например:
    с мастера репликацию читают 3 реплики, с каждой из них репликацию получают ещё по 3 реплики - вот уже 9 реплик, а использовали только по 3 walsender на хост.

    И при таком числе реплик весьма рекомендую делать каскад. И сеть на мастере поберечь и в случае переключения мастера придётся меньше баз рестартовать (primary_conninfo всё ещё меняется только рестартом всей базы, шанс у моего патча быть принятым в pg13 весьма призрачный)
    Ответ написан
  • Какие инструменты есть в postgres для разделения ресурсов между пользователями?

    Melkij
    @Melkij
    PostgreSQL DBA
    Никаких.

    главное возможно ли при такой схеме гарантировать базам на кластере какие-то ресурсы (кроме диска)?

    Вы даже io гарантировать так не сможете. Потому что WAL пишется строго один.

    один пользователь не может выесть больше чем work_mem на бекэнд

    Может. Запросто. Вы некорректно читаете документацию, там про это отдельно сказано.
    И может №2 - set work_mem to '..'
    Ответ написан
  • Как изменить путь к логу postgresql на работающей реплике?

    Melkij
    @Melkij
    PostgreSQL DBA
    Создал реплику, все реплицируется. Но у нее нет postgresql.conf

    Всё у неё есть. Без config_file postgresql вовсе не запустится с ошибкой "could not access the server configuration file".
    Ищите где потеряли.
    show config_file;
    на запущенной базе.
    Ответ написан
  • Как организовать репликацию баз данных с одним и тем-же именем в Postgres?

    Melkij
    @Melkij
    PostgreSQL DBA
    Репликация физическая:
    реплицировать несколько мастеров в один слейв невозможно
    можете запустить нужное число реплик на разных портах одной системы. Имена баз соответственно безразличны.

    Репликация логическая:
    я сильно удивлюсь если для какой-то из реализаций логической репликации будет проблемой разные имена баз. Но логическая репликация это логическая репликация. Про DDL операции надо будет помнить и вносить соответствующим образом.
    Ответ написан
  • Как исправить ошибку raster does not exists?

    Melkij
    @Melkij
    PostgreSQL DBA
    Сейчас версия postgis 3.0.0

    https://postgis.net/2019/10/20/postgis-3.0.0/
    Note that a major change in 3.0 is that the raster functionality has been broken out as a separate extension.

    -- install these if you need them
    CREATE EXTENSION postgis_raster;
    Ответ написан
  • Как быстро сортировать в большой таблице по часто изменяемому полю?

    Melkij
    @Melkij
    PostgreSQL DBA
    explain (analyze,buffers) с включенным track_io_timing покажите.

    Варианты:
    - вы проводите весь час времени не на сортировке, а где-то ещё о чём решили не писать. Соответственно вопрос неактуален и искать надо там где обронили, а не где светло.
    - у вас несоответствующие настройки автовакуума (читай дефолтные)
    - время занимает filesort, а от ssd у вас по ошибке стоят read-optimised.
    Ответ написан
  • Как правильно объединить запросы без лишних строк?

    Melkij
    @Melkij
    PostgreSQL DBA
    Не надо использовать not in там где нужен not exists.
    select * from data where not exists (select from actives where data_id = data.id);


    Из-за требования стандарта по части обработки NULL в not in и из-за отсутствия специальной оптимизации для таких not in они очень различаются в производительности.
    Ответ написан
  • Как грамотно повысить отказоустойчивость WEB приложения?

    Melkij
    @Melkij
    PostgreSQL DBA
    повысить надёжность приложения, разместив его на нескольких серверах

    А вы решайте ту задачу которую заявили изначально. Для hot spare никакая балансировка не нужна, от базы простая hot standby реплика.
    При необходимости вывести основной сервер реплику базы поднимаете до нового мастера и работаете как на старом сервере.

    Для бюджетного веба болезненный вопрос "как быстро перекинуть запросы пользователей на нужный IP". Через DNS даже с маленьким TTL это всё равно долго. Посмотрите у вашего хостера и у Cloudflare раз вы его используете, нет ли у них подходящего решения.

    работа по сети достаточно замедляет ответы от БД

    На время латентности сети, от этого никуда не деться. В пределах одной стойки можно пренебречь.
    Безопасность - разумеется об этом при настройке надо будет подумать.
    Ответ написан
  • Как получить increment созданного sequence?

    Melkij
    @Melkij
    PostgreSQL DBA
    information_schema.sequences верный ответ, только стоит имя схемы так же учитывать чтобы не получить случайно несколько строк с разных схем.
    SELECT increment FROM information_schema.sequences where sequence_name = 'example' and sequence_schema = 'public';


    В postgresql 10+ можно обратиться к системному каталогу так
    select increment_by from pg_sequences where schemaname = 'public' and sequencename = 'example';

    До 10 релиза посмотрите в
    select * from public.example;
    (нет под рукой чтобы уточнить имя поля)

    Но если не нужны детали реализации postgresql - то information_schema.sequences вероятно будет уместнее т.к. маловероятно будет изменяться в major релизах.
    Ответ написан
  • Как массив чисел превратить в массив данных?

    Melkij
    @Melkij
    PostgreSQL DBA
    Ну раз определения таблиц давать не хотите - то ответ будет соответствующий. Сделайте join с jsonb_array_elements/json_array_elements/json_array_elements_text/jsonb_array_elements_text
    Ответ написан
  • Насколько актуален PgQ в 2020 году?

    Melkij
    @Melkij
    PostgreSQL DBA
    Ну раз уж позвали...

    очень плохо документирован

    Тут полностью согласен.

    давно не обновляется и не поддерживается

    А вот тут смотря что подразумевать.
    pgq доступен для postgresql 12 - и для этого был ряд коммитов в pgq в этом году. Аналогично в этом году адаптировали скрипты для python3 вместо бывшего ранее python2. То есть вообще-то поддерживается если трактовать как усилия направленные на восстановление работоспособности.

    или вообще не стоит использовать postgresql, а использовать специализированную базу данных?

    Если вам нужна транзакционная очередь в postgresql - ничего лучше pgq мне неизвестно. Самописные реализации делаются просто и дают фейерверки в работе под нагрузкой. О том смотрите доклады моих коллег, ссылку на доклад Алексея вот уже привели.
    Если вам нужна очередь без привязки к транзакциям в базе - имеет смысл посмотреть специализированные брокеры. Затруднюсь что-то подсказать конкретное.
    Ответ написан
  • Как исправить ошибку Docker контейнера Adminer?

    Melkij
    @Melkij
    PostgreSQL DBA
    Проверьте что у вас согласованы форматы.
    То есть в каком формате у вас данные есть и в каком их может принять вот это некое "Adminer".

    На вид похоже что вот это Adminer умеет только plain text SQL (опционально пожатый в gz), тогда как файл у вас pg_dump custom формате. Он как раз начинается с бинарной метки 50 47 44 4d 50 (строка "PGDMP" в ASCII)
    Ответ написан