Настройка PostgreSQL сервера для 1С

Подключаемся к виртуальной машине по ssh

# Будет запрошен пароль дополнительного шифрования ключа ssh
ssh -i ~/.ssh/auth_keys_kit kit@192.168.122.123

Создаём каталог, в который будем копировать файлы с локального хоста

mkdir ~/psql -p

Отключаемся от виртуальной машины

exit

Открываем страницу с дистрибутивами PostgreSQL на сайте 1С, спускаемся в самый конец страницы

Релизы PostgreSQL от 1С (releases.1c.ru)

Сохраняем файл "Зависимости postgres для Ubuntu 20.04-server x86_64 от 30.12.2022" в каталог ~/Downloads/psql/

Проверяем наличие файла ubuntu20.x86_64.tar.gz

ls -l ~/Downloads/psql

Копируем файлы с зависимостями PostgreSQL на виртуальную машину

# Синтаксис команды, копируем туда: scp path/myfile kit@192.168.122.123:/full/path/to/new/location/
# Синтаксис команды, копируем обратно: scp kit@192.168.122.123:/full/path/to/file /path/to/put/here
# Будет запрошен пароль дополнительного шифрования ключа ssh
scp -i ~/.ssh/auth_keys_kit ~/Downloads/psql/ubuntu20.x86_64.tar.gz kit@192.168.122.123:/home/kit/psql/ubuntu20.x86_64.tar.gz

Подключаемся к виртуальной машине по ssh

# Будет запрошен пароль дополнительного шифрования ключа ssh
ssh -i ~/.ssh/auth_keys_kit kit@192.168.122.123

Распаковываем архив с зависимостями PostgreSQL

tar xf ~/psql/ubuntu20.x86_64.tar.gz -C ~/psql/

Устанавливаем зависимости от нужной версии PostgreSQL: 10, 11, 12, 13, 14, или 15

