Домашнее задание 46

Репликация postgresql

Для выполнение задания используется vagrant box Ubuntu 22.04

Создание Vagrantfile

Создадим 3 VM c характеристиками

• CPU 1
• RAM 1024Mb

Готовый Vagrantfile

ansible.yml

В сценарий для Ansible добавлены следующие действия:

1. На VM sql-master и sql-slave устанавливается postgresql-14 сервер
2. Настраивается репликации с VM sql-master на sql-slave
3. На сервере backup устанавливается barman
4. Настраиваем бекап с помощью barman

Готовый ansible.yml

Проверка

Запускаем vagrant

alex@ubuntu-pc:~/Документы/46$ vagrant up 
Bringing machine 'sql-master' up with 'virtualbox' provider...
Bringing machine 'sql-slave' up with 'virtualbox' provider...
Bringing machine 'backup' up with 'virtualbox' provider...
==> sql-master: Importing base box 'ubuntu/jammy64'...
==> sql-master: Matching MAC address for NAT networking...
==> sql-master: Checking if box 'ubuntu/jammy64' version '20241002.0.0' is up to date...
==> sql-master: Setting the name of the VM: 46_sql-master_1783010934875_59066
==> sql-master: Clearing any previously set network interfaces...
==> sql-master: Preparing network interfaces based on configuration...
    sql-master: Adapter 1: nat
    sql-master: Adapter 2: intnet
==> sql-master: Forwarding ports...
    sql-master: 22 (guest) => 2222 (host) (adapter 1)
==> sql-master: Running 'pre-boot' VM customizations...
==> sql-master: Booting VM...
==> sql-master: Waiting for machine to boot. This may take a few minutes...
    sql-master: SSH address: 127.0.0.1:2222
    sql-master: SSH username: vagrant
    sql-master: SSH auth method: private key
    sql-master: Warning: Connection reset. Retrying...
    sql-master: 
    sql-master: Vagrant insecure key detected. Vagrant will automatically replace
    sql-master: this with a newly generated keypair for better securi
...

...


==> backup: Mounting shared folders...
    backup: /home/alex/Документы/46 => /vagrant
==> backup: Running provisioner: ansible...
    backup: Running ansible-playbook...

PLAY [sql-master,sql-slave] ****************************************************
skipping: no hosts matched

PLAY [backup] ******************************************************************

TASK [Gathering Facts] *********************************************************
ok: [backup]

TASK [install barman] **********************************************************
changed: [backup]

TASK [config barman] ***********************************************************
changed: [backup]

TASK [cron config] *************************************************************
changed: [backup]

PLAY RECAP *********************************************************************
backup                     : ok=4    changed=3    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   

Подключимся к sql-master, и проверим статус репликации,

vagrant@sql-master:~$ sudo -u postgres psql
could not change directory to "/home/vagrant": Permission denied
psql (14.23 (Ubuntu 14.23-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# SELECT application_name, client_addr AS replica_ip, state, sync_state FROM pg_stat_replication;
  application_name  | replica_ip |   state   | sync_state 
--------------------+------------+-----------+------------
 walreceiver        | 10.0.0.11  | streaming | async
 barman_receive_wal | 10.0.0.12  | streaming | async
(2 rows)

Видим, что связь с 10.0.0.11 (sql-slave) и 10.0.0.12 (backup) установлена.

Создадим новую тестовую базу

postgres=# create database test_database;

Теперь подключимся к реплики, и посмотрим список баз

vagrant@sql-slave:~$ sudo -u postgres psql
could not change directory to "/home/vagrant": Permission denied
psql (14.23 (Ubuntu 14.23-0ubuntu0.22.04.1))
Type "help" for help.

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

Как видим, база test_database успешно реплицировалась.

Репликация настроена.

Подключимся к backup, и проверим настройки barman

vagrant@backup:~$ sudo -u barman barman check pg-server
Server pg-server:
    PostgreSQL: OK
    superuser or standard user with backup privileges: OK
    PostgreSQL streaming: OK
    wal_level: OK
    replication slot: OK
    directories: OK
    retention policy settings: OK
    backup maximum age: OK (no last_backup_maximum_age provided)
    backup minimum size: OK (33.6 MiB)
    wal maximum age: OK (no last_wal_maximum_age provided)
    wal size: OK (0 B)
    compression settings: OK
    failed backups: OK (there are 0 failed backups)
    minimum redundancy requirements: OK (have 1 backups, expected at least 0)
    pg_basebackup: OK
    pg_basebackup compatible: OK
    pg_basebackup supports tablespaces mapping: OK
    systemid coherence: OK
    pg_receivexlog: OK
    pg_receivexlog compatible: OK
    receive-wal running: OK
    archiver errors: OK

Так как в тестовой стреде бекап настроен каждые 10 минут, то немного оставим наши VM для того что бы создалось несколько бекапов Через 30 минут смотрим, что по бекапам

vagrant@backup:~$ sudo -u barman barman list-backup pg-server
pg-server 20260702T174001 - Thu Jul  2 17:40:03 2026 - Size: 49.6 MiB - WAL Size: 0 B
pg-server 20260702T173002 - Thu Jul  2 17:30:03 2026 - Size: 49.6 MiB - WAL Size: 32.0 MiB
pg-server 20260702T172002 - Thu Jul  2 17:20:04 2026 - Size: 49.6 MiB - WAL Size: 32.0 MiB

Видим, что каждые 10 минут создается новый бэкап

Задание выполнено!