######## 0. 실습 환경

$ docker run -d --init --name pglab --hostname pglab pg-internals:rel18-lab sleep infinity
8cd799cbc7c8eb369c85839e846248854f810bda9d714c3b7a381cf41f124323
[exit=0]

$ postgres --version
$ initdb -D $PGDATA  > /home/postgres/initdb.log 2>&1 && echo "initdb ok"
postgres (PostgreSQL) 18.6
initdb ok
[exit=0]

$ cat >> $PGDATA/postgresql.conf <<'CONF'
$ log_line_prefix = '%m [%p] %b '
$ cluster_name = 'primary'
$ CONF
$ pg_ctl -D $PGDATA -l /home/postgres/primary.log start
$ psql -X -q -c "CREATE TABLE acct (id int PRIMARY KEY, balance int, pad text)"
$ psql -X -q -c "INSERT INTO acct SELECT g, 100, repeat('p', 200) FROM generate_series(1, 200000) g"
waiting for server to start.... done
server started
[exit=0]


######## 1. standby 만들기: pg_basebackup -R

$ pg_basebackup -D /home/postgres/standby -R -C -S standby1 -c fast && echo "base backup ok"
$ ls /home/postgres/standby/standby.signal
$ cat /home/postgres/standby/postgresql.auto.conf
$ cat >> /home/postgres/standby/postgresql.auto.conf <<'CONF'
$ port = 5433
$ cluster_name = 'standby1'
$ CONF
$ pg_ctl -D /home/postgres/standby -l /home/postgres/standby.log start
$ sleep 1
$ grep -E "entering standby mode|redo starts|consistent recovery state|ready to accept read-only|started streaming" /home/postgres/standby.log | cut -c1-160
base backup ok
/home/postgres/standby/standby.signal
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=postgres passfile=''/home/postgres/.pgpass'' channel_binding=disable port=5432 sslmode=disable sslnegotiation=postgres sslcompression=0 sslcertmode=disable sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=disable krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'standby1'
waiting for server to start.... done
server started
2026-09-24 04:29:34.369 UTC [74] startup LOG:  entering standby mode
2026-09-24 04:29:34.372 UTC [74] startup LOG:  redo starts at 0/6000028
2026-09-24 04:29:34.372 UTC [74] startup LOG:  consistent recovery state reached at 0/6000120
2026-09-24 04:29:34.372 UTC [68] postmaster LOG:  database system is ready to accept read-only connections
2026-09-24 04:29:34.374 UTC [75] walreceiver LOG:  started streaming WAL from primary at 0/7000000 on timeline 1
[exit=0]


######## 2. 복제를 담당하는 프로세스

$ ps -eo pid,args | grep -E "postgres: (primary|standby1): (walsender|walreceiver|startup)" | grep -v grep
     74 postgres: standby1: startup waiting for 000000010000000000000007
     75 postgres: standby1: walreceiver 
     76 postgres: primary: walsender postgres [local] START_REPLICATION
[exit=0]


######## 3. pg_stat_replication과 pg_stat_wal_receiver

$ psql -X -p 5432 -x -c "SELECT pid, application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_state FROM pg_stat_replication"
$ psql -X -p 5433 -x -c "SELECT pid, status, receive_start_lsn, written_lsn, flushed_lsn, slot_name FROM pg_stat_wal_receiver"
$ psql -X -p 5432 -At -c "SELECT 'primary: in_recovery=' || pg_is_in_recovery()"
$ psql -X -p 5433 -At -c "SELECT 'standby: in_recovery=' || pg_is_in_recovery()"
-[ RECORD 1 ]----+----------------
pid              | 76
application_name | standby1
state            | streaming
sent_lsn         | 0/7000000
write_lsn        | 0/7000000
flush_lsn        | 0/7000000
replay_lsn       | 0/7000000
write_lag        | 00:00:00.000024
flush_lag        | 00:00:00.000024
replay_lag       | 00:00:00.000024
sync_state       | async

-[ RECORD 1 ]-----+----------
pid               | 75
status            | streaming
receive_start_lsn | 0/7000000
written_lsn       | 
flushed_lsn       | 0/7000000
slot_name         | standby1

primary: in_recovery=false
standby: in_recovery=true
[exit=0]


######## 4. primary의 변경이 standby에 보인다, standby는 읽기 전용

$ psql -X -p 5432 -q -c "INSERT INTO acct VALUES (200001, 1, 'from primary')"
$ psql -X -p 5432 -c "SELECT pg_current_wal_lsn() AS primary_lsn"
$ sleep 1
$ psql -X -p 5433 -c "SELECT pg_last_wal_receive_lsn() AS received, pg_last_wal_replay_lsn() AS replayed"
$ psql -X -p 5433 -c "SELECT * FROM acct WHERE id = 200001"
$ psql -X -p 5433 -c "INSERT INTO acct VALUES (200002, 1, 'from standby')"
 primary_lsn 
-------------
 0/7002148
(1 row)

 received  | replayed  
-----------+-----------
 0/7002148 | 0/7002148
(1 row)

   id   | balance |     pad      
