Требования к читателям

1. Знание компьютерного аппаратного обеспечения (в особенности CPU, RAM и дисковой подсистемы).

2. Знание компьютерных сетей на базовом уровне.

3. Умение работать с ОС семейства Linux со всеми вытекающими:

• Сам дистрибутив и основные встроенные утилиты.

• Знание файловых систем и умение работать с дисковой подсистемой.

• Командная оболочка bash.

• Менеджер пакетов.

• Управление фаерволом (ufw, firewalld, nftables или др.).

• Настройка сетевых адаптеров (netplan, networkmanager или др.).

• Локальный мониторинг основных подсистем (htop, netstat и т.д.).

• Синхронизация времени (chrony, ntpd).

• SSH. Самый популярный – OpenSSH. Сделайте упор на безопасность.

Предупреждение: все, что описано в данной статье, вы сможете без проблем найти в других статьях, документации, видеоконференциях и книгах. Так что не удивляйтесь большому количеству ссылок на документацию и другие статьи. Эта статья создана в первую очередь для систематизации знаний, и предназначена для тех, кто не знает, с чего начать изучение PostgreSQL. Только не более 5% информации – это мои рассуждения и личный опыт.

В целом, подход к администрированию PostgreSQL серьезно отличается от MySQL или MS SQL Server. Если у вас уже есть опыт администрирования других СУБД, то он, конечно, может быть полезен (особенно язык SQL), однако может и ввести в заблуждение.

Чего не будет в этой статье

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

• Подробного разбора нюансов работы этой СУБД и встроенных утилит.

• Подробного разбора объектов схемы БД.

• Руководства по работе со сторонними системами резервного копирования.

• Руководства по работе с репликацией и кластеризацией.

• GUI и web интерфейсов.

• PostgresP*O.

• Windows.

• Других СУБД (Таких как MySQL, MS SQL Server, Oracle DB и т.д.).

Информации о приведенных выше вещах будет крайне мало или не будет вообще, потому что каждая из них – это либо большая тема для отдельной статьи (к примеру, репликация или написание запросов), либо большинство может без нее обойтись. Эта статья предназначена для ознакомления с базовыми аспектами администрирования СУБД PostgreSQL.

Операционная система хоста

Серверная и клиентская части PostgreSQL работают практически на всех известных семействах операционных систем. Но я бы рекомендовал развертывать (разворачивают колбасу из газеты) сервер именно на ОС семейств Unix/Linux, а не на Windows. И дело не в совместимости, дело в проблемах, связанных с файловой системой, антивирусным ПО и, что самое важное, в доступных руководствах по администрированию и траблшутингу. Клиентская же часть прекрасно себя чувствует на любых ОС.

Далее все примеры будут показаны именно для дистрибутивов Debian/Ubuntu.

https://www.postgresql.org/docs/current/supported-platforms.html

Аппаратное обеспечение хоста

Вы должны понять несколько вещей:

1. По возможности всегда используйте актуальное аппаратное обеспечение. Далее мы посмотрим, как увеличить производительность путем настройки некоторых параметров. Однако если у вас HW (hardware) десятилетней давности, то никакие оптимизации и последние версии вам не помогут. Чем больше производительность CPU, дисковой подсистемы, объем памяти и ее пропускная способность – тем выше производительность системы в целом (но она растет нелинейно, об этом позже). К примеру, сейчас практически все базы данных хранят на массивах из дисков типа SSD, а не HDD. Также учтите, что невозможно настроить СУБД таким образом, чтобы производительность выросла из ниоткуда (точнее, можно, но прирост будет минимальным, и тут просто проблема с параметрами по умолчанию, которые не позволяют реализовать весь потенциал системы). Вам всегда придется чем-то жертвовать. Чтобы где-то стало много, где-то должно стать мало. К примеру, чтобы уменьшить объем баз, необходимо выполнить очистку ненужных данных, а для этого необходимы ресурсы CPU и RAM, которые не всегда могут быть доступны. Или другая дилемма: стоит ли незначительно жертвовать производительностью дисковой подсистемы, чтобы восстановиться быстрее в случае сбоя.

2. Думайте об отказоустойчивости и делайте бэкапы. Конечно, у всех инфраструктура разная. И у кого-то сервер PostgreSQL может быть виртуальной машиной на HA (High Availability) кластере гипервизора, а у кого-то он может быть развернут непосредственно на физической машине. Но чем больше копий во всевозможных местах – тем лучше. Да, это дорого. Но потеря всех данных и простой сервисов обойдутся куда дороже. Все ключевые системы должны дублироваться, а планы восстановления должны быть отработаны.

3. Еще немного про потребление ресурсов. СУБД PostgreSQL думает, что все доступные ресурсы сервера предназначены именно для нее. Так что даже думать не смейте развертывать на том же хосте другие сервисы, к примеру, другие СУБД или само приложение (привет любителям развертывать на одном хосте СУБД и само приложение, то ли из-за тупости, то ли ради экономии на лицензировании хостовой ОС). При неправильной настройке и эксплуатации все ресурсы RAM и дисковое пространство будут нещадно поглощены. С другой стороны, PostgreSQL разрабатывался далеко не глупыми людьми, так что все потребляемые ресурсы для чего-то используются и приносят пользу. Другой вопрос, нужна ли нам эта польза и насколько она ощутима. Все зависит от того, как вы систему настроите и какие у вас нагрузки. Также сервисы ssh сервера, клиента синхронизации времени и т.п. не в счет, потому что они необходимы, так что очень желательно их процессам выставить больший приоритет (все равно они потребляют не так много ресурсов, как процессы самой СУБД).

4. Оставляйте очень большой запас свободного дискового пространства для хранилища баз данных и резервных копий, потому что менее чем 5% доступного места могут превратиться в катастрофу (ограничить рост вручную, задать какой-то потолок мы никак не можем). Основная причина: никто не пишет ПО подобного уровня с расчетом на то, что ресурсы могут внезапно закончится. И под словом «внезапно» я имею ввиду увеличение занятого базами пространства в хранилище, к примеру, с 50% до 95% менее чем за месяц. Да, такое возможно, и за объемом и скоростью роста баз необходимо тщательно следить. Кто виноват – СУБД или приложение – вопрос крайне спорный, так что мы не будем искать виноватых (это вы) и просто примем эту проблему как данность. Так что всегда планируйте ресурсы с запасом. Забегая вперед, скажу сразу: в хранилище БД должно быть как минимум в 3 (а желательно в 4-5 раз) больше свободного пространства, чем занимают базы.

