05 Занятие 05: Репликация / Переключение на реплику

α=> ALTER SYSTEM SET checkpoint_timeout = '1d';

ALTER SYSTEM

student$ sudo pg_ctlcluster 16 alpha reload

α=> CREATE DATABASE replica_switchover;

CREATE DATABASE

α=> \c replica_switchover postgres

You are now connected to database "replica_switchover" as user "postgres".

α=> CREATE EXTENSION pg_walinspect;

CREATE EXTENSION

α=> \c - student

You are now connected to database "replica_switchover" as user "student".

α=> CREATE TABLE test(s text);

CREATE TABLE

α=> INSERT INTO test VALUES ('Исходное состояние. Сервер alpha — единственный.');

INSERT 0 1

student$ pg_basebackup \
  --format=tar \
  --compress=gzip \
  --checkpoint=fast \
  --pgdata=/home/student/tmp/backup

student$ sudo pg_ctlcluster 16 beta stop

Cluster is not running.

postgres$ rm -rf /var/lib/postgresql/16/beta

postgres$ mkdir /var/lib/postgresql/16/beta

postgres$ chmod 700 /var/lib/postgresql/16/beta

student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/beta

student$ sudo tar xzf /home/student/tmp/backup/pg_wal.tar.gz -C /var/lib/postgresql/16/beta/pg_wal

postgres$ touch /var/lib/postgresql/16/beta/standby.signal

postgres$ cat << EOF >> /var/lib/postgresql/16/beta/postgresql.auto.conf
primary_conninfo = 'user=student port=5432'
primary_slot_name = 'replica'
EOF

student$ pg_receivewal --create-slot --slot=replica

student$ sudo pg_ctlcluster 16 beta start

α=> INSERT INTO test VALUES ('Проверка репликации: alpha — мастер, beta — реплика.');

INSERT 0 1

student$ psql -p 5433 -d replica_switchover

β=> SELECT * FROM test;

                          s
------------------------------------------------------
 Исходное состояние. Сервер alpha — единственный.
 Проверка репликации: alpha — мастер, beta — реплика.
(2 rows)

α=> INSERT INTO test VALUES ('Перед корректным остановом мастера — сервера alpha.');

INSERT 0 1

student$ sudo pg_ctlcluster 16 alpha stop

postgres$ /usr/lib/postgresql/16/bin/pg_controldata -D ~postgres/16/alpha | \
  grep -E '^.*(cluster|checkpoint.*location).*:'

Database cluster state:               shut down
Latest checkpoint location:           0/30002B8
Latest checkpoint's REDO location:    0/30002B8

β=> SELECT start_lsn, prev_lsn, resource_manager, record_type, record_length,
  left(description, 46) || '...' description
FROM pg_get_wal_records_info('0/30002B8', 'FFFFFFFF/FFFFFFFF') \gx

-[ RECORD 1 ]----+--------------------------------------------------
start_lsn        | 0/30002B8
prev_lsn         | 0/3000290
resource_manager | XLOG
record_type      | CHECKPOINT_SHUTDOWN
record_length    | 114
description      | redo 0/30002B8; tli 1; prev tli 1; fpw true; x...

β=> SELECT pg_last_wal_receive_lsn(), pg_wal_lsn_diff(pg_last_wal_receive_lsn(), '0/30002B8' );

 pg_last_wal_receive_lsn | pg_wal_lsn_diff
-------------------------+-----------------
 0/3000330               |             120
(1 row)

β=> SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 t
(1 row)

student$ sudo pg_ctlcluster 16 beta promote

β=> SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 f
(1 row)

postgres$ touch /var/lib/postgresql/16/alpha/standby.signal

postgres$ cat << EOF >> /var/lib/postgresql/16/alpha/postgresql.auto.conf
primary_conninfo = 'user=student port=5433'
EOF

student$ sudo pg_ctlcluster 16 alpha start

student$ psql -d replica_switchover

α=> SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 t
(1 row)

β=> INSERT INTO test VALUES ('Сейчас сервер beta — мастер, alpha запущена как реплика.');

INSERT 0 1

α=> SELECT * FROM test;

                            s
