Ответы пользователя по тегу PostgreSQL
  • Есть ли разница в БД Postgres: хранить string(255) или string?

    Melkij
    @Melkij
    PostgreSQL DBA
    Для начала, в postgresql нет типа данных string. (а если это самодельный тип, что postgresql сделать тоже позволяет, то смотреть надо как он реализован у вас)

    Для текстовых данных в postgresql есть:
    - text. Просто text, без специфических ограничений на содержимое
    - varchar - это всё тот же самый text. К varchar можно добавить указание ограничения максимальной длины хранимого текста в символах. Почему это отдельный тип данных? Потому что такого требование стандарта SQL
    - char - фиксированный длины (в символах! в байтах они всё равно динамической длины получатся для того же utf8). Смысла его использовать в postgresql, как вы сами процитировали документацию, нет. Зачем он нужен? Потому что описан в стандарте.

    В отличии от, например, mysql, для postgresql нет ничего особого в значении 255. Если в определении таблицы видно такое ограничение на 255 символов - обычно это означает именно "никто не обдумывал цель такого ограничения для этого поля, просто какое-то число"

    Говоря о выборе между text или varchar(N) - varchar(N) с разумным для этих данных N предпочтительнее для коротких строк, потому что не даст записать 10мб туда где должно быть, например, не более 64 символов (это не совсем шутка, случай из практики). А по ошибке в месте записи дебажить логику приложения куда как проще, чем искать потом откуда такое взялось.
    Ответ написан
    2 комментария
  • Возможно подключить через PG Admin к удаленной БД на продакшене?

    Melkij
    @Melkij
    PostgreSQL DBA
    а зачем нужен pgadmin для управления базой?

    Ну а помимо этого, pgadmin не только умеет подключаться удалённо куда ему скажут, но и самостоятельно запускать для этого ssh туннель.
    Ответ написан
    Комментировать
  • Как оптимизировать update postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    UPDATE orders_new
    SET client_code=access.title
    FROM orders_new O

    Ну классика жанра. Если не видите self-join - то посмотрите ещё раз. Очевидно ошибочный запрос.

    https://www.postgresql.org/docs/current/sql-update.html
    Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).
    Ответ написан
  • Какую базу и способ хранения выбрать?

    Melkij
    @Melkij
    PostgreSQL DBA
    База на 30гб? Какая смешная кроха.

    Да сгенерируйте себе синтетических данных и поиграйтесь с ними. Я понимаю неудобно на локальной машине щупать базу в десяток террабайт - но лишь только 30гб не проблема просто сгенерировать и посмотреть вживую.
    Ответ написан
    1 комментарий
  • Как избавиться от ошибки с prepared statement при работе с PostgreSQL через PgBouncer?

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

    Замените слово "PgBouncer" на "ваше приложение" и тогда получите относительно верное описание происходящего.

    С практически 100% вероятностью у вас pgbouncer сконфигурирован в режиме transaction pool mode. А выполнение любого нового запроса в extended protocol (prepared statement) состоит из трёх вызовов:
    - prepare
    - bind
    - execute
    Нет ничего ошибочного в том, что pgbouncer сконфигурированный в режиме пула транзакций может направить каждое из этих 3 сообщений в разные коннекты базы. Это прямым текстом задокументированное ограничение пула транзакций. Итог закономерный, соответствующие процессы базы не понимают о чём речь и выражают удивление такими ошибками.

    Отключите использование server-side prepared statements на приложении.
    Ответ написан
    2 комментария
  • Почему не компилируется эта функция?

    Melkij
    @Melkij
    PostgreSQL DBA
    Запрос корректен. Посмотрите в логах базы, какой запрос реально отправляет ваш клиент.
    Ответ написан
    Комментировать
  • Как сделать правильный комплексный индекс?

    Melkij
    @Melkij
    PostgreSQL DBA
    Надо найти самую старую по обновленности запись, где n_flag=0 и num_status>=10.

    btree(update_date) where n_flag=0 and num_status>=10
    Ответ написан
    1 комментарий
  • Как убрать readonly в Postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    Postgresql вам редактировать и не препятствует. Раз вы смогли сделать create table - значит база не в read only режиме. Напишите соответствующий update и выполните.

    А вот вашему GUI клиенту скорей всего не нравится отсутствие primary key.
    Ответ написан
    Комментировать
  • Почему Postgres не завершает IDLE-транзакции?

    Melkij
    @Melkij
    PostgreSQL DBA
    idle != idle in transaction. Это принципиально разные статусы.

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

    В частности, где вызов release()?
    Ответ написан
  • Оператор BETWEEN для диапазона значений?

    Melkij
    @Melkij
    PostgreSQL DBA
    close_price between open_price * 0.9 and open_price * 1.1

    вы имеете в виду простую математическую операцию?
    Ответ написан
    Комментировать
  • Как задать название таблице переменной в postgresql python?

    Melkij
    @Melkij
    PostgreSQL DBA
    Привести схему данных в нормальную форму. (это термин)
    Затем удивительным образом пропадёт вся проблема как таковая.
    Ответ написан
    Комментировать
  • В чём разница между Postgre SQL и Postgre Pro?

    Melkij
    @Melkij
    PostgreSQL DBA
    Это две разные СУБД.

    postgres pro - местный закрытый форк свободной postgresql.
    Ответ написан
    Комментировать
  • Как создать нового юзера и новую базу в postgres, на убунту?

    Melkij
    @Melkij
    PostgreSQL DBA
    Парольная аутентификация лишь один из множества доступных вариантов. Хотя наиболее простой и распространённый. Какой способ аутентификации будет требовать СУБД - зависит от списка правил в pg_hba.conf. Очень редко нужно что-то кроме служебного правила peer для postgres (именно из-за него psql после sudo -iu postgres не спрашивает никакие пароли, но только у postgres'а) и md5 для всего остального.

    Базово создание новой базы с отдельным пользователем-владельцем выглядит так (от суперпользователя базы):
    CREATE USER "$NEWOWNER" PASSWORD '$NEWPASS';
    CREATE DATABASE ${NEWDB} OWNER "$NEWOWNER";
    REVOKE ALL ON DATABASE $NEWDB FROM public;


    В соседнем ответе вам неверно подсказывают, что "можно сразу указать какому юзеру разрешен доступ". create database разрешит доступ всем (но именно подключение к базе, а не ко всему внутри базы - что частая проблема непонимания прав). И имеет смысл наоборот отобрать доступ от всех прочих, т.е. от public. Owner, конечно, доступ сохранит.

    Чуть ближе к production имеет смысл использовать такой шаблон:
    CREATE USER "$NEWOWNER" PASSWORD '$NEWPASS';
    CREATE DATABASE ${NEWDB} OWNER "$NEWOWNER";
    CREATE ROLE ${NEWDB}_role;
    CREATE ROLE ${NEWDB}_ro;
    CREATE ROLE ${NEWDB}_rw;
    GRANT ${NEWDB}_role TO ${NEWDB}_ro, ${NEWDB}_rw;
    REVOKE ALL ON DATABASE $NEWDB FROM public;
    GRANT CONNECT ON DATABASE $NEWDB TO ${NEWDB}_role;
    GRANT ${NEWDB}_rw TO "$NEWOWNER";
    \c $NEWDB
    ALTER SCHEMA public OWNER TO "$NEWOWNER";
    REVOKE ALL ON SCHEMA public FROM public;
    GRANT USAGE ON SCHEMA public TO ${NEWDB}_ro, ${NEWDB}_rw;
    ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON SEQUENCES TO ${NEWDB}_ro;
    ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT,USAGE ON SEQUENCES TO ${NEWDB}_rw;
    ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO ${NEWDB}_ro;
    ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO ${NEWDB}_rw;
    ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT ON SEQUENCES TO ${NEWDB}_ro;
    ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT,USAGE ON SEQUENCES TO ${NEWDB}_rw;
    ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT ON TABLES TO ${NEWDB}_ro;
    ALTER DEFAULT PRIVILEGES FOR ROLE "$NEWOWNER" GRANT SELECT,INSERT,DELETE,UPDATE ON TABLES TO ${NEWDB}_rw;

    В результате получим:
    - пользователя-владельца базы, который предназначен выполнять всякие миграции схемы данных
    - роль имя_базы_role которую можно давать другим пользователям для возможности подключения к этой базе, но без доступа к таблицам приложения (например, используем для мониторинга)
    - роль имя_базы_ro которая даст select-only права ко всем (в том числе будущим) таблицам, созданным от пользователя-владельца это базы
    - роль имя_базы_rw - соответственно для выполнения select,insert,update,delete
    Ответ написан
    Комментировать
  • Почему SELECT CASE WHEN возвращает только первое значение?

    Melkij
    @Melkij
    PostgreSQL DBA
    А вот если заглянуть в документацию...
    Day full capitalized day name (blank-padded to 9 chars)

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

    Что попросили у to_char - то вполне ожидаемо и получили. При том, в чём вообще смысл получать название дня недели, а не его номер? А вдруг Day окажется locale-specific штукой?
    Ответ написан
    1 комментарий
  • Как настроить PITR PostgreSQL?

    Melkij
    @Melkij
    PostgreSQL DBA
    Для pitr вам нужен:
    - basebackup как основа, должно быть завершено его снятие до датывремени требуемого восстановления pitr
    - непрерывный архив всех и абсолютно каждого сегмента wal без пропусков от момента checkpoint перед снятием используемого в восстановлении basebackup и до требуемой точки восстановления

    database system was shut down at 2022-02-10 13:31:25 UTC
    starting point-in-time recovery to 2022-02-10 07:00:00+00

    Это, конечно, невозможно даже с корректным архивом WAL.
    У postgresql REDO recovery, а не UNDO. Pitr только вперёд от текущей позиции. И не раньше достижения точки консистентности состояния (окончания снятия basebackup)

    Чтобы не ходить по граблям консистентности ещё и файловой системы, basebackup предпочтительнее снимать с самого postgresql, а не снимком блочного устройства. Но в целом вариант предусмотренный, ничем неотличимый от обычного crash recovery вроде старта после пропадания электричества (лишь бы fsync работал на всех уровнях корректно и не игнорировался).
    Ответ написан
    6 комментариев
  • Как будет выглядеть схема для таблицы с отчетами за месяц?

    Melkij
    @Melkij
    PostgreSQL DBA
    create table aggregate_montly (
        user_id bigint not null references users(id),
        month date not null check (date_trunc('month', date_period) = date_period),
        orders_count bigint not null default 0,
        -- прочие предаггрегированные данные
        primary key (user_id, month)
    );


    Дату писать всегда как первое число месяца, check constraint гарантирует, что вы не запишете по ошибке данные за 8 февраля вместо месяца.
    id выкинут за бессмысленностью. Тем более uuid.
    Ответ написан
    Комментировать
  • Как уменьшить Wal в postgresql?

    Melkij
    @Melkij
    PostgreSQL DBA
    Не уж то postgres нужно так много журналов хранить?

    А то что перенесено куда-либо через archive_command самому postgresql вообще не нужно и база об этих файлах никак не беспокоится. Это нужно тому, кто это настраивает. И в ведении как раз этого человека и будет вопрос "сколько валов нужно хранить".
    Если тот кто настраивал archive_command тоже не беспокоится об архиве - то архив будет бесконечен (16 EiB, это не шутка).

    Архив wal нужен для pitr (или file-shipment replication). Для pitr нужны абсолютно все wal без исключения с момента снятия basebackup до требуемой точки восстановления (при том точка восстановления не может быть ранее времени окончания взятого за основу basebackup). В каких временных рамках вам нужен pitr - выбираете соответственно при определении собственной же политики требований к бекапам.
    (10гб места под бекапы звучит откровенно поразительно, если у вас настолько маленькая база - то зачем вам 80гб основной диск? И 8 было бы много)
    Ответ написан
    6 комментариев
  • Как реализовать балансировку нагрузки на кластер PostgreSQL, чтобы запросы на чтение поступали только на слейв?

    Melkij
    @Melkij
    PostgreSQL DBA
    А как? Ну вот прямым текстом: как это в принципе и в теории возможно сделать без участия приложения?

    begin isolation level repeatable read ;
    select ... from users;
    -- а дальше-то что?

    Окей, begin действительно можно отсрочить до прихода следующего запроса, но вот абсолютно никак не позже запроса select вы обязаны решить, отправлять эту транзакцию на реплику либо на мастер. Как вы это определите? С равной вероятностью следующей командой может быть commit, другой select или пишущий запрос. Вы это никак не можете узнать, но вам уже необходимо эту транзакцию отправить куда-то и получить результат. И ровно так же вы не можете отправить запрос сначала на реплику, а запись потом на мастере потому что у вас развалится снэпшот транзакции.

    select * from calculate_user_rating(423);
    А это читающий или пишущий запрос? Да этого сама база не знает! Хрен его разберёт, что этой pl/python хранимке на непонятном для базы языке вздумается делать.

    Репликами должно уметь пользоваться само приложение. Кроме самого приложения больше никто не знает, допустимо ли запрос отправлять на реплики и если да, то на какие именно
    Ответ написан
    7 комментариев
  • Как в PosgreSQL, в JSON добавить key:value?

    Melkij
    @Melkij
    PostgreSQL DBA
    array_to_json(array_agg(row_to_json (r)))
    не первый раз вижу такой конструкт, но не понимать зачем оно такое надо в таком переусложненном виде. Если нашли array_agg - значит могли заметить и json_agg там же, что приводит к простейшему select json_agg(r) from r

    Для json оператора конкатенации нет, возьмите jsonb:
    select jsonb_agg(r) || jsonb_build_object('city', 'SanAndreas') from tablename r;
    Ответ написан
    Комментировать
  • Что значит 100% колонка idle в iostat –dx?

    Melkij
    @Melkij
    PostgreSQL DBA
    iostat, как бы это ни было неожиданно по своему названию, показывает статистику устройств I/O, а так же CPU.

    Заинтересовавший вас %idle относится avg-cpu секции и как поясняет прямым текстом man iostat
    %idle
    Show the percentage of time that the CPU or CPUs were
    idle and the system did not have an outstanding disk I/O
    request.

    Это процент времени, когда CPU не был чем-либо занят.
    То есть 99% idle - система фактически ничем не занята.

    Обычно смотрится вживую в iostat -x 1
    Ответ написан
    Комментировать