# Имитация сбоя основного сервера

## Сбой основного сервера

1. Останавливаем сервер на порту 5434. Убираем синхронную репликацию и поменяем минимальное количество wal файлов. Для чего это сделано будет объяснено ниже

```bash
postgres$ pg_ctl -D /var/lib/pgsql/12/main5434 stop
postgres$ nano /var/lib/pgsql/12/main5432/postgresql.auto.conf
postgres$ echo "min_wal_size=500MB" >> ~/12/main5432/postgresql.conf
```

2. Перезапустим PostgreSQL на порту 5432, чтобы изменения вступили в силу

```log
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 restart
waiting for server to stop
waiting for server to start....2021-04-12 14:01:10.615 UTC [3064] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-12 14:01:10.615 UTC [3064] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-12 14:01:10.619 UTC [3064] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-12 14:01:10.626 UTC [3064] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-12 14:01:10.641 UTC [3064] LOG:  redirecting log output to logging collector process
2021-04-12 14:01:10.641 UTC [3064] HINT:  Future log output will appear in directory "log".
 done
server started
```

3. Проверяем, что мы инициализировали кластер с включенными контрольными суммами

```bash
postgres$ psql
5432=> \x
5432=> select * from pg_settings where name like '%checksum%';
-[ RECORD 1 ]---+-------------------------------------------------------------
name            | data_checksums
setting         | on
unit            |
category        | Preset Options
short_desc      | Shows whether data checksums are turned on for this cluster.
extra_desc      |
context         | internal
vartype         | bool
source          | override
min_val         |
max_val         |
enumvals        |
boot_val        | off
reset_val       | on
sourcefile      |
sourceline      |
pending_restart | f
```

4. Проверяем, что реплика работает в режиме чтения

```sql
5432=> \q
postgres$ psql -p5433 -d synch_replica
5433=> select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)
5433=> select * from test;
 id | name
----+-------
  1 | name1
  2 | name2
(2 rows)

5433=> create table t1(id int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
5432=> \q
```

5. Теперь предположим, что у нас моргнула сеть и наше выбранное решение, обеспечивающее высокодоступный кластер PostgreSQL, решило сделать реплику лидером и исключить "бывшего" лидера из пула доступных адресов. Промоутим нашу реплику на порту 5433 до лидера

```bash
postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 promote
waiting for server to promote.... done
server promoted
```

6. Проверим, что реплика стала лидером

```bash
postgres$ psql -p5433
psql (12.5)
Type "help" for help.

5433=> select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)
```

7. Но как всегда бывает, у нас за время принятия решения остались соединения, которые успели сделать изменения в лидере. Проделаем их

```sql
5432=> \q

postgres$ psql -d synch_replica
You are now connected to database "synch_replica" as user "postgres".

5432=> insert into test values(3,'name3');
INSERT 0 1

5432=> select * from test;
 id | name
----+-------
  1 | name1
  2 | name2
  3 | name3
(3 rows)
```

8. А теперь на "новом" лидере удалим записи из таблицы test

```sql
5432=> \q

postgres$ psql -d synch_replica -p5433
psql (12.5)
Type "help" for help.

5433=> delete from test;
DELETE 2

5433=> select * from test;
 id | name
----+------
(0 rows)
```

9. Сейчас мы получаем "нехорошую" ситуацию. У нас два лидера и оба могут принимать подключения и менять данные.
Давайте избавимся от этого. Для этого остановим "бывший" лидер на порту 5432

```bash
5433=> \q

postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 stop
waiting for server to shut down.... done
server stopped
```

10. Запустим утилиту pg_rewind, которая прочитает все наши файлы wal из источника (сервер на порту 5433) и проиграет эти изменения на порту 5432