Отдельно про любителей ECC. Прочитайте еще раз про механизм работы памяти с коррекцией ошибок, а также ознакомьтесь с архитектурой компьютерной техники. Если у вас диск и память умирают, то они будут портить данные. Если они хотя бы минуту проработали в недопустимых температурных диапазонах или продолжают работать выше срока эксплуатации, они также будут портить данные. Даже новая память только что из магазина тоже может портить данные. И ECC вам не поможет. Разумеется, у части организаций оборудование серверного класса, и там поддерживается только ECC. Поэтому выбора просто нет. Также не путайте bit rot на уровне физической памяти и на уровне файловой системы. ZFS не замена ECC, и наоборот. Также если вы не проверяете модули памяти, то ваши «жизненно необходимые» RDIMM тоже будут портить данные (и еще диски надо тестировать). Разумеется, модули никто из вас чаще чем раз в год не проверяет, потому что это трата рабочего времени и простои, пусть и запланированные. Поэтому проще поверить в миф, что ECC память – это лекарство от всех болезней. Решите сначала основные проблемы, а потом рассказывайте про преимущества RDIMM над UDIMM. Очевидно также, что чем больше модулей и срок их эксплуатации (особенно если он выходит за рамки срока службы), тем больше проблем.

Расчет производительности

На самом деле, тема больная. Потому что все зависит не от самой СУБД, а именно от приложения, которое с ним работает. Так что у вас вариантов несколько:

1. Обратиться к документации или техподдержке вашего приложения (не всегда есть, зачастую там просто вода в стиле «Use powerful and reliable hardware» и «CPU and RAM is very important»).

2. Найти статьи и форумы в интернете на эту тему (тоже не всегда есть, обычно там просто информация о настройке параметров конфигов).

3. Самому организовать и провести нагрузочное тестирование (долго, сложно и дорого, но прекрасно подходит для больших систем).

Выше я уже сказал, что чем больше ресурсов в распоряжении СУБД, тем выше будет производительность. Так что прикидывайте примерно, берите HW с запасом и не переживайте, что у вас ресурсы простаивают впустую.

Я провел небольшое тестирование 1С:Предприятие с PostgreSQL в ТРС-G1С, где сама СУБД была развернута на ВМ, которой я выдавал память от 8 до 32 GB с шагом в 1GB. Углубляться в подробности не буду, просто покажу результат, чтобы вы видели, что я имел ввиду.

Как вы можете видеть, производительность растет нелинейно, а график немного напоминает логарифмическую функцию. После 25 GB производительность перестает расти, однако при меньшем объеме памяти система все еще работает. Дополнительный объем памяти все равно лишним не будет. RAM – это самая важная вещь в расчете производительности. Вы можете сэкономить на CPU, но только не на подсистеме памяти. У подсистемы памяти есть три основных, важных для нас параметра. Это объем, пропускная способность, которая складывается из всего остального, а также задержки. Чем больше объем и пропускная способность, и чем ниже задержки – тем лучше. Но 2X GB с пропускной способностью Y это не то же самое, что X GB с пропускной способностью 2Y. Объем гораздо важнее.

Отдельно про CPU скажу лишь, что несмотря на то, что PostgreSQL является бесплатной и не лицензируется по ядрам, а также прекрасно распараллеливает нагрузку на все ядра CPU, она все еще зависит от производительности на ядро (точнее, некоторые из ее встроенных и сторонних сервисов не распараллеливаются). Измерять производительность количеством ядер или их тактовой частотой неправильно, даже в рамках одной архитектуры CPU. Надо измерять на практике производительность конкретных моделей и сравнивать их между собой. А в целом все то же, что и с подсистемой памяти: чем выше производительность CPU – тем лучше. Система всегда найдет, куда деть свободные ресурсы. Но есть одна проблема. Если вы работали с какой-либо системой мониторинга, то прекрасно знаете, что нагрузка на систему всегда выглядит следующим образом: 5-10% CPU и 80-90% RAM. Так что, если вы ограничены по финансам, на CPU можно экономить, но только не на подсистеме памяти. С дисковой подсистемой все намного интереснее. В большинстве случаев она плавает в диапазоне от 10% до 50%, и изредка поднимается до 100% при создании чекпоинта вручную или при создании/восстановлении резервной копии. У вас, конечно же, все может выглядеть иначе.

Производительность также зависит от версии СУБД. Я ниже оставил ссылку на сравнение 9.6 – 15 версий, и вывод простой: чем новее версия – тем выше производительность (без исключений), но в целом производительность между соседними версиями возрастает незначительно. Однако при сравнении 9.6 и 15 версий прирост уже будет ощутимым.

Из той же ссылки вы заметили, что производительность также плавает в зависимости от количества клиентов. И два сервера, обслуживающих по 50 клиентов не сравнимы с одним сервером, обслуживающим 100 клиентов. Позже мы разберем, почему так происходит.

Также интересно отметить производительность PostgreSQL в сравнении с другими СУБД в одних и тех же приложениях. К примеру, в 1С между PostgreSQL и MS SQL Server. Перевес по временным таблицам у последней в два и более раза, однако по всем остальным пунктам (регистры и справочники) либо паритет, либо лидирует PostgreSQL. Это вам пригодится для расчета производительности при миграции баз между этими СУБД.

Я также прекрасно понимаю, что компьютерное оборудование у всех организаций разное. И что эта информация бесполезна для единственного системного администратора в компании, где не более 200 рабочих станций и до 10 физических серверов. Просто держите в голове все, что написано здесь про тему производительности, а дальше на основе полученной информации организуйте вашу систему так, как считаете нужным. Вы ведь администратор.

Ну и в заключении, конечно же, производительность также зависит от приложения, от версии приложения и СУБД, а также от того, как вы все это дело организуете.

https://www.enterprisedb.com/blog/performance-comparison-major-PostgreSQL-versions

https://pganalyze.com/blog/postgres-14-performance-monitoring

Версионирование

Начиная с 10.X версии PostgreSQL, все мажорные релизы обозначаются одним числом, а не двумя, разделенными точкой, как было в версии 9.6.X и более ранних. Минорная версия обозначается одним числом, которое расположено правее всех.

Новые мажорные версии выходят примерно каждый год (пока что, но все может измениться).

От себя добавлю, что не нужно гнаться за последними версиями и обновляться сразу после выхода более новой. Также не нужно до последнего сидеть на старых версиях и обновляться только, если закончится LTS. Найдите баланс. К примеру, сейчас вторая половина 2024 года. Новейшей мажорной версией является 16, которая вышла 09.2023. Последней поддерживаемой версией является 12, которая вышла 10.2019 и поддержка которой закончится 11.2024. При таком раскладе лучшим вариантом будет 14 или 15 версия.

