Задать вопрос
Ответы пользователя по тегу PostgreSQL
  • Какую утилиту лучше использовать для реорганизации таблиц PostgreSQL (например: pg_repack vs pgcompactor vs подстройка автовакума)?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    pg_repack - это community extension, форк более раннего pg_reorg. И не отрицая вклад участников pgpro в разработку postgresql в целом - я не вижу их среди контрибьютеров pg_repack.

    Мы активно используем обе. Основные моменты касательно pg_repack vs pgcompacttable в общем-то именно я и изложил в README компактора. Если говорить на русском:
    - pg_repack делает копию таблицы, т.е. требуется двукратный запас диска. Компактор работает в рамках одной таблицы и требует место только под копию наиболее толстого индекса и то, толстые индексы обрабатываются самыми последними. Т.е. расчёт на работу когда места уже мало.
    - компактор намеренно выполняется медленно чтобы не задевать работающий проект
    - компактор не может обрабатывать toast таблицы. Репак - может.
    - pg_repack характеризуется очень заметными пиками io и wal. Вся таблица едет одной транзакцией и это никак не регулируется. Поэтому может аффектить прод на неважных дисках и в том числе оторвать реплики при недостатке сетевой полосы.
    - pg_repack умеет перемещать между tablespace и может делать команду cluster

    (или лучше подстроить автовакум)?

    Конечно необходимо настроить автовакуум и не мешать ему работать - т.е. исключить долгие транзакции. Но всё равно бывают хитрые моменты, когда репак или компактор бывают нужны.
    Ответ написан
    2 комментария
  • Как вы измеряете профиль нагрузки (соотношение кол-ва чтений/кол-ву записей) в PostgreSQL?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Посмотрите в циферки что собирает stats collector, их много разных. https://www.postgresql.org/docs/10/static/monitori...
    например, запись буферов на диски - это buffers_checkpoint, buffers_clean и buffers_backend в pg_stat_bgwriter
    pg_stat_database c blks_read, blks_hit, blk_read_time и blk_write_time

    И разумеется pg_stat_statements с детализацией ресурсов по отдельным запросам.

    Вот кстати, хорошая картинка куда смотреть за детализацией каких подсистем базы
    postgres-observability-9.6.png
    Ответ написан
  • Как получить от PostgreSQL массив данных сразу в Json формате для Symfony?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    В postgresql есть штатная агреггирующая функция json_agg. Поэтому возможно даже без привлечения хранимок.
    Ответ написан
    6 комментариев
  • Как написать функцию итератор?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Это называется не итератором.

    insert into terms (term, freq) values (?, 1) on conflict (term) do update freq = excluded.freq + 1 returning freq;

    В функцию завернуть по желанию. 9.5+
    Ответ написан
    Комментировать
  • Почему не выводит строки при корректном запросе postgre?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    А с кавычками выводит "ничего" т.е. ноль полей.

    С какими именно?

    user='40'
    'user'='40'
    "user"='40'

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

    user - зарезервированное слово по требованию стандарта. По стандарту не уверен что обозначает в контексте where, надо искать (может как часть create user и подобных только зарезервирован), а в postgresql обозначает имя текущего пользователя. Имеет тип name, что и провоцирует ошибку.
    Т.е. первое условие в принципе не имеет никакого отношения к каким-либо пользовательским таблицам.

    'user' - просто текстовая константа. Никто не мешает в условии сравнивать одну константу с другой, where 1=1 в некоторых query builder отсюда же.

    "user" - обращение к объекту, игнорируя зарезервированные слова. То есть если у вас в таблице proxy есть поле user - то запрос должен быть записан как
    select * from proxy where "user"='40'

    Но общая практика - лучше не надо использовать зарезервированные слова в качестве идентификаторов.
    Ответ написан
    1 комментарий
  • Как избавиться от блокировок при обслуживание партиций внешней таблицы в БД PostgreSQL?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    vacuum full и reindex требуют тяжёлую блокировку и держать блокировку будут долго. Поэтому обычно их избегают.
    drop разумеется тоже требует эксклюзивную блокировку, но на короткое время. Важно понимать скользкую ситуацию - если вы пытаетесь взять эксклюзивную блокировку, но не можете её получить из-за других выполняемых транзакций - вы будете ждать окончания этих мешающих транзакций, но уже будете блокировать работу последующих транзакций.

    Если вы считаете, что много места пропало впустую - то это можно посчитать (поставив contrib расширение pgstattuple) вот этими нашими запросами: для таблиц и для индексов
    Если да, проблема с местом в этом - то вам помогут pgcompacttable или pg_repack

    Если данные по большей части архивные - имеет смысл заменить btree индексы на brin. Они медленнее, но очень сильно компактнее по диску.

    А отцеплять партиции - посмотреть в pg_stat_activity что вообще происходит, нет ли аномально длинных транзакций или idle in transaction (т.е. затупов на приложении). Затем с statement_timeout и lock_timeout в 1 секунду (или меньше даже) пробовать отцепить пока не получится.
    Ответ написан
    4 комментария
  • Есть ли возможность восстановить данные базы postgree?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    У postgresql есть всего одна директория - data_directory (aka PGDATA). Для работы необходима консистентная копия всей директории целиком, включая содержимое по всем симлинкам.

    Можете поковырять pg_filedump, но лучше достаньте бекап.
    Ответ написан
    2 комментария
  • Почему не работает подключение к Postgres без разрешения IPv6?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Значит подключаетесь вы именно через ipv6.
    На локалхосте обычно unixsock используют вообще, а не tcp loopback.

    Но вообще вполне обычная практика предпочитать ipv6, если приложение не указывало протокол явно, а адреса хоста известны и для v6 и для v4. У вас ведь наверняка в /etc/hosts для localhost указаны оба адреса.
    Ответ написан
    3 комментария
  • Как избежать увеличения автоинкремента при неуспешном запросе?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Никак.
    Сиквент гарантированно уникальный и не транзакционный. Ничего о последовательности выдачи значений и уж тем более без пропусков он не говорит.
    Ответ написан
    Комментировать
  • Что использовать: pgpool II или pgbouncer + haproxy?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    pgpool - это в общем больно в поддержке. Лучше не надо.
    Далее надо учитывать что автофейловер - это свой букет проблем и особенностей. И зачастую проблем от него больше, чем без него. Тест на понимание repmgr: "как именно выбирается новый мастер?". Обычно используется штатная потоковая репликация и мастер переключается вручную. Потому что основная сложность в определении, что надо переключаться, а не это кратковременный сбой, например, сети.
    pgbouncer + haproxy вполне широко используются для балансировки по равнозначным репликам (и по нескольким pgbouncer если вы до гигабита трафика доросли). По равнозначным - то есть не надо на OLTP реплики пихать OLAP запросы аналитики - для этого нужны отдельные реплики специально для этого настроенные.

    Упомянутый Александр Кузнецов patroni - во всяком случае я не смог спровоцировать его на split brain, поэтому в целом он мне нравится. Да и в паре мест в продакшене уже видел. Умеет и синхронные и асинхронные реплики, с haproxy дружит вполне штатно для опроса где нынче мастер и так авторами и используется.
    Ответ написан
    1 комментарий
  • Что такое РОЛИ в PostgreSQL?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    объясните, пожалуйста, смысл ролей в постгрес

    Пользователь - это алиас для системы ролей.

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

    Это ложь. Необходимости нет.

    Поэтому для созданного нами ранее пользователя с именем sammy соответствующая роль будет по умолчанию пытаться соединиться с базой данных sammy

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

    можно ли так: несколько ролей и одна база данных?

    Разумеется. Хоть дерево со множественным наследованием ролей.

    Только важный момент - роли глобальны в пределах кластера. Вы не можете сделать пользователя user в базе foo с одним паролем и пользователя user в базе bar с другим паролем. А вот дать readonly на одну базу и полные права на другую - это пожалуйста.
    Ответ написан
    1 комментарий
  • Как перенести большую базу с одного сервера на другой?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Настраиваете потоковую репликацию, через pg_basebackup снимаете копию, запускаете как реплику. Когда догонит ведующую базу выключаете мастер и повышаете реплику до нового мастера.
    Даунтайм минута вне зависимости от объёма данных. Ну может ещё минута если понадобится рестарт мастера из-за смены wal_level, listen_addresses или max_wal_senders

    https://wiki.postgresql.org/wiki/Streaming_Replication
    Ответ написан
    Комментировать
  • Почему на ставиться ltree?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Вы что-то делаете странное. У вас какая версия postgresql?
    Если 9.1 и выше - то единственный верный способ ставить extension - это сказать create extension в нужной базе. Обычно нужны привилегии суперпользователя базы (обычно postgres на linux или pgsql на freebsd)

    Ругать на локаль - ошибка настройки самой ОС, к базе отношения не имеет.
    Ответ написан
  • Из-за чего ошибка POSTGRESQL процедуры?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Причина ошибки хранимки полностью и достоверно указана в подробностях текста ошибки:
    It could refer to either a PL/pgSQL variable or a table column.

    У вас в табличке есть поле id, у хранимки аргумент id - который из них подразумевается парсер не понимает.
    Ответ написан
    Комментировать
  • Как правильно отправить sql ошибку клиенту?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    А зачем вообще отправлять ошибку запроса на клиент?
    Ошибке запроса самое место в логе и только там. Клиенту - HTTP 500 и "извините, не шмогла" написанное любым вариантом какой вам понравится.
    Если требуется другое сообщение (или действие) для конкретной ошибки - ловите этот конкретный код ошибки. На допустимость логина обычно проверяют предварительным запросом, в апдейт on conflict пока не завезли. Плюс можно хапнуть advisory lock для устранения race condition и закатать всё в одну хранимку чтобы сэкономить на планировщике и сети.
    Ответ написан
    2 комментария
  • Добавление и обновление записей через Insert Into?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    С insert или update по результату select (к слову, совершенно ненужному запросу) вы вляпываетесь в race condition
    on conflict специально сделан для нормальной сериализации происходящего и использоваться и должен. Другие способы сериализации race condition из времён до on conflict производительность просаживают по вполне очевидным причинам.
    Ответ написан
    2 комментария
  • В чем ошибка при установки ноты?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    could not find driver

    Скорей всего ошибка от PDO о том, что у вас не установлен модуль PHP с поддержкой запрошенной СУБД. Соответственно проверьте конфигурацию приложения, правильно ли указан DSN и конфигурацию PHP - установлен ли модуль для нужной СУБД.
    Ответ написан
    3 комментария
  • Postgresql запрос, сравнение полученных данных и перевод в секунды, как сделать?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    select extract(epoch from now() - MAX(DATE_OP)) seconds_ago from tablename;
    Ответ написан
    Комментировать
  • Насколько правильно использовать json для хранения данных в базе данных?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    json нужно использовать если у вам надо хранить json и сохранять полностью его форматирование. Форматирование именно самого json с пробелами, табами и переносами строк - т.е. как текст, но с валидацией что здесь именно json.
    если вам нужно хранить json - лучше использовать jsonb
    А для описанной задачи и просто массива достаточно, bigint[] или text[]. Контактные данные для заказа уместны именно в заказе, а по jsonb и массивам возможно вполне внятно и искать в том числе
    Ответ написан
    Комментировать
  • Текст в первичном ключе и его переиндексация?

    Melkij
    @Melkij
    DBA для вашего PostgreSQL?
    Для uuid в принципе не нужен текст, есть нативный одноимённый тип данных.

    При этому line иногда плавает.

    Проверьте память. Физическую на сервере. Подозрение что косячит.
    И диски тоже проверьте.
    Потому что есть всего одно место, где можно поймать "compressed data is corrupted" и связано оно с распаковкой сжатых данных из toast. reindex тоже говорит, что прочитано что-то совсем не то, что предполагалось.
    Ответ написан