Поставил убунту, next, next, finish

Добавил себя в sudoers

echo $USER ALL=NOPASSWD: ALL | sudo tee /etc/sudoers.d/$USER

Обновил систему

sudo apt update && sudo apt upgrade -y && sudo reboot

Поставил postgres

sudo apt install -y postgres postgres-contrib

Забыл и уже после выставил часовой пояс

sudo timedatectl set-timezone Europe/Kiev

Добавил в /etc/postgresql/12/main/pg_hba.conf

Из нутри базы

select line_number, error from pg_hba_file_rules where error is not null; -- не сразу получилось, peer может использоваться только для подключения по сокетам, поменял на trust select line_number, type, database, user_name, address, auth_method from pg_hba_file_rules; -- постгрес уже видит мою настройку select pg_reload_conf(); -- применяем

Подключиться все равно не удалось, пришлось таки идти в /etc/postgresql/12/main/postgresql.conf и править listen_addresses = '*' и перезапускать сервер, после чего получилось подключиться

pgbench

по мотивам видео хотел погонять pgbench

create database sample;

инициализация базы (х50 раз больше базовой)

sudo su - postgres pgbench -i -s 50 sample

дальше в отдельном терминале монитор памяти

watch free -m

и запуск теста

pgbench -c 10 -j 2 -t 10000 sample

где:

• -с 10 - 10 клиентов
• -j 2 - 2 потока
• -t 10000 - каждый клиент выполнит 10К транзакций

результат

transaction type: <builtin: TPC-B (sort of)> scaling factor: 50 query mode: simple number of clients: 10 number of threads: 2 number of transactions per client: 10000 number of transactions actually processed: 100000/100000 latency average = 1.946 ms tps = 5138.114222 (including connections establishing) tps = 5139.000224 (excluding connections establishing)

память при этом не потреблялась

тут уже не так важно что менять, менял так же как и он (увеличил в 4 раза)

shared_buffers = 512MB # 128MB

после чего рестарт сервера и повторный прогон теста

на этот раз память начала потребляться, результаты прогона

tps = 5347.684715

и еще один тест

#effective_cache_size = 4GB effective_cache_size = 512MB

и любопытно получилось даже хуже

tps = 5173.036936

Нужно дальше смотреть что умеет pgbench - тут прямо непаханное поле

Бекап

по мотивам видео

Логический бекап

Формат plain

Аля sql файлики с create table foo, insert into foo

pg_dump -d sample -f sample.sql

так не вышло, надо переключаться в пользователя postgres и делать каждый раз западло, по аналогии с прошлым разом поменял peer на trust на локальное подключение

pg_dump -U postgres -d sample -f sample.sql

файлик вышел на 472мб

востановление

psql -f sample.sql

Custom формат

pg_dump -d sample -F c -f sample pg_restore -d sample -j 2 sample

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

файл получился всего 18мб, внутри бинарь

Формат directory

pg_dump -U postgres -d sample -F d -j 2 -f ./sampledir/ pg_restore -d sample -j 2 ./sampledir/

Папку создаст сам

В папке будет серия файлов 1234.dat.gz и файл toc.dat

Все те же 18мб

Выглядит так что формат directory в этом случае наиболее предпочтителен, т.к. умеет все, если будет сильно много нагрузки при бекапе, можно будет уменьшать число потоков

pg_dumpall

примечание - pg_dump не бекапит пользователей и табличные пространства

pg_dumpall -U postgres -f db1.sql psql -f db1.sql

эта штука не умеет архивирование, паралельность и т.п.

обычно используют просто

pg_dumpall -U postgress - globals only

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

Настройки

• при восстановлении копии на системе с другим набором ролей

  • -O, --no-owner - не генерировать команды для установки владельца объектов
  • -X, --no-acl - не генерировать команды для установки привилегий

• для выгрузки и загрузки данных частями

  • -s, --schema-only - выгрузить только определения обхектов баз данных
  • -a, --data-only - выгрузить только данные, без создания объектов

• если восстанавливать копию на системе, в которой уже есть данные (и наоборот, на чистой системе)

  • -c, --clean - генерировать команды DROP для создаваемых объектов
  • -C, --create - генерировать команды создания БД и подключения к ней

• ключи для выбора объектов которые должны попасть в резервную копию

  • -n, --schema - шаблон для имен схем
  • -t, --table - шабло для имен таблиц

• инверсия, включить в бекап все кроме

  • -N, --exclude-schema - шаблон для имен схем
  • -T, --exclude-table - шабло для имен таблиц

Физический бекап

Цели и форматы

• Немедленное развертывание нового экземпляра

  • --format=plain
  • удаленный запуск на сервере, где будет развернут экземпляр
  • копирует файлы и каталоги кластера (PGDATA) в указанный каталог
  • копирует табличные пространства по тем же абсолютным путям, но можно сопоставить и дргие (--tablespace-mapping)

