스트리밍 복제와 Replication Slot

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]

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]

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]

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]

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]

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]

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
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]

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]

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
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]

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]

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]

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]

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]