Все дело не только в самой СУБД. На ее стороне как раз проблем с совместимостью между ближайшими мажорными релизами нет. Проблема в первую очередь на стороне приложения, которому необходима для работы PostgreSQL. Так что не спешите обновляться и ждите, пока разработчики залатают все дыры (или хотя бы большую их часть), а также не забывайте обновлять на более новые версии само приложение.

Если вы все же обновляетесь, если вам досталась устаревшая инфраструктура, то я не рекомендую вам перепрыгивать через несколько мажорных релизов (к примеру, сразу с 10 на 16). Обновляйтесь постепенно, шаг за шагом. К примеру, с 13 на 14 версию в начале года, с 14 на 15 версию в середине года. Так у вас будет время на тестирование, а вы накопите опыт миграции и минимизируете вероятность ошибок. Да, я выше указал, что чем новее версия – тем выше производительность, однако снижение производительности на 5% это не так страшно по сравнению с возможными проблемами. Однако, если вам не выделяют технологические окна, то вам придется перескакивать сразу через несколько мажорных версий.

В целом, все, о чем дальше пойдет речь, актуально для 15-16 версий PostgreSQL. В некоторых случаях я отметил, к примеру, какой из параметров был добавлен/изменен и в какой из версий. Но все же рекомендую вам каждый раз уточнять это самостоятельно в документации, все равно вам с ней придется плотно работать.

Также давайте разберемся с тем, как мы будем эту СУБД называть. Примерно 30 лет назад POSTGRES был переименован в PostgreSQL, поскольку начал поддерживать SQL. Называть эту СУБД можете как хотите, хоть «Слоник», однако в документах лучше прописывать нормально «PostgreSQL», если имеется ввиду сама СУБД, потому что «Postgres» (читается как пóстгрэс) обозначает отдельные экземпляры сервера. Если вы от кого-то слышите, что название СУБД коверкают вплоть до «постгри», «постгрéсь» или «постсикьюэл», то этот человек PostgreSQL еще даже не пытался изучать.

https://www.postgresql.org/support/versioning/

Совместимость с ОС

Здесь все просто. Смотрите, чтобы версия СУБД и версия хостовой ОС вышли в ± одинаковое время. Не надо устанавливать 16 версию на Debian 8 или Ubuntu 14. Также не надо устанавливать старые версии PostgreSQL (до 10) на последнюю версию вашего дистрибутива. Есть ли какие-то действительно существенные проблемы – я не могу сказать, потому что такими непотребствами занимаются единицы, и может они проблемы просто не замечают. Мы в первую очередь минимизируем возможные риски из-за разброса версий.

Установка

Каждый дистрибутив уже содержит в своих репозиториях пакеты PostgreSQL. Так, например, в Ubuntu 22.04 стала доступной 14 версия, а в 24.04 –16. В Debian 10 – 13 версия, а в Debian 11 – 15. И при этом все в рамках LTS. Иными словами, нет никаких проблем с загрузкой и установкой актуальной LTS версии сервера PostgreSQL.

Однако лучше всего использовать непосредственно репозитории PostgreSQL, а не вашего дистрибутива. Так вы получите доступ ко всем актуальным версиям не только сервера и клиента, но и, к примеру, к актуальным версиям системы резервного копирования Barman и многих модулей, которые репозитории дистрибутивов не всегда имеют. Это касается любых пакетов – всегда смотрите, откуда разработчики рекомендуют их загружать.

Установка репозиториев PostgreSQL на Debian/Ubuntu происходит в две команды:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

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

postgresql-XX – сервер, сама СУБД XX версии

postgresql-client-XX – клиент XX версии

Репозитории разработчика также позволят вам устанавливать конкретные версии PostgreSQL. К примеру, если у вас Debian 12 или Ubuntu server 24.04 и вы хотите установить 14 версию PostgreSQL сервера, то просто загрузите пакет с именем postgresql-14.

https://www.postgresql.org/download/

Пользователь postgres

После установки сервера, автоматически создается пользователь с логином postgres. Это суперпользователь именно для PostgreSQL, который не имеет ничего общего с суперпользователями в Linux.

Пользователь postgres является владельцем практически всех директорий и файлов, которые относятся к PostgreSQL.

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

От имени суперпользователя PostgreSQL (postgres или другого) должны выполняться все операции, связанные с PostgreSQL. Позже я введу вас в базовое администрирование ролей, а пока что работайте от имени postgres. Нам сначала необходимо понять, что вообще здесь происходит, а не бежать к созданию ролей и баз. А то вы просто создадите базу, назначите ее пользователю postgres, и на этом закончите чтение.

Домашней директорией для пользователя postgres является:

/var/lib/postgresql

Рекомендую не пытаться ее изменить или создать пользователя с тем же именем перед установкой пакетов. Все равно потом мы будем создавать новые роли PostgreSQL, а для роли postgres запретим все подключения в целях безопасности, однако пытаться удалить эту роль я бы крайне не рекомендовал.

https://man7.org/linux/man-pages/man5/shadow.5.html

https://help.ubuntu.com/community/FilePermissions

Термины

Для начала разберемся с определениями, чтобы не путаться:

• СУБД (система управления базами данных, DBMS – Database Management System) – это программное обеспечение для работы с базами данных. Примеры СУБД: PostgreSQL, MS SQL Server, Oracle DB, IBM DB2, MySQL и т. п. Все они разные.

• БД (база данных, DB – Database) – это структурированный набор данных. В нашем случае, просто набор файлов. Как они выглядят – разберем позже в разделе, который посвящен именно БД.

• Кластер (cluster) – на самом деле имеет два значения. В первом случае, это набор баз данных, которые управляются процессом postmaster и группой подпроцессов. Во втором случае кластер – это несколько хостов СУБД, между которыми настроена репликация. Еще есть команда CLUSTER, которая определяет организацию таблиц. В общем, весело. Пока что нам необходим только первый вариант: кластер – это набор баз данных, которые управляются одним экземпляром PostgreSQL. Ко всем БД одного кластера применяются одни и те же настройки. Также все БД одного кластера имеют общие ресурсы по типу ролей, модулей, фоновых процессов, однако каждая из БД все еще имеет свои собственные таблицы, индексы и т.д.

• Экземпляр (instance) – это сервер базы данных, который называется postgres. Один экземпляр (инстанс) сервера postgres может управлять только одним кластером баз данных. На одном хосте под управлением дистрибутивов на основе Debian может быть развернуто несколько экземпляров сервера баз данных. Каждому экземпляру принадлежит свой собственный процесс postgres, который управляет множеством БД в кластере. Администратор управляет именно экземплярами, даже несмотря на то, что в командах пакета postgresql-common есть слово «cluster» (просто из-за наличия процесса postmaster).

