Типовая конфигурация 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;

Обновление Postgres 12.1

 

Подготовительные действия:
1. Выключаем экземпляр БД 12.1 предварительно посмотрев параметр

SHOW wal_segment_size;

2. Кладем в /u01/distr дистрибутив 13.2

3. Ставим по инструкции как написано в тут
Но не делаем шаги:
- Необходимые пакеты
- Настройка параметров ядра
- Лимиты
- Параметры монтирования

4. Что делаем но с правками: - Создание необходимых директорий

mkdir -p /u01/postgres/13.2/pgsql/data

chown -R postgres:postgres /u01/postgres/13.2/

5. Cборка Postgres 13.2 из исходников

cd /u01/distr

tar xvzf postgresql-13.2.tar.gz

cd ./postgresql-13.2

./configure --prefix=/u01/postgres/13.2/pgsql 

make -C /u01/distr/postgresql-13.2 world

make -C /u01/distr/postgresql-13.2 install-world

chown -R postgres:postgres /u01/postgres/13.2/

6. Правка сервиса systemd
vim /etc/systemd/system/postgresql.service

[Unit]

Description=PostgreSQL database server

Documentation=man:postgres(1)

[Service]

User=postgres

ExecStart=/u01/postgres/13.2/pgsql/bin/postgres -D /u01/postgres/13.2/pgsql/data

ExecReload=/bin/kill -HUP $MAINPID

KillMode=mixed

KillSignal=SIGINT

TimeoutSec=0

[Install]

WantedBy=multi-user.target

7. Перечитаем конфигурацию systemd Обязательно

systemctl daemon-reload

8. Правка профиля пользователя postgres
Здесь важно не забыть поменять пути до bin постгресовых
иначе кластер будет иницилизироваться старой версии но по новому пути
вобщем меняем везде
vim  /u01/postgres/.profile

export LD_LIBRARY_PATH=/u01/postgres/13.2/pgsql/lib

export PATH=/u01/postgres/13.2/pgsql/bin:$PATH

export MANPATH=/u01/postgres/13.2/pgsql/share/man:$MANPATH

export PGDATA=/u01/postgres/13.2/pgsql/data

export PGDATABASE=postgres

export PGUSER=postgres

export PGPORT=5432

9. Инициализация и запуск кластера  делаем из под su -l postgres

Проверяем версию initdb

initdb --version

если ответ 13.2 все ок можно инициализировать (ну или на какую версию вы обновляетесь)

Для того, что бы понять какой у нас размер wal сегмента мы ранее выполняли команду

Далее либо:

Для больших систем можно сделать больший размер wal-лога. Для небольших оставить по умолчанию (16 МБ).

# Для небольших баз

initdb --data-checksums  --pgdata=/u01/postgres/13.2/pgsql/data --encoding=utf8 --username=postgres --pwprompt

# Для больших баз

initdb --data-checksums --wal-segsize=256 --pgdata=/u01/postgres/13.2/pgsql/data --encoding=utf8 --username=postgres --pwprompt

С подготовлениями закончили, теперь само обновление.
Обновление на 13.2 с копированием содержимого data в новый экземляр *(дальше будет пояснение)
Оба экземпляра БД 12.1 и 13.2 должны быть выключены

В режиме check проверяем все ли у нас ок.
Везде ответы должны быть, что ок. Если что то не так, обязательно напишет.
(Отступы и кавычки исторически менялись пару раз от версии к версии, о том что актуально в настоящий момент можно почитать к версии на которую обновляемся в утилите pg_upgrade)

/u01/postgres/13.2/pgsql/bin/pg_upgrade \

 --old-datadir "/u01/postgres/12.1/pgsql/data" \

 --new-datadir "/u01/postgres/13.2/pgsql/data" \

 --old-bindir "/u01/postgres/12.1/pgsql/bin" \

 --new-bindir "/u01/postgres/13.2/pgsql/bin" \

 --old-options "-c config_file=/u01/postgres/12.1/pgsql/data/postgresql.conf" \

 --new-options "-c config_file=/u01/postgres/13.2/pgsql/data/postgresql.conf" \

 --check