```bash
postgres$ pg_rewind -D /var/lib/pgsql/12/main5432 --source-server="port=5433" -P
pg_rewind: connected to server
pg_rewind: servers diverged at WAL location 0/3000610 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/2000060 on timeline 1
pg_rewind: reading source file list
pg_rewind: reading target file list
pg_rewind: reading WAL in target
pg_rewind: need to copy 53 MB (total source directory size is 80 MB)
54325/54325 kB (100%) copied
pg_rewind: creating backup label and updating control file
pg_rewind: syncing target data directory
pg_rewind: Done!
```

11. Всё, утилита отработала. Но есть проблема - для её работы нужны wal файлы предыдущей контрольной точки. Поэтому мы в начале ставили большой `min_wal_size` и запустить наш "бывший" лидер просто так не сможем. В папке main5432 теперь конфигурация с папки 5433. Меняем порт и запускаем сервер

```bash
postgres$ echo "port=5432" >> /var/lib/pgsql/12/main5432/postgresql.conf
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 start
waiting for server to start....2021-04-12 13:57:59.888 UTC [3014] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-12 14:03:59.888 UTC [3014] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-12 14:03:59.892 UTC [3014] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-12 14:03:59.899 UTC [3014] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-12 14:03:59.916 UTC [3014] LOG:  redirecting log output to logging collector process
2021-04-12 14:03:59.916 UTC [3014] HINT:  Future log output will appear in directory "log".
 done
server started
```

12. Проверим данные в таблице test

```sql
postgres$ psql -d synch_replica
psql (12.5)
Type "help" for help.

5432=> select * from test;
 id | name
----+------
(0 rows)
5432=>\q
```

Кластер на порту 5432 проиграл wal файлы с "нового" лидера. Теперь давайте поднимем на нем реплику.

13. Подключаем сервер на порту 5432 к "новому" лидеру

```sql
postgres$ psql -p5433
5433=> SELECT pg_create_physical_replication_slot('synch_slot5432');
 pg_create_physical_replication_slot
-------------------------------------
 (synch_slot5432,)
(1 row)
5432=>\q
```

14. Меняем строку подключения к мастеру на сервере 5432.
Нам нужно подключаться теперь не к серверу на порту 5432, а к серверу на порту 5433 и слот репликации тоже нужно поменять с `synch_slot5433` на `synch_slot5432`

```bash
postgres$ nano /var/lib/pgsql/12/main5432/postgresql.auto.conf
```

15. Создаем файл, указывающий что наш сервер теперь работает в режиме реплики, и перезагружаем сервер на порту 5432

```log
postgres$ touch /var/lib/pgsql/12/main5432/standby.signal
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 restart
waiting for server to shut down......... done
server stopped
waiting for server to start....2021-04-12 15:57:20.918 UTC [2840] LOG:  starting PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-12 15:57:20.918 UTC [2840] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-12 15:57:20.922 UTC [2840] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-12 15:57:20.930 UTC [2840] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-12 15:57:20.946 UTC [2840] LOG:  redirecting log output to logging collector process
2021-04-12 15:57:20.946 UTC [2840] HINT:  Future log output will appear in directory "log".
 done
server started
```

16. Проверяем

```bash
postgres$ psql
psql (12.5)
Type "help" for help.
5432=> select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)
5432=> \q
postgres$ psql -p5433
5433=> \x
5433=> select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 2979
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2021-04-12 16:01:05.972088+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/3024F60
write_lsn        | 0/3024F60
flush_lsn        | 0/3030CE0
replay_lsn       | 0/3030CE0
write_lag        | 00:00:00.001146
flush_lag        | 00:00:00.001146
replay_lag       | 00:00:00.001146
sync_priority    | 0
sync_state       | async
reply_time       | 2021-04-12 16:03:36.304406+00
```

## Домашнее задание.
## Сбой репликации и восстановления отказоустойчивого решения

1. Настроить асинхронную репликацию с архивом. Остановить реплику. На лидере внести изменения и `checkpoint`, так чтобы посматстер сгенерил `wal` файл и чекпоинт удалил старые `wal` файлы.