Коротко говоря, кластер – это просто набор баз данных, который управляется одним экземпляром. А экземпляр – это набор баз данных, а также серверный процесс и среда, в которой работают БД. На рисунке ниже я попытался приблизительно изобразить то, как это можно представить.

Кластер привязан к версии СУБД и расположен в директории:

/var/lib/postgresql/<major_postgres_instance_version>/<cluster_name>

Причем на одном хосте под управлением СУБД может быть несколько экземпляров с различными версиями и именами, например:

/var/lib/postgresql/14/main

/var/lib/postgresql/14/postgres_1c_dbs

/var/lib/postgresql/15/postgres_zabbix_dbs

/var/lib/postgresql/16/test

Однако для начинающих заниматься подобным я бы не рекомендовал. Всегда стремитесь к тому, чтобы на одном хосте был один экземпляр, на котором будут храниться базы (да, дистрибутивы на основе Debian поддерживают несколько экземпляров на одном хосте, однако на практике смысла в этом немного). Мы просто минимизируем вероятность ошибок и упрощаем администрирование. А заодно вы получите опыт администрирования Linux. Несколько хостов с одним экземпляром на каждом администрируются проще, чем несколько экземпляров на одном сервере. В общем, все зависит от количества баз, их объема, то есть везде все по-разному. Однако начинать я рекомендую именно со стандартной рекомендуемой всеми стратегии «один сервер – один инстанс», а дальше базы распределяются по серверам. Позже вы поймете, почему именно так. Правда, никто вам не мешает поступить иначе, я же не знаю, какая у вас инфраструктура.

При установке пакета postgresql по умолчанию создается экземпляр с именем main и соответствующей ему версией. Например, если вы установили 16 версию PostgreSQL (пакет с именем postgresql-16), то у вас будет один экземпляр postgres:

/var/lib/postgresql/16/main

https://www.postgresql.org/docs/current/app-postgres.html

Управление экземплярами

Теперь, когда мы знаем теорию и понимаем разницу между БД и СУБД, кластером и экземпляром, пришло время научиться работать с экземплярами сервера напрямую.

Управление экземплярами возможно при помощи команды systemctl подсистемы systemd и команд, входящих в состав пакета postgresql-common (начинаются с pg_*, а также имейте ввиду, что они доступны только в дистрибутивах Debian и его форках, поскольку пакет postgresql-common разработан конкретно для Debian). При использовании systemctl вам необходимы права суперпользователя Linux, а при использовании команд из пакета postgresql-common вам требуются права суперпользователя экземпляра. Некоторые команды являются взаимоисключающими (т.е. «мешают» друг другу), поэтому для каждой конкретной операции я ниже буду указывать конкретный метод. Однако ничто вам не мешает опробовать их все.

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

https://man7.org/linux/man-pages/man1/systemctl.1.html

https://www.opennet.ru/man.shtml?topic=pg_ctlcluster

https://www.opennet.ru/man.shtml?topic=pg_lsclusters

Запуск (systemctl)

Запуск экземпляра через systemd:

sudo systemctl start postgresql@<version>-<cluster_name>

К примеру:

sudo systemctl start postgresql@16-main

Запустить саму СУБД (а заодно и все экземпляры) можно командой:

sudo systemctl start postgresql

В этом случае мы просто не указываем конкретную версию и имя экземпляра, однако я рекомендую всегда работать с конкретными экземплярами, даже если их много, именно через systemctl, потому что сами разработчики не рекомендуют запускать экземпляры в Linux через pg_ctlcluster (конечно, consider != must, но все же):

Автозапуск

Добавить или убрать экземпляр из автозапуска при помощи systemctl просто так не выйдет:

sudo systemctl enable postgresql@<version>-<cluster_name>
sudo systemctl disable postgresql@<version>-<cluster_name>

Экземпляры PostgreSQL защищены от включения и отключения автозагрузки через systemctl, благодаря файлу start.conf, который расположен в директории экземпляра (соответственно, у каждого экземпляра он свой).

Файл start.conf имеет больший приоритет, чем systemctl. Так что, если вы, к примеру, отключили автозапуск экземпляра в systemctl, но включили в файле, то после перезапуска ОС экземпляр все равно будет запущен.

Рекомендую вам самим это проверить и попрактиковаться на тестовом стенде.

Абсолютный путь файла (у каждого экземпляра он свой):

/etc/postgresql/<version>/<cluster_name>/start.conf

Например:

/etc/postgresql/16/main/start.conf

/etc/postgresql – это директория, в которой располагаются файлы конфигурации и логи для каждого конкретного экземпляра сервера.

Параметрами файла start.conf могут быть:

auto – означает, что экземпляр будет запущен автоматически при запуске операционной системы. Это значение по умолчанию.

manual – означает, что сервер НЕ будет запущен автоматически при запуске ОС, но может быть запущен вручную при помощи systemctl или pg_ctlcluster. Полезен, когда экземпляр необходимо будет запустить в конкретное время, а не сразу после загрузки ОС.

disabled – означает, что сервер НЕ будет запущен ни при каких условиях при запуске ОС. Это значение необходимо для того, чтобы случайно не запустить экземпляр сервера (например, в целях безопасности). Утилита pg_ctlcluster также не сможет запустить экземпляр сервера, равно как и systemctl. Но эту защиту довольно легко обойти, просто изменив содержимое файла start.conf, а затем перезагрузив экземпляр (о перезагрузке и перезапуске вы узнаете ниже).

Идея такого файла замечательная, однако основная проблема в самом сервере. Если мы отключим автозапуск СУБД через systemctl:

sudo systemctl disable postgresql

Но включим для экземпляра автозапуск через файл start.conf, то ни сервер, ни экземпляр не будут запущены после запуска ОС. Имейте это ввиду и всегда в первую очередь проверяйте доступность самой СУБД.

Статус

Запросить статус (состояние) работы экземпляра:

sudo systemctl status postgresql@<version>-<cluster_name>

Вывод для конкретного экземпляра и для самой СУБД может отличаться. Так что будьте внимательнее и работайте с конкретными экземплярами, а саму СУБД никогда не останавливайте и всегда мониторьте ее доступность.

Запросить статус работы экземпляра также можно при помощи команды pg_lsclusters.

В плане запроса статуса systemctl и pg_lsclusters выводят разную информацию. Так что все зависит от того, что конкретно вам необходимо.

Остановка (systemctl)

В идеале, ваш сервер никогда не должен переставать работать. Но его все же придется останавливать, к примеру, для обновления кластера на другую версию. Если к вашим БД на сервере обращаются внешние приложения, то принудительная остановка (о которой чуть ниже) сервера может привести к потере данных.

