Задать вопрос
Ответы пользователя по тегу PostgreSQL
  • Почему не создается бд в postgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    должно быть
    postgres=# create database mydb;


    нет такого сокращение у парсера как create db, но, что важнее, SQL запрос должен завершаться символом ;

    Приглашение командной строки потому и меняется с postgres=# на postgres-# как индикатор того, что psql ожидает дальнейший ввод многострочного SQL, пока не встретится ;
    Метакоманды psql, в то же время, действительно продолжают обрабатываться во время многострочного ввода.
    Ответ написан
    Комментировать
  • Можно ли создать пользователя во время миграции?

    Melkij
    @Melkij
    PostgreSQL DBA
    Можно, это даже транзакционная команда. Разумеется, до окончания транзакции авторизоваться с именем этого пользователя не получится.

    PS: больше одного супера - большой вопрос к организации работы базы
    Ответ написан
    Комментировать
  • CREATE USER не учитывает регистр?

    Melkij
    @Melkij
    PostgreSQL DBA
    https://www.postgresql.org/docs/current/sql-syntax...
    соответствовать синтаксису идентификаторов.
    Ответ написан
    Комментировать
  • Как выбрать записи из таблицы с макимальным значением за каждый день в PostgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    select distinct on (created_at::date) created_at, value from tablename order by created_at::date, value desc;
    Ответ написан
    Комментировать
  • Почему real может хранить числа от 1E-37 до 1E+37, но выводимое значение не больше 9 цифр?

    Melkij
    @Melkij
    PostgreSQL DBA
    См. IEEE 754 потому что это именно он. И на всё наблюдаемое "странное" поведение типов с плавающей запятой ответ един - потому что IEEE 754.
    Ответ написан
    Комментировать
  • Почему postgresql выбирает неоптимальный план выполнения для простого JOIN?

    Melkij
    @Melkij
    PostgreSQL DBA
    Я выполняю 2 одинаковых запроса

    "WHERE friends_info.user_id = 1" vs "WHERE friends_info.friend_id= 1"

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

    Bitmap Index Scan on ix_friends_user_id (cost=0.00..61.56 rows=5465 width=0) (actual time=0.742..0.743 rows=88 loops=1)

    Суть ошибки выбора плана. Какое распределение данных в таблице? Каков размер самой таблицы? Вероятно несколько пользователей занимают значительную часть таблицы и это сбивает оценку селективности.
    Простое чуть приподнять SET STATISTICS по полю, собрать новый analyze и посмотреть на оценку числа строк.

    PS: индекс ix_friends_user_id должен быть удалён как бесполезный при наличии friends_info_user_id_friend_id_key
    Ответ написан
    3 комментария
  • Как задать пароль пользователю postgres, если авторизация по ssh-ключу?

    Melkij
    @Melkij
    PostgreSQL DBA
    А как между собой связаны postgres, пароль пользователя unix и способ авторизации ssh? Да никак.

    ssh аутентификация по ключу не исключает наличия пароля пользователя, sudo может быть настроен без пароля, авторизация postgresql может быть настроена без sudo раз вы как-то собрали и запустили СУБД без использования запароленного sudo этого пользователя.

    Проблема-то где?
    Ответ написан
  • Как pgcompacttable работает с HOT-updates?

    Melkij
    @Melkij
    PostgreSQL DBA
    maintainer here.

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

    Melkij
    @Melkij
    PostgreSQL DBA
    Буквально так и пишется дай messages для который нет вот таких
    select ... from messages where not exists (select from comments where comments.message_id = messages.id and comments.author_id = 100);
    Ответ написан
    Комментировать
  • Что не так в LIKE?

    Melkij
    @Melkij
    PostgreSQL DBA
    tldr: WHERE a LIKE concat('%',$1,'%') либо конкатенировать к параметру на стороне приложения.

    '%$1%' - это строковой литерал, константа из 4 символов. Внутри литерала никто параметры искать и подставлять не будет. Поэтому в запросе не найдено ни одного места для параметров, а вы передаёте один параметр - что явная ошибка логики программы, об этом и выводится ошибка.
    Ответ написан
    1 комментарий
  • Можно ли гарантировать надежность снапшота?

    Melkij
    @Melkij
    PostgreSQL DBA
    Когда появляются требования ко времени восстановления из бекапа - то почти наверняка уже доросли до полноценного PiTR и лучше рассматривать именно полновесный бекап postgresql, чем сооружать снэпшоты.
    Берёте pgbackrest, настраиваете архив WAL, желаемую периодичность снятия basebackup и сроки удержания. Ну либо barman или wal-g, тут по вкусу, смысл тот же.

    Получаете полностью корректный с точки зрения самой базы снэпшот (ака basebackup), а так же возможность восстановиться на произвольную точку времени, а не только на момент снятия слепка. Без штрафа производительности записи от снэпшота LVM, без CoW файловых систем.

    Реплика - не бекап, решают разные задачи. Реплика ответит на вопрос "как быстро мы можем восстановить базу при полном отказе основного сервера", но не ответит на вопрос "как быстро можем восстановить удалённую табличку". Если у вас важная база и всё ещё нет реплики - ну, это странно =)
    Ответ написан
    Комментировать
  • Как правильно организовать выборку зашифрованных данных PostgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Никак.

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

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

    Единственное возможное исключение: если ваша функция шифрования обязуется быть immutable в терминах postgresql, т.е. на один и тот же ключ и исходные данные генерировать всегда один и тот же бинарно идентичный результат. Тогда делаете индекс по parent_iin и ищете по условию where parent_iin = функция_шифрования(данные, ключ) то есть по зашифрованному представлению данных.
    Ответ написан
    Комментировать
  • Как установить plrust под windows для создания функций в pgAdmin?

    Melkij
    @Melkij
    PostgreSQL DBA
    Ни документация ни гитхаб не упоминает поддержку экзотических платформ вроде windows. Только обычный linux.

    Попробуйте творчески адаптировать процедуру установки plrust из исходников под вашу платформу (возможно что не получится). Ну или просто поставить обычный linux, например в виртуалке.
    Ответ написан
    Комментировать
  • Почему большой iowait в cloud vm c postgres?

    Melkij
    @Melkij
    PostgreSQL DBA
    У яндекса весьма медлительные сетевые диски, совершенно верно. Именно по latency, что куда более критично для СУБД чем обещанная полоса пропускания или IOPS (которые у большинства хранилок предполагают совершенно абсурдную для СУБД глубину очереди команд).
    Ответ написан
    2 комментария
  • Кластер 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 комментариев