2. Запустить реплику и посмотреть логи postgresql. Убедится, что реплика "отстала" и не может присоединится к лидеру.

3. В конфиги реплики добавить команду восстановления из архива и перезапустить реплику.

4. Убедится, что все `wal` файлы были получены и реплика "догнала" лидер.

### Чек-лист домашнего задания лекция 7-1

1. Настроена асинхронная репликация с архивом. Остановлена реплика. 
2. Запущена реплика и она отстает от лидера.
3. В конфигурации реплики добавлена команда восстановления из архива;
4. Получено подтверждение, что реплика "догнала" лидер.

<details>
  <summary>Решение:</summary>

1. Настроить асинхронную репликацию с архивом. Остановить реплику. На лидере внести изменения и `checkpoint`, так чтобы посматстер сгенерил `wal` файл и чекпоинт удалил старые `wal` файлы.
```
student$ sudo -u postgres -i
postgres$ pg_ctl initdb "-D" "/var/lib/pgsql/12/main5432" -o "--data-checksums"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory /var/lib/pgsql/12/main5432 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/main5432 -l logfile start
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 start
waiting for server to start....2021-04-24 12:24:25.308 UTC [2265] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 12:24:25.309 UTC [2265] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-24 12:24:25.313 UTC [2265] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-24 12:24:25.321 UTC [2265] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-24 12:24:25.334 UTC [2265] LOG:  redirecting log output to logging collector process
2021-04-24 12:24:25.334 UTC [2265] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ echo "wal_level=replica" >> /var/lib/pgsql/12/main5432/postgresql.auto.conf
postgres$ echo "archive_mode=on" >> /var/lib/pgsql/12/main5432/postgresql.auto.conf
postgres$echo "archive_command='cp %p /var/lib/pgsql/12/backups/wal/%f >> /var/lib/pgsql/12/main5432/log/archive_command.log 2>&1' " >> /var/lib/pgsql/12/main5432/postgresql.auto.conf
postgres$ echo "restore_command='cp /var/lib/pgsql/12/backups/wal/%f %p >> /var/lib/pgsql/12/main5432/log/restore_command.log 2>&1' " >> /var/lib/pgsql/12/main5432/postgresql.auto.conf
postgres$ cat ~/12/main5432/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_level=replica
archive_mode=on
archive_command='cp %p /var/lib/pgsql/12/backups/wal/%f >> /var/lib/pgsql/12/main5432/log/archive_command.log 2>&1'
restore_command='cp /var/lib/pgsql/12/backups/wal/%f %p >> /var/lib/pgsql/12/main5432/log/restore_command.log 2>&1'
postgres$ mkdir /var/lib/pgsql/12/backups/wal
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2021-04-24 12:25:17.695 UTC [2283] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 12:25:17.695 UTC [2283] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-24 12:25:17.699 UTC [2283] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-24 12:25:17.707 UTC [2283] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-24 12:25:17.725 UTC [2283] LOG:  redirecting log output to logging collector process
2021-04-24 12:25:17.725 UTC [2283] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ psql
psql (12.6)
Type "help" for help.

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/18C2258
(1 row)

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/2000000
(1 row)
5432=>\q
postgres$ pg_basebackup -D /var/lib/pgsql/12/main5433 -R -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2312"
pg_basebackup: write-ahead log end point: 0/5000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
postgres$ sed -i 's/#port = 5432/port = 5433/' /var/lib/pgsql/12/main5433/postgresql.conf
postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 start
waiting for server to start....2021-04-24 12:26:07.427 UTC [2326] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 12:26:07.427 UTC [2326] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-04-24 12:26:07.431 UTC [2326] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-04-24 12:26:07.439 UTC [2326] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-04-24 12:26:07.452 UTC [2326] LOG:  redirecting log output to logging collector process
2021-04-24 12:26:07.452 UTC [2326] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ psql -p5433
psql (12.6)
Type "help" for help.

5433=> select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

5433=> \x
Expanded display is on.
5433=> select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 2338
status                | streaming
receive_start_lsn     | 0/6000000
receive_start_tli     | 1
received_lsn          | 0/6000060
received_tli          | 1
last_msg_send_time    | 2021-04-24 12:26:07.619552+00
last_msg_receipt_time | 2021-04-24 12:26:07.619629+00
latest_end_lsn        | 0/6000060
latest_end_time       | 2021-04-24 12:26:07.619552+00
slot_name             |
sender_host           | /var/run/postgresql
sender_port           | 5432
conninfo              | user=postgres passfile=/var/lib/pgsql/.pgpass dbname=replication port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any
5433=> \q
postgres$ psql
psql (12.6)
Type "help" for help.

5432=> \x
Expanded display is on.
5432=> select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 2339
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2021-04-24 12:26:07.617048+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/6000060
write_lsn        | 0/6000060
flush_lsn        | 0/6000060
replay_lsn       | 0/6000060
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-04-24 12:26:27.734615+00
```