Безопасная (при которой гарантируется сохранность данных) остановка экземпляра через systemctl:

sudo systemctl stop postgresql@<version>-<cluster_name>

Использование pg_cltcluster при остановке процессов разработчиками также не рекомендуется.

Остановка самой СУБД влечет за собой остановку всех экземпляров.

При выполнении команды остановки все пользователи, подключения и транзакции сбрасываются. Перед тем, как завершить работу, PostgreSQL создает чекпоинт (checkpoint – контрольная точка), то есть, грубо говоря, сбрасывает на диск кеш всех данных во избежание их потери. Чекпоинты создаются автоматически время от времени. Однако, чем больше информации чекпоинту необходимо обработать и записать на диск, тем дольше сервер будет выключаться (на перезапуск это тоже влияет).

Остановка в случае полного П

Иногда бывают ситуации, когда остановка экземпляра невозможна через systemctl (и я искренне желаю вам никогда с ними не сталкиваться), однако все же есть способ остановить экземпляр принудительно (с возможной потерей данных, разумеется). Самый быстрый и надежный способ – это выключение хостовой ОС и ее изоляция для дальнейших разбирательств.

Однако есть и способ остановки экземпляра без вмешательства в работу ОС:

pg_ctlcluster <version> <cluster_name> stop -m immediate

Эта команда с параметром immediate просто убивает все связанные с конкретным экземпляром процессы. Однако имейте ввиду, что последующий процесс запуска будет ощутимо дольше, чем обычный.

Также возможно просто убить процесс. Имя процесса находится в первой строке файла postmaster.pid в директории кластера (если вы знаете Linux, то сможете сами это сделать, и есть еще куча способов на самом деле, однако ни один из них разработчиками не рекомендован к использованию).

Перезапуск (systemctl)

Перезапуск экземпляра:

sudo systemctl restart postgresql@<version>-<cluster_name>

Перезапуск – это просто выключение, за котором следует немедленное включение.

В общем говоря, это просто вызов двух команд:

systemctl stop
systemctl start

С pg_ctlcluster ситуация в плане перезапуска аналогичная:

Перезагрузка

Некоторые путают перезапуск и перезагрузку. Я тоже. Такие уж сложности перевода. Так что всегда указывайте конкретный метод. Перезагрузка (reload) не перезапускает (restart) сам сервер, она просто перезагружает (обновляет, если так понятнее) конфигурационные файлы, чтобы изменения в них вступили в силу. Но есть параметры, которые требуют перезапуска (restart) самого экземпляра. Перезагрузка (reload) не сбрасывает подключения. Очень полезная вещь, если вы хотите изменить какой-либо параметр на продуктивной системе.

Перезагрузка конфигурационных файлов экземпляра:

sudo systemctl reload postgresql@<version>-<cluster_name>

В плане перезагрузки утилита pg_cltcluster не испытывает никаких проблем. Поэтому, что именно использовать – вы выбираете сами (ну или не выбираете, если у вас дистрибутив, отличный от Debian и его форков).

pg_cltcluster <version> <cluster_name> reload

Работа с экземплярами (кластерами)

Из операций выше вы уже поняли, что использовать для управления экземплярами команду pg_ctlcluster нежелательно, однако это исключение. Все остальные команды очень просты в освоении и полезны. К примеру, вы уже познакомились с командой pg_lsclusters. Также прошу вас нормально относиться к тому, что я мешаю понятия экземпляра и кластера. Да, для многих это разные вещи, но для нас пока что это не имеет значения.

Создание кластера

Команда pg_createcluster создает новый кластер (это просто оболочка над initdb).

Перед созданием нового кластера необходимо определиться с мажорной версией и именем кластера. Мажорная версия должна быть той же, что и у сервера. Очевидно также, что вы не сможете создать кластер 16 версии, если у вас не установлена 16 версия сервера PostgreSQL.

Чтобы иметь несколько кластеров различных версий на одном хосте, необходимо установить несколько различных версий самой СУБД. К примеру, есть задача создать два кластера 15 и 16 версий. Для ее выполнения необходимо установить пакеты postgresql-15 и postgresql-16.

Синтаксис команды:

pg_createcluster [options] <cluster_version> <cluster_name>

Из параметров (options) нам необходимы лишь несколько:

-u <user> – задает пользователя, который будет владельцем создаваемого кластера. По умолчанию владельцем кластера будет пользователь postgres.

-p <port> – задает конкретный номер порта, по которому экземпляр будет принимать подключения. Этот же порт будет записан в параметры конфига postgresql.conf. По умолчанию будет назначен 5432 порт, а все создаваемые далее кластера будут идти по следующим портам: 5433, 5434 и т.д. (вне зависимости от версий или имен кластеров). Не рекомендую использовать 5432 порт, лучше сразу идти с 5433. Порт может быть любым, хоть 54321, главное, чтобы он не использовался для других сервисов. У каждого из экземпляров в рамках одного хоста должен быть оригинальный порт.

--start – запускает кластер после его успешного создания. По умолчанию кластер не запускается.

--start-conf=<auto|manual|disabled> – задает параметр для конфига start.conf. По умолчанию в конфиге задан параметр auto.

Все остальные параметры рекомендую не трогать и оставить по умолчанию.

К примеру, создадим новый кластер 16 версии с именем production, который будет прослушивать 5433 порт, а также прикажем серверу сразу его запустить, и вдобавок отключим автозапуск и назначим владельцем кластера пользователя postgres:

pg_createcluster -u postgres -p 5433 --start --start-conf=manual 16 production

https://manpages.ubuntu.com/manpages/trusty/man8/pg_createcluster.8.html

Удаление кластера

Все просто. Команда pg_dropcluster полностью удаляет существующий кластер. Необходимо задать только версию и имя. Перед удалением кластер необходимо остановить. Чтобы удалить кластер, вам необходимы права владельца кластера или суперпользователя root.

Синтаксис команды:

pg_dropcluster [--stop] <cluster_version> <cluster_name>

--stop – это параметр, который принудительно останавливает работающий кластер перед его удалением.

Например, удалим кластер 16 версии с именем main, который в данный момент запущен:

pg_dropcluster --stop 16 main

https://manpages.ubuntu.com/manpages/trusty/en/man8/pg_dropcluster.8.html

Изменение имени кластера

Изменить имя кластера можно при помощи команды pg_renamecluster. Для этого необходимо знать версию кластера, текущее имя и новое имя. Во время выполнения команды кластер будет автоматически перезапущен (не следует изменять имя на продуктивной системе, потому что все подключения будут сброшены). У вас может быть два кластера с одним именем на различных версиях. Однако дальше это может сыграть с вами злую шутку, так что стремитесь к тому, чтобы имена всегда отличались.

