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

    Melkij
    @Melkij
    PostgreSQL DBA
    Some of the PostgreSQL parameters must hold the same values on the primary and the replicas. For those, values set either in the local patroni configuration files or via the environment variables take no effect. To alter or set their values one must change the shared configuration in the DCS. Below is the actual list of such parameters together with the default values:
    max_connections: 100
    max_locks_per_transaction: 64
    max_worker_processes: 8
    max_prepared_transactions: 0
    wal_level: hot_standby
    track_commit_timestamp: off

    https://patroni.readthedocs.io/en/latest/patroni_c...
    всё выглядит штатно, как задумано разработчиками patroni
    Ответ написан
    3 комментария
  • Почему не удалось перенести базу zabbix?

    Melkij
    @Melkij
    PostgreSQL DBA
    1. запустили намеренно pg_dump с отказом -O - то есть --no-owner
    2. развернули дамп от супера
    3. все объекты теперь ожидаемо принадлежат суперу, owner'а же не переносили
    4. удивляемся, что постороннему пользователю нет прав чтения

    Что же тут могло пойти не так?

    Самое простое для баз с одним пользователем - импортируйте дамп базы от имени этого самого пользователя. Если в базе есть какие-то extension - то сперва их создать от суперпользователя.
    Ответ написан
    3 комментария
  • Как pgbouncer обрабатывает idle сесии?

    Melkij
    @Melkij
    PostgreSQL DBA
    Зависит от pool_mode.

    В режиме session баунсер прозрачен для приложения: не налагает каких-то явных ограничений на использование, но один клиент к баунсеру = один коннект к базе. Соединение с базой сможет быть передано другому клиенту только после того как предыдущий клиент отключится. Поэтому этот режим используется очень редко.

    В режиме transaction пула коннект к базе выдаётся клиенту только на время транзакции. Самый распространённый режим работы, любим именно за то, что сколько бы тысяч коннектов не открыло приложение к баунсеру, на базе открыто небольшое число действительно потребовавшихся соединений.
    Но этот режим пула налагает ограничения на работу приложения. Вы не сможете нормально использовать ничего, что меняет состояние коннекта, потому что следующий ваш запрос с высокой вероятностью попадёт в другой коннект. То есть курсоры, временные таблицы, set (кроме set local), prepared statements (где parse, bind и execute могут разойтись по 3 разным коннектам)

    С prepared statements в transaction pool mode с недавних пор может помочь настройка max_prepared_statements, но только если prepare и deallocate выполняются именно командами протокола, но не SQL запросами. Тут многие широкоиспользуемые библиотеки оказались в пролёте.
    Ответ написан
    1 комментарий
  • Можно ли всем строковым полям задавать тип TEXT и повлияет ли это сильно на производительность?

    Melkij
    @Melkij
    PostgreSQL DBA
    text и varchar - это одно и то же на уровне реализации postgresql.

    varchar с каким-то разумным ограничением (не бессмысленный взятый с потолка 255 везде, а разумный для этого конкретного поля) тем не менее смысл имеет: куда проще найти ошибку в месте записи данных, чем потом искать, откуда в поле обычно содержащем до 30 символов взялось 10 мегабайт текста (история из практики, да)

    Про char ограничусь цитатой письма Tom Lane
    Type character(N) is a hangover from the days of punched cards. Don't use it.

    Просто забудьте про такой тип данных. Он не только бесполезен, но и вреден.
    Ответ написан
    5 комментариев
  • Как настроить слоты репликации в patroni для сохранения неограниченного кол-ва WAL при отключении реплики?

    Melkij
    @Melkij
    PostgreSQL DBA
    Если wal_status становится lost - значит max_slot_wal_keep_size был изменён с дефолтного значения -1 и установлен в какое-то другое значение.
    Потому что именно для этого добавляли настройку max_slot_wal_keep_size, чтобы отставший слот репликации не ронял базу.
    Ответ написан
  • По какому принципу работает набор индексов в одной таблице?

    Melkij
    @Melkij
    PostgreSQL DBA
    Нет, ни одно из утверждений не является верным.

    btree индекс - это структура, располагающаяся отдельно, сбоку, от таблицы. Порядок данных в таблице никак не меняется от создания индекса.
    Индекс btree(a,b,c) и три индекса btree(a), btree(b), btree(c) - разные вещи.
    Ответ написан
    6 комментариев
  • Какой максимальный размер temp_buffers для PostgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Максимум INT_MAX / 2 числа блоков, что при обычном размере блока в 8кб будет равняться лимиту до 8TiB.
    melkij=> set temp_buffers to '8192GB';
    ERROR:  1073741824 8kB is outside the valid range for parameter "temp_buffers" (100 .. 1073741823)
    melkij=> set temp_buffers to '8191GB';
    SET


    Действует независимо для каждого процесса базы. При max_connection = 100 каждый из этих 100 процессов имеет возможность выделить по столько памяти для хранения блоков временных таблиц этой сессии.
    Ответ написан
    Комментировать
  • Как решить ошибку пула соединений к PostgreSql?

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

    remaining connection slots are reserved for non-replication superuser connections

    означает, что у базы заняты все возможные max_connections - superuser_reserved_connections, поэтому не может принимать больше коннектов, пока не отключится кто-то из подключившихся ранее (либо не будет обнаружен обрыв tcp сессии и поэтому коннект будет освобождён).
    Ответ написан
  • Как избежать ошибки запрошенный segment wal уже удалён?

    Melkij
    @Melkij
    PostgreSQL DBA
    wal_keep_size размером больше ожидаемого лага репликации по объёму wal (это место будет занято постоянно, зато из-за этого не угрожает уронить мастер по переполнению диска)

    либо использовать слоты репликации. Очень желательно с разумным max_slot_wal_keep_size, чтобы забытый слот репликации умер, а не уронил мастер по исчерпанию места на диске.

    либо архив wal, но если не нужен для других задач, то чаще используется wal_keep_size или слоты репликации
    Ответ написан
  • Postgres потоковая репликация Master-Slame как очиcтить архив wal?

    Melkij
    @Melkij
    PostgreSQL DBA
    Нафиг вам вообще archive_command, если нужна только потоковая репликация?

    Архив wal - это когда нужен point in time recovery либо особые случаи file shipping репликации (например, нужна реплика где-то без возможности подключиться к мастеру по сети)
    Ответ написан
    5 комментариев
  • Postgres Логическая репликация, почему не идёт репликация?

    Melkij
    @Melkij
    PostgreSQL DBA
    The schema definitions are not replicated, and the published tables must exist on the subscriber.

    https://www.postgresql.org/docs/current/logical-re...
    По-моему, написано однозначно и не допускает вариантов прочтения. Нет?

    | psql

    иначе говоря, где вы создали таблицы и где потом пытаетесь create subscription делать. Это явно два разных места.
    Ответ написан
    Комментировать
  • Почему в crosstab нужно указывать VALUES в запросе на выборку?

    Melkij
    @Melkij
    PostgreSQL DBA
    https://www.postgresql.org/docs/current/tablefunc....
    The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn't work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

    crosstab(text, text) вариант не требует именно values. Второй аргумент функции может быть любым запросом, который сгенерирует полный список категорий. Этот вариант crosstab нужен, если первый запрос может возвращать данные не для всех категорий.

    С точки зрения синтаксиса, insert .. values - это то же самое, что insert .. select, а values - это частный случай select только списка констант
    melkij=> VALUES ('кофты'), ('ботинки'), ('пальто');
    column1
    ---------
    кофты
    ботинки
    пальто
    (3 строки)

    это самодостаточный запрос. Можно записать как select 'кофты' union all select 'ботинки' union all select 'пальто', но зачем?
    Ответ написан
    1 комментарий
  • Почему пропадает вывод таблицы в терминале psql?

    Melkij
    @Melkij
    PostgreSQL DBA
    По-умолчанию, psql делегирует вывод размером больше вашей консоли в утилиту пагинации, сконфигурированную в вашей системе. Например, more или less. Дальнейшее поведение соответственно на усмотрение этой программы.
    \pset pager off выключает pager
    Ответ написан
    1 комментарий
  • Какой будет более правильный вариант при секционировании таблиц?

    Melkij
    @Melkij
    PostgreSQL DBA
    Можно объявить PK индивидуально на каждой партиции. Глобально-уникального индекса всё равно нет.
    Составной PK включающий ключ партицирования логичным образом изменит своё поведение.

    PS: в чём вы предполагаете смысл от BY HASH ("created_at")?
    PPS: под пользователями понимаются не люди? Просто людей на Земле маловато чтобы имело смысл делать партицирование по пользователям.
    Ответ написан
  • Как исправить несоответствие версии сортировки?

    Melkij
    @Melkij
    PostgreSQL DBA
    Специальный варнинг, очень явно подсказывающий, что держать физическую репликацию на разных ОС - идея плохая. Причина в время от времени происходящих изменении правил сортировки строк: https://wiki.postgresql.org/wiki/Locale_data_changes
    СУБД это задевает в части индексов, если на мастере было '1-1' < '11', то запись запишется в одно место btree, а если на реплике оператор сравнения говорит что '1-1' > '11', то выполнение запроса смотрит в логичное для этого результата место индекса и вообще не находит данные. Потому что мастер их записал располагая другим ответом функции сравнения строк.
    Так и проявляется IRL, поиск по индексу говорит что данных нет, но если форсировать seqscan - то данные находятся.

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

    Melkij
    @Melkij
    PostgreSQL DBA
    serial - это и есть integer, а типа данных serial вообще не существует. Это просто синтаксический сахар вокруг неявного создания sequence из времён, когда GENERATED ALWAYS AS IDENTITY не было ни в стандарте ни в postgresql.

    Обратно к вопросу: тип данных может не совпадать. Но обычно это ошибка схемы данных, нежели запланированное различие.
    Ответ написан
    Комментировать
  • Как понять к какой БД относиться таблица из pg_stat_io?

    Melkij
    @Melkij
    PostgreSQL DBA
    Это всегда current_database()
    У каждой БД свой собственный системный каталог со своим собственным pg_class, pg_namespace и так далее (за исключением буквально пары действительно глобальных вроде pg_database и pg_authid).
    Ответ написан
    1 комментарий
  • Как настроить prepared statement в pgbouncer?

    Melkij
    @Melkij
    PostgreSQL DBA
    PHP PDO несовместим с max_prepared_statements фичей баунсера.

    PDO отправляет deallocate как SQL запрос, а не как команду протокола.
    Ответ написан
  • Как можно получить изменения во VIEW, когда изменяется базовая таблица?

    Melkij
    @Melkij
    PostgreSQL DBA
    view не хранит никаких данных. Это просто сохранённое дерево запроса, встраиваемое в обратившийся к этой view запрос.
    Вам нужен триггер на таблице с данными, который будет писать в отдельную аудит таблицу историю изменений. Потом из этой таблицы получайте историю.
    Ответ написан
    Комментировать
  • Как партиционировать большую таблицу в PG 11?

    Melkij
    @Melkij
    PostgreSQL DBA
    если clock это unixtime, а так же именно по нему и хотим партиционировать в дальнейшем:

    begin;
    set local statement_timeout to '100ms';
    alter table history add constraint partition_key check (clock < :N) not valid;
    commit;
    alter table history validate constraint partition_key;
    begin;
    set local statement_timeout to '100ms';
    alter table history rename to history_old_data;
    create table history (...) partition by range (clock);
    alter table history attach partition history_old_data for values from (minvalue) to (:N);
    create table history_part_... partition of history for values from (:N) to (...);
    commit;


    :N - некая дата в будущем, до неё данные будут писаться в старую 5тб табличку, после - в новую партицию. Если до выбранного :N не успеете завершить миграцию - то удалить check constraint, иначе запись, конечно, встанет.
    Ответ написан
    2 комментария