--------+---------+--------------
 200001 |       1 | from primary
(1 row)

ERROR:  cannot execute INSERT in a read-only transaction
[exit=1]


######## 5. 전송, 기록, 재생: 재생만 늦추면

$ psql -X -p 5433 -q -c "ALTER SYSTEM SET recovery_min_apply_delay = '5s'" -c "SELECT pg_reload_conf()" > /dev/null
$ sleep 1
$ psql -X -p 5432 -q -c "INSERT INTO acct VALUES (200003, 1, 'delayed')"
$ sleep 1
$ psql -X -p 5432 -x -c "SELECT pg_current_wal_lsn() AS primary_lsn, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag FROM pg_stat_replication"
$ psql -X -p 5433 -c "SELECT count(*) AS delayed_row_visible FROM acct WHERE id = 200003"
$ sleep 6
$ psql -X -p 5433 -c "SELECT count(*) AS delayed_row_visible FROM acct WHERE id = 200003"
$ psql -X -p 5432 -x -c "SELECT replay_lsn, replay_lag FROM pg_stat_replication"
$ psql -X -p 5433 -q -c "ALTER SYSTEM RESET recovery_min_apply_delay" -c "SELECT pg_reload_conf()" > /dev/null
-[ RECORD 1 ]----------------
primary_lsn | 0/70021F8
sent_lsn    | 0/70021F8
write_lsn   | 0/70021F8
flush_lsn   | 0/70021F8
replay_lsn  | 0/7002148
write_lag   | 00:00:00.000221
flush_lag   | 00:00:00.000687
replay_lag  | 00:00:00.000687

 delayed_row_visible 
---------------------
                   0
(1 row)

 delayed_row_visible 
---------------------
                   1
(1 row)

-[ RECORD 1 ]--------------
replay_lsn | 0/70021F8
replay_lag | 00:00:05.00795

[exit=0]


######## 6. 동기 복제: 커밋이 standby를 기다린다

$ psql -X -p 5432 -q -c "ALTER SYSTEM SET synchronous_standby_names = 'standby1'" -c "SELECT pg_reload_conf()" > /dev/null
$ sleep 1
$ psql -X -p 5432 -c "SELECT application_name, sync_state FROM pg_stat_replication"
$ psql -X -p 5432 -c "\timing on" -c "INSERT INTO acct VALUES (200004, 1, 'sync ok')"
$ pg_ctl -D /home/postgres/standby stop -m fast
 application_name | sync_state 
------------------+------------
 standby1         | sync
(1 row)

Timing is on.
INSERT 0 1
Time: 1.655 ms
waiting for server to shut down.... done
server stopped
[exit=0]

# 세션 A 시작: psql -X -p 5432

[세션 A] $ INSERT INTO acct VALUES (200005, 1, 'sync wait');

$ psql -X -p 5432 -c "SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event = 'SyncRep'"
$ psql -X -p 5432 -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE wait_event = 'SyncRep'"
 pid | state  | wait_event_type | wait_event |                       query                       
-----+--------+-----------------+------------+---------------------------------------------------
 171 | active | IPC             | SyncRep    | INSERT INTO acct VALUES (200005, 1, 'sync wait');
(1 row)

 pg_cancel_backend 
-------------------
 t
(1 row)

[exit=0]

[세션 A] (앞 명령의 결과를 기다림)
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1

$ psql -X -p 5432 -c "SELECT * FROM acct WHERE id = 200005"
$ pg_ctl -D /home/postgres/standby -l /home/postgres/standby.log start
$ sleep 1
$ psql -X -p 5433 -c "SELECT * FROM acct WHERE id = 200005"
$ psql -X -p 5432 -q -c "ALTER SYSTEM RESET synchronous_standby_names" -c "SELECT pg_reload_conf()" > /dev/null
   id   | balance |    pad    
--------+---------+-----------
 200005 |       1 | sync wait
(1 row)

waiting for server to start.... done
server started
   id   | balance |    pad    
--------+---------+-----------
 200005 |       1 | sync wait
(1 row)

[exit=0]


######## 7. standby 쿼리와 WAL 재생의 충돌

$ psql -X -p 5433 -c "SHOW max_standby_streaming_delay" -c "SHOW hot_standby_feedback"
$ psql -X -p 5433 -q -c "ALTER SYSTEM SET max_standby_streaming_delay = '3s'" -c "SELECT pg_reload_conf()" > /dev/null
 max_standby_streaming_delay 
-----------------------------
 30s
(1 row)

 hot_standby_feedback 
----------------------
 off
(1 row)

[exit=0]

# 세션 B 시작: psql -X -p 5433

[세션 B] $ SELECT count(*), pg_sleep(20) FROM acct;

$ psql -X -p 5432 -q -c "DELETE FROM acct WHERE id > 200000"
$ psql -X -p 5432 -q -c "VACUUM acct"
[exit=0]

[세션 B] (앞 명령의 결과를 기다림)
ERROR:  canceling statement due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.