2. Запустить реплику и посмотреть логи postgresql. Убедится, что реплика "отстала" и не может присоединится к лидеру.
3. В конфиги реплики добавить команду восстановления из архива и перезапустить реплику.

```
5432=> \q
postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 stop
waiting for server to shut down.... done
server stopped
postgres$ psql
psql (12.6)
Type "help" for help.

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/6000078
(1 row)

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/7000078
(1 row)

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/8000000
(1 row)

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/8000000
(1 row)

5432=> select pg_switch_wal();
 pg_switch_wal
---------------
 0/8000000
(1 row)

5432=> checkpoint;
CHECKPOINT
5432=> \q
postgres$ ls ~/12/backups/wal/
total 114704
drwxr-xr-x 2 postgres postgres     4096 Apr 24 12:26 ./
drwx------ 3 postgres postgres     4096 Apr 24 12:25 ../
-rw------- 1 postgres postgres 16777216 Apr 24 12:25 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Apr 24 12:25 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Apr 24 12:25 000000010000000000000003
-rw------- 1 postgres postgres      337 Apr 24 12:25 000000010000000000000003.00000028.backup
-rw------- 1 postgres postgres 16777216 Apr 24 12:25 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Apr 24 12:25 000000010000000000000005
-rw------- 1 postgres postgres      337 Apr 24 12:26 000000010000000000000005.00000028.backup
-rw------- 1 postgres postgres 16777216 Apr 24 12:26 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Apr 24 12:26 000000010000000000000007
postgres$ ls ~/12/main5432/pg_wal/
total 65552
drwx------  3 postgres postgres     4096 Apr 24 12:26 ./
drwx------ 20 postgres postgres     4096 Apr 24 12:25 ../
-rw-------  1 postgres postgres      337 Apr 24 12:25 000000010000000000000005.00000028.backup
-rw-------  1 postgres postgres 16777216 Apr 24 12:27 000000010000000000000008
-rw-------  1 postgres postgres 16777216 Apr 24 12:26 000000010000000000000009
-rw-------  1 postgres postgres 16777216 Apr 24 12:25 00000001000000000000000A
-rw-------  1 postgres postgres 16777216 Apr 24 12:26 00000001000000000000000B
drwx------  2 postgres postgres     4096 Apr 24 12:26 archive_status/
postgres$ ls ~/12/main5433/pg_wal/
total 32780
drwx------  3 postgres postgres     4096 Apr 24 12:26 ./
drwx------ 20 postgres postgres     4096 Apr 24 12:26 ../
-rw-------  1 postgres postgres 16777216 Apr 24 12:26 000000010000000000000005
-rw-------  1 postgres postgres 16777216 Apr 24 12:26 000000010000000000000006
drwx------  2 postgres postgres     4096 Apr 24 12:26 archive_status/
```

4. Убедится, что все `wal` файлы были получены и реплика "догнала" лидер.

