Методичка: PostgreSQL

Материал из Записи-напоминании
Перейти к навигации Перейти к поиску

Конфигурация системы

Организация объектов файловой системы

mkdir -p /home/postgres/{archive,backup,tablespace} /home/postgres/{cluster,wal}/$MAJOR_VERSION

chown -R postgres:postgres /home/postgres

Директория Назначение Имеет поддиректорию
$MAJOR_VERSION		 Применяемые
права доступа
$HOME/archive Долгосрочный архив копий баз данных Нет chmod 0750
$HOME/backup Осуществление систематического резервного копирования баз данных Нет chmod 0750
$HOME/cluster Головная директория данных кластера Да chmod 0700
$HOME/{,lvm[0-9]/}tablespace Директория табличных пространств Нет chmod 0700
$HOME/{,lvm[0-9]/}wal Директория содержит по средством символической ссылки вынесенную из директории кластера поддиректорию pg_wal Да chmod 0700
/var/socket/postgresql Директория подключения к СУБД посредством unix-сокета
Доступ к директории имеет только СУБД и приложение, подключаемое к СУБД 		Да chown postgres:postgres
chmod 0700
setfacl -m u:$user_name:rwx 
setfacl -d -m u:$user_name:rwx 
Файл Назначение Применяемые
права доступа
$HOME/.pgpass Содержание паролей подключения к базам данных chmod 0600

Подсистема управления службами

Основной юнит systemd запуска службы СУБД

Имя юнита: postgresql-$MAJOR_VERSION.service 

[Unit]
Description=PostgreSQL $MAJOR_VERSION database server
#Requires=home-postgres-cluster-$MAJOR_VERSION-pg_stat_tmp.mount
#After=home-postgres-cluster-$MAJOR_VERSION-pg_stat_tmp.mount
After=network.target

[Service]
Type=forking
OOMScoreAdjust=-1000
TimeoutStartSec=0
TimeoutStopSec=1h

User=postgres
Group=postgres
Environment=PGDATA=/home/postgres/cluster/$MAJOR_VERSION

ExecStart=/usr/lib/postgresql/$MAJOR_VERSION/bin/pg_ctl start -D ${PGDATA} -s
ExecStop=/usr/lib/postgresql/$MAJOR_VERSION/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/lib/postgresql/$MAJOR_VERSION/bin/pg_ctl reload -D ${PGDATA} -s

[Install]
WantedBy=multi-user.target


Вспомогательный юнит systemd монтирования директории /home/postgres/cluster/$MAJOR_VERSION/pg_stat_tmp в tmpfs

Начиная с PostgreSQL 15 версии статистика сохраняется в shared memory

Имя юнита: home-postgres-cluster-$MAJOR_VERSION-pg_stat_tmp.mount 

[Unit]
Description=PostgreSQL $MAJOR_VERSION (pg_stat_tmp mount)
DefaultDependencies=no
Conflicts=umount.target
Before=local-fs.target

[Mount]
What=tmpfs
Where=/home/postgres/cluster/$MAJOR_VERSION/pg_stat_tmp
Type=tmpfs
Options=size=1G,mode=0700,uid=postgres,gid=postgres,nosuid,noexec,nodev

[Install]
WantedBy=local-fs.target

Конфигурация кластера

Методичка от 1С

.pgpass 

//host_name:tcp_port:base_name:role_name:password
*:*:*:dbuser0:password

Переменная окружения PGPASSFILE переопределяет путь файла

pg_hba.conf 

//type {local,host}, database, role, address, method {trust,md5}
//local all postgres trust
//local all all md5
local all dbuser0 md5
host all all 127.0.0.1/32 md5

postgresql.conf 

listen_addresses = '127.0.0.1'
unix_socket_directories = '/var/socket/postgresql/$MAJOR_VERSION'

shared_buffers = 4GB
temp_buffers = 16MB
work_mem = 128MB
maintenance_work_mem = 1GB
max_files_per_process = 8000
bgwriter_delay = 1000ms
bgwriter_lru_maxpages = 500
bgwriter_lru_multiplier = 4.0
effective_io_concurrency = {1,100} //{HDD,SSD}
max_worker_processes = //кол-во ядер
max_parallel_maintenance_workers = //кол-во парралельных процессов на один узел / одну служебную команду
max_parallel_workers_per_gather = max_parallel_maintenance_workers
max_parallel_workers = max_worker_processes

wal_level = minimal
fsync = off
synchronous_commit = off

checkpoint_timeout = 10min
max_wal_size = 2GB
min_wal_size = 512MB
max_wal_senders = 0

max_logical_replication_workers = 0

effective_cache_size = RAM - shared_buffers

log_timezone = 'Europe/Astrakhan'

seq_page_cost = {1.0,0.5,0.1} //{HDD,SSD,NVME}
random_page_cost = {2.0,1.0,0.1} //{HDD,SSD,NVME}

autovacuum = on
autovacuum_worker_slots = 4
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_cost_delay = 10ms

row_security = off
timezone = 'Europe/Astrakhan'
max_locks_per_transaction = 2000
escape_string_warning = off
standard_conforming_strings = off

local_number=$( grep -E -n "^#temp_buffers" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\temp_buffers = 16MB" postgresql.conf

local_number=$( grep -E -n "^#work_mem" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\work_mem = 128MB" postgresql.conf

local_number=$( grep -E -n "^#maintenance_work_mem" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\maintenance_work_mem = 1GB" postgresql.conf

local_number=$( grep -E -n "^#max_worker_processes" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\max_worker_processes = 2" postgresql.conf

local_number=$( grep -E -n "^#wal_level" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\wal_level = minimal" postgresql.conf

local_number=$( grep -E -n "^#fsync" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\fsync = off" postgresql.conf

local_number=$( grep -E -n "^#synchronous_commit" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\synchronous_commit = off" postgresql.conf

local_number=$( grep -E -n "^#max_wal_senders" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\max_wal_senders = 0" postgresql.conf

local_number=$( grep -E -n "^#row_security" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\row_security = off" postgresql.conf

local_number=$( grep -E -n "^#escape_string_warning" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\escape_string_warning = off" postgresql.conf

local_number=$( grep -E -n "^#standard_conforming_strings" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\standard_conforming_strings = off" postgresql.conf

local_number=$( grep -E -n "^#max_locks_per_transaction" postgresql.conf | cut -d ":" -f 1 )
sed -i "${local_number}i\max_locks_per_transaction = 2000" postgresql.conf

Связанные разделы

Основной раздел по PostgreSQL

Источник — https://adm30.ru/index.php?title=Методичка:_PostgreSQL&oldid=3054