$ grep -E "conflict with recovery|recovery conflict" /home/postgres/standby.log | tail -3 | cut -c1-200
$ psql -X -p 5433 -c "SELECT datname, confl_snapshot FROM pg_stat_database_conflicts WHERE datname = 'postgres'"
$ psql -X -p 5433 -q -c "ALTER SYSTEM RESET max_standby_streaming_delay" -c "SELECT pg_reload_conf()" > /dev/null
2026-09-24 04:29:59.251 UTC [276] client backend ERROR:  canceling statement due to conflict with recovery
 datname  | confl_snapshot 
----------+----------------
 postgres |              1
(1 row)

[exit=0]


######## 8. replication slot은 standby가 멈춰도 WAL을 붙잡는다

$ psql -X -p 5432 -q -c "ALTER SYSTEM SET max_wal_size = '64MB'" -c "SELECT pg_reload_conf()" > /dev/null
$ pg_ctl -D /home/postgres/standby stop -m fast
$ psql -X -p 5432 -c "SELECT slot_name, active, restart_lsn, wal_status, pg_size_pretty(safe_wal_size) AS safe_wal_size FROM pg_replication_slots"
$ for i in 1 2 3; do psql -X -p 5432 -q -c "UPDATE acct SET balance = balance + 1"; done
$ psql -X -p 5432 -q -c "CHECKPOINT"
$ psql -X -p 5432 -c "SELECT slot_name, active, restart_lsn, wal_status, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained FROM pg_replication_slots"
$ du -sh $PGDATA/pg_wal
waiting for server to shut down.... done
server stopped
 slot_name | active | restart_lsn | wal_status | safe_wal_size 
-----------+--------+-------------+------------+---------------
 standby1  | f      | 0/A0543F0   | reserved   | 
(1 row)

 slot_name | active | restart_lsn | wal_status | retained 
-----------+--------+-------------+------------+----------
 standby1  | f      | 0/A0543F0   | extended   | 479 MB
(1 row)

497M	/var/lib/postgresql/data/pg_wal
[exit=0]


######## 9. max_slot_wal_keep_size: 너무 많이 붙잡으면 slot을 포기한다

$ psql -X -p 5432 -q -c "ALTER SYSTEM SET max_slot_wal_keep_size = '128MB'" -c "SELECT pg_reload_conf()" > /dev/null
$ psql -X -p 5432 -q -c "UPDATE acct SET balance = balance + 1"
$ psql -X -p 5432 -q -c "CHECKPOINT"
$ psql -X -p 5432 -c "SELECT slot_name, active, restart_lsn, wal_status, invalidation_reason FROM pg_replication_slots"
$ grep -E "invalidating obsolete replication slot|exceeds the limit" /home/postgres/primary.log | cut -c1-200
$ du -sh $PGDATA/pg_wal
$ pg_ctl -D /home/postgres/standby -l /home/postgres/standby.log start
$ sleep 2
$ grep -E "could not start WAL streaming" /home/postgres/standby.log | tail -1 | cut -c1-200
 slot_name | active | restart_lsn | wal_status | invalidation_reason 
-----------+--------+-------------+------------+---------------------
 standby1  | f      |             | lost       | wal_removed
(1 row)

2026-09-24 04:30:04.892 UTC [42] checkpointer LOG:  invalidating obsolete replication slot "standby1"
2026-09-24 04:30:04.892 UTC [42] checkpointer DETAIL:  The slot's restart_lsn 0/A0543F0 exceeds the limit by 419085328 bytes.
65M	/var/lib/postgresql/data/pg_wal
waiting for server to start.... done
server started
2026-09-24 04:30:05.567 UTC [398] walreceiver FATAL:  could not start WAL streaming: ERROR:  can no longer access replication slot "standby1"
[exit=0]


######## 10. idle_replication_slot_timeout (PG18)

$ psql -X -p 5432 -c "SELECT * FROM pg_create_physical_replication_slot('forgotten', true)"
$ psql -X -p 5432 -q -c "ALTER SYSTEM SET idle_replication_slot_timeout = '1s'" -c "SELECT pg_reload_conf()" > /dev/null
$ sleep 2
$ psql -X -p 5432 -q -c "CHECKPOINT"
$ psql -X -p 5432 -c "SELECT slot_name, active, inactive_since IS NOT NULL AS has_inactive_since, wal_status, invalidation_reason FROM pg_replication_slots ORDER BY slot_name"
$ grep -E "invalidating obsolete replication slot \"forgotten\"" -A1 /home/postgres/primary.log | cut -c1-200
 slot_name |    lsn     
-----------+------------
 forgotten | 0/3355E610
(1 row)

 slot_name | active | has_inactive_since | wal_status | invalidation_reason 
-----------+--------+--------------------+------------+---------------------
 forgotten | f      | t                  | lost       | idle_timeout
 standby1  | f      | t                  | lost       | wal_removed
(2 rows)

2026-09-24 04:30:09.773 UTC [42] checkpointer LOG:  invalidating obsolete replication slot "forgotten"
2026-09-24 04:30:09.773 UTC [42] checkpointer DETAIL:  The slot's idle time of 2s exceeds the configured "idle_replication_slot_timeout" duration of 1s.
[exit=0]

done