Настройка 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_14.7_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_14.7_1.1C_amd64_deb.tar.bz2 kit@192.168.122.123:/home/kit/psql/postgresql_14.7_1.1C_amd64_deb.tar.bz2

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

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

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

tar xfvj ~/psql/postgresql_14.7_1.1C_amd64_deb.tar.bz2 -C ~/psql/

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

sudo dpkg -i ~/psql/postgresql-14.7-1.1C_amd64_deb/libpq5_14.7-1.1C_amd64.deb
sudo dpkg -i ~/psql/postgresql-14.7-1.1C_amd64_deb/postgresql-client-14_14.7-1.1C_amd64.deb
sudo dpkg -i ~/psql/postgresql-14.7-1.1C_amd64_deb/postgresql-14_14.7-1.1C_amd64.deb

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

sudo apt -f install

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

sudo dpkg -l | grep 14.7-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@14-main.service

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

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

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

/usr/lib/postgresql/14/bin/initdb -D /var/lib/postgresql/14/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/14/main/postgresql.conf

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

sudo systemctl start postgresql@14-main.service

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

sudo systemctl status postgresql@14-main.service

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

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

Свежие версии Postgres с патчами 1С (releases.1c.ru)

Свежие версии Postgres, совместимых с 1С, бесплатно (postgres.ru)

Свежие версии Postgres Pro Standart & Enterprise, совместимых с 1С, лицензируется (postgrespro.ru)

Документация 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)