01 октября 2024 02 октября 2024 СуБД

Настройка определяются практически в одном месте, на главном сервер, а дочерний сервер отличается от главного только наличием сигнального файла postgres-slave1/standby.signal в директории с данными на подчинённом сервере. То-есть, сначала конфигурируется главный сервер так, чтобы в случае обнаружения файла  standby.signal он стал вторичным, но перед этим, на том сервере который должен стать вторичным необходимо удалить базу и восстановить с первичного, а затем создать файл  standby.signal

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

Для начала создам файл docker-compose.yml и определю там запуск и настройки главного сервера

version: "3.9"

services:
  master:
    image: postgres:14
    container_name: master
    volumes:
      - ./postgres-master:/var/lib/postgresql/data/
      - ./wals:/var/lib/postgresql/archive/
    env_file:
      - .env

так же небходимо создать файл с настройками по умолчанию для postgres

POSTGRES_USER=test
POSTGRES_PASSWORD=test++test
POSTGRES_DB=test
POSTGRES_INITDB_ARGS=

создадим директории для хранения данных СУБД вне контейнеров

mkdir {postgres-master,postgres-slave1,postgres-slave2}

создадим директорию для хранения wal файлов

mkdir wals

Запускаем главный сервер и проверяем его работу

docker-compose up  -d

docker-compose exec master psql -U test # войти в консоль главным пользователем и посмотреть список баз

create database test111;  -- создать базу test111

\l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 template1 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 test      | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 test111   | test  | UTF8     | en_US.utf8 | en_US.utf8 | 

Готово, сервер работает. Теперь склонируем секцию настройки главного сервера в docker-compose.yml, назовём его slave1 и перезапустим клустер, а так же проверим какие базы имеются на подчинённом сервере

version: "3.9"

services:
  master:
    image: postgres:14 
    container_name: master
    volumes:
      - ./postgres-master:/var/lib/postgresql/data/
      - ./wals:/var/lib/postgresql/archive/
    env_file:
      - .env

  slave1:
    image: postgres:14 
    container_name: slave1 
    volumes:
      - ./postgres-slave1:/var/lib/postgresql/data/
      - ./wals:/var/lib/postgresql/archive/
    env_file:
      - .env

Перезапуск клустера

docker-compose down
docker-compose up -d

docker-compose ps 
NAME                COMMAND                  SERVICE             STATUS              PORTS
master              "docker-entrypoint.s…"   master              running             5432/tcp
slave1              "docker-entrypoint.s…"   slave1              running             5432/tcp

Проверка баз на подчинённом сервере

docker-compose exec slave1 psql -U test
psql (14.13 (Debian 14.13-1.pgdg120+1))
Type "help" for help.

test=# \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 template1 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 test      | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
(4 rows)

Тут видно, что на подчинённом сервере отсутствует таблица test111, это произошло потому, что slave1 пока что не является подчинённым сервером, так как она ещё не настроен

Для того чтобы slave1 стал подчинённым необходимо разрешить главному серверу принимать внешние подключения от подчинённых серверов.

Для этого необходимо на главном сервере создать нового пользоваетля с правами репликации и разрешить этого пользоватлю внешние подключения

create user newuser replication login PASSWORD '2024';

теперь необходимо разрешить удалённый доступ этому пользователю

Для этого необходимо на главном сервере в файле postgres-master/pg_hba.conf добавить разрешение. Адрес 0.0.0.0/0 я использую для среды докер контейнером, потому там адрес контейнером могут менять, по этому будем принимать все входящие

host    replication     newuser         0.0.0.0/0               md5

Перезапускае главный сервер и проверяем возможность доступ с починённого

docker-compose restart 
docker-compose exec slave1 psql postgres://newuser:2024@master/test111 # тут я использую упрощённый способ формирования строки запрос в виде урла, такие урлы применяются во многих приложениях совместимых libpg

psql (14.13 (Debian 14.13-1.pgdg120+1))
Type "help" for help.

test111=> \l
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 template1 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 test      | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 test111   | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
(5 rows)

test111=> 

Тут видно, что я из среды подчинённого сервера подключаюсь через внутренную сеть среды докер к терминалу главного сервера и там доступна новую базу данных

