Установка pos. PostgreSQL: установка, настройка, обслуживание. Итак! Страдальцами Windows посвящается

PostgreSQL напрямую "из коробки" применяться для использования с 1С Предприятем не может. Необходима именно адаптированная версия от 1С, превращающая PostgreSQL в блокировочник, причем нужно понимать, что блокировки будут накладываться на всю таблицу сразу. Если нужны блокировки на уровне записей, включаем в 1С режим управляемых блокировок и прописываем их в конфигурации ручками. Вывод: необходимо скачать специальный дистрибутив с сайта 1С или взять на диске ИТС.

Установка

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

Для русского языка

initdb --locale=ru_RU.UTF-8 --lc-collate=ru_RU.UTF-8 --lc-ctype=ru_RU.UTF-8 --encoding=UTF8 -D /db/postgresql

Для украинского языка

initdb --locale=uk_UA.UTF-8 --lc-collate=uk_UA.UTF-8 --lc-ctype=uk_UA.UTF-8 --encoding=UTF8 -D /db/postgresql

где /db/postgresql ваш каталог данных PostgreSQL. Кодировка, конечно же, UTF-8.

Подробный вариант пересоздания кластера

1.Необходимо выдать полные права на папку в которую мы установили PostgreSQL, обычно это C:\Program Files\PostgreSQL

2.Из под админских прав запускаем cmd. Если это делаете в win7, то запускаем от Администратора.

3.Создаем папку где будет храниться кластер. Например d:\postgredata.

md d:\postgredata

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

“C:\Program Files\PostgreSQL\9.1.2-1.1C\bin\initdb.exe” -D d:\postgredata --locale=Russian_Russia --encoding=UTF8 -U postgres

5.Удаляем службу PostgreSQL, которая была установлена в ходе установки.

sc delete pgsql-9.1.2-1.1C-x64

Где pgsql-9.1.2-1.1C-x64 – Это название службы. Если не знаете название точно, можно посмотреть свойствах службы “PostgreSQL Database Server…” (Пуск – Панель управления – Администрирование – Службы)

6.Создаем новый сервис с указанием нашего кластера

“C:\Program Files\PostgreSQL\9.1.2-1.1C\bin\pg_ctl” register -N pgsql -U postgresql -P пароль -D d:/postgredata

7.Теперь заходим в службы. Пуск – Панель управления – Администрирование – Службы и стартуем нашу службу.

Ошибка СУБД: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (WIN1251).

HINT: Use the same encoding as in the template database, or use template0 as template.

Вы выбрали неправильную локаль при установке СУБД (WIN1251) для сервера и клиента, нужно изменить на UTF-8 в конфигурации или переустановить СУБД со следующими параметрами:

Внимание при установке НЕ выбирайте локаль Настройки ОС, выбирайте из списка Russian, Russia

Настройка PostgreSQL

Следует помнить о рекомендации 1С не использовать в запросах конструкции ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ и заменять его, используя, например, комбинацию из нескольких левых соединений. Известна также проблема с потерей производительности в запросах, где применяется соединение с виртуальной таблицей СрезПоследних, к ней рекомендуется делать отдельные запросы и сохранять результаты во временных таблицах.

Настройка конфигурации производится редактированем файла postgresql.conf.

Наиболее важные параметры

effective_cache_size = 0,5 от ёмкости RAM

fsync = off отключаем сброс на диск после каждой транзации (Внимание! Применять только при использовании надежного UPS, есть опасность потери данных при неожиданном отключении)

synchronous_commit = off отключаем синхронную запись в лог (риски теже, что и у fsync)

wal_buffers = 0,25 от ёмкости RAM

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

service postgresql restart

Настройка сети

Для подключения клиентов 1С к серверу извне и работы сервера баз данных, на файрволе, должны быть открыты следующие порты:

Агент сервера (ragent) & tcp:1540 Главный менеджер кластера (rmngr) & tcp:1541 Диапазон сетевых портов, для динамического распределения рабочих процессов & tcp:1560&1591, tcp:5432 & Postgresql. Создадим правило через стандартный интерфейс, либо с помощью команды:

netsh advfirewall firewall add rule name="1Cv8-Server" dir=in action=allow protocol=TCP localport=1540,1541,5432,1560-1590 enable=yes profile=ANY remoteip=ANY interfacetype=LAN

Теперь с другого компьютера мы спокойно запускаем клиент 1С:Предприятия, добавляем существующую информационную базу newdb. Не забываем про лицензии, программной / аппаратной защиты.

Резервное копирование

Создание дампа базы данных делаем командой

su postgres -c "pg_dump -U postgres -Fc -Z9 -f baza1.sql baza1"

Восстановление из дампа

su postgres -c "pg_restore -U postgres -c -d baza1 -v baza1.sql"

Периодическое обслуживание

su postgres -c "/usr/bin/vacuumdb --dbname=$i --analyze --full --quiet"

Просмотр активности PostgreSQL

Иногда полезно видеть чем сейчас занимается сервер. Поможет такая конструкция:

watch -n 1 "ps auxww | grep ^postgres"

Устанавливать будем сборку от компании Postgres Professional . На странице с версией для 1С:Предприятие найдем информацию об установке на CentOS 7 свежей версии PostgreSQL.

Подключим репозитории и установим PostgreSQL 9.6:

Sudo rpm -ivh http://1c.postgrespro.ru/keys/postgrespro-1c-centos96.noarch.rpm sudo yum makecache sudo yum install postgresql-pro-1c-9.6

Базовая настройка PostgreSQL

Инициализируем служебные базы данных с русской локализацией:

Su postgres /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data --locale=ru_RU.UTF-8 exit service postgresql-9.6 initdb

Запускаем службу PostgreSQL и добавляем его в автозагрузку:

Systemctl enable postgresql-9.6 systemctl start postgresql-9.6 systemctl status postgresql-9.6

Задаем пароль пользователю postgres, для того чтобы была возможность подключаться к серверу удаленно:

Su - postgres psql ALTER USER postgres WITH ENCRYPTED PASSWORD "yourpassword"; \q exit

Mcedit /var/lib/pgsql/9.6/data/pg_hba.conf

в открывшемся файле раскомментируем и изменим строки:

host all all 127.0.0.1/32 ident на host all all 127.0.0.1/32 md5

host all all 0.0.0.0/0 ident на host all all 0.0.0.0/0 md5

Оптимизация настроек PostgreSQL (postgresql.conf) для 1С:Предприятие

Здесь будут настройки для PostgreSQL, работающей в виртуальной машине ESXi 6.5.

Ресурсы выделенные для ВМ:

процессор — 8 vCPU;

память — 48 GB;

диск для ОС — 50 GB на LUN аппаратном RAID1 из SAS HDD;

диск для БД — 170 GB на программном RAID1 из SSD

диск для логов — 100 GB на программном RAID1 из SSD

Для редактирования настроек выполним команду:

Mcedit /var/lib/pgsql/9.6/data/postgresql.conf

Закомментированные параметры, которые будем изменять необходимо активировать.

Процессор

autovacuum_max_workers = 4

autovacuum_max_workers = NCores/4..2 но не меньше 4

Количество процессов автовакуума. Общее правило - чем больше write-запросов, тем больше процессов. На read-only базе данных достаточно одного процесса.

ssl = off

Выключение шифрования. Для защищенных ЦОД’ов шифрование бессмысленно, но приводит к увеличению загрузки CPU

Память

shared_buffers = 12GB

shared_buffers = RAM/4

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

temp_buffers = 256MB

Максимальное количество страниц для временных таблиц. Т.е. это верхний лимит размера временных таблиц в каждой сессии.

work_mem = 64MB