----------------------------------------------------------
 Исходное состояние. Сервер alpha — единственный.
 Проверка репликации: alpha — мастер, beta — реплика.
 Перед корректным остановом мастера — сервера
alpha.
 Сейчас сервер beta — мастер, alpha запущена как
реплика.
(4 rows)

student$ sudo pg_ctlcluster 16 alpha promote

β=> INSERT INTO test VALUES ('Die hard');

INSERT 0 1

β=> \dconfig data_checksums|full_page_writes

List of configuration parameters
    Parameter     | Value
------------------+-------
 data_checksums   | on
 full_page_writes | on
(2 rows)

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/beta/postmaster.pid)

postgres$ /usr/lib/postgresql/16/bin/pg_rewind \
  -D /var/lib/postgresql/16/beta \
  --config-file=/etc/postgresql/16/beta/postgresql.conf \
  -R \
  --source-server='user=student port=5432'

pg_rewind: executing "/usr/lib/postgresql/16/bin/postgres" for target server to complete
crash recovery
2026-04-10 18:06:02.619 MSK [74028] LOG:  database system was interrupted; last known up
at 2026-04-10 18:05:54 MSK
2026-04-10 18:06:02.619 MSK [74028] LOG:  database system was not properly shut down;
automatic recovery in progress
2026-04-10 18:06:02.621 MSK [74028] LOG:  redo starts at 0/3000360
2026-04-10 18:06:02.622 MSK [74028] LOG:  invalid record length at 0/3006BF0: expected at
least 24, got 0
2026-04-10 18:06:02.622 MSK [74028] LOG:  redo done at 0/3006BB0 system usage: CPU: user:
0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-04-10 18:06:02.624 MSK [74028] LOG:  checkpoint starting: end-of-recovery immediate
wait
2026-04-10 18:06:02.625 MSK [74028] LOG:  checkpoint complete: wrote 8 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s;
sync files=0, longest=0.000 s, average=0.000 s; distance=26 kB, estimate=26 kB;
lsn=0/3006BF0, redo lsn=0/3006BF0

PostgreSQL stand-alone backend 16.11 (Ubuntu 16.11-1.pgdg24.04+1)
backend> 2026-04-10 18:06:02.633 MSK [74028] LOG:  checkpoint starting: shutdown immediate
2026-04-10 18:06:02.634 MSK [74028] LOG:  checkpoint complete: wrote 0 buffers (0.0%); 0
WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s, total=0.002 s;
sync files=0, longest=0.000 s, average=0.000 s; distance=0 kB, estimate=23 kB;
lsn=0/3006C68, redo lsn=0/3006C68

pg_rewind: servers diverged at WAL location 0/3002C38 on timeline 2
pg_rewind: rewinding from last common checkpoint at 0/3002968 on timeline 2
pg_rewind: Done!

postgres$ cat /var/lib/postgresql/16/beta/backup_label

START WAL LOCATION: 0/3000360 (file 000000020000000000000003)
CHECKPOINT LOCATION: 0/3002968
BACKUP METHOD: pg_rewind
BACKUP FROM: standby
START TIME: 2026-04-10 18:06:03 MSK

postgres$ ls -l /var/lib/postgresql/16/beta/standby.signal

-rw------- 1 postgres postgres 0 апр 10 18:06
/var/lib/postgresql/16/beta/standby.signal

postgres$ echo primary_slot_name = 'replica' >> /var/lib/postgresql/16/beta/postgresql.auto.conf

postgres$ cat /var/lib/postgresql/16/beta/postgresql.auto.conf

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
checkpoint_timeout = '1d'
primary_conninfo = 'user=student port=5433'
primary_conninfo = 'user=student passfile=''/var/lib/postgresql/.pgpass''
channel_binding=prefer port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0
sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer
krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = replica

student$ sudo pg_ctlcluster 16 beta start

α=> SELECT * FROM pg_replication_slots \gx

-[ RECORD 1 ]-------+----------
slot_name           | replica
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 74281
xmin                |
catalog_xmin        |
restart_lsn         | 0/305E000
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         |

student$ psql -p 5433 -d replica_switchover

β=> SELECT * FROM test;

                            s