sudo dpkg -i ~/psql/libs10/*

sudo dpkg -i ~/psql/libs11/*

sudo dpkg -i ~/psql/libs12/*

sudo dpkg -i ~/psql/libs13/*

sudo dpkg -i ~/psql/libs14/*

sudo dpkg -i ~/psql/libs15/*

Подключаемся к виртуальной машине по ssh

# Будет запрошен пароль дополнительного шифрования ключа ssh
ssh -i ~/.ssh/auth_keys_kit kit@192.168.122.123

Создаём каталог, в который будем копировать файлы с локального хоста

mkdir ~/psql -p

Устанавливаем libjson-perl, ssl-cert, libllvm6.0

sudo apt install libjson-perl
sudo apt install ssl-cert
sudo apt install libllvm6.0

Устанавливаем libicu60 из репозиториев Ubuntu 18.04 LTS

# https://launchpad.net/ubuntu/bionic/amd64/libicu60/60.2-3ubuntu3.2
curl http://launchpadlibrarian.net/565584693/libicu60_60.2-3ubuntu3.2_amd64.deb --output ~/psql/libicu60_60.2-3ubuntu3.2_amd64.deb
sudo dpkg -i ~/psql/libicu60_60.2-3ubuntu3.2_amd64.deb

Устанавливаем postgresql-client-common 241 из репозиториев Ubuntu 22.10

# https://launchpad.net/ubuntu/kinetic/amd64/postgresql-client-common/241
curl http://launchpadlibrarian.net/600899938/postgresql-client-common_241_all.deb --output ~/psql/postgresql-client-common_241_all.deb
sudo dpkg -i ~/psql/postgresql-client-common_241_all.deb

Устанавливаем postgresql-common 241 из репозиториев Ubuntu 22.10

# https://launchpad.net/ubuntu/kinetic/amd64/postgresql-common/241
curl http://launchpadlibrarian.net/600899943/postgresql-common_241_all.deb --output ~/psql/postgresql-common_241_all.deb
sudo dpkg -i ~/psql/postgresql-common_241_all.deb

Устанавливаем libreadline7 из репозиториев Debian 10

# https://packages.debian.org/buster/amd64/libreadline7/download
curl http://ftp.ru.debian.org/debian/pool/main/r/readline/libreadline7_7.0-5_amd64.deb --output ~/psql/libreadline7_7.0-5_amd64.deb
sudo dpkg -i ~/psql/libreadline7_7.0-5_amd64.deb

Отключаемся от виртуальной машины

exit

Открываем страницу с дистрибутивами PostgreSQL на сайте 1С

Релизы PostgreSQL от 1С (releases.1c.ru)

Сохраняем файл "Дистрибутив СУБД PostgreSQL для Linux x86 (64-bit) одним архивом (DEB)" в каталог ~/Downloads/psql/

Проверяем наличие файла postgresql_15.2_1.1C_amd64_deb.tar.bz2

ls -l ~/Downloads/psql

Копируем файлы PostgreSQL на виртуальную машину

# Синтаксис команды, копируем туда: scp path/myfile kit@192.168.122.123:/full/path/to/new/location/
# Синтаксис команды, копируем обратно: scp kit@192.168.122.123:/full/path/to/file /path/to/put/here
# Будет запрошен пароль дополнительного шифрования ключа ssh
scp -i ~/.ssh/auth_keys_kit ~/Downloads/psql/postgresql_15.2_1.1C_amd64_deb.tar.bz2 kit@192.168.122.123:/home/kit/psql/postgresql_15.2_1.1C_amd64_deb.tar.bz2

Подключаемся к виртуальной машине по ssh

# Будет запрошен пароль дополнительного шифрования ключа ssh
ssh -i ~/.ssh/auth_keys_kit kit@192.168.122.123

Распаковываем архив PostgreSQL

tar xfvj ~/psql/postgresql_15.2_1.1C_amd64_deb.tar.bz2 -C ~/psql/

Устанавливаем основные пакеты PostgreSQL от 1С, в указанном порядке

sudo dpkg -i ~/psql/postgresql-15.2-1.1C_amd64_deb/libpq5_15.2-1.1C_amd64.deb
sudo dpkg -i ~/psql/postgresql-15.2-1.1C_amd64_deb/postgresql-client-15_15.2-1.1C_amd64.deb
sudo dpkg -i ~/psql/postgresql-15.2-1.1C_amd64_deb/postgresql-15_15.2-1.1C_amd64.deb

Устанавливаем отсутствующие в системе зависимости пакетов, для автоматической настройки postgresql-common и postgresql-client-15

sudo apt -f install

Отмечаем пакеты PostgreSQL как установленные вручную, чтобы они не обновлялись автоматически

sudo dpkg -l | grep 15.2-1.1C | awk -F' ' '{print $2}' | sudo xargs apt-mark hold
sudo apt-mark hold postgresql-common postgresql-client-common

Указываем пароль для пользователя postgres (это пользователь системы, он создаётся автоматически, под ним запускается кластер)

sudo passwd postgres

Указываем мастер-пароль суперпользователя postgres для сервера PostgreSQL (он используется для управления базами данных в утилитах PostgreSQL, и при добавлении новой базы через диалог создания новой базы 1С)

sudo su - postgres
psql
\password postgres
exit

Отключаемся от виртуальной машины

exit

Подключаемся к виртуальной машине по ssh

# Будет запрошен пароль дополнительного шифрования ключа ssh
ssh -i ~/.ssh/auth_keys_kit kit@192.168.122.123

Узнаём текущую локаль

locale

Если используемая локаль ru_RU.UTF-8, и на момент установки была она же - то дальше можно ничего не делать, если только мы не хотим изменить каталог с данными кластера PostgreSQL

Находим имя службы PostgreSQL

systemctl --type=service | grep postgres

Останавливаем службу PostgreSQL

sudo systemctl stop postgresql@15-main.service

Удаляем файлы автоматически созданного при установке кластера

su - postgres
rm -r /var/lib/postgresql/15/main/*

Инициализируем новый кластер для 1С с нужной локалью

/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/main/ --locale=ru_RU.UTF-8 --lc-collate=ru_RU.UTF-8 --lc-ctype=ru_RU.UTF-8 --encoding=UTF8

Указываем каталог PGDATA, в котором хотим хранить данные PostgreSQL

Каталог указывается в параметре data_directory файла postgresql.conf

sudo nano /var/lib/postgresql/15/main/postgresql.conf

Запускаем службу PostgreSQL

sudo systemctl start postgresql@15-main.service

Проверяем статус службы PostgreSQL

sudo systemctl status postgresql@15-main.service

Отключаемся от виртуальной машины

exit

Добавляем в систему репозитории PostgreSQL 15 Pro

Оригинал инструкции: Получить сборку СУБД PostgreSQL Pro для 1C

wget https://repo.postgrespro.ru/1c-15/keys/pgpro-repo-add.sh

sh pgpro-repo-add.sh

Устанавливаем кластер с автоматической инициализацией, если это будет единственный Postgres

sudo apt install postgrespro-1c-15

Устанавливаем кластер с ручной инициализацией, если будет несколько Postgres, и мы хотим, чтобы они работали параллельно

sudo apt install postgrespro-1c-15-contrib

/opt/pgpro/1c-15/bin/pg-setup initdb

/opt/pgpro/1c-15/bin/pg-setup service enable

/opt/pgpro/1c-15/bin/pg-setup service start

Подключаемся к виртуальной машине по ssh

# Будет запрошен пароль дополнительного шифрования ключа ssh
ssh -i ~/.ssh/auth_keys_kit kit@192.168.122.123

Открываем стандартный порт PostgreSQL - 5432

sudo ufw allow 5432

Настраиваем сетевые права доступа к серверу PostgreSQL

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

Редактируем pg_hba.conf

cd /etc/postgresql/15/main/

sudo nano pg_hba.conf

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

host       all       0.0.0.0/0       trust

Редактируем postgresql.conf

cd /etc/postgresql/15/main/

sudo nano postgresql.conf

Меняем строку [#listen_addresses = 'localhost'] вот на эту:

listen_addresses = '*'

После чего перезапускаем сервер PostgreSQL

sudo systemctl restart postgresql@15-main.service

И проверяем, что PostgreSQL действительно ожидает соединение со всех адресов, об этом скажет вывод с адресом 0.0.0.0:5432

sudo netstat -pant | grep postgres

Создаём ссылку на сокет Unix

sudo ln -s /tmp/.s.PGSQL.5432 /var/run/postgresql/.s.PGSQL.5432

Если не настроить ссылку, то возможна ошибка: ConnectionBad could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Отключаемся от виртуальной машины

exit

Создаём резервную копию одной базы данных PostgreSQL в виде дампа SQL, упаковываем её в архив .gz

su - postgres
cd /etc/postgresql/15/bin/
EXPORT path_backup = /mnt/psql_backup
EXPORT dbname = name-base-psql
pg_dump  | gzip > //-$(date "+%Y-%m-%d").psqldump.gz

Настраиваем создание резервных копий всех баз данных PostgreSQL в виде дампов SQL, упаковываем их в архив .gz

Назначение прав и владельца на каталог для резервных копий

sudo chmod -R 7777 /mnt/pgsql_backup
sudo chown postgres:postgres /mnt/pgsql_backup/

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

sudo mkdir /etc/postgresql/15/main/scripts/
sudo nano /etc/postgresql/15/main/scripts/postgresql_dump.sh

Содержимое скрипта создания резервных копий баз данных PostgreSQL

#!/bin/bash
PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin
path_backup=/mnt/pgsql_backup
find  \( -name "*-1[^5].*" -o -name "*-[023]?.*" \) -ctime +61 -delete
for dbname in `echo "SELECT datname FROM pg_database;" | psql | tail -n +3 | head -n -2 | egrep -v 'template0|template1|postgres'`; do
pg_dump  | gzip > //-$(date "+%Y-%m-%d").psqldump.gz
done;

Назначение владельца для файла скрипта, установка признака исполняемого файла

sudo chown postgres:postgres /etc/postgresql/15/main/scripts/postgresql_dump.sh
sudo chmod +x /etc/postgresql/15/main/scripts/postgresql_dump.sh

Настройка автозапуска скрипта с помощью планировщика заданий crontab

sudo crontab -e -u postgres

Помещаем в скрипт планировщика эту строку

Синтаксис строки: минута час день месяц день-недели /путь-к-исполняемому-файлу

В этом примере запуск происходит в 03:00, каждый день

0 3 * * * /etc/postgresql/15/main/scripts/postgresql_dump.sh

В этом примере запуск происходит каждые 3 часа

0 */3 * * * /etc/postgresql/15/main/scripts/postgresql_dump.sh