Синтаксис:

pg_renamecluster <cluster_version> <cluster_old_name> <cluster_new_name>

К примеру, переименуем кластер 16 версии с имени main на имя production:

pg_renamecluster 16 main production

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

https://manpages.ubuntu.com/manpages/lunar/man1/pg_renamecluster.1.html

Обновление кластера (мажорная версия)

Обновление – это перемещение кластера на новую мажорную версию. Перед обновлением всегда читайте примечания к новой версии в документации, там всегда пишут обо всех несовместимых вещах и о способах их устранения. Также проверяйте все на тестовой среде и составляйте планы отката в случае сбоя.

Перед обновлением необходимо:

1. Узнать все версии установленных пакетов сервера СУБД при помощи команды:

apt list --installed | grep postgresql*

(команда pg_config --version показывает только последнюю версию).

2. Определить версии и имена всех существующих кластеров (например, при помощи команды pg_lsclusters).

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

4. Значение в конфиге start.conf у обновляемого кластера не должно иметь значение disabled.

Обновление возможно только между мажорными релизами. При этом возможно перешагнуть через несколько, однако я так делать не рекомендую. Обновление на более раннюю версию при помощи pg_upgradecluster невозможно. Если вам необходимо откатиться на старую версию, то придется восстанавливать кластер при помощи утилиты pg_dump.

Синтаксис:

pg_upgradecluster [-v <new_cluster_version>] <old_cluster_version> <cluster_name> 

-v – это параметр, который указывает новую версию, на которую обновление будет произведено. Если он не будет задан, то обновление будет произведено на последнюю версию, которая установлена в системе.

Например, обновим кластер с именем production с 14 до 15 версии:

pg_upgradecluster -v 15 14 production

После обновления останется старая версия кластера с тем же именем, которая будет остановлена. Ее необходимо удалить вручную.

Обновленный кластер будет автоматически запущен, даже если до этого он был выключен, а в конфигурационном файле было задано значение manual.

Все конфигурационные файлы из старой версии кластера будут скопированы в новую.

Обновленному кластеру будет назначен новый порт (потому что, по сути, мы создали новый кластер).

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

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

https://manpages.ubuntu.com/manpages/trusty/en/man8/pg_upgradecluster.8.html

Обновление кластера (минорная версия)

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

Конфигурационные файлы

Для каждого из экземпляров конфиги (конфигурационные файлы) свои и расположены в директории:

/etc/postgresql/<major_release>/<cluster_name>

С конфигом start.conf вы уже знакомы. Он определяет параметр автозапуска экземпляра.

postgresql.conf – основной конфигурационный файл.

pg_hba.conf – определяет политику подключений.

pg_ident.conf – предназначен для сопоставления имен.

pg_ctl.conf – не трогать.

environment – тоже не трогать.

conf.d – директория, предназначенная для хранения дополнительных конфигурационных файлов, которые будут автоматически загружены в основной конфигурационный файл при запуске экземпляра.

https://www.postgresql.org/docs/current/runtime-config-file-locations.html

Изменение параметров конфигурационных файлов

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

Синтаксис утилиты:

pg_conftool [options] <cluster_version> <cluster_name> <config_file_name> command

В первую очередь отмечу, что желательно задавать версию, имя кластера и имя конфигурационного файла так, как надо. Например, посмотрим, какой порт назначен кластеру с именем production 16 версии:

pg_conftool 16 production postgresql.conf show port

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

Разумеется, в документации написано, что, к примеру, можно не указывать версию, если вместо имени конфигурационного файла указан его абсолютный путь. Однако это увеличивает вероятность изменения не тех файлов. Так что прописывайте все отдельно.

Команды (те, которые задаются в command):

show <parameter>|all – показывает конкретный или все параметры вместе с их именем и значением (в алфавитном порядке, а не в том, в котором они расположены в самом файле).

set <parameter> <value> – задает значение параметру.

remove <parameter> – делает параметр недействительным (просто его комментирует).

https://manpages.ubuntu.com/manpages/focal/man1/pg_conftool.1.html

Разумеется, как я уже сказал выше, вы можете вручную редактировать конфигурационные файлы при помощи любимого текстового редактора и впасть в апатию от количества параметров. Далее будут разобраны только основные из них, которые будут полезны в любых сценариях.

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

Postgresql.conf

Расположения файлов

data_directory

Определяет директорию, в которой будут храниться все файлы всех БД этого экземпляра. Внимательно смотрите за тем, сколько свободного пространства осталось в хранилище БД.

hba_file

Определяет полный путь для конфигурационного файла pg_hba.conf.

ident_file

То же для pg_ident.conf.

Параметры подключений

listen_addresses

Задает IP-адреса локальных сетевых интерфейсов, с которых СУБД будет прослушивать подключения. Если не хотите парить себе мозги, оставляйте звездочку.

port

Задает конкретный номер порта, по которому экземпляр будет принимать подключения ко всем базам на своем кластере (при подключении мы будем задавать имя БД и порт, а не имя кластера). По умолчанию задается 5432 порт, но я не рекомендую его оставлять, лучше сразу идти с 5433. Порт может быть любым, хоть 54321, главное, чтобы он не использовался для других сервисов. У каждого из экземпляров в рамках одного хоста должен быть свой уникальный порт. Я это уже писал, но напишу еще раз. Потому что для подключения необходимо знать номер порта, это важно.

max_connections

Задает максимальное количество подключений (сессий), которые сервер будет держать одновременно. Разумеется, никакого принципа FIFO тут нет. Если для нового подключения нет места – оно просто будет сброшено. Значение по умолчанию 100. И нет, это не соединения с одним приложением. Это именно количество одновременно работающих подключений (connections) со стороны приложения. Сколько их будет – просчитать невозможно, все зависит от того, насколько кривыми были руки у разработчиков, потому что подключений может быть в разы больше, чем живых клиентов. Иными словами, уточняйте у разработчиков приложения, какое значение задавать, или просто выставляйте его как можно больше вплоть до 2000 или выше. Но, забегая вперед, отмечу, что практически везде рекомендуют задавать количество подключений равным количеству клиентов с запасом в 10-20%. Разумеется также, что каждому подключению требуется память, но об этом чуть ниже. И ваш сервер, к примеру, не сможет вытянуть 10000 одновременных подключений, если памяти мало. И нет, я не понимаю, зачем разработчики оставили 100, все равно мы в первую очередь ограничены по аппаратным ресурсам. И нет, неоправданное увеличение не приведет к потере аппаратных ресурсов. Нагрузка действительно может вырасти, но и производительность вырастет вместе с ней. Все зависит от того, сколько приложение наплодило подключений.