----------------------------------------------------------
 Исходное состояние. Сервер alpha — единственный.
 Проверка репликации: alpha — мастер, beta — реплика.
 Перед корректным остановом мастера — сервера
alpha.
 Сейчас сервер beta — мастер, alpha запущена как
реплика.
(4 rows)

α=> INSERT INTO test VALUES ('Еще строка с сервера alpha.');

INSERT 0 1

β=> SELECT * FROM test;

                            s
----------------------------------------------------------
 Исходное состояние. Сервер alpha — единственный.
 Проверка репликации: alpha — мастер, beta — реплика.
 Перед корректным остановом мастера — сервера
alpha.
 Сейчас сервер beta — мастер, alpha запущена как
реплика.
 Еще строка с сервера alpha.
(5 rows)

student$ sudo pg_ctlcluster 16 alpha stop

student$ sudo pg_ctlcluster 16 beta stop

postgres$ rm -rf /var/lib/postgresql/16/alpha

postgres$ mkdir /var/lib/postgresql/16/alpha

postgres$ chmod 700 /var/lib/postgresql/16/alpha

postgres$ rm -rf /var/lib/postgresql/16/beta

postgres$ mkdir /var/lib/postgresql/16/beta

postgres$ chmod 700 /var/lib/postgresql/16/beta

student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/alpha

student$ sudo tar xzf /home/student/tmp/backup/pg_wal.tar.gz -C /var/lib/postgresql/16/alpha/pg_wal

student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/beta

student$ sudo tar xzf /home/student/tmp/backup/pg_wal.tar.gz -C /var/lib/postgresql/16/beta/pg_wal

postgres$ rm -rf /var/lib/postgresql/archive

postgres$ mkdir /var/lib/postgresql/archive

postgres$ cat << EOF | tee -a \
  /var/lib/postgresql/16/alpha/postgresql.auto.conf \
  /var/lib/postgresql/16/beta/postgresql.auto.conf
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'
EOF

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

postgres$ touch /var/lib/postgresql/16/beta/standby.signal

postgres$ cat << EOF >> /var/lib/postgresql/16/beta/postgresql.auto.conf
primary_conninfo = 'user=student port=5432'
primary_slot_name = 'replica'
EOF

student$ sudo pg_ctlcluster 16 alpha start

student$ pg_receivewal --create-slot --slot=replica

student$ sudo pg_ctlcluster 16 beta start

student$ psql -d replica_switchover

α=> SELECT pg_walfile_name(pg_current_wal_lsn());

     pg_walfile_name
--------------------------
 000000010000000000000003
(1 row)

α=> SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/3006658
(1 row)

α=> INSERT INTO test SELECT now();

INSERT 0 1

α=> SELECT
  pg_walfile_name(pg_current_wal_lsn()) current_wal,
  last_archived_wal,
  last_failed_wal
FROM pg_stat_archiver;

       current_wal        |    last_archived_wal     | last_failed_wal
--------------------------+--------------------------+-----------------
 000000010000000000000004 | 000000010000000000000003 |
(1 row)

α=> ALTER SYSTEM SET archive_command = 'exit 1';

ALTER SYSTEM

student$ sudo pg_ctlcluster 16 alpha reload

α=> SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/4002C08
(1 row)

α=> INSERT INTO test SELECT now();

INSERT 0 1

α=> SELECT
  pg_walfile_name(pg_current_wal_lsn()) current_wal,
  last_archived_wal,
  last_failed_wal
FROM pg_stat_archiver;

       current_wal        |    last_archived_wal     |     last_failed_wal
--------------------------+--------------------------+--------------------------
 000000010000000000000005 | 000000010000000000000003 | 000000010000000000000004
(1 row)

student$ tail -n 4 /var/log/postgresql/postgresql-16-alpha.log

2026-04-10 18:07:21.223 MSK [75035] LOG:  archive command failed with exit code 1
2026-04-10 18:07:21.223 MSK [75035] DETAIL:  The failed archive command was: exit 1
2026-04-10 18:07:22.224 MSK [75035] LOG:  archive command failed with exit code 1
2026-04-10 18:07:22.224 MSK [75035] DETAIL:  The failed archive command was: exit 1

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/alpha/postmaster.pid)