```
postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 start
waiting for server to start....2021-04-24 12:27:26.811 UTC [2365] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 12:27:26.812 UTC [2365] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-04-24 12:27:26.816 UTC [2365] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-04-24 12:27:26.825 UTC [2365] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-04-24 12:27:26.838 UTC [2365] LOG:  redirecting log output to logging collector process
2021-04-24 12:27:26.838 UTC [2365] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ tail ~/12/main5433/log/postgresql-Sat.log
2021-04-24 12:26:43.677 UTC [2326] LOG:  database system is shut down
2021-04-24 12:27:26.844 UTC [2367] LOG:  database system was shut down in recovery at 2021-04-24 12:26:43 UTC
2021-04-24 12:27:26.847 UTC [2367] LOG:  entering standby mode
2021-04-24 12:27:26.864 UTC [2367] LOG:  restored log file "000000010000000000000005" from archive
2021-04-24 12:27:26.929 UTC [2367] LOG:  redo starts at 0/5000028
2021-04-24 12:27:26.947 UTC [2367] LOG:  restored log file "000000010000000000000006" from archive
2021-04-24 12:27:27.022 UTC [2367] LOG:  consistent recovery state reached at 0/6000060
2021-04-24 12:27:27.033 UTC [2365] LOG:  database system is ready to accept read only connections
2021-04-24 12:27:27.039 UTC [2367] LOG:  restored log file "000000010000000000000007" from archive
2021-04-24 12:27:27.123 UTC [2381] LOG:  started streaming WAL from primary at 0/8000000 on timeline 1
postgres$ psql -p5433
psql (12.6)
Type "help" for help.

5433=> \x
Expanded display is on.
5433=> select * from pg_stat_wal_receiver ;
-[ RECORD 1 ]---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid                   | 2381
status                | streaming
receive_start_lsn     | 0/8000000
receive_start_tli     | 1
received_lsn          | 0/8000110
received_tli          | 1
last_msg_send_time    | 2021-04-24 12:27:27.123847+00
last_msg_receipt_time | 2021-04-24 12:27:27.123915+00
latest_end_lsn        | 0/8000110
latest_end_time       | 2021-04-24 12:27:27.123847+00
slot_name             |
sender_host           | /var/run/postgresql
sender_port           | 5432
conninfo              | user=postgres passfile=/var/lib/pgsql/.pgpass dbname=replication port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

5433=> select pg_is_in_recovery();
-[ RECORD 1 ]-----+--
pg_is_in_recovery | t
5433=> \q
postgres$ psql
psql (12.6)
Type "help" for help.

5432=> \x
Expanded display is on.
5432=> select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 2382
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2021-04-24 12:27:27.122465+00
backend_xmin     |
state            | streaming
sent_lsn         | 0/8000110
write_lsn        | 0/8000110
flush_lsn        | 0/8000110
replay_lsn       | 0/8000110
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2021-04-24 12:27:47.248852+00

5432=> \q
```
</details>

## Домашнее задание.
## Каскадная репликация

1. Создать каскадную репликацию по следующей схеме лидер -> реплика -> реплика.

2. Схема "лидер -> реплика" - это потоковая репликация со слотом репликации

3. Схема "реплика-реплика" - это репликация с архивом

4. Промоутить первую реплику до лидера

5. Подключить "бывший" лидер к "новому" лидеру

### Чек-лист домашнего задания лекция 7-3

1. Создана каскадная репликация по следующей схеме лидер -> реплика -> реплика;
2. Схема "лидер -> реплика" - это потоковая репликация со слотом репликации;
3. Схема "реплика-реплика" - это репликация с архивом;
4. Переключена первая реплика до лидера;
5. Подключен "бывший" лидер к "новому" лидер;
6. Проверена, что вторая реплика принимает изменения с лидера.

<details>
  <summary>Решение:</summary>

1. Создать каскадную репликацию по следующей схеме лидер -> реплика -> реплика.

