Типовая конфигурация Postgres 12.1 Разворачивание Postgres 12.1 под SuSe 12.3

 Данная инструкция также применима для 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 -print

find ${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 -print

find ${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;