######## 0. 실습 환경 $ docker run -d --init --name pglab --hostname pglab pg-internals:rel18-lab sleep infinity 55c972d387088fdc3925bb59dcd12f8e65b972878059a1043438b4a2d5c03304 [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 ' $ CONF $ pg_ctl -D $PGDATA -l /home/postgres/server.log start $ psql -X -q -c "CREATE EXTENSION pg_walinspect" $ 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. pg_control: 서버가 기억하는 마지막 체크포인트 $ pg_controldata $PGDATA | grep -E "cluster state|Latest checkpoint location|Latest checkpoint's REDO location|Latest checkpoint's REDO WAL file|Time of latest checkpoint" $ psql -X -c "SHOW checkpoint_timeout" -c "SHOW max_wal_size" -c "SHOW checkpoint_completion_target" -c "SHOW log_checkpoints" Database cluster state: in production Latest checkpoint location: 0/1758C08 Latest checkpoint's REDO location: 0/1758C08 Latest checkpoint's REDO WAL file: 000000010000000000000001 Time of latest checkpoint: Thu Sep 24 04:11:56 2026 checkpoint_timeout -------------------- 5min (1 row) max_wal_size -------------- 1GB (1 row) checkpoint_completion_target ------------------------------ 0.9 (1 row) log_checkpoints ----------------- on (1 row) [exit=0] ######## 2. 수동 CHECKPOINT $ psql -X -q -c "UPDATE acct SET balance = balance + 1 WHERE id <= 50000" $ psql -X -c "SELECT num_timed, num_requested, num_done, buffers_written FROM pg_stat_checkpointer" $ psql -X -c "SELECT pg_current_wal_insert_lsn() AS before_checkpoint" $ psql -X -q -c "CHECKPOINT" $ grep -E "checkpoint (starting|complete)" /home/postgres/server.log | tail -2 $ pg_controldata $PGDATA | grep -E "Latest checkpoint location|Latest checkpoint's REDO location" $ psql -X -c "SELECT num_timed, num_requested, num_done, buffers_written FROM pg_stat_checkpointer" num_timed | num_requested | num_done | buffers_written -----------+---------------+----------+----------------- 0 | 0 | 0 | 0 (1 row) before_checkpoint ------------------- 0/6C91478 (1 row) 2026-09-24 04:11:57.320 UTC [42] checkpointer LOG: checkpoint starting: immediate force wait 2026-09-24 04:11:57.367 UTC [42] checkpointer LOG: checkpoint complete: wrote 8337 buffers (50.9%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0 removed, 5 recycled; write=0.012 s, sync=0.024 s, total=0.048 s; sync files=52, longest=0.013 s, average=0.001 s; distance=87266 kB, estimate=87266 kB; lsn=0/6C914D0, redo lsn=0/6C91478 Latest checkpoint location: 0/6C914D0 Latest checkpoint's REDO location: 0/6C91478 num_timed | num_requested | num_done | buffers_written -----------+---------------+----------+----------------- 0 | 1 | 1 | 8337 (1 row) [exit=0] ######## 3. WAL 안의 체크포인트 레코드 $ R=$(pg_controldata $PGDATA | awk -F': *' '/REDO location/ {print $2}') $ C=$(pg_controldata $PGDATA | awk -F': *' '/Latest checkpoint location/ {print $2}') $ echo "REDO=$R CHECKPOINT=$C" $ psql -X -c "SELECT start_lsn, resource_manager AS rmgr, record_type, record_length AS len, left(description, 90) AS description FROM pg_get_wal_records_info('$R', pg_current_wal_insert_lsn())" REDO=0/6C91478 CHECKPOINT=0/6C914D0 start_lsn | rmgr | record_type | len | description -----------+---------+-------------------+-----+-------------------------------------------------------------------------------------------- 0/6C91478 | XLOG | CHECKPOINT_REDO | 30 | wal_level replica 0/6C91498 | Standby | RUNNING_XACTS | 50 | nextXid 756 latestCompletedXid 755 oldestRunningXid 756 0/6C914D0 | XLOG | CHECKPOINT_ONLINE | 114 | redo 0/6C91478; tli 1; prev tli 1; fpw true; wal_level replica; xid 0:756; oid 24576; mult (3 rows) [exit=0] ######## 4. WAL이 많이 쌓여도 체크포인트가 일어난다 $ psql -X -q -c "ALTER SYSTEM SET max_wal_size = '64MB'" -c "SELECT pg_reload_conf()" > /dev/null $ psql -X -c "SHOW max_wal_size" $ psql -X -q -c "UPDATE acct SET balance = balance + 1" $ psql -X -q -c "UPDATE acct SET balance = balance + 1" $ sleep 2 $ grep -E "checkpoint starting|checkpoints are occurring too frequently" /home/postgres/server.log | tail -4 | cut -c1-140 $ psql -X -c "SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer" $ psql -X -q -c "ALTER SYSTEM RESET max_wal_size" -c "SELECT pg_reload_conf()" > /dev/null max_wal_size -------------- 64MB (1 row) 2026-09-24 04:11:58.436 UTC [42] checkpointer LOG: checkpoints are occurring too frequently (0 seconds apart) 2026-09-24 04:11:58.436 UTC [42] checkpointer LOG: checkpoint starting: wal 2026-09-24 04:11:58.595 UTC [42] checkpointer LOG: checkpoints are occurring too frequently (0 seconds apart) 2026-09-24 04:11:58.595 UTC [42] checkpointer LOG: checkpoint starting: wal num_timed | num_requested | num_done -----------+---------------+---------- 0 | 9 | 8 (1 row) [exit=0] ######## 5. 시간 기준 체크포인트는 쓰기를 나눠서 한다 $ psql -X -q -c "ALTER SYSTEM SET checkpoint_timeout = '30s'" -c "SELECT pg_reload_conf()" > /dev/null $ psql -X -q -c "CHECKPOINT" $ psql -X -q -c "UPDATE acct SET balance = balance + 1" $ for i in $(seq 1 120); do $ grep -A1 "checkpoint starting: time" /home/postgres/server.log | grep -q "checkpoint complete" && break $ sleep 1 $ done $ grep -A1 "checkpoint starting: time" /home/postgres/server.log | head -2 | cut -c1-230 $ psql -X -q -c "ALTER SYSTEM RESET checkpoint_timeout" -c "SELECT pg_reload_conf()" > /dev/null 2026-09-24 04:12:30.769 UTC [42] checkpointer LOG: checkpoint starting: time 2026-09-24 04:12:57.043 UTC [42] checkpointer LOG: checkpoint complete: wrote 6666 buffers (40.7%), wrote 1 SLRU buffers; 0 WAL file(s) added, 0 removed, 11 recycled; write=26.234 s, sync=0.024 s, total=26.274 s; sync files=5, lo [exit=0] ######## 6. 장애 복구: 마지막 체크포인트부터 WAL을 다시 적용한다 $ psql -X -c "SELECT sum(balance) AS total, count(*) FROM acct" $ psql -X -q -c "CHECKPOINT" $ psql -X -q -c "UPDATE acct SET balance = balance + 1 WHERE id <= 100000" $ psql -X -q -c "INSERT INTO acct VALUES (300001, 777, 'after checkpoint')" $ psql -X -c "SELECT pg_current_wal_insert_lsn() AS insert_lsn, pg_current_wal_flush_lsn() AS flush_lsn" $ pg_controldata $PGDATA | grep -E "Latest checkpoint's REDO location" $ pg_ctl -D $PGDATA stop -m immediate $ pg_controldata $PGDATA | grep -E "cluster state" total | count ----------+-------- 20650000 | 200000 (1 row) insert_lsn | flush_lsn ------------+------------ 0/30E51AC0 | 0/30E51AC0 (1 row) Latest checkpoint's REDO location: 0/2CDA3468 waiting for server to shut down.... done server stopped Database cluster state: in production [exit=0] $ pg_ctl -D $PGDATA -l /home/postgres/server.log start $ sed -n '/database system was interrupted/,/database system is ready/p' /home/postgres/server.log | tail -12 | cut -c1-220 $ psql -X -c "SELECT sum(balance) AS total, count(*) FROM acct" $ psql -X -c "SELECT * FROM acct WHERE id = 300001" $ pg_controldata $PGDATA | grep -E "cluster state" waiting for server to start.... done server started 2026-09-24 04:12:58.535 UTC [347] startup LOG: database system was interrupted; last known up at 2026-09-24 04:12:58 UTC 2026-09-24 04:12:58.561 UTC [347] startup LOG: database system was not properly shut down; automatic recovery in progress 2026-09-24 04:12:58.562 UTC [347] startup LOG: redo starts at 0/2CDA3468 2026-09-24 04:12:58.643 UTC [347] startup LOG: invalid record length at 0/30E51AC0: expected at least 24, got 0 2026-09-24 04:12:58.643 UTC [347] startup LOG: redo done at 0/30E51A98 system usage: CPU: user: 0.06 s, system: 0.01 s, elapsed: 0.08 s 2026-09-24 04:12:58.644 UTC [345] checkpointer LOG: checkpoint starting: end-of-recovery immediate wait 2026-09-24 04:12:58.681 UTC [345] checkpointer LOG: checkpoint complete: wrote 6615 buffers (40.4%), wrote 3 SLRU buffers; 0 WAL file(s) added, 4 removed, 0 recycled; write=0.011 s, sync=0.013 s, total=0.038 s; sync fil 2026-09-24 04:12:58.683 UTC [341] postmaster LOG: database system is ready to accept connections total | count ----------+-------- 20750777 | 200001 (1 row) id | balance | pad --------+---------+------------------ 300001 | 777 | after checkpoint (1 row) Database cluster state: in production [exit=0] ######## 7. 복구 시간은 마지막 체크포인트 이후 WAL 양에 비례한다 $ psql -X -q -c "ALTER SYSTEM SET max_wal_size = '4GB'" -c "ALTER SYSTEM SET checkpoint_timeout = '1h'" -c "SELECT pg_reload_conf()" > /dev/null $ psql -X -q -c "CHECKPOINT" $ for i in 1 2 3 4 5; do psql -X -q -c "UPDATE acct SET balance = balance + 1"; done $ psql -X -c "SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_insert_lsn(), (SELECT redo_lsn FROM pg_control_checkpoint()))) AS wal_since_redo" $ pg_ctl -D $PGDATA stop -m immediate $ pg_ctl -D $PGDATA -l /home/postgres/server.log start $ grep -E "redo starts|redo done" /home/postgres/server.log | tail -2 | cut -c1-200 wal_since_redo ---------------- 453 MB (1 row) waiting for server to shut down.... done server stopped waiting for server to start.... done server started 2026-09-24 04:13:01.721 UTC [394] startup LOG: redo starts at 0/33DCE5B8 2026-09-24 04:13:02.570 UTC [394] startup LOG: redo done at 0/50320F30 system usage: CPU: user: 0.75 s, system: 0.09 s, elapsed: 0.84 s [exit=0] ######## 8. PITR: 백업과 보관한 WAL로 원하는 시점까지만 복구한다 $ mkdir -p /home/postgres/archive $ psql -X -q -c "ALTER SYSTEM RESET ALL" -c "ALTER SYSTEM SET archive_mode = on" -c "ALTER SYSTEM SET archive_command = 'cp %p /home/postgres/archive/%f'" $ pg_ctl -D $PGDATA -l /home/postgres/server.log restart > /dev/null $ pg_basebackup -D /home/postgres/backup -c fast && echo "base backup ok" $ ls /home/postgres/backup | tr '\n' ' '; echo $ cat /home/postgres/backup/backup_label $ psql -X -q -c "INSERT INTO acct VALUES (400001, 1, 'before mistake')" $ psql -X -c "SELECT pg_create_restore_point('before_drop')" $ psql -X -q -c "DROP TABLE acct" $ psql -X -q -c "SELECT pg_switch_wal()" > /dev/null $ sleep 2 $ psql -X -c "SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver" $ ls /home/postgres/archive base backup ok PG_VERSION backup_label backup_manifest base global pg_commit_ts pg_dynshmem pg_hba.conf pg_ident.conf pg_logical pg_multixact pg_notify pg_replslot pg_serial pg_snapshots pg_stat pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_wal pg_xact postgresql.auto.conf postgresql.conf START WAL LOCATION: 0/51000028 (file 000000010000000000000051) CHECKPOINT LOCATION: 0/51000080 BACKUP METHOD: streamed BACKUP FROM: primary START TIME: 2026-09-24 04:13:03 UTC LABEL: pg_basebackup base backup START TIMELINE: 1 pg_create_restore_point ------------------------- 0/52001CD0 (1 row) archived_count | last_archived_wal | failed_count ----------------+--------------------------+-------------- 4 | 000000010000000000000052 | 0 (1 row) 000000010000000000000050 000000010000000000000051 000000010000000000000051.00000028.backup 000000010000000000000052 [exit=0] $ cat >> /home/postgres/backup/postgresql.auto.conf <<'CONF' $ port = 5433 $ restore_command = 'cp /home/postgres/archive/%f %p' $ recovery_target_name = 'before_drop' $ recovery_target_action = 'promote' $ CONF $ touch /home/postgres/backup/recovery.signal $ pg_ctl -D /home/postgres/backup -l /home/postgres/pitr.log start $ sleep 2 $ grep -E "starting point-in-time|redo starts|restored log file|recovery stopping|redo done|selected new timeline|archive recovery complete|ready to accept" /home/postgres/pitr.log | cut -c1-200 $ psql -X -p 5433 -c "SELECT count(*), max(id) FROM acct" $ psql -X -p 5432 -c "SELECT count(*) FROM acct" $ ls /home/postgres/backup/pg_wal $ cat /home/postgres/backup/pg_wal/00000002.history waiting for server to start.... done server started 2026-09-24 04:13:06.105 UTC [466] startup LOG: restored log file "000000010000000000000051" from archive 2026-09-24 04:13:06.119 UTC [466] startup LOG: starting point-in-time recovery to "before_drop" 2026-09-24 04:13:06.122 UTC [466] startup LOG: redo starts at 0/51000028 2026-09-24 04:13:06.124 UTC [466] startup LOG: restored log file "000000010000000000000052" from archive 2026-09-24 04:13:06.137 UTC [460] postmaster LOG: database system is ready to accept read-only connections 2026-09-24 04:13:06.137 UTC [466] startup LOG: recovery stopping at restore point "before_drop", time 2026-09-24 04:13:03.921572+00 2026-09-24 04:13:06.137 UTC [466] startup LOG: redo done at 0/52001C68 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s 2026-09-24 04:13:06.139 UTC [466] startup LOG: restored log file "000000010000000000000052" from archive 2026-09-24 04:13:06.155 UTC [466] startup LOG: selected new timeline ID: 2 2026-09-24 04:13:06.176 UTC [466] startup LOG: archive recovery complete 2026-09-24 04:13:06.180 UTC [460] postmaster LOG: database system is ready to accept connections count | max --------+-------- 200002 | 400001 (1 row) ERROR: relation "acct" does not exist LINE 1: SELECT count(*) FROM acct ^ 000000010000000000000052 00000002.history 000000020000000000000052 000000020000000000000053 000000020000000000000054 000000020000000000000055 000000020000000000000056 000000020000000000000057 archive_status summaries 1 0/52001CD0 at restore point "before_drop" [exit=0] done