Для проверки работоспособности запускаем скрипт вручную

sudo -u postgres "/etc/postgresql/15/main/scripts/postgresql_dump.sh"

Удаляем базу данных PostgreSQL

su - postgres
cd /etc/postgresql/15/bin/
psql
\l
DROP DATABASE name-base-psql;
\q
exit

Восстанавливаем базу данных PostgreSQL

su - postgres
cd /etc/postgresql/15/bin/

В переменной PGPASSWORD указывается мастер-пароль суперпользователя postgres, чтобы не вводить его каждый раз

EXPORT var PGPASSWORD=psw
EXPORT path_backup = /mnt/psql_backup
EXPORT dbname = name-base-psql
EXPORT date_restore = 2023-07-07

Создание новой базы данных, при необходимости

createdb name-base-psql

Извлечение дампа из архива

tar xf //-.psqldump.gz -C //

Восстановление базы данных из разархивированного дампа

pg_restore --host 127.0.0.1 --username postgres --dbname name-base-psql --clean --if-exists //-.psqldump

Устанавливаем DBeaver из пакета DEB (вариант 1)

mkdir ~/psql -p

curl https://dbeaver.io/files/23.3.0/dbeaver-ce_23.3.0_amd64.deb --output ~/psql/dbeaver-ce_23.3.0_amd64.deb