Если все ок, то можем обновляться

(убираем check)

/u01/postgres/13.2/pgsql/bin/pg_upgrade \

 --old-datadir "/u01/postgres/12.1/pgsql/data" \

 --new-datadir "/u01/postgres/13.2/pgsql/data" \

 --old-bindir "/u01/postgres/12.1/pgsql/bin" \

 --new-bindir "/u01/postgres/13.2/pgsql/bin" \

 --old-options "-c config_file=/u01/postgres/12.1/pgsql/data/postgresql.conf" \

 --new-options "-c config_file=/u01/postgres/13.2/pgsql/data/postgresql.conf" \

 --check

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

Запускаем новый 13.2

systemctl start postgresql.service

чекаем в процессах или статусе systemd или в psql, что работает новая версия

ps auxf | grep postgres

psql -c "SELECT version();"

На новом кластере нет никакой статистики. Нужно запустить ANALYZE по кластеру

./analyze_new_cluster.sh

Может так получится что наш кастомный postgresql.conf и pg_hba.conf после обновления станут стандартными.
Правим все из старых в новые. Рестартуем PG.

*Пояснение к обновлению
У меня указано обновление по сути с переносом data в новый кластер.
Тоесть потребуется место по размеру как уже существующая БД.
Но можно с помощью параметра --link сделать жесткую ссылку на data старого кластера это будет без копирования data

При режиме --link
при обновлении будет нотка(для отката)
If you want to start the old cluster, you will need to remove
the ".old" suffix from /u01/postgres/12.1/pgsql/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Так же есть параметр --clone но мне он не совсем понятен пока.
Вот описание.
(Использовать эффективное клонирование файлов (в ряде систем это называется «reflink») вместо копирования файлов в новый кластер. В результате файлы данных могут копироваться практически мгновенно, как и с использованием -k/--link, но последующие изменения не будут затрагивать старый кластер.)

Залипший archive wal log

 На версии 12.

бывает что залипает архивирование wal логов. Логи онлайновые в pg_wal начинают копиться.
В логах Postgres будет примерно такое сообщение об ошибках.

2021-03-06 09:54:40.319 MSK [9448] LOG:  archive command failed with exit code 1

2021-03-06 09:54:40.319 MSK [9448] DETAIL:  The failed archive command was: test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094 && cp pg_wal/00000001000034F400000094 /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094

2021-03-06 09:54:40.319 MSK [9448] WARNING:  archiving write-ahead log file "00000001000034F400000094" failed too many times, will try again later

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

archive_command = 'rm -rf /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/* && cp %p /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/%f && test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/%f && mv /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/%f /u01/postgres/12.1/pgsql/pg_wal_archive/%f'

Предварительно создав временную папку:

/u01/postgres/12.1/pgsql/pg_wal_archive_tmp

Файл создается в временной директории, а потом перемещается в pg_wal_archive

Но иногда даже после создания временной директории может случится такая ошибка.
Все равно залипает архивация. Главное удалить косячный архив лог(не в коем случае не !wal!)

2021-03-06 10:19:47.152 MSK [13137] LOG:  archive command failed with exit code 1

