스트리밍 복제와 Replication Slot

$ 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
waiting for server to start.... done
server started

primary=# CREATE TABLE acct (id int PRIMARY KEY, balance int, pad text);
primary=# INSERT INTO acct SELECT g, 100, repeat('p', 200) FROM generate_series(1, 200000) g;

$ pg_basebackup -D /home/postgres/standby -R -C -S standby1 -c fast && echo "base backup ok"
base backup ok
$ ls /home/postgres/standby/standby.signal
/home/postgres/standby/standby.signal
$ cat /home/postgres/standby/postgresql.auto.conf
# 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'
$ 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
waiting for server to start.... done
server started
$ 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
2026-09-26 10:11:24.580 UTC [70] startup LOG:  entering standby mode
2026-09-26 10:11:24.581 UTC [70] startup LOG:  redo starts at 0/6000028
2026-09-26 10:11:24.582 UTC [70] startup LOG:  consistent recovery state reached at 0/6000120
2026-09-26 10:11:24.582 UTC [64] postmaster LOG:  database system is ready to accept read-only connections
2026-09-26 10:11:24.583 UTC [71] walreceiver LOG:  started streaming WAL from primary at 0/7000000 on timeline 1

$ ps -eo pid,args | grep -E "postgres: (primary|standby1): (walsender|walreceiver|startup)" | grep -v grep
     70 postgres: standby1: startup waiting for 000000010000000000000007
     71 postgres: standby1: walreceiver 
     72 postgres: primary: walsender postgres [local] START_REPLICATION

primary=# 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 \gx
-[ RECORD 1 ]----+----------------
pid              | 72
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.000019
flush_lag        | 00:00:00.000019
replay_lag       | 00:00:00.000019
sync_state       | async

standby=# SELECT pid, status, receive_start_lsn, written_lsn, flushed_lsn, slot_name FROM pg_stat_wal_receiver \gx
-[ RECORD 1 ]-----+----------
pid               | 71
status            | streaming
receive_start_lsn | 0/7000000
written_lsn       | 
flushed_lsn       | 0/7000000
slot_name         | standby1

primary=# SELECT 'primary: in_recovery=' || pg_is_in_recovery() \g (format=unaligned tuples_only=on)
primary: in_recovery=false
standby=# SELECT 'standby: in_recovery=' || pg_is_in_recovery() \g (format=unaligned tuples_only=on)
standby: in_recovery=true

primary=# INSERT INTO acct VALUES (200001, 1, 'from primary');
primary=# SELECT pg_current_wal_lsn() AS primary_lsn;
 primary_lsn 
-------------
 0/7002148
(1 row)

standby=# SELECT pg_last_wal_receive_lsn() AS received, pg_last_wal_replay_lsn() AS replayed;
 received  | replayed  
-----------+-----------
 0/7002148 | 0/7002148
(1 row)

standby=# SELECT * FROM acct WHERE id = 200001;
   id   | balance |     pad      
--------+---------+--------------
 200001 |       1 | from primary
(1 row)

standby=# INSERT INTO acct VALUES (200002, 1, 'from standby');
ERROR:  cannot execute INSERT in a read-only transaction