• резервная копия для последующего использования

  • --format=tar
  • --gzip или --compress=0..9 для сжатия
  • удаленный или локальный запуск
  • помещает PGDATA в base.tar, файлы журнала в pg_wal.tar
  • помещает каждое табличное пространство в отдельный файл OID.tar, пути могут быть изменены в файле tablespace_map

pg_basebackup -U postgres --pgdata=./db1bak1/

для рестора просто перенести db1bak1 на второй сервер

Репликация

по мотивам видео

• synchronous_commit

  • off - асинхронная фиксация и репликация
  • local - локальная фиксация
  • remote_write - дожидаемся сброса на диск на реплике
  • remote_apply - дождемся пока реплика применит (это самое жесткое)

• synchronous_standby_names

  • FIRST N
  • ANY N

Репликация

по мотивам видео

на обоих серверах поставил postgres плюс repmgr

sudo apt install -y postgresql postgresql-contrib repmgr

будет запущенно две службы

systemctl status postgresql systemctl status repmgrd

примечание: данные живут тут /var/lib/postgresql/12/main/

правим /etc/postgresql/12/main/postgresql.conf

listen_addresses = '*'

для repmgr добавляем

shared_preload_libraries = 'repmgr' max_wal_sender = 10 # 10 default max_replication_slots = 15 # 10 default wal_level = 'replice' # default hot_standby = on # default archive_mode = on # off by default archive_command = '/bin/true' # empty by default

создаем /etc/postgresql/12/main/conf.d/repmgr.conf

shared_preload_libraries = 'repmgr'

правим /etc/postgresql/12/main/pg_hba.conf и по срединке добавляем

local replication repmgr trust host replication repmgr 127.0.0.1/32 trust host replication repmgr 192.168.106.0/24 trust local repmgr repmgr trust host repmgr repmgr 127.0.0.1/32 trust host repmgr repmgr 192.168.106.0/24 trust

Создаем пользователя repmgr

createuser -U postgres -s repmgr createdb -U postgres repmgr -O repmgr psql -U postgres \c repmgr ALTER USER repmgr SET search_path TO repmgr, "$user", public;

Репликация 3

https://www.digitalocean.com/community/tutorials/how-to-set-up-physical-streaming-replication-with-postgresql-12-on-ubuntu-20-04

Поменяли listen_addressess на звездочку

завели пользователя

sudo -u postgres psql CREATE ROLE test WITH REPLICATION PASSWORD 'testpassword' LOGIN; \q

добавили строку в pg_hba.conf

host replication test 192.168.106.0/24 md5

перезапустились

sudo systemctl restart postgresql

на второй машине

смотрим где лежат данные

sudo -u postgres psql -c 'SHOW data_directory;'

стопаем базу

sudo systemctl stop postgresql

чистим файлы

sudo su rm -rf /var/lib/postgresql/12/main/* exit

ресторимся с мастера

sudo -u postgres pg_basebackup -h 192.168.106.195 -p 5432 -U test -D /var/lib/postgresql/12/main/ -Fp -Xs -R

спросит пароль testpassword

после рестора папка наполниться файлами

благодаря опции -R добавиться инфа о подключении и необходимые настройки что бы пометить сервер как реплику

запускаем сервер

sudo systemctl start postgresql

проверяем что как на первой ноде

sudo -u postgres psql -c 'SELECT client_addr, state FROM pg_stat_replication;'

-- client_addr | state -- -----------------+----------- -- 192.168.106.194 | streaming

Все заработало, причем благодаря тому что репликация физическая оно синкает всякие DDL

Пробую файловер из подручных средств видео демо

проверяем что реплика в режиме востановления

sudo -u postgres psql -c 'SELECT pg_is_in_recovery();'

промоутим его как мастер

sudo pg_ctlcluster 12 main promote

после этого предыдущая команда вернет false, сервер стал мастером, можно вставлять данные, но предыдущий мастер все еще работает и тоже принимает запись - из-за чего могут быть проблемы

sudo -u postgres psql -d sample -c "insert into test values ('mac was here');" sudo -u postgres psql -d sample -c "select * from test;"

при этом на первом сервере эти же команды тоже сработают

на первом сервере - стопаем базу

sudo systemctl stop postgresql

что бы не ресторить весь сервер, подхачим утилитой pg_rewind

pg_rewind -D /var/lib/postgresql/12/main --source-server='user=test password=testpassword host=192.168.106.194 port=5432' -P

якась фигня в системе нет pg_rewind, сделал задом наперед

чистим файлы

sudo su rm -rf /var/lib/postgresql/12/main/* exit

ресторимся с мастера

sudo -u postgres pg_basebackup -h 192.168.106.194 -p 5432 -U test -D /var/lib/postgresql/12/main/ -Fp -Xs -R

спросит пароль testpassword

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