Теперь попробуем на подчинённом сервере загрузить резервную копию главного сервер

docker-compose exec slave1 pg_basebackup -d postgres://newuser:2024@master -v -D /tmp/base

Тут мы из среды подчинённого сервер подключаемся к главному и выкачиваем бекап в директорию /tmp/base

Если всё хорошо сработало, то пришло время настраивать отношения master слейв для клустера.

Для этого переопределим настройки главного сервера в файле sudo vim postgres-master/postgresql.conf

wal_level = replica
max_wal_senders = 5                # я указываю 5
archive_mode = on

wal_keep_size = 1024                # Обеспечивает хранение WAL файлов до передачи
hot_standby = on

archive_command = 'test ! -f /var/lib/postgresql/data/archive/%f && cp %p /var/lib/postgresql/archive/%f'

Строчку est ! -f /var/lib/postgresql/data/archive/%f && cp %p /var/lib/postgresql/archive/%f необходимо пояснить: эта строчка описывает комаду которая создаёт архивную копию WAL файл, в моём случае я копию её в директорию которая доступна снаружи конейнера: ./wals:/var/lib/postgresql/archive/

Затем уже для подчинёного сервера привести переменную primary_conninfo к такому виду, эта переменная редактируется в файле мастер сервера, но она будет игнорироваться мастеро

primary_conninfo = 'host=master port=5432 user=newuser password=2024'

Теперь необходимо на удалить все данные на подчинённом сервере в директории /var/lib/postgresql/data/ и на освободившееся место поместить бекап главного сервера с помощь утилиты pg_basebackup, но перед этим необходимо оставить подчинённй сервер постгрес

docker-compose stop slave1

docker-compose run slave1 rm -R /var/lib/postgresql/data/

Теперь скачиваем бека с главного сервера запускаем подчинённый

docker-compose run slave1 pg_basebackup -d postgres://newuser:2024@master -v -D /var/lib/postgresql/data

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

sudo touch postgres-slave1/standby.signal

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

docker-compose start slave1

echo \\l | docker-compose exec -T master psql -U test
                             List of databases
   Name    | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
-----------+-------+----------+------------+------------+-------------------
 postgres  | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 template1 | test  | UTF8     | en_US.utf8 | en_US.utf8 | =c/test          +
           |       |          |            |            | test=CTc/test
 test      | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
 test111   | test  | UTF8     | en_US.utf8 | en_US.utf8 | 
(6 rows)

Теперь создадим новую скопируем живую базу на главный сервер клустера

# создание новой базы
docker-compose exec master createdb -U test global-art

# клонирование живой базы с одного из моих тестовых серверов
pg_dump  -c globalart | docker-compose exec -T master psql -U test global-art

# проверка структуры новой базы на главном сервере
echo \\dt | docker-compose exec -T master psql -U test global-art
                      List of relations
 Schema |                Name                | Type  | Owner 
--------+------------------------------------+-------+-------
 public | auth_group                         | table | test
 public | auth_group_permissions             | table | test
 public | auth_permission                    | table | test
 public | auth_user                          | table | test
 public | auth_user_groups                   | table | test
 public | auth_user_user_permissions         | table | test
 public | catalog_accessory                  | table | test
 public | catalog_accessory_subproducts      | table | test
 public | catalog_additionalfile             | table | test

Тут видно, что главном сервере появилась новая база данных и в этой базе есть таблицы. Подсчитаем контрольную сумму этой базы с помощью md5

docker-compose exec master pg_dump -U test global-art |md5sum
164858e3f201d1f6261c3fad4b5ff59a  -

Видим, что контрольная сумма  базы на главном сервере равна 164858e3f201d1f6261c3fad4b5ff59a

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

docker-compose exec slave1 pg_dump -U test global-art |md5sum
164858e3f201d1f6261c3fad4b5ff59a  -

Видим, что базы одинаковые. То-есть, репликация происходит в нормальном режиме

Визуализация работы клустера

Добавление новых записей в главной базе

Проверка добавления новых записей на подчинённых серверах

Логи работы клустера

18 декабря 2023 СуБД fail2ban postgres