work_mem = RAM/32..64 или 32MB..128MB

Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая часть сессий почти всегда висит в ожидании. Это рекомендательное значение используется оптимайзером: он пытается предугадать размер необходимой памяти для запроса, и, если это значение больше work_mem, то указывает экзекьютору сразу создать временную таблицу. work_mem не является в полном смысле лимитом: оптимайзер может и промахнуться, и запрос займёт больше памяти, возможно в разы. Это значение можно уменьшать, следя за количеством создаваемых временных файлов:

maintenance_work_mem = 2GB

maintenance_work_mem = RAM/16..32 или work_mem * 4 или 256MB..4GB

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

effective_cache_size = 36GB

effective_cache_size = RAM — shared_buffers

Оценка размера кеша файловой системы. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. И это хорошо.

Диски

effective_io_concurrency = 5

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Для одиночного диска = 1, для RAID - 2 или больше.

random_page_cost = 1.3

random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD

Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.

autovacuum = on

Включение автовакуума.

autovacuum_naptime = 20s

Время сна процесса автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать вакуумиться и, как следствие, вырастет bloat и размер таблиц и индексов. Малая величина приведет к бесполезному нагреванию.

bgwriter_delay = 20ms

Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.

bgwriter_lru_multiplier = 4.0

bgwriter_lru_maxpages = 400

Параметры, управляющие интенсивностью записи фонового процесса записи. За один цикл bgwriter записывает не больше, чем было записано в прошлый цикл, умноженное на bgwriter_lru_multiplier, но не больше чем bgwriter_lru_maxpages.

synchronous_commit = off

Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.

wal_keep_segments = 256

wal_keep_segments = 32..256

Максимальное количество сегментов WAL между checkpoint. Слишком частые checkpoint приводят к значительной нагрузке на дисковую подсистему по записи. Каждый сегмент имеет размер 16MB

wal_buffers = 16 MB

Объём разделяемой памяти, который будет использоваться для буферизации данных WAL, ещё не записанных на диск. Значение по умолчанию, равное -1, задаёт размер, равный 1/32 (около 3%) от , но не меньше, чем 64 КБ и не больше, чем размер одного сегмента WAL (обычно 16 МБ). Это значение можно задать вручную, если выбираемое автоматически слишком мало или велико, но при этом любое положительное число меньше 32 КБ будет восприниматься как 32 КБ. Этот параметр можно задать только при запуске сервера.

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

default_statistics_target = 1000

Устанавливает целевое ограничение статистики по умолчанию, распространяющееся на столбцы, для которых командой ALTER TABLE SET STATISTICS не заданы отдельные ограничения. Чем больше установленное значение, тем больше времени требуется для выполнения ANALYZE , но тем выше может быть качество оценок планировщика. Значение этого параметра по умолчанию - 100.

checkpoint_completion_target = 0.9

Степень «размазывания» checkpoint’a. Скорость записи во время checkpoint’а регулируется так, что бы время checkpoint’а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_ target.

min_wal_size = 4G
max_wal_size = 8G

min_wal_size = 512MB .. 4G
max_wal_size = 2 * min_wal_size

Минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments

fsync = on

Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания. Внимание: если RAID имеет кеш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кеша RAID контроллера! Иначе данные записанные в кеш RAID могут быть потеряны при выключении питания, и, как следствие, PgSQL не гарантирует целостность данных.

row_security = off

Отключение контроля разрешения уровня записи

enable_nestloop = off

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

Блокировки

max_locks_per_transaction = 256

Максимальное число блокировок индексов/таблиц в одной транзакции

Настройки под платформу 1С

standard_conforming_strings = off

Разрешить использовать символ \ для экранирования

escape_string_warning = off

Не выдавать предупреждение о использовании символа \ для экранирования

Настройка безопасности

Сделаем так, чтобы сервер PostgreSQL был виден только для сервера 1С: Предприятие, установленного на этой же машине.

listen_addresses = ‘localhost’