2021-03-06 10:19:47.152 MSK [13137] DETAIL:  The failed archive command was: rm -rf /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/* && cp pg_wal/00000001000034F400000094 /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/00000001000034F400000094 && test ! -f /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094 && mv /u01/postgres/12.1/pgsql/pg_wal_archive_tmp/00000001000034F400000094 /u01/postgres/12.1/pgsql/pg_wal_archive/00000001000034F400000094

2021-03-06 10:19:47.152 MSK [13137] WARNING:  archiving write-ahead log file "00000001000034F400000094" failed too many times, will try again later

После
этого архивирование должно работать ок. Но обязательно нужно попросить
ответсвенного за РК , что бы был сделан Full backup т.к система резервного копирования может ругаться на пропуск 
инкрементальных
бекапов - PostgreSQL Database: [~Archive Log files are missing. Please run a FULL backup~] Data Backup Failed.




СРК
видит, что образовался пробел в  архив логах на опр момент времени.

Поэтому нужно запустить fullbackup. Дальше инкрементальные бекапы будут
создаваться в штатном режиме.

Настройка памяти в PostgreSQL

Обсуждение

Я не претендую на истину, так как мнения коллег очень расходятся.

Возьмем очень условный пример для нашей среды.
Предположим. 8 ГБ, 4 ЦПУ, 300 коннектов к бд.

Если воспользоваться pgtune автор инструмента считает почти по формулам, которые описаны в офф вики постгреса.

Вот как он считает память. Рассмотрим основное.
Но тут не соблюдается условие 1гб под ОС.

max_connections = 300

work_mem = 1747kB

shared_buffers = 2GB

effective_cache_size = 6GB

maintenance_work_mem = 512MB

 

Мы задаем лимит для потсгреса в 7гб(7168MB/7340032KB) памяти в limits.conf
Теперь работает с тем, что есть.

max_connections(тут уже cколько мы планируем предполагаем)

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

work_mem

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

Т.к мы не знаем сколько у нас сортировок/агрегаций в одном запросе то рекумендуется в качестве начального значения для параметра можно взять 2–4% доступной памяти.

так же есть мнение от leopard

Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ

ВАЖНО!

общее количество

памяти, которое может быть выделено серверным процессам

определяется как work_mem * max_connections.

тоесть если мы берем

work_mem = 1747kB * 300 активных коннектов то получаем примерно 512МБ расход памяти на все 300 подключение в которых происходит сложный запрос.

еще пример с ruhighload.com

Следовательно, если у Вас 10 активных клиентов и каждый выполняет 1 сложный запрос, то значение в 10Мб для этого параметра скушает 100Мб оперативной

мои выводы и наблюдения.

что к базе не бывает никогда такого открытого кол-ва коннектов до "потолка" , если смотреть в графану то есть минимальное значение и среднее за минуту.

обычно до 100

значит можно

начать с стандарта для веб приложений что делают другие дба в 32/64MB

при этом тоже наблюдение..

Если в логах или explain видно, что создаются временные файлы для 

для внутренних операций сортировки и хеш-таблиц

То стоит увеличить в 2-3 раза это значение. После изменения этого параметра может дать большой прирост в производительности.

у нас же он 128МБ и 1000 коннектов.

Если реально столько будет соединений то база ляжет(?)

shared_buffers

25% от памяти сервера

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

shared_buffers = 7168/100*25=1792MB

effective_cache_size

Этот параметр не влияет на размер разделяемой памяти, выделяемой Postgres Pro, и не задаёт размер резервируемого в ядре дискового кеша; он используется только в качестве ориентировочной оценки.

Не выделяет память, это лишь указание оптимизатору запросов о количестве оперативной памяти используемой в ОС для кэша файловой системы.

Что нашел:

1/2 стандартная настройка. Планировщик будет сам определять как ему лучше поступить.

3/4 ( более 50% )от памяти выделенной ПГ. - будут чаще использоваться индексы

effective_cache_size = 7/4*3 = 5,25 (5GB)

maintenance_work_mem

Задаёт максимальный объём памяти для операций обслуживания БД, в частности VACUUM, CREATE INDEX и ALTER TABLE ADD FOREIGN KEY. По умолчанию его значение — 64 мегабайта (64MB). Так как в один момент времени в сеансе может выполняться только одна такая операция и обычно они не запускаются параллельно, это значение вполне может быть гораздо больше work_mem. Увеличение этого значения может привести к ускорению операций очистки и восстановления БД из копии.

Есть мнение

от aka leopard(pgtune)

Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить не возможно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа.Например, при памяти 1–4 ГБ рекомендуется устанавливать 128–512 MB

Есть еще мнение что 

стоит сделать 10% от памяти сервера

увидел в гитхабе одного ДБА

т.е

7168 память под ПГ - 10% = 716.8 ˜ = 700 MB

Установка расширения jsonknife

 

Качаем тут https://github.com/niquola/jsonknife

1. Закидываем архив jsonknife-master.zip в /u01/distr

2. unzip jsonknife-master.zip

3.  chown -R postgres:postgres jsonknife-master chmod 755 -R jsonknife-master cp -r jsonknife-master /u01/distr/postgresql-12.1/contrib/ cd /u01/distr/postgresql-12.1/contrib/jsonknife-master

4.Т.к сборка идет под пользователем postgres и у него обьявлены все переменные

EDITOR=vim visudo 
postgres ALL=(ALL:ALL) NOPASSWD:ALL

5. make && sudo make install && make installcheck

В конце должно выдать, что то типо:

============== dropping database "contrib_regression" ============== NOTICE: database "contrib_regression" does not exist, skipping DROP DATABASE ============== creating database "contrib_regression" ============== CREATE DATABASE ALTER DATABASE ============== running regression test queries ============== test test ... ok 26 ms ===================== All 1 tests passed. ===================== 

Тестовую таблицу "contrib_regression" можно дропнуть, она не нужна. Далее \c имя_бд и create extension jsonknife; \dx можно глянуть расширения.

Установка сторонних расширений в Postgres

 Рассмотрим установку расширения на примере pgsentinel

Стоит отметить, что расширение загружается в конкретную базу, а не в экземпляр.

pgsentinel – хранение истории активных сессий

Расширение pgsentinel предоставляет возможность собирать и просматривать историю активных сессий в PostgreSQL.

Требуется установленная версия PostgreSQL  не ниже PostgreSQL 9.6  или выше.
Перед сборкой убедится что есть:

•  PostgreSQL version is 9.6 или выше.
  
•  Установлен пакет разработки PostgreSQL(postgresql(номер версии)-devel)(gcc,llvm,clang  ) или собран PostgreSQL из исходников(у нас из снепшота в 12.1 уже все стоит, можно ничего не ставить)
  Если это не 12.1 из снепшотов , то каких пакетов не хватает в конкретном случае, можно узнать из сообщений об ошибках при компиляции.
• Переменная PATH настроена таким образом, что команда pg_config доступна и запускается. (у нас запускается в 12.1) (команду можно запустить для проверки она безопасная)

Типичная установка для 12.1
Поскольку pgsentinel использует расширение pg_stat_statements (официально связанное с PostgreSQL) для отслеживания того, какие запросы выполняются в  базе данных, нужно добавить следующие записи в postgresql.conf:

$ shared_preload_libraries = 'pg_stat_statements,pgsentinel'

$ # Icncrease the max size of the query strings Postgres records

$ track_activity_query_size = 2048 -- можно больше если запросы очень большие

$ # Track statements generated by stored procedures as well

$ pg_stat_statements.track = all

После добавления этих строк нужно перезапустить экземпляр бд.
Важно: Лучше конечно pg_stat_statements,pgsentinel ставить до запуска прода в эксплуатацию, но если забыли то лучше сделать после 18-00 по согласованию с командой продукта.

После того, как экземпляр БД перезапущен
Создаем в /u01/postgres директорию external_extensions
владелец директории и все что внутри postgres

В эту директорию (external_extensions) закидываем  архив с расширением скачать можно тут → https://github.com/pgsentinel/pgsentinel
и разархивируем

unzip pgsentinel-master.zip

Далее

$ cd pgsentinel/src

$ make

$ sudo make install

После чего можно уже создать расширение:

Коннект в БД

\c имя_БД

create extension pg_stat_statements;

create extension pgsentinel;

Проверить можно так:

Коннект в нужную бд и выполняем

\dx

Закачка видео с ютуб и конвертация

youtube-dl --extract-audio --audio-format mp3 -i 'ссылка на плейлист на ютубе"