reserved_connections

Новый параметр в 16 версии. Задает максимальное количество зарезервированных подключений для ролей с привилегией pg_use_reserved_connections. Должно быть меньше, чем разница между max_connections и superuser_reserved_connections. Очень полезно для ключевых ролей, к примеру, для учетной записи, которая отвечает за резервное копирование. Однако все равно в нем нет смысла, если параметр max_connections задан гораздо больше, чем максимальное количество подключений в вашей системе.

superuser_reserved_connections

Определяет количество подключений, которые зарезервированы для суперпользователей PostgreSQL. По умолчанию задано значение 3. Также бесполезен, если max_connections задан сильно выше необходимого.

Остальные параметры не важны для большинства. Но если они вам интересны, вы можете ознакомиться с ними в документации.

https://www.postgresql.org/docs/current/runtime-config-connection.html

Потребление ресурсов

Сразу скажу: есть множество хороших статей, где уже описаны рекомендации по настройке параметров конкретно для вашего приложения. Однако я отмечу лишь основные.

shared_buffers

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

Общие буферы – это кеш, которому мы выделяем конкретный объем памяти. То есть мы просто выгружаем в RAM данные БД, к которым сервер обращается чаще всего. Нам не интересно, как он устроен и как работает. Нас интересует только то, какой объем памяти необходимо выставить для достижения максимальной производительности.

Вы, наверное, не раз слышали, что золотой серединой является значение 1/4 от общего объема RAM. На самом деле это неправда, потому что все индивидуально для каждого случая. По умолчанию объем памяти задан 128MB просто для возможности запуска СУБД на системах с малым объемом памяти.

Во-первых, чем больше пропускная способность памяти – тем лучше, даже несмотря на то, что чуть меньший объем памяти с большей пропускной способностью будет интенсивнее гонять данные.

Во-вторых, все подсистемы аппаратного обеспечения связаны. И, к примеру, дисковая подсистема и CPU также влияют на производительность системы в целом, поэтому объем памяти, выделяемый для кеша, у всех будет разным, даже если объем и пропускная способность RAM одинаковые (просто из-за значительного влияния дисковой подсистемы и незначительного со стороны CPU). Но, как бы то ни было, кеш CPU всегда будет быстрее, а дисковая подсистема всегда будет медленнее, чем подсистема памяти. И дело не только в скорости передачи данных, основная проблема в задержках.

В-третьих, не смотрите на процент. Под процентом мы подразумеваем именно какой-то конкретный целый объем памяти. Поэтому 25% от 8 GB и 50% от 4 GB конкретно по кешу выдадут примерно одинаковый уровень производительности.

Теоретически, возможно выгрузить вообще все данные в оперативную память. Однако, мы должны обладать объемом памяти, которого будет достаточно, чтобы вместить в себя все данные БД. И это не сильно увеличит производительность, поскольку мы выгрузим вообще все данные, включая и те, к которым сервер будет обращаться очень редко. То есть это впустую потраченные средства. Иными словами, есть какой-то максимальный порог, после которого производительность перестает расти или, наоборот, уменьшается. И у каждого этот порог будет разным (как было у меня выше с 25 GB).

Так откуда взялись 25%? Из устаревшей документации о кеше ядра, равно как и миф про ограничение в 64 GB. Конкретно 25% получили, по всей видимости, просто ткнув пальцем в небо.

Что по итогу ставить? Если у вас в отделе меньше трех системных администраторов, то можете ставить любой понравившийся объем в диапазоне от 25% до 50% от общего объема памяти и перейти к настройке следующих параметров или вообще на этом ограничиться. Правда, он вам ничем не поможет, если у вас на сервере всего 4 GB памяти.

Если же у вас большая нормальная система, то придется с умом подбирать объем памяти для каждой из ваших инфраструктурных единиц. На самом деле подбирается он просто. Начните, к примеру, с так любимых всеми 1/4 от общего объема RAM, а дальше увеличивайте или уменьшайте его с шагом 4, 8 или 16 GB (в зависимости от общего объема памяти на сервере). И так до момента, когда производительность перестанет расти. Однако я вас заранее предупреждаю: может возникнуть ситуация, когда 25% выдадут меньший уровень производительности, чем 10% и 50%. Так что будьте внимательнее и сравнивайте как по уровню возрастания, так и по уровню убывания. У всех системы разные. Единственная проблема: изменение этого параметра требует перезагрузки сервера (в общем, как и многих других).

Также многие опасаются, что кеш может занять практически всю память и не оставить ее для остальных процессов и подключений. Именно поэтому я рекомендовал до 50%. Можно и больше, однако в таком случае памяти для подключений (сессий) и операций обслуживания может не хватить. Чем больше объем кеша – тем ниже нагрузка на дисковую подсистему (но, как вы уже поняли, производительность может не увеличиться).

Всегда все проверяйте и не верьте людям на слово. Так что если кто-то вам говорит, что 25% это универсальная золотая середина для всех случаев – то этот человек просто увидел значение 25% в документации, даже не задаваясь вопросом, почему именно так.

huge_pages

Всегда отключайте, потому что рабочие нагрузки для баз данных не являются непрерывными при обращении к памяти. Мы получим более высокий уровень производительности, если БД будет помещена в общие буферы (shared_buffers). Основная проблема состоит в том, что Transparent Huge Pages плохо работают с базами, хотя сами по себе HP просто прекрасные. Если вы захотите включить HP – то готовьтесь подбирать еще и их размер методом проб и ошибок на продуктивной системе. По умолчанию стоит значение try, его можно оставить или полностью отключить.

temp_buffers

Это максимальный объем памяти, выделяемый для работы с временными объектами. Чем больше ваше приложение любит временные объекты – тем больше стоит задать значение. К примеру, 1Ску постоянно прорывает то есть, 1С любит создавать временные таблицы. Поэтому для нее желательно выделить памяти побольше, вплоть до нескольких гигабайт, в зависимости от количества пользователей (посмотрите в общем, какое значение рекомендуют выставлять разработчики вашего приложения).

work_mem

Умные люди в комментариях меня поправили. Лучше вообще не менять и оставить по умолчанию 4MB.

maintenance_work_mem

Задает объем памяти для операций обслуживания (при ручной и автоматической очистке, массовом перестроении индексов, анализе и т.п.). Рекомендую выставить от 1/32 до 1/8 от общего объема памяти. Все зависит от того, будете ли вы назначать память отдельно для процесса автоматической очистки (об этом ниже). Чем больше памяти выделите – тем быстрее будут работать операции обслуживания. Если вы будете задавать процессу автоматической очистки отдельный объем памяти, то назначайте 1/32. Если нет – то 1/8 (на самом деле можно даже меньше, просто задавать выше 1/8 смысла нет).