Если сервер 1С: Предприятие установлен на другой машине или существует необходимость подключиться подключиться к серверу СУБД с помощью оснастки PGAdmin, то вместо localhost нужно указать адрес этой машины.

Хранение базы данных

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

Останавливаем сервер

Systemctl stop postgresql-9.6

Переносим логи на из 120GB SSD:

Mv /var/lib/pgsql/9.6/data/pg_xlog /raid120 mv /var/lib/pgsql/9.6/data/pg_clog /raid120 mv /var/lib/pgsql/9.6/data/pg_log /raid120

Ln -s /raid120/pg_xlog /var/lib/pgsql/9.6/data/pg_xlog ln -s /raid120/pg_clog /var/lib/pgsql/9.6/data/pg_clog ln -s /raid120/pg_log /var/lib/pgsql/9.6/data/pg_log

Так же перенесем каталог с базами:

Mv /var/lib/pgsql/9.6/data/base /raid200

Ln -s /raid200/base /var/lib/pgsql/9.6/data/base

запустим сервер и проверим его статус

Systemctl start postgresql-9.6 systemctl status postgresql-9.6

• Tutorial

Мне хотелось создать прекрасный объемлющий мануал Getting Start без всякой воды, но включающий основные плюшки для начинающих по системе PostgreSQL в Linux.

PostgreSQL является объектно-реляционной системой управления базами данных (ОРСУБД) на основе POSTGRES, версия 4.2 , разработанной в Университете Калифорнии в Беркли департаменте компьютерных наук.

PostgreSQL является open source потомком оригинального кода Berkeley. Он поддерживает большую часть стандарта SQL и предлагает множество современных функций:

• Cложные запросы
• Управление конкурентным доступом с помощью многоверсионности

• типов данных
• функций
• операторов
• агрегатных функций
• индекс методов
• процедурных языков

Сборка и установка

Wget http://ftp.postgresql.org/pub/source/v9.2.2/postgresql-9.2.2.tar.gz tar xzf postgresql-9.2.2.tar.gz
Теперь у нас есть директория с исходниками сей прекрасной базы данных.
По умолчанию файлы базы будут установлены в директорию /usr/local/pgsql, но эту директорию можно изменить задав

Prefix=/path/to/pgsql
перед командой./configure
Перед сборкой можно указать компилятор С++

Export CC=gcc
PostgeSQL может использовать readline библиотеку, если у вас её нет и нет желания её ставить просто укажите опцию

Without-readline
Надеюсь у всех есть Autotools ? Тогда вперед к сборке:

Cd postgresql-9.2.2 ./configure --without-readline sudo make install clean
Все господа! Поздравляю!

Настройка

Есть один нюанс - владельцем директории данных и пользователь, который может запускать базу должен быть не root. Это сделано в целях безопасности системы. Поэтому создадим специального пользователя
sudo useradd postgres -p postgres -U -m
И далее все понятно

Sudo chown -R postgres:postgres /usr/local/pgsql
Важный процесс. Мы должны инициализировать кластер баз дынных. Сделать мы должны это от имени пользователя postgres

Initdb -D /usr/local/pgsql/data
Теперь нужно добавить запуск PostgreSQL в автостарт. Для этого существует уже готовый скрипт и лежит он в postgresql-9.2.2/contrib/start-scripts/linux
Этот файл можно открыть и обратить внимание на следующие переменные:

• prefix - это место куда мы ставили PostgreSQL и задавали в./configure
• PGDATA - это то, где хранится кластер баз данных и куда должен иметь доступ наш пользователь postgres
• PGUSER - это тот самый пользователь, от лица которого будет все работать

Sudo cp ./postgresql-9.2.2/contrib/start-scripts/linux /etc/init.d/postgres sudo update-rc.d postgres defaults
Перезапускам систему, чтобы проверить что наш скрипт работает.
Вводим

/usr/local/pgsql/bin/psql -U postgres
И если появится окно работы с базой, то настройка прошла успешно! Поздравляю!
По умолчанию создается база данных с именем postgres

# TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 trust host all all::1/128 trust
Первая строка отвечает за локальное соединение, вторая - за соединение про протоколу IPv4, а третья по протоколу IPv6.
Самый последний параметр - это как раз таки метод авторизации. Его и рассмотрим (только основные)

• trust - доступ к базе может получить кто угодно под любым именем, имеющий с ней соединение.
• reject - отклонить безоговорочно! Это подходит для фильтрации определенных IP адресов
• password - требует обязательного ввода пароля. Не подходит для локальных пользователей, только пользователи созданные командой CREATE USER
• ident - позволяет только пользователем зарегистрированным в файле /usr/local/pgsql/data/pg_ident.conf устанавливать соединение с базой.

Утилиты для работы с базой

pg_config

initdb

pg_ctl

psql

createdb

dropdb

createuser

dropuser

createlang

droplang

pg_dump

pg_restore

pg_dumpall

reindexdb

clusterdb

vacuumdb

Менеджеры по работе с базой

Попробуйте PostgreSQL - объектно-реляционную СУБД. Она подходит для коммерческого использования. В ней много дополнительных модулей и функций. PgSQL-синтаксис этого приложения чем-то похож на знаменитую утилиту MySQL. Если вы умеете ей пользоваться, то переход на новую площадку будет лёгким. Чтобы установка PostgreSQL Ubuntu прошла успешно, не нужно вводить много команд.

Установить и настроить СУБД PostreSQL на Ubuntu несложно — просто следуйте нашим инструкциям

В программе доступны разные методы аутентификации. Лучше выбрать IDENT или MD5. Первый стоит по умолчанию. Чтобы использовать второй:

1. Откройте файл pg_hba.conf. Он в каталоге «/etc/postgresql/[Версия программы]/main/».
2. Найдите там параметр «local all postgres» и напечатайте рядом md5. Если такой строки нет, впишите её.

Использование

Руководство по использованию можно скачать прямо из терминала. Введите в «apt-get install postgresql-doc-[Версия утилиты]». Вот некоторые важные команды:

Работа с таблицами

• Добавление таблицы - «CREATE TABLE [Название таблицы]».
• После этой команды в скобках напишите параметры сетки «[Название строки] [Тип] ([Количество позиций]) [Возможные ограничения]». Таким образом введите несколько строк, разделяя их запятыми. После каждой нажимайте Enter. У колонок могут быть разные названия и типы. Когда закончите записывать данные, закройте скобку и поставьте точку с запятой. Количество позиций тоже указывайте в скобках.
• Посмотреть содержимое таблицы - «\d». А лучше - «SELECT * FROM [Имя]»
• Удалить её - «DROP TABLE [Название]». После этого нажмите Enter и напишите «DROP TABLE» ещё раз.
• Ввести данные - «INSERT INTO [Имя сетки] (Столбец1, Столбец2, Столбец3) VALUES (‘Запись1’, ‘Запись2’, ‘Запись3’);». В столбцы будут добавлены соответствующие записи. Можете повторять команду, чтобы вписывать новую информацию
• Удалить значение - «DELETE FROM [Название таблицы] WHERE [Название столбца] = ‘[Значение]’;».
• Новый столбец - «ALTER TABLE [Имя сетки] ADD [Имя колонки]».
• Удалить столбец - «ALTER TABLE [Таблица] DROP [Название столбца]».

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

Я же опишу ниже как установить и быстро настроить PostgreSQL 10 на Debian 9

Исходные данные: Debian 9 Stretch (amd64)
Задача: Установить PostgreSQL 10.x

1. Предварительная настройка сервера:

Добавляем на сервер русскую локаль, для начала проверяем её отсутствие/присутствие командой

Locale -a | grep ru

если в ответ ничего нет, то запускаем

Dpkg-reconfigure locales

