193 lines
6.7 KiB
Markdown
193 lines
6.7 KiB
Markdown
# Домашнее задание 46
|
||
|
||
## Репликация postgresql
|
||
|
||
Для выполнение задания используется vagrant box Ubuntu 22.04
|
||
|
||
### Создание Vagrantfile
|
||
|
||
Создадим 3 VM c характеристиками
|
||
|
||
- CPU 1
|
||
- RAM 1024Mb
|
||
|
||
Готовый [Vagrantfile](Vagrantfile)
|
||
|
||
### ansible.yml
|
||
|
||
|
||
В сценарий для Ansible добавлены следующие действия:
|
||
|
||
|
||
1. На VM **sql-master** и **sql-slave** устанавливается postgresql-14 сервер
|
||
2. Настраивается репликации с VM **sql-master** на **sql-slave**
|
||
3. На сервере **backup** устанавливается barman
|
||
3. Настраиваем бекап с помощью barman
|
||
|
||
|
||
|
||
Готовый [ansible.yml](ansible.yml)
|
||
|
||
|
||
### Проверка
|
||
|
||
Запускаем vagrant
|
||
|
||
```bash
|
||
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**, и проверим статус репликации,
|
||
```bash
|
||
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) установлена.
|
||
|
||
|
||
Создадим новую тестовую базу
|
||
|
||
```bash
|
||
postgres=# create database test_database;
|
||
|
||
```
|
||
|
||
|
||
Теперь подключимся к реплики, и посмотрим список баз
|
||
|
||
|
||
|
||
```bash
|
||
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
|
||
```bash
|
||
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 минут смотрим, что по бекапам
|
||
```bash
|
||
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 минут создается новый бэкап
|
||
|
||
|
||
Задание выполнено! |