standby=# ALTER SYSTEM SET recovery_min_apply_delay = '5s';
standby=# SELECT pg_reload_conf();
primary=# INSERT INTO acct VALUES (200003, 1, 'delayed');
primary=# 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 \gx
-[ 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.000275
flush_lag   | 00:00:00.000586
replay_lag  | 00:00:00.000586

standby=# SELECT count(*) AS delayed_row_visible FROM acct WHERE id = 200003;
 delayed_row_visible 
---------------------
                   0
(1 row)

standby=# SELECT count(*) AS delayed_row_visible FROM acct WHERE id = 200003;
 delayed_row_visible 
---------------------
                   1
(1 row)

primary=# SELECT replay_lsn, replay_lag FROM pg_stat_replication \gx
-[ RECORD 1 ]---------------
replay_lsn | 0/70021F8
replay_lag | 00:00:05.007244

standby=# ALTER SYSTEM RESET recovery_min_apply_delay;
standby=# SELECT pg_reload_conf();

primary=# ALTER SYSTEM SET synchronous_standby_names = 'standby1';
primary=# SELECT pg_reload_conf();
primary=# SELECT application_name, sync_state FROM pg_stat_replication;
 application_name | sync_state 
------------------+------------
 standby1         | sync
(1 row)

primary=# \timing on
Timing is on.
primary=# INSERT INTO acct VALUES (200004, 1, 'sync ok');
INSERT 0 1
Time: 1.558 ms

$ pg_ctl -D /home/postgres/standby stop -m fast
waiting for server to shut down.... done
server stopped

A=# INSERT INTO acct VALUES (200005, 1, 'sync wait');

primary=# SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event = 'SyncRep';
 pid | state  | wait_event_type | wait_event |                       query                       
-----+--------+-----------------+------------+---------------------------------------------------
 167 | active | IPC             | SyncRep    | INSERT INTO acct VALUES (200005, 1, 'sync wait');
(1 row)

primary=# SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE wait_event = 'SyncRep';
 pg_cancel_backend 
-------------------
 t
(1 row)

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

primary=# SELECT * FROM acct WHERE id = 200005;
   id   | balance |    pad    
--------+---------+-----------
 200005 |       1 | sync wait
(1 row)

$ pg_ctl -D /home/postgres/standby -l /home/postgres/standby.log start
waiting for server to start.... done
server started
$ sleep 1

standby=# SELECT * FROM acct WHERE id = 200005;
   id   | balance |    pad    
--------+---------+-----------
 200005 |       1 | sync wait
(1 row)

primary=# ALTER SYSTEM RESET synchronous_standby_names;
primary=# SELECT pg_reload_conf();

standby=# SHOW max_standby_streaming_delay;
 max_standby_streaming_delay 
-----------------------------
 30s
(1 row)

standby=# SHOW hot_standby_feedback;
 hot_standby_feedback 
----------------------
 off
(1 row)

standby=# ALTER SYSTEM SET max_standby_streaming_delay = '3s';
standby=# SELECT pg_reload_conf();

B=# SELECT count(*), pg_sleep(20) FROM acct;
primary=# DELETE FROM acct WHERE id > 200000;
primary=# VACUUM acct;

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
2026-09-26 10:11:49.202 UTC [271] client backend ERROR:  canceling statement due to conflict with recovery

standby=# SELECT datname, confl_snapshot FROM pg_stat_database_conflicts WHERE datname = 'postgres';
 datname  | confl_snapshot 
----------+----------------
 postgres |              1
(1 row)

standby=# ALTER SYSTEM RESET max_standby_streaming_delay;
standby=# SELECT pg_reload_conf();

primary=# ALTER SYSTEM SET max_wal_size = '64MB';
primary=# SELECT pg_reload_conf();

$ pg_ctl -D /home/postgres/standby stop -m fast
waiting for server to shut down.... done
server stopped

primary=# SELECT slot_name, active, restart_lsn, wal_status, pg_size_pretty(safe_wal_size) AS safe_wal_size FROM pg_replication_slots;
 slot_name | active | restart_lsn | wal_status | safe_wal_size 
-----------+--------+-------------+------------+---------------
 standby1  | f      | 0/A0543F0   | reserved   | 
(1 row)

primary=# UPDATE acct SET balance = balance + 1;
primary=# UPDATE acct SET balance = balance + 1;
primary=# UPDATE acct SET balance = balance + 1;
primary=# CHECKPOINT;
primary=# 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;
 slot_name | active | restart_lsn | wal_status | retained 
-----------+--------+-------------+------------+----------
 standby1  | f      | 0/A0543F0   | extended   | 480 MB
(1 row)

$ du -sh $PGDATA/pg_wal
497M	/var/lib/postgresql/data/pg_wal

primary=# ALTER SYSTEM SET max_slot_wal_keep_size = '128MB';
primary=# SELECT pg_reload_conf();
primary=# UPDATE acct SET balance = balance + 1;
primary=# CHECKPOINT;
primary=# SELECT slot_name, active, restart_lsn, wal_status, invalidation_reason FROM pg_replication_slots;
 slot_name | active | restart_lsn | wal_status | invalidation_reason 
-----------+--------+-------------+------------+---------------------
 standby1  | f      |             | lost       | wal_removed
(1 row)

$ grep -E "invalidating obsolete replication slot|exceeds the limit" /home/postgres/primary.log | cut -c1-200
2026-09-26 10:11:54.892 UTC [39] checkpointer LOG:  invalidating obsolete replication slot "standby1"
2026-09-26 10:11:54.892 UTC [39] checkpointer DETAIL:  The slot's restart_lsn 0/A0543F0 exceeds the limit by 419085328 bytes.
$ du -sh $PGDATA/pg_wal
65M	/var/lib/postgresql/data/pg_wal
$ pg_ctl -D /home/postgres/standby -l /home/postgres/standby.log start
waiting for server to start.... done
server started
$ sleep 2
$ grep -E "could not start WAL streaming" /home/postgres/standby.log | tail -1 | cut -c1-200
2026-09-26 10:11:55.591 UTC [392] walreceiver FATAL:  could not start WAL streaming: ERROR:  can no longer access replication slot "standby1"

primary=# SELECT * FROM pg_create_physical_replication_slot('forgotten', true);
 slot_name |    lsn     
-----------+------------
 forgotten | 0/33639AA8
(1 row)

primary=# ALTER SYSTEM SET idle_replication_slot_timeout = '1s';
primary=# SELECT pg_reload_conf();
primary=# CHECKPOINT;
primary=# 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;
 slot_name | active | has_inactive_since | wal_status | invalidation_reason 
-----------+--------+--------------------+------------+---------------------
 forgotten | f      | t                  | lost       | idle_timeout
 standby1  | f      | t                  | lost       | wal_removed
(2 rows)

$ grep -E "invalidating obsolete replication slot \"forgotten\"" -A1 /home/postgres/primary.log | cut -c1-200
2026-09-26 10:11:59.786 UTC [39] checkpointer LOG:  invalidating obsolete replication slot "forgotten"
2026-09-26 10:11:59.786 UTC [39] checkpointer DETAIL:  The slot's idle time of 2s exceeds the configured "idle_replication_slot_timeout" duration of 1s.