Если postgres используется в качестве мастерсервера для репликаций то у него должен быть открыт порт для подключения клиентов извне, соответственно открытый порт начнут сканировать с целью подобрать пароли и тогда в логах появится вот такое записи

postgres@postgres FATAL:  no pg_hba.conf entry for host "115.216.124.164", user "postgres", database "postgres", no encryption
postgres@postgres FATAL:  no pg_hba.conf entry for host "222.90.83.209", user "postgres", database "postgres", no encryption

Причём, подбор паролей идёт активно, десятки тысяч запросов за сутки

for f in /var/log/postgresql/postgresql-14-main.log.*.gz; do 
echo `date -r $f +"%Y-%m-%d"` `zcat $f | grep 'no pg_hba.conf entry for host' |wc -l` ; 
done

2023-10-15 79090
2023-12-10 81664
2023-12-03 33115
2023-11-26 22769
2023-11-19 100753
2023-11-12 70794
2023-11-05 130725
2023-10-29 82514
2023-10-22 119528

Для блокировки адресов источников запроса необходимо добавить фильтр и добавить новое правило в fail2ban

/etc/fail2ban/filter.d/postgres.conf

[Definition]
failregex = FATAL:  no pg_hba.conf entry for host "<HOST>"

Затем созданное имя фильтра необходимо использовать в правиле, добавив в конец файла /etc/fail2ban/jail.conf

[postgresql]
enabled   = true
filter    = postgres
action    = iptables[name=PostgreSQL, port=5432, protocol=tcp, blocktype=DROP]
           sendmail-whois[name=PostgreSQL, dest=root]
logpath   = /var/log/postgresql/postgresql-14-main.log
maxretry  = 3
findtime  = 600
bantime   = 604800

После этого необходимо перезапустить fail2ban: service fail2ban restart 

и проконтролировать перезапуск сервиса просмотрел логи: tail -f /var/log/fail2ban.log

• fail2ban-client status # — покажет список сервисов события которых обслуживает fail2ban
• fail2ban-client status postgresql # — показать статус обработки postgresql
• fail2ban-client set postgresql  unbanip 115.216.124.164 222.90.83.209 34.76.158.233 # — удалить из бана заданные IP

29 сентября 2023 СуБД Postgres RECURSIVE

Рекурсивных запрос к Postgres состоит из двух, объединённых запросов

1. запрос задающий начальные условия рекурсии
2. запросов определяющий следующую порцию данных для выборы

Рекурсия начинает с добавляния в результат данных от первой выборки  и будет продолждать до тех пока второй запрос будет возвращать данные

WITH RECURSIVE family_children as (

select id, 0 as level, fio from family where fio ~ '^Иванов'

union 

select family.id, level+1 as level, family.fio from family join parent on family.id=parent.child join family on family.id=parent.id

) select * from family_children;

Приведённы в примере запрос начинается с поиска в Таблице Имён записей всех Ивановых. В результат подмешивается переменная level, level определяет уровень родства

Затем запрос продолжает поиском записей родителей любых Ивановых. При этом уровень родства увеличился единицу

Запросы будут продолжаться пока будут находиться родители для предыдущей порции детей

10 октября 2022 СуБД awk

for t in $(echo \\dt | psql $BASE |grep public |awk '{print $3}'); do 
    echo "SELECT setval('$t"_id_seq"', (select max(id)+1 from $t), true);"; 
done | psql $BASE

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

19 июля 2022 СуБД awk

После неудачного эксперимента с бекапами база данных может содержать по несколько копий каждой строки таблицы, это приводит любой ORM в ступор, впрочем и руками удалить такие записи не полуться, так записи имеют одинаковые ID. Ниже решение как легко удалить дубли и оставить только по одной уникальной строке
 

table_name=auth_users

data_base=breys

echo "
begin;

select count(1) from $table_name ;

create  table ttt as select distinct id x,  * from $table_name ;

alter table ttt drop  column x;

truncate table $table_name; 

insert into $table_name (select * from ttt );

drop table ttt ;

select count(1) from $table_name ;

end;
" | psql $data_base

Суть метода такова:

1. создать копию таблицы содержающую только уникальные по ID записи, для этого используется DISTINCT,
2. затем нужно удалить сигнальное поле x
3. почистить целевую таблицу
4. затем перенести данные из временной таблицы в целевую
5. удалить временную таблицу

Если необходимо обработать все таблицы базы данных то скрипт можно использовать в цикле

for table_name in $(echo '\dt' | psql $data_base | awk '{print $3}'| grep -v ^$|sort ); do  

echo "
begin;

select count(1) from $table_name ;

create  table ttt as select distinct id x,  * from $table_name ;

alter table ttt drop  column x;

truncate table $table_name; 

insert into $table_name (select * from ttt );

drop table ttt ;

select count(1) from $table_name ;

end;
" | psql $data_base; 

done

с помощь транзакций можно предовратить удаление данных в случае проблемного импорта

28 июня 2018 04 марта 2022 СуБД

Есть таблица dispatch_dispatch_form в дублирующимися строками

чтобы быстро почистить таблицу от дублей необходимо:

delete from dispatch_dispatch_form  a using dispatch_dispatch_form b WHERE a.id < b.id AND a.email = b.email ;

вот так всё просто

08 января 2018 30 августа 2021 СуБД Postgresql Mysql | решать тесты

• Нужно ли перезапускать сервер Mysql?

Нет простого способа перекинуть данные из разных СУБД, даже в OpenSource. Но всё же можно и сделать это можно через промежуточный формат CSV следующим образом в терминале Postgresql выгружаем нужную таблицу в файл /tmp/test.csv

Copy (Select * from region) To '/tmp/test.csv' With CSV DELIMITER '|';

Затем, необходимо разрешить Mysql загружать данные из внешних файлов, для этого нужно временно добавить в файл /etc/mysql/my.cnf в секцию добавить параметр secure-file-priv

[mysqld]
#
# * Basic Settings
#
secure-file-priv = ""

затем нужно сохранить файл и перезапустить Mysql сервер. После того как сервер перезапущен необходимо переложить файл в специальную директориую которая разрешена для чтения в рамках Mysql

mv /tmp/test.csv /var/lib/mysql-files/

после это заходим в терминал mysql и загружаем данные из нашего файла

LOAD DATA INFILE '/var/lib/mysql-files/test.csv' INTO TABLE region FIELDS TERMINATED BY '|' ;

всё, теперь можно удалить файл и вернуть настройки Mysql сервера на место

22 апреля 2021 26 августа 2022 СуБД Postgresql psql bash awk for drop table cascade | решать тесты

• Знаете ли вы другой способ удаления группы таблиц в Postgres

Скрипт каскадного удаления таблиц в psql
 

for t in $(echo '\dt' | psql $data_base |awk -F\| '{print $2}'); do 
    echo "drop table $t cascade;" ; 
done | psql $data_base

Суть скрипта проста:

• echo '\dt' | psql $database # выводит список таблиц базы данных из переменной $database
• awk -F\| '{print $2}' # вывести второй столбец из списка
• do echo "drop table $t cascade;" ; done # тело цикла формирующее набор SQL команд
• | psql $database # выполнить SQL код

30 августа 2021 СуБД select psql concat

Иногда появляется необходимость скопировать некоторые поля из одной базы данных в другую. Как мне кажется, самым простым способом копирования отдельных полей у некоторых строк является генерация SQL скрипта в одной базе и исполнение этого запроса на другой. Например,

select order_num, slug  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num

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

select concat('update catalog_humanmenu set order_num=',order_num,' where slug=''',slug,''';')  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num;

теперь переключаем вывод результатов запроса не в терминал, а файл на локальном или удалённом диске

\o ordering-guid.txt

-- затем повторяем генератор SQL скрипта и вывод этой команды сохранится в файле ordering-guid.txt

select concat('update catalog_humanmenu set order_num=',order_num,' where slug=''',slug,''';')  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num;

-- закрываем SQL сессию

\q

в новообразованном файле ordering-guid.txt необходимо удалить первые и последние строки, а затем выполнить его в базе назначения

psql < ordering-guid.txt

Всё, необходимые поля из отобранных строк перенесены в соответствующие поля и строки базы назначения