student$ sudo pg_ctlcluster 16 beta promote

student$ psql -p 5433 -d replica_switchover

β=> SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/5002158
(1 row)

β=> INSERT INTO test SELECT now();

INSERT 0 1

student$ ls -l /var/lib/postgresql/archive

total 49156
-rw------- 1 postgres postgres 16777216 апр 10 18:07 000000010000000000000003
-rw------- 1 postgres postgres 16777216 апр 10 18:07 000000010000000000000005.partial
-rw------- 1 postgres postgres 16777216 апр 10 18:07 000000020000000000000005
-rw------- 1 postgres postgres       41 апр 10 18:07 00000002.history

student$ sudo pg_ctlcluster 16 alpha stop

Cluster is not running.

student$ sudo pg_ctlcluster 16 beta stop

postgres$ rm -rf /var/lib/postgresql/16/alpha

postgres$ mkdir /var/lib/postgresql/16/alpha

postgres$ chmod 700 /var/lib/postgresql/16/alpha

postgres$ rm -rf /var/lib/postgresql/16/beta

postgres$ mkdir /var/lib/postgresql/16/beta

postgres$ chmod 700 /var/lib/postgresql/16/beta

student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/alpha

student$ sudo tar xzf /home/student/tmp/backup/pg_wal.tar.gz -C /var/lib/postgresql/16/alpha/pg_wal

student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/beta

student$ sudo tar xzf /home/student/tmp/backup/pg_wal.tar.gz -C /var/lib/postgresql/16/beta/pg_wal

postgres$ rm -rf /var/lib/postgresql/archive

postgres$ mkdir /var/lib/postgresql/archive

postgres$ cat << EOF | tee -a /var/lib/postgresql/16/alpha/postgresql.auto.conf
archive_mode = on
archive_command = 'test -f /var/lib/postgresql/archive/%f || cp %p /var/lib/postgresql/archive/%f'
EOF

archive_mode = on
archive_command = 'test -f /var/lib/postgresql/archive/%f || cp %p
/var/lib/postgresql/archive/%f'

postgres$ cat << EOF | tee -a /var/lib/postgresql/16/beta/postgresql.auto.conf
archive_mode = always
archive_command = 'test -f /var/lib/postgresql/archive/%f || cp %p /var/lib/postgresql/archive/%f'
primary_conninfo = 'user=student port=5432'
primary_slot_name = 'replica'
EOF

archive_mode = always
archive_command = 'test -f /var/lib/postgresql/archive/%f || cp %p
/var/lib/postgresql/archive/%f'
primary_conninfo = 'user=student port=5432'
primary_slot_name = 'replica'

postgres$ touch /var/lib/postgresql/16/beta/standby.signal

student$ sudo pg_ctlcluster 16 alpha start

student$ pg_receivewal --create-slot --slot=replica

student$ sudo pg_ctlcluster 16 beta start

student$ psql -d replica_switchover

α=> SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/3006658
(1 row)

α=> INSERT INTO test SELECT now();

INSERT 0 1

α=> SELECT
  pg_walfile_name(pg_current_wal_lsn()) current_wal,
  last_archived_wal,
  last_failed_wal
FROM pg_stat_archiver;

       current_wal        |    last_archived_wal     | last_failed_wal
--------------------------+--------------------------+-----------------
 000000010000000000000004 | 000000010000000000000003 |
(1 row)

α=> ALTER SYSTEM SET archive_command = 'exit 1';

ALTER SYSTEM

student$ sudo pg_ctlcluster 16 alpha reload

α=> SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/4002C08
(1 row)

α=> INSERT INTO test SELECT now();

INSERT 0 1

α=> SELECT
  pg_walfile_name(pg_current_wal_lsn()) current_wal,
  last_archived_wal,
  last_failed_wal
FROM pg_stat_archiver;

       current_wal        |    last_archived_wal     |     last_failed_wal
--------------------------+--------------------------+--------------------------
 000000010000000000000005 | 000000010000000000000003 | 000000010000000000000004
(1 row)

student$ ls -l /var/lib/postgresql/archive