sudo dpkg -i ~/psql/dbeaver-ce_23.3.0_amd64.deb

dbeaver-ce

Устанавливаем DBeaver с помощью пакетного менеджера Snap (вариант 2)

sudo apt install snapd

sudo snap install dbeaver-ce

snap run dbeaver-ce

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

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

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

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

Увеличение количества дисков в RAID-массиве и объема кеша RAID-контроллера позволяет компенсировать снижение производительности, обусловленное включением параметра fsync.

Рекомендуется отключить контроль на уровне записей.

row_security = off

Выключение шифрования, которое может приводить к увеличению загрузки CPU.

ssl = off

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

shared_buffers = RAM/4

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

Это рекомендательное значение используется оптимизатором: он оценивает размер памяти для выполнения запроса, и, если это значение больше work_mem, запрос будет выполняться с использованием временных таблиц (для промежуточных результатов, сортировки, группировки).

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

select sum(temp_files) as temp_files, pg_size_pretty(sum(temp_bytes)) as temp_size from pg_stat_database;

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

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

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

Не менее половины объема оперативной памяти, установленной на компьютере

effective_cache_size = RAM - shared_buffers

Максимальное количество страниц для временных таблиц - верхний лимит размера временных таблиц в каждой сессии. Стоит рассчитывать исходя из объема памяти на сервере и количества одновременно установленных соединений. Чем больше соединений и меньше объем памяти, тем меньше стоит ставить temp_buffers, но не стремиться ставить его в сторону большего значения.

temp_buffers = 32..256MB

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

standard_conforming_strings = off

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

escape_string_warning = off

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

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

max_locks_per_transaction = 512…2000

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

Нужно понимать, что для обычной работы нужно подобрать такое число соединений, которое соответствует числу сеансов в кластере серверов 1С:Предприятие примерно в такой пропорции: от max_connections = <число сеансов> / 8 до примерно max_connections = <число сеансов> / 4.

max_connections = 500..8000

Документация PostgresPro & PostgresPro на русском языке (postgrespro.ru)

Настройки PostgreSQL для работы с 1С:Предприятием. Часть 1 (its.1c.ru)

Настройки PostgreSQL для работы с 1С:Предприятием. Часть 2 (its.1c.ru)

Утилита расчета оптимальных настроек PostgreSQL (pgtune, leopard.in.ua)

Способы диагностики PostgreSQL (2015 год, доклад с PG Day, habrahabr.ru)

Держи данные в тепле, транзакции в холоде, а VACUUM в голоде (infostart.ru)

Настройка параметров PostgreSQL для оптимизации производительности (2019 год, habrahabr.ru)

Немного о конфигурировании PostgreSQL (2015 год, infostart.ru)

dbeaver - IDE для разработки на PostgreSQL (dbeaver.io)

PGAdmin - утилита администрирования PostgreSQL (pgadmin.org)

Топ инструментов разработки для PostgreSQL (habr.com)

Чем PostgreSQL может быть полезен разработчику 1С (infostart.ru)

Настройка непрерывного архивирования в PostgreSQL 9.6 (kb.1c.ru)

Настройка непрерывного архивирования в PostgreSQL 12, восстановление на любой момент времени (infostart.ru)

Настройка непрерывного архивирования в PostgreSQL, восстановление на любой момент времени (habr.com)

Передача баз PostgreSQL на удалённый сервер резервных копий, удобное восстановление (infostart.ru)

pgbasebackup (postgrespro.ru)

pgdump (postgrespro.ru)

pgrestore (postgrespro.ru)