```
postgres$ pg_ctl initdb "-D" "/var/lib/pgsql/12/main5432" -o "--data-checksums"
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory /var/lib/pgsql/12/main5432 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/pgsql-12/bin/pg_ctl -D /var/lib/pgsql/12/main5432 -l logfile start
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 start
waiting for server to start....2021-04-24 13:28:43.588 UTC [2320] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 13:28:43.588 UTC [2320] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-24 13:28:43.592 UTC [2320] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-24 13:28:43.601 UTC [2320] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-24 13:28:43.616 UTC [2320] LOG:  redirecting log output to logging collector process
2021-04-24 13:28:43.616 UTC [2320] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ psql
psql (12.6)
Type "help" for help.
```

2. Схема "лидер -> реплика" - это потоковая репликация со слотом репликации

```
postgres=# SELECT pg_create_physical_replication_slot('synch_slot5433');
 pg_create_physical_replication_slot
-------------------------------------
 (synch_slot5433,)
(1 row)
postgres=# \q
postgres$ pg_basebackup -D /var/lib/pgsql/12/main5433 -R -S synch_slot5433 -v
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: write-ahead log end point: 0/2000138
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
```

3. Схема "реплика-реплика" - это репликация с архивом

```
postgres$ mkdir /var/lib/pgsql/12/backups/wal
postgres$ echo "port=5433" >> /var/lib/pgsql/12/main5433/postgresql.conf
postgres$ echo "wal_level=replica" >> /var/lib/pgsql/12/main5433/postgresql.auto.conf
postgres$ echo "archive_mode=on" >> /var/lib/pgsql/12/main5433/postgresql.auto.conf
postgres$ echo "archive_command='cp %p /var/lib/pgsql/12/backups/wal/%f >> /var/lib/pgsql/12/main5433/log/archive_command.log 2>&1' " >> /var/lib/pgsql/12/main5433/postgresql.auto.conf
postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 start
waiting for server to start....2021-04-24 13:30:12.857 UTC [2354] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 13:30:12.858 UTC [2354] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-04-24 13:30:12.862 UTC [2354] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2021-04-24 13:30:12.870 UTC [2354] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5433"
2021-04-24 13:30:12.884 UTC [2354] LOG:  redirecting log output to logging collector process
2021-04-24 13:30:12.884 UTC [2354] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ pg_basebackup -D /var/lib/pgsql/12/main5434 -R -v -h localhost -p5433
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_2366"
pg_basebackup: write-ahead log end point: 0/3000060
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: base backup completed
postgres$ echo "restore_command='cp /var/lib/pgsql/12/backups/wal/%f %p >> /var/lib/pgsql/12/main5434/log/restore_command.log 2>&1' " >> /var/lib/pgsql/12/main5434/postgresql.auto.conf
postgres$ echo "port=5434" >> /var/lib/pgsql/12/main5434/postgresql.conf
postgres$ nano ~/12/main5434/postgresql.auto.conf
postgres$ cat ~/12/main5434/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
wal_level=replica
archive_mode=on
archive_command='cp %p /var/lib/pgsql/12/backups/wal/%f >> /var/lib/pgsql/12/main5433/log/archive_command.log 2>&1'
restore_command='cp /var/lib/pgsql/12/backups/wal/%f %p >> /var/lib/pgsql/12/main5434/log/restore_command.log 2>&1'
postgres$ pg_ctl -D /var/lib/pgsql/12/main5434 start
waiting for server to start....2021-04-24 13:32:39.187 UTC [2502] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 13:32:39.188 UTC [2502] LOG:  listening on IPv4 address "127.0.0.1", port 5434
2021-04-24 13:32:39.192 UTC [2502] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5434"
2021-04-24 13:32:39.201 UTC [2502] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5434"
2021-04-24 13:32:39.219 UTC [2502] LOG:  redirecting log output to logging collector process
2021-04-24 13:32:39.219 UTC [2502] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ cat ~/12/main5434/log/postgresql-Sat.log
2021-04-24 13:32:39.077 UTC [2384] LOG:  aborting any active transactions
2021-04-24 13:32:39.079 UTC [2391] LOG:  shutting down
2021-04-24 13:32:39.090 UTC [2384] LOG:  database system is shut down
2021-04-24 13:32:39.225 UTC [2504] LOG:  database system was shut down in recovery at 2021-04-24 13:32:39 UTC
2021-04-24 13:32:39.228 UTC [2504] LOG:  entering standby mode
2021-04-24 13:32:39.235 UTC [2504] LOG:  redo starts at 0/2000028
2021-04-24 13:32:39.239 UTC [2504] LOG:  consistent recovery state reached at 0/3000060
2021-04-24 13:32:39.239 UTC [2504] LOG:  invalid record length at 0/3000060: wanted 24, got 0
2021-04-24 13:32:39.239 UTC [2502] LOG:  database system is ready to accept read only connections
2021-04-24 13:32:39.248 UTC [2514] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
postgres$ psql
psql (12.6)
Type "help" for help.

5432=> create database cascade_replica;
CREATE DATABASE
5432=>\c cascade_replica
You are now connected to database "cascade_replica" as user "postgres".
5432=>create table tt(id int);
CREATE TABLE
5432=>nsert into tt select 1;
INSERT 0 1
5432=> \q
postgres$ psql -p5433 cascade_replica
psql (12.6)
Type "help" for help.

5433=> select * from tt;
 id
----
  1
(1 row)

5433=> \q
postgres$ psql -p5434 cascade_replica
psql (12.6)
Type "help" for help.

5434=> select * from tt;
 id
----
  1
(1 row)

5434=> select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)
```