total 32768
-rw------- 1 postgres postgres 16777216 апр 10 18:08 000000010000000000000003
-rw------- 1 postgres postgres 16777216 апр 10 18:08 000000010000000000000004

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/alpha/postmaster.pid)

student$ sudo pg_ctlcluster 16 beta promote

student$ psql -p 5433 -d replica_switchover

β=> SELECT pg_switch_wal();

 pg_switch_wal
---------------
 0/5002158
(1 row)

β=> INSERT INTO test SELECT now();

INSERT 0 1

student$ ls -l /var/lib/postgresql/archive

total 65540
-rw------- 1 postgres postgres 16777216 апр 10 18:08 000000010000000000000003
-rw------- 1 postgres postgres 16777216 апр 10 18:08 000000010000000000000004
-rw------- 1 postgres postgres 16777216 апр 10 18:08 000000010000000000000005.partial
-rw------- 1 postgres postgres 16777216 апр 10 18:08 000000020000000000000005
-rw------- 1 postgres postgres       41 апр 10 18:08 00000002.history

student$ sudo pg_ctlcluster 16 beta stop

Cluster is not running.

postgres$ rm -rf /var/lib/postgresql/16/beta

postgres$ pg_basebackup \
  --checkpoint=fast \
  --pgdata=/var/lib/postgresql/16/beta \
  -R \
  --create-slot \
  --slot=replica

postgres$ cat /var/lib/postgresql/16/beta/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/postgresql/.pgpass''
channel_binding=prefer host=''/var/run/postgresql'' port=5432 sslmode=prefer
sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1
ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0
target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica'

student$ sudo pg_ctlcluster 16 beta start

α=> CREATE DATABASE replica_switchover;

CREATE DATABASE

α=> \c replica_switchover

You are now connected to database "replica_switchover" as user "student".

α=> CREATE TABLE test(s text);

CREATE TABLE

α=> INSERT INTO test VALUES ('Привет, мир!');

INSERT 0 1

student$ psql -p 5433

β=> \c replica_switchover

You are now connected to database "replica_switchover" as user "student".

β=> SELECT * FROM test;

      s
--------------
 Привет, мир!
(1 row)

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/alpha/postmaster.pid)

student$ sudo pg_ctlcluster 16 beta promote

postgres$ rm -f /var/lib/postgresql/16/beta/postgresql.auto.conf

postgres$ rm -rf /var/lib/postgresql/16/alpha

postgres$ pg_basebackup \
  --port=5433 \
  --checkpoint=fast \
  --pgdata=/var/lib/postgresql/16/alpha \
  -R \
  --create-slot \
  --slot=replica

postgres$ cat /var/lib/postgresql/16/alpha/postgresql.auto.conf

primary_conninfo = 'user=postgres passfile=''/var/lib/postgresql/.pgpass''
channel_binding=prefer port=5433 sslmode=prefer sslnegotiation=postgres sslcompression=0
sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer
krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica'

postgres$ ls -l /var/lib/postgresql/16/alpha/standby.signal

-rw------- 1 postgres postgres 0 апр 10 18:20
/var/lib/postgresql/16/alpha/standby.signal

student$ sudo pg_ctlcluster 16 alpha start

β=> SELECT * FROM pg_replication_slots \gx

-[ RECORD 1 ]-------+----------
slot_name           | replica
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 99939
xmin                |
catalog_xmin        |
restart_lsn         | 0/5000060
confirmed_flush_lsn |
wal_status          | reserved
safe_wal_size       |
two_phase           | f
conflicting         |

β=> INSERT INTO test VALUES ('Я — бывшая реплика (новый мастер).');

INSERT 0 1

student$ psql -d replica_switchover

α=> SELECT * FROM test;

                 s
------------------------------------
 Привет, мир!
 Я — бывшая реплика (новый мастер).
(2 rows)

student$ sudo pg_ctlcluster 16 alpha promote

α=> select pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 f
(1 row)

α=> \c postgres

You are now connected to database "postgres" as user "student".

α=> DROP DATABASE replica_switchover;

DROP DATABASE

student$ sudo pg_ctlcluster 16 alpha restart

β=> \c postgres

You are now connected to database "postgres" as user "student".

β=> SELECT pg_drop_replication_slot('replica');

 pg_drop_replication_slot