effective_cache_size

Этот параметр не выделяет память, он просто помогает планировщику запросов составить наиболее точную оценку затрат на обработку этих самых запросов. Звучит сложно, непонятно, потому что мы вообще ничего не знаем о запросах. Проще говоря, незначительно повышает производительность. Учтите также, что параметры effective_cache_size и shared_buffers взаимосвязаны. Значение effective_cache_size не должно быть меньше shared_buffers, а в идеале оно должно быть в 2 раза больше, чем shared_buffers (но не более 80% от общего объема памяти, что как бы намекает нам на то, что 40% для shared_buffers это действительно золотая середина).

random_page_cost

Здесь неплохо бы знать на хорошем уровне устройство дисков. Дело в том, что у HDD и SSD разная скорость работы и разное время позиционирования на нужную дорожку (у SSD нет механических деталей, поэтому время позиционирования там будет очень маленькое). Основная проблема в том, что PostgreSQL не знает, на какой дисковой подсистеме у вас находятся базы (в смысле, HDD или SSD), поэтому мы можем ему об этом сообщить, чтобы СУБД не использовала последовательное сканирование на SSD или рандомное сканирование по индексу на HDD.

По умолчанию стоит значение 4.0, это означает, что PostgreSQL думает, что у нас базы расположены на HDD.  Значение 4.0 означает, что случайный доступ к данным на диске считается в 4 раза дороже по производительности, чем последовательный. Чем выше производительность диска – тем ниже будет значение. Так что выставляем значение в зависимости от того, какая у вас дисковая подсистема:

0.1 – для SSD NVMe.

1.0 – для SSD без NVMe.

2.0 – для RAID из HDD.

4.0 – для одного HDD.

Первые два значения подходят и для RAID на SSD (то есть, если у вас RAID 0 из сотни SSD NVMe, то все равно оставляйте значение 0.1). В третьем случае количество рабочих дисков (исключая диски четности) HDD не имеет значения. Я также понимаю, что все диски разные, это просто универсальные параметры, которые не являются точными и подходящими для всех. Но это все равно лучше, чем значение 4.0 по умолчанию, если у вас RAID из SSD.

Основная задача параметра – оптимизировать производительность дисковой подсистемы. Если мы выставим 0.1 на HDD, система встанет. Этот параметр не зависит от приложения, зависит только от дисковой подсистемы.

effective_io_concurrency

Задает максимально допустимое число параллельных операций IO. Расчет значения отличается от параметра выше. Какое значение указать – опять же, все зависит от вашей дисковой подсистемы. Ниже я разместил примерные параметры.

1 – для HDD на 7200 или менее RPM.

2 – для HDD с более чем 7200 RPM.

50 – для SSD без NVMe.

100 – для SSD с NVMe PCI-E 3.0.

200 – для SSD с NVMe PCI-E 4.0.

И далее умножаем на количество рабочих дисков в массиве (то есть из общего количества дисков в массиве вычитаем диски паритета). К примеру, если у вас RAID 6 из шести дисков SSD без NVMe, то выставляйте значение 200 ( 50 * (6 – 2) ). Если же у вас RAID 1+0 из четырех HDD на 10000 RPM, то выставляйте значение 8 ( 2 * 4 ), поскольку все диски в массивах RAID 1 и 0 рабочие (В отличие от RAID 5 и 6, к примеру). И нет, это не значит, что SSD без NVMe в 50 раз быстрее HDD на 7200 RPM. Все дело в скорости обработки параллельных операций.

Еще раз: значения примерные, потому что все диски разные, и системы у всех разные (нагрузка, типы запросов, операционные системы, аппаратное обеспечение и т.д.). Но все равно это лучше, чем значение по умолчанию 1.

Итог

Не забывайте резервировать необходимый объем памяти для хостовой ОС и ее сервисов, а также настраивайте приоритет распределения памяти между процессами (просто задавайте всем процессам PostgreSQL низкий приоритет, потому что все остальные процессы, зачастую, гораздо важнее, а также потребляют значительно меньше ресурсов.

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

Вы наверняка уже посчитали, что весь объем памяти, который мы определили, может выходить за пределы объема памяти хоста. Поэтому рассчитывайте для вашего случая. Я не буду рассказывать о том, какой приоритет имеют эти параметры при выделении памяти, а где выделяется фиксированное значение, а где плавающее. Просто следуйте рекомендациям и, если возможно, проводите сравнение.

Также для многих открытие, но изменение этих параметров согласно рекомендациям из документации или статей может, наоборот, уменьшить производительность. Однако это неправда. И тут две проблемы: сами тесты и аппаратное обеспечение.

Тесты (те, что со стороны приложения) не отображают реальное положение дел. Те же тесты Гилева могут выдавать одни и те же значения до и после оптимизации, однако на практике пользователи будут работать поживее, а нагрузка на дисковую подсистему снизится в разы. Используйте нормальные тесты для самой СУБД, а далее за все проблемы с производительностью должны отвечать разработчики приложения (которые, разумеется, будут во всем винить СУБД).

В аппаратное обеспечение все равно придется вкладываться, потому что оптимизировать систему, которая еле-еле отвечает минимальным системным требованиям, бессмысленно. 32 GB памяти и хранилище баз на HDD для той же 1Ски на 300 пользователей это все равно, что стакан воды для слона, и при этом неважно, какая СУБД используется: PostgreSQL, MS SQL или Oracle DB.

В целом подход к оптимизации для всех приложений один и тот же. Отличаться лишь будут значения параметров. Еще раз: читайте рекомендации по оптимизации конкретно для вашего приложения и документацию по каждому из параметров.

Также не стоит переживать, если вы чего-то не поняли. Если вы просто оставите какой-либо из параметров по умолчанию – вы не сломаете систему. Все и так будет работать, правда производительность будет чуть ниже, чем могла бы быть. В малых системах вы вообще разницы не заметите. Большинство параметров было разработано для тех времен, когда каждый KB памяти был на счету, а каждый MB на диске имел значение. Сейчас же это не так важно (или все еще важно, но только в крупных системах), именно поэтому оптимизация параметров не так сильно отражается на общем уровне производительности и загрузки системы.

Не гонитесь за идеальными значениями параметров и высочайшими показателями производительности, просто следуйте рекомендациям. Лучшее – враг хорошего.

https://www.postgresql.org/docs/current/runtime-config-resource.html

https://www.postgresql.org/docs/current/runtime-config-query.html

Вторая часть:
https://habr.com/ru/articles/843012/