4. Промоутить первую реплику до лидера

```
cascade_replica=# \q
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 stop
waiting for server to shut down.... done
server stopped
postgres$ pg_ctl -D /var/lib/pgsql/12/main5433 promote
waiting for server to promote.... done
server promoted
```

5. Подключить "бывший" лидер к "новому" лидеру

```
postgres$ psql -p5433
psql (12.6)
Type "help" for help.

5433=> SELECT pg_create_physical_replication_slot('synch_slot5433');
 pg_create_physical_replication_slot
-------------------------------------
 (synch_slot5433,)
(1 row)
postgres=# \q
postgres$ touch /var/lib/pgsql/12/main5432/standby.signal
postgres$ nano /var/lib/pgsql/12/main5432/postgresql.auto.conf
postgres$ cat /var/lib/pgsql/12/main5432/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres passfile=''/var/lib/pgsql/.pgpass'' port=5433 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
primary_slot_name = 'synch_slot5433'
postgres$ pg_ctl -D /var/lib/pgsql/12/main5432 start
waiting for server to start....2021-04-24 13:36:25.747 UTC [2604] LOG:  starting PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-04-24 13:36:25.747 UTC [2604] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-04-24 13:36:25.751 UTC [2604] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2021-04-24 13:36:25.759 UTC [2604] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-04-24 13:36:25.773 UTC [2604] LOG:  redirecting log output to logging collector process
2021-04-24 13:36:25.773 UTC [2604] HINT:  Future log output will appear in directory "log".
 done
server started
postgres$ psql
psql (12.6)
Type "help" for help.

5432=> select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 t
(1 row)

5432=> \q
```

6. Проверить, что вторая реплика принимает изменения с лидера

```
postgres$ psql -p5433 cascade_replica
psql (12.6)
Type "help" for help.

5433=> insert into tt select 2;
INSERT 0 1
5433=> \q
postgres$ psql -p5434 cascade_replica
psql (12.6)
Type "help" for help.

5434=> select * from tt;
 id
----
  1
  2
(2 rows)

5434=> \q
postgres$ psql cascade_replica
psql (12.6)
Type "help" for help.

5432=> select * from tt;
 id
----
  1
  2
(2 rows)

5432=> \q
```
</details>