--------------------------
(1 row)

student$ sudo pg_ctlcluster 16 beta stop

α=> ALTER SYSTEM SET wal_keep_size = 32;

ALTER SYSTEM

student$ sudo pg_ctlcluster 16 beta stop

Cluster is not running.

postgres$ rm -rf /var/lib/postgresql/16/beta

postgres$ pg_basebackup --checkpoint=fast -R --pgdata=/var/lib/postgresql/16/beta

student$ sudo pg_ctlcluster 16 beta start

α=> CREATE DATABASE replica_switchover;

CREATE DATABASE

α=> \c replica_switchover

You are now connected to database "replica_switchover" as user "student".

α=> CREATE TABLE test AS SELECT 'Сервер alpha — мастер.' txt;

SELECT 1

student$ psql -p 5433 -d replica_switchover

β=> SELECT * FROM test;

          txt
------------------------
 Сервер alpha — мастер.
(1 row)

α=> SELECT * FROM pg_stat_replication \gx

-[ RECORD 1 ]----+------------------------------
pid              | 100787
usesysid         | 10
usename          | postgres
application_name | 16/beta
client_addr      |
client_hostname  |
client_port      | -1
backend_start    | 2026-04-10 18:21:16.297541+03
backend_xmin     |
state            | streaming
sent_lsn         | 0/3432440
write_lsn        | 0/3432440
flush_lsn        | 0/3432440
replay_lsn       | 0/3432440
write_lag        | 00:00:00.000407
flush_lag        | 00:00:00.007474
replay_lag       | 00:00:00.008946
sync_priority    | 0
sync_state       | async
reply_time       | 2026-04-10 18:21:23.621078+03

β=> SELECT pid, status, written_lsn FROM pg_stat_wal_receiver;

  pid   |  status   | written_lsn
--------+-----------+-------------
 100786 | streaming | 0/3432440
(1 row)

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/alpha/postmaster.pid)

student$ sudo pg_ctlcluster 16 beta promote

β=> INSERT INTO test VALUES('Сервер beta повышен, он выполняет пишущие транзакции.');

INSERT 0 1

student$ sudo pg_ctlcluster 16 alpha start

student$ psql -d replica_switchover

α=> INSERT INTO test VALUES('Die Hard...');

INSERT 0 1

α=> SELECT * FROM test;

          txt
------------------------
 Сервер alpha — мастер.
 Die Hard...
(2 rows)

β=> SELECT * FROM test;

                          txt
-------------------------------------------------------
 Сервер alpha — мастер.
 Сервер beta повышен, он выполняет пишущие
транзакции.
(2 rows)

student$ sudo pg_ctlcluster 16 alpha stop

postgres$ /usr/lib/postgresql/16/bin/pg_rewind \
  -D /var/lib/postgresql/16/alpha \
  --config-file=/etc/postgresql/16/alpha/postgresql.conf \
  --source-server='user=postgres port=5433' \
  -R

pg_rewind: servers diverged at WAL location 0/3432440 on timeline 1
pg_rewind: rewinding from last common checkpoint at 0/2000060 on timeline 1
pg_rewind: Done!

β=> INSERT INTO test VALUES('На сервере beta продолжаются изменения.');

INSERT 0 1

student$ sudo pg_ctlcluster 16 alpha start

student$ psql -d replica_switchover

α=> SELECT * FROM test;

                          txt
-------------------------------------------------------
 Сервер alpha — мастер.
 Сервер beta повышен, он выполняет пишущие
транзакции.
 На сервере beta продолжаются изменения.
(3 rows)

β=> \c postgres postgres

You are now connected to database "postgres" as user "postgres".

β=> DROP DATABASE replica_switchover;

DROP DATABASE

β=> ALTER SYSTEM RESET ALL;

ALTER SYSTEM

student$ sudo pg_ctlcluster 16 beta stop

α=> \c postgres postgres

You are now connected to database "postgres" as user "postgres".

α=> SELECT pg_promote();

 pg_promote
------------
 t
(1 row)

α=> ALTER SYSTEM RESET ALL;

ALTER SYSTEM

student$ sudo pg_ctlcluster 16 alpha restart