выбираем в списке локаль ru_RU.UTF-8
и жмем Yes
выбираем локаль по умолчанию en_US.UTF-8

2. Начинаем установку PostgreSQL 10:

Echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -sc)-pgdg main" > /etc/apt/sources.list.d/pgdg.list wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - apt-get update apt-get install postgresql-10 -y

Краткая справка по местоположению основных файлов PostgreSQL 10:
Местоположение баз данных:
/var/lib/postgresql/10
Местоположение логов:
/var/log/postgresql/postgresql-10-main.log
Настройка ротации логов:
/etc/logrotate.d/postgresql-common
Основные файлы конфигурации:
/etc/postgresql/10/main/postgresql.conf
/etc/postgresql/10/main/pg_hba.conf

Первым делом меняем пароль пользователя postgres:

Su - postgres psql postgres=# \password postgres postgres=# \q

3. Тюнинг настроек PostgreSQL:

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

Для PostgreSQL 10 открываем основной файл настроек

Vi /etc/postgresql/10/main/postgresql.conf

и раскомментируем строку

Listen_addresses = "localhost"

исправим её на

Listen_addresses = "localhost,192.168.100.1"

таким образом мы укажем PostgreSQL слушать сетевые соединения на интерфейсе localhost и на нашем внутреннем интерфейсе локальной сети с IP адресом 192.168.100.1

Далее смотрим на параметр max_connections , который определяет максимальное количество одновременных соединений, которые будет обслуживать сервер PostgreSQL. В принципе, это число должно определяться исходя из требований к системе. Этот параметр в большей степени влияет на использование ресурсов. Если Вы только запустили БД, устанавливайте это значение небольшим (16…32), по умолчанию он установлен 100. Постепенно можно увеличивать max_connections (по мере необходимости — такой мерой будет получение ошибок от postgresql «too many clients»).
Учтите! На поддержку каждого активного клиента, PostgreSQL тратит немалое количество ресурсов, и если Вам необходимо добиться производительности в несколько тысяч активных соединений, то стоит использовать менеджеры соединений, например: или .
Важно! Значение параметра max_wal_senders должно быть меньше max_connections, поэтому если Вы установили max_connections = 10, то max_wal_senders нужно поменять к примеру на 5

Смотрим на параметр shared_buffers
Этот параметр определяет, сколько памяти будет выделяться PostgreSQL для кеширования данных.
В стандартной поставке значение этого параметра 128 МБ, то есть по сути мизерное — для обеспечения совместимости. В практических условиях это значение следует установить в 15..25% от всей доступной оперативной памяти сервера. Учтите, слово всей доступной, то есть учитывайте память которую занимают текущие процессы и могут занять в случае роста потребления, к примеру у нас 8 ГБ ОЗУ и стоит MySQL, который в текущем состоянии занять 1 ГБ ОЗУ, а при росте количества соединений исходя из настроек может занять все 6 ГБ ОЗУ, тогда для PostgreSQL остается не так много памяти и shared_buffers никак нельзя поставить 2 ГБ. Если у Вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, Вам доступен большой объем ОЗУ или большее количество процессоров, то можно увеличивать значение shared_buffers и смотреть результат, чтобы не привести к «деградации» (падению) производительности. Выделив слишком много памяти для shared_buffers, мы можем получить ухудшение производительности, поскольку PostgreSQL также использует кэш операционной системы (увеличение данного параметра более 40% оперативной памяти может давать «нулевой» прирост производительности).

Параметр effective_cache_size
Этот параметр помогает планировщику postgresql определить количество доступной памяти для дискового кеширования. На основе того, доступна память или нет, планировщик будет делать выбор между использованием индексов и использованием сканирования таблицы. Это значение следует устанавливать в 50%…75% всей доступной оперативной памяти, в зависимости от того, сколько памяти доступно для системного кеша. Еще раз — этот параметр не влияет на выделяемые ресурсы — это оценочная информация для планировщика.

Параметры min_wal_size, max_wal_size, checkpoint_timeout, checkpoint_completion_target, wal_buffers
Существует несколько параметров конфигурации, связанных с WAL, которые влияют на производительность базы данных. На эти и некоторые другие настройки стоит обратить внимание, если у Вас происходит немалое количество записей в БД (для высоконагруженных систем это нормальная ситуация).
Более подробно следует прочитать или .

Параметр work_mem
Важный параметр для запросов, использующих всевозможные сложные выборки и сортировки. Увеличение его
позволяет выполнять эти операции в оперативной памяти, что гораздо более эффективно, чем на диске (еще бы).
Если объём памяти недостаточен для сортировки некоторого результата, то серверный процесс будет использовать временные файлы. Если же объём памяти слишком велик, то это может привести к своппингу.
Будьте внимательны! Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10 МБ для этого параметра скушает 100 МБ оперативной памяти.
Этот параметр стоит увеличивать, если у Вас большое количество памяти в распоряжении. Чем больше max_connections тем меньше должен быть work_mem. В качестве начального значения для параметра можете взять 2–4% доступной памяти. Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ. С другой стороны, приложения для поддержки принятия решений с сотнями строк в каждом запросе и десятками миллионов столбцов в таблицах фактов часто требуют work_mem порядка 500 Мб. Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии. Например, при памяти 1–4 ГБ рекомендуется устанавливать 32–128 МБ.

Параметр synchronous_commit
Обратите особое внимание на этот параметр! Он включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.
Допустим, в Вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы. Но жизненно важно обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).

Параметр maintenance_work_mem
Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию его значение - 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция, и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии, но слишком большие значения приведут к использованию свопа.
Учтите, что когда выполняется автоочистка, этот объём может быть выделен autovacuum_max_workers раз, поэтому не стоит устанавливать значение по умолчанию слишком большим. Возможно, будет лучше управлять объёмом памяти для автоочистки отдельно, изменяя autovacuum_work_mem.
Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей БД. Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ. Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 МБ.
Временное увеличение maintenance_work_mem рекомендуют для ускорения загрузки больших объёмов данных в БД. Это приведёт к увеличению быстродействия CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. На скорость самой команды COPY это не повлияет, так что этот совет будет полезен, только если вы применяете какой-либо из двух вышеописанных приёмов.

Более детально о всех параметрах или .

Давайте настроим эти параметры исходя из
— объема свободного ОЗУ в 1 ГБ;
— максимального количества одновременных соединений 10;
— количество CPU — 1;
— сервер будет выполнять задачи БД для Web-приложений;
— база будет располагаться на массиве RAID 1 из 2 дисков SATA HDD;

Max_connections = 10 shared_buffers = 256MB effective_cache_size = 768MB maintenance_work_mem = 64MB checkpoint_completion_target = 0.7 wal_buffers = 7864kB default_statistics_target = 100 random_page_cost = 4 effective_io_concurrency = 2 work_mem = 26214kB min_wal_size = 1GB max_wal_size = 2GB

Хочу заметить, что многие настройки PostgreSQL зависят не только от аппаратной конфигурации, но и от размера базы данных, числа клиентов и сложности запросов, так что оптимально настроить базу данных возможно только учитывая все параметры системы и приложения (например учитывать, SSD диски и влезает ли база в память). Для облегчения первоначальной настройки Pg существует от Алексея Васильева.

Теперь разрешим подключение из локальной сети с любых хостов и к любым БД, для этого в конец файла /etc/postgresql/10/main/pg_hba.conf добавим:

# localnet host all all 192.168.100.0/24 md5

Service postgresql restart

Проверяем открытые порты

Netstat -ltupn |grep postgre tcp 0 0 192.168.100.4:5432 0.0.0.0:* LISTEN 32658/postgres tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 32658/postgres

Отлично! Теперь мы можем подключиться к PostgreSQL c локального сервера и из нашей локальной сети.

На этом все, до скорых встреч.

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