Данная
инструкция также применима для Postgres 12.2 и SuSe 15 с соответствующими
правками по путям.
Подготовка ОС.
Необходимые пакеты
В действительности
тут прозапас. Взят список для Oracle. Но внятной инструкции для Postgres я не
нашёл. Полагаю, ему вообще достаточно пакетов по умолчанию. Но, поскольку этот
список может меняться в зависимости от дистрибутива, то пусть будет с запасом.
zypper install binutils \
gcc \
gcc48 \
glibc \
glibc-32bit \
glibc-devel \
glibc-devel-32bit \
libaio1 \
libaio-devel \
libcap1 \
libstdc++48-devel \
libstdc++48-devel-32bit \
libstdc++6 \
libstdc++6-32bit \
libstdc++-devel \
libstdc++-devel-32bit \
libgcc_s1 \
libgcc_s1-32bit \
mksh \
make \
sysstat \
readline-devel \
zlib-devel
Настройка параметров ядра
vi
/etc/sysctl.conf
# Конфигурация для RAM = 4GB
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.hung_task_timeout_secs = 600
kernel.sem = 250 32000 100 128
kernel.shmall = 1011681
kernel.shmmax = 2071922688
kernel.shmmni = 4096
kernel.sysrq = 0
kernel.watchdog = 1
kernel.watchdog_thresh = 60
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.ip_forward = 0
net.ipv4.ip_local_port_range = 9000 65500
net.ipv4.tcp_syncookies = 1
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.all.forwarding = 0
vm.dirty_expire_centisecs=500
vm.dirty_writeback_centisecs=100
vm.swappiness=1
vm.dirty_background_ratio=3
vm.dirty_ratio=15
Лимиты
Обычно
устанавливаем лимиты для пользователя postgres в размер памяти минус 1 ГБ для
работы ОС (4 - 1 = 3 в данном примере). Для систем с размером памяти более 16
ГБ можно оставить 2 ГБ под ОС, остальное под postgres.
Добавляем в
/etc/security/limits.conf следующий блок параметров
# PostgreSQL
postgres soft nproc 2047
postgres hard nproc 16384
postgres soft nofile 4096
postgres hard nofile 65536
postgres soft stack 10240
postgres hard stack 32768
postgres soft memlock 3145728
postgres hard memlock 3145728
Параметры монтирования
В /etc/fstab
добавляем следующую строк. Также с размером в размер памяти минус 1-2 ГБ.
none /dev/shm tmpfs defaults,size=3G 0 0
Перезагрузка
shutdown -r now
Создание пользователя и необходимых
директорий
mkdir -p /u01/postgres/12.1/pgsql/data
useradd --base-dir=/u01 --user-group --shell=/bin/bash --home-dir=/u01/postgres --create-home postgres
chown -R postgres:postgres /u01
passwd postgres
Сборка Postgres 12.1 из исходников
На сервер в
папку /u01/distr копируется дистрибутив postgresql-12.1.tar.gz
cd /u01/distr
tar xvzf postgresql-12.1.tar.gz
cd ./postgresql-12.1
./configure --prefix=/u01/postgres/12.1/pgsql
make -C /u01/distr/postgresql-12.1 world
make -C /u01/distr/postgresql-12.1 install-world
chown -R postgres:postgres /u01/postgres
Создание сервиса systemd
Создаём файл
/etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
User=postgres
ExecStart=/u01/postgres/12.1/pgsql/bin/postgres -D /u01/postgres/12.1/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
Права и
автозагрузка.
chmod 755 postgresql.service
systemctl enable postgresql
Профиль пользователя postgres
В файл
/u01/postgres/.profile добавить следующие переменные
export LD_LIBRARY_PATH=/u01/postgres/12.1/pgsql/lib
export PATH=/u01/postgres/12.1/pgsql/bin:$PATH
export MANPATH=/u01/postgres/12.1/pgsql/share/man:$MANPATH
export PGDATA=/u01/postgres/12.1/pgsql/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=5432
Инициализация и запуск кластера
Для больших
систем можно сделать больший размер wal-лога. Для небольших оставить по
умолчанию (16 МБ).
# Для больших баз
initdb --data-checksums --wal-segsize=256 --pgdata=/u01/postgres/12.1/pgsql/data --encoding=utf8 --username=postgres --pwprompt
# Для небольших баз
initdb --data-checksums --pgdata=/u01/postgres/12.1/pgsql/data --encoding=utf8 --username=postgres --pwprompt
Конфиругирование параметров
экземпляра.
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# Other connections
host all all 0.0.0.0/0 md5
postgresql.conf
Ниже
приводятся только изменённые параметры
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 1000 # (change requires restart)
superuser_reserved_connections = 10 # (change requires restart)
authentication_timeout = 10s # 1s-600s
# - Memory -
shared_buffers = 2GB # min 128kB
# (change requires restart)
huge_pages = off # on, off, or try
# (change requires restart)
temp_buffers = 32MB # min 800kB
work_mem = 64MB # min 64kB
maintenance_work_mem = 64MB # min 1MB
# - Settings -
wal_level = replica # minimal, replica, or logical
# (change requires restart)
fsync = on # flush data to disk for crash safety
# (turning this off can cause
# unrecoverable data corruption)
wal_sync_method = fsync
max_wal_size = 4GB
min_wal_size = 512MB
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/%f && cp %p /u01/postgres/12.1/pgsql/pg_wal_archive/%f' # command to use to archive a logfile segment
restore_command = 'cp /u01/postgres/12.1/pgsql/pg_wal_archive/%f %p' # command to use to restore an archived logfile segment
random_page_cost = 3.0 # same scale as above
hot_standby = on
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
# can include strftime() escapes
log_file_mode = 0600 # creation mode for log files,
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 100MB # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
log_min_messages = info
log_min_error_statement = error
log_checkpoints = on
log_lock_waits = off # log lock waits >= deadlock_timeout
log_statement = 'ddl' # none, ddl, mod, all
log_replication_commands = on
log_timezone = 'Europe/Moscow'
# - Query and Index Statistics Collector -
track_activities = on
track_counts = on
track_io_timing = on
autovacuum = on # Enable autovacuum subprocess? 'on'
autovacuum_vacuum_scale_factor = 0.02 # fraction of table size before vacuum
autovacuum_analyze_scale_factor = 0.01 # fraction of table size before analyze
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
timezone = 'Europe/Moscow'
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Shared Library Preloading -
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
Настройка регулярных заданий
(crontab)
Используются
три основных скрипта.
# Postgres pg_dump
# Необходимо добавить в скрипт в PG_DATABASES список баз данных, для которых необходим дамп
0 23 * * * /u01/postgres/scripts/backup_pg_dump.sh
# Резервная копия средствами pg_basebackup
20 23 * * * /u01/postgres/scripts/backup_pg_basebackup.sh
# Удаление старых логов Posgres
0 * * * * /u01/postgres/scripts/remove_old_pg_logs.sh
Создание
необходимых директорий
mkdir /u01/postgres/scripts
mkdir -p /u01/postgres/backup/pg_dump
mkdir -p /u01/postgres/backup/pg_basebackup
/u01/postgres/scripts/backup_pg_dump.sh
В
PG_DATABASES необходимо добавлять через пробел список баз данных, для которых
требуется создавать дамп
#!/bin/bash
source /u01/postgres/.profile
PG_DATABASES=(postgres mse)
BACKUP_PATH=/u01/postgres/backup/pg_dump
DUMP_FILE=$BACKUP_PATH/dump_`date +%Y%m%d`.dump
LOG_FILE=$BACKUP_PATH/dump_`date +%Y%m%d`.log
for PG_DB_NAME in ${PG_DATABASES[*]} do
DUMP_FILE=$BACKUP_PATH/dump_${PG_DB_NAME}_`date +%Y%m%d`.dump LOG_FILE=$BACKUP_PATH/dump_${PG_DB_NAME}_`date +%Y%m%d`.log date &> ${LOG_FILE} echo "Dumping database ${PG_DB_NAME}" &>> ${LOG_FILE} echo &>> ${LOG_FILE} pg_dump -v -F c -f ${DUMP_FILE} ${PG_DB_NAME} &>> ${LOG_FILE} done;
find ${BACKUP_PATH} -name "*.dump" -type f -mtime +3 -printfind ${BACKUP_PATH} -name "*.dump" -type f -mtime +3 -delete
/u01/postgres/scripts/backup_pg_basebackup.sh
#!/bin/bash
source /u01/postgres/.profile
BACKUP_PATH=/u01/postgres/backup/pg_basebackup
BACKUP_SUBPATH=`date +%Y%m%d`
LOG_FILE=$BACKUP_PATH/base_backup_`date +%Y%m%d`.log
mkdir ${BACKUP_PATH}/${BACKUP_SUBPATH}date &> ${LOG_FILE}echo "Making base backup of ${PGDATA}" &>> ${LOG_FILE}echo &>> ${LOG_FILE}
pg_basebackup -D ${BACKUP_PATH}/${BACKUP_SUBPATH} --username postgres --wal-method=fetch --gzip --format=tar --no-verify-checksums
find ${BACKUP_PATH}/* -name "*.tar.gz" -type f -mtime +7 -printfind ${BACKUP_PATH}/* -name "*.tar.gz" -type f -mtime +7 -delete
/u01/postgres/scripts/remove_old_pg_logs.sh
#!/bin/bash
source /u01/postgres/.profile
find $PGDATA/log -name "*.log" -type f -mtime +3 -delete
Создание пользовательской базы
данных
Всячески
призываю всех создавать отдельные базы данных и отдельные схемы под приложения.
Практика
размещения пользовательских данных в схеме public создаёт проблемы с
безопасностью, т.к. в public имеют доступ все.
# В psql
postgres@postgres=# create user covidmob with encrypted password '******';
postgres@postgres=# CREATE DATABASE covidmob OWNER covidmob;
postgres@postgres=# \c covidmob postgres
You are now connected to database "covidmob" as user "postgres".
postgres@covidmob=# create extension pg_stat_statements;
postgres@covidmob=# create schema covidmob AUTHORIZATION covidmob;
postgres@covidmob=# GRANT CONNECT ON DATABASE covidmob to covidmob;
postgres@covidmob=# GRANT USAGE ON SCHEMA covidmob to covidmob;
