PostgreSQLͷϨϓϦέʔγϣϯΛ ࢖ͬͯΈΑ͏ Open Source Conferenc e 2021 Online/Osaka

ࣗݾ঺հ ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ ) ॴଐ: ೔ຊPostgreSQLϢʔβձ
 ؔ੢ࢧ෦௕ Twitter/GitHub: @ester4 1 ొஃࢿྉ: https://speakerdeck.com/ester41 ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ

͸͡Ίʹ ຊηογϣϯ࡞੒ʹ͋ͨΓɺҎԼͷॻ੶Λࢀߟʹ͍ͯ͠·͢ɻ

͸͡Ίʹ ࢿྉ࡞੒ʹ͋ͨΓɺҎԼͷ؀ڥͰௐࠪΛߦͬͯ ͍·͢ɻ • OS: CentOS 7 • PostgreSQLόʔδϣϯ: 13.1

ΞδΣϯμ ϨϓϦέʔγϣϯͱ͸ ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ ϩδΧϧϨϓϦέʔγϣϯͷઃఆ ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چํ๏ ϩδΧϧϨϓϦέʔγϣϯͷ෮چํ๏

ϨϓϦέʔγϣϯͱ͸

ϨϓϦέʔγϣϯͱ͸ ෳ਺ͷαʔόʔʹσʔλΛෳ੡͢ΔػೳͰ͢ɻ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡

ϨϓϦέʔγϣϯͱ͸ ߴՄ༻ੑͱෛՙ෼ࢄΛߦ͏͜ͱ͕ՄೳͰ͢ɻ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡ ࢀর ߋ৽ ߴՄ༻ੑ ෛՙ෼ࢄ ࢀরͷΈՄೳ

ϨϓϦέʔγϣϯͱ͸ ߴՄ༻ੑͱෛՙ෼ࢄΛߦ͏͜ͱ͕ՄೳͰ͢ɻ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡ ࢀর ߋ৽ ߴՄ༻ੑ ෛՙ෼ࢄ ෛՙ͕1఺ʹूத͠ͳ͍ͨΊɺ γεςϜશମͱͯ͠ੑೳ͕޲্ʂ 1୆͕ނোͯ͠΋ผαʔόʔͰ ॲཧ͕Մೳʂ

ϨϓϦέʔγϣϯͱ͸ (ϨϓϦέʔγϣϯํࣜ) ԼهͷΑ͏ͳ͞·͟·ͳϨϓϦέʔγϣϯ͕ଘࡏ͠·͢ɻ
 ·ͨɺελϯόΠ΁ͷΞΫηεՄ൱͕ଘࡏ͠·͢ɻ ํࣜ ಛ௃ ελϯόΠํࣜ ڞ༗σΟεΫ ϓϥΠϚϦʔͱελϯόΠͰσʔλϕʔ εΫϥελΛڞ༗͢Δɻ ΢ΥʔϜελϯόΠ ϑΝΠϧγεςϜϨϓϦέʔγϣϯ ڞ༗σΟεΫ૬౰ͷػೳΛιϑτ΢ΣΞ ϨϕϧͰ࣮૷͠ɺϓϥΠϚϦʔͱελϯ όΠͰσʔλϕʔεΫϥελΛڞ༗͢ Δɻ ϩάγοϐϯά WALϕʔεͷϨϓϦέʔγϣϯɻ ϗοτελϯόΠ τϦΨʔϕʔεϨϓϦέʔγϣϯ ϓϦϚϦʔ΁ͷߋ৽ΛτϦΨʔͱͯ͠ε λϯόΠʹಉ͡ߋ৽Λ఻೻͢Δɻ SQLϕʔεϨϓϦέʔγϣϯ ϓϥΠϚϦʔͱελϯόΠʹಉ͡SQLΛ ૹΔϛυϧ΢ΣΞΛ஥հͤ͞Δɻ ※΢ΥʔϜελϯόΠ: ঢ֨·Ͱ࢖༻ෆՄ / ϗοτελϯόΠ: ঢ֨͠ͳͯ͘΋࢖༻Մ / WAL: τϥϯβΫγϣϯϩά(Write Ahead Logging)

ϨϓϦέʔγϣϯͱ͸ (ϨϓϦέʔγϣϯํࣜ) ԼهͷΑ͏ͳ͞·͟·ͳϨϓϦέʔγϣϯ͕ଘࡏ͠·͢ɻ
 ·ͨɺελϯόΠ΁ͷΞΫηεՄ൱͕ଘࡏ͠·͢ɻ ํࣜ ಛ௃ ελϯόΠํࣜ ڞ༗σΟεΫ ϓϥΠϚϦʔͱελϯόΠͰσʔλϕʔ εΫϥελΛڞ༗͢Δɻ ΢ΥʔϜελϯόΠ ϑΝΠϧγεςϜϨϓϦέʔγϣϯ ڞ༗σΟεΫ૬౰ͷػೳΛιϑτ΢ΣΞ ϨϕϧͰ࣮૷͠ɺϓϥΠϚϦʔͱελϯ όΠͰσʔλϕʔεΫϥελΛڞ༗͢ Δɻ ϩάγοϐϯά WALϕʔεͷϨϓϦέʔγϣϯɻ ϗοτελϯόΠ τϦΨʔϕʔεϨϓϦέʔγϣϯ ϓϦϚϦʔ΁ͷߋ৽ΛτϦΨʔͱͯ͠ε λϯόΠʹಉ͡ߋ৽Λ఻೻͢Δɻ SQLϕʔεϨϓϦέʔγϣϯ ϓϥΠϚϦʔͱελϯόΠʹಉ͡SQLΛ ૹΔϛυϧ΢ΣΞΛ஥հͤ͞Δɻ PostgreSQLඪ४Ͱ࢖༻Մೳͳ ϩάγοϐϯάํࣜͷϨϓϦέʔ γϣϯʹ͍ͭͯղઆ͠·͢ɻ ※΢ΥʔϜελϯόΠ: ঢ֨·Ͱ࢖༻ෆՄ / ϗοτελϯόΠ: ঢ֨͠ͳͯ͘΋࢖༻Մ / WAL: τϥϯβΫγϣϯϩά(Write Ahead Logging)

ϨϓϦέʔγϣϯͱ͸ (PostgreSQLඪ४ํࣜ) PostgreSQL͕ඪ४ͰରԠ͍ͯ͠ΔϨϓϦέʔγϣϯ͸ɺ2छྨଘࡏ͠·͢ɻ • ετϦʔϛϯάϨϓϦέʔγϣϯ
 WALΛϑΝΠϧ୯ҐͰ͸ͳ͘มߋ಺༰୯ҐͰૹΓ·͢ɻ
 ϓϥΠϚϦʔͱελϯόΠ͸෺ཧతʹಉ͡σʔλͰ͢ɻ • ϩδΧϧ(࿦ཧ)ϨϓϦέʔγϣϯ
 ϩδΧϧσίʔσΟϯά(WALΛɺ֎෦ͷγεςϜ͕ղऍͰ͖Δσʔλʹ
 σίʔυ͢Δϓϩηε)ʹΑΓ࿦ཧతͳมߋ಺༰ΛελϯόΠʹૹΓ·͢ɻ
 ϓϥΠϚϦʔͱελϯόΠ͸෺ཧతʹҟͳΔσʔλͰ͢ɻ

ͦΕͧΕͷϨϓϦέʔγϣϯͷϝϦοτɾσϝϦοτ͸ҎԼͷ௨ΓͰ͢ɻ ϨϓϦέʔγϣϯͱ͸ (PostgreSQLඪ४ํࣜ) ϝϦοτ σϝϦοτ ετϦʔϛϯά ϨϓϦέʔγϣϯ • ෺ཧతʹಉ͡σʔλϕʔεΛෳ੡͢Δ͜ͱ͕ Ͱ͖Δɻ • ϓϥΠϚϦʔͰίϛοτࡁΈσʔλΛελϯ όΠͰඞͣॻ͖ࠐΈࡁΈʹ͢Δ͜ͱ͕Մೳɻ • ࢀরෛՙ෼ࢄ͕Մೳɻ • ಛఆͷσʔλϕʔεɺදΛෳ੡͢Δ͜ͱ͕ग़ དྷͳ͍ɻ • ϝδϟʔόʔδϣϯ͕ҟͳΔPostgreSQLͷؒͰ ͸ར༻Ͱ͖ͳ͍ɻ • ಈ࡞؀ڥ͕ಉҰͰͳ͚Ε͹͍͚ͳ͍ɻ • ελϯόΠ͸ߋ৽Ͱ͖ͳ͍ɻ ϩδΧϧ ϨϓϦέʔγϣϯ • ϓϥΠϚϦʔͷҰ෦ͷදʹର͢Δߋ৽͚ͩ ΛɺελϯόΠʹૹΔ͜ͱ͕Ͱ͖Δɻ • ෳ਺ͷϓϥΠϚϦʔͷग़ྗΛ1ͭͷελϯόΠ Ͱड͚औΔ͜ͱ͕Ͱ͖Δɻ • ϝδϟʔόʔδϣϯ͕ҟͳΔPostgreSQLͷؒͰ ΋ར༻Ͱ͖Δɻ • ελϯόΠͷσʔλϕʔεΛߋ৽͢Δ͜ͱ͕ Ͱ͖Δɻ • ϨϓϦέʔγϣϯͰ͖ͳ͍SQL΍ΦϒδΣΫ τ͕͋ΓɺϓϥΠϚϦʔͱελϯόΠͰෆ੔ ߹͕ൃੜ͠ͳ͍Α͏஫ҙͯ͠ӡ༻͢Δඞཁ͕ ͋Δɻ • ϓϥΠϚϦʔͷσʔλϕʔεΛߋ৽ͨ͠৔ ߹ɺελϯόΠͰͷߋ৽಺༰ͱڝ߹͢ΔՄೳ ੑ͕͋Δɻ

ετϦʔϛϯά ϨϓϦέʔγϣϯͷઃఆ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ετϦʔϛϯάϨϓϦέʔγϣϯͷ࢓૊Έ) 1. ϓϥΠϚϦʔ͸WALΛελϯόΠʹૹ৴͢Δɻ 2. ελϯόΠ͸సૹ͞ΕͨWALΛϩʔυ(ϦΧόϦʔ)ͯ͠σʔλϕʔεʹదԠ ͢Δɻ ্هಈ࡞͸ɺϓϥΠϚϦʔଆͰwalsenderϓϩηε͕ɺ
 ελϯόΠଆͰwalreceiverϓϩηε͕ಈ࡞͢Δ͜ͱͰ࣮ݱ͍ͯ͠·͢ɻ WAL ϓϥΠϚϦʔ ΫϥΠΞϯτ ελϯόΠ WAL 1. ߋ৽ SQL 2. WAL 
 ॻࠐ 5. WAL 
 ॻࠐ 3. WAL 
 సૹ 4. WAL ϩʔυ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ) ετϦʔϛϯάϨϓϦέʔγϣϯΛઃఆ͢Δʹ͸ɺϓϥΠ ϚϦʔଆٴͼελϯόΠଆͷpostgresql.confͷमਖ਼͕ඞ ཁͰ͢ɻ
 ·ͨɺσʔλ࿈ܞΛߦ͏ͨΊɺϨϓϦέʔγϣϯ༻Ϣʔ βʔͷ௥Ճͱ઀ଓઃఆ(pg_hba.conf)͕ඞཁͱͳΓ·͢ɻ
 ϓϥΠϚϦʔଆ΁ॳظσʔλ౤ೖ࣌ɺϨϓϦέʔγϣϯͷ ઃఆΛߦΘͳ͍Ͱߴ଎ʹσʔλ౤ೖΛߦ͏ํ๏͕͋Γ·͢ ͕ɺࠓճ͸આ໌Λল͖·͢ɻ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ϓϥΠϚϦʔଆ઀ଓઃఆ) ϨϓϦέʔγϣϯΛߦ͏ͨΊʹ͸ɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞ ੒͢Δඞཁ͕͋Γ·͢ɻ ϓϥΠϚϦʔଆͷσʔλϕʔε΁؅ཧऀϢʔβʔͰ઀ଓΛߦ͍ɺ REPLICATIONݖݶΛ෇༩ͨ͠ϢʔβʔΛ࡞੒͠·͢ɻ
 ϓϥΠϚϦʔଆͷσʔλϕʔεͷpg_hba.conf΁ɺ্هϢʔβʔͷ઀ଓ ઃఆͷ௥ՃΛߦ͍·͢ɻ =# CREATE USER repl_user LOGIN REPLICATION PASSWORD '...'; host replication repl_user <ελϯόΠଆIPΞυϨε>/<αϒωοτϚεΫ> md5

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ϓϥΠϚϦʔଆઃఆ) ϓϥΠϚϦʔଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ىಈ ͠·͢ɻ postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ
 PostgreSQLΛ࠶ىಈ͠·͢ɻ listen_addresses = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ max_wal_senders = 2 # ελϯόΠDBͷ୆਺ + 1 # systemctl restart postgresql-13

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ελϯόΠଆσʔλಉظ) ϓϥΠϚϦʔଆͷσʔλΛελϯόΠଆ΁ಉظ͠·͢ɻ
 ॲཧ͸ɺpostgresϢʔβʔͰ࣮ࢪ͠·͢ɻ σʔλϕʔεΫϥελΛ࡟আ͠·͢ɻ
 ಉظΛߦ͍·͢ɻ $ #PGDATA=/var/lib/pgsql/13/data/ ͕ઃఆ͞Ε͍ͯΔ΋ͷͱ͢Δɻ $ rm -rf ${PDATA} # /usr/pgsql-13/bin/pgbasebackup -R -D ${PGDATA} -U repl_user -h <ϓϥΠϚϦʔଆIPΞυϨε>

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ελϯόΠଆઃఆ) ελϯόΠଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ىಈ͠ ·͢ɻ postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ
 PostgreSQLΛ࠶ىಈ͠·͢ɻ listen_addresses = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ #max_wal_senders = 2 # ίϝϯτΞ΢τ͠·͢ # systemctl restart postgresql-13

PostgreSQL ϓϥΠϚϦʔଆͰςʔϒϧ࡞੒౳Λ࣮ߦ͠ɺελϯόΠʹ࿈ܞ͞Ε͍ͯΔ͜ ͱΛ֬ೝͯ͠Έ·͠ΐ͏ɻ ϓϥΠϚϦʔଆͰςʔϒϧΛ࡞੒ɺσʔλ௥Ճ͠·͢ɻ
 
 ελϯόΠଆͰςʔϒϧΛ֬ೝͯ͠Έ·͢ɻ
 ελϯόΠଆͰσʔλ௥Ճͨ͠৔߹ɺΤϥʔʹͳΔ͜ͱΛ֬ೝͯ͠Έ·͢ɻ =# CREATE TABLE test (test text); =# INSERT INTO test VALUES (‘ςετσʔλ'); =# SELECT * FROM test; =# INSERT INTO test VALUES (‘ςετσʔλ’); ERROR: cannot execute INSERT in a read-only transaction

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷҧ͍) ετϦʔϛϯάϨϓϦέʔγϣϯʹ͸ಉظͱඇಉظͷ2छ ྨͷϞʔυ͕ଘࡏ͠·͢ɻ
 (લड़ͷઃఆͰ͸ɺඇಉظઃఆͱͳ͍ͬͯ·͢ɻ)
 2ͭͷҧ͍͸ɺελϯόΠଆͰWAL͕ͲͷΑ͏ͳঢ়ଶʹ ͳͬͨΒϓϥΠϚϦʔͰॲཧΛ׬ྃ(ίϛοτ׬ྃΛ௨஌) ͤ͞Δ͔ͷҧ͍Ͱ͢ɻ


ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷҧ͍) ಉظɺඇಉظͷҧ͍͸Լهͷ௨ΓͰ͢ɻ ಛ௃ ϝϦοτ σϝϦοτ ಉظ ελϯόΠͰWAL͕ਖ਼ৗʹಉظॻ ͖ࠐΈ͕׬ྃ͢Δ·ͰϓϥΠϚϦ ʔ͸ॲཧΛ଴ػ͢Δɻ • ϓϥΠϚϦʔͱελϯόΠͰͷ σʔλͷ੔߹ੑ͕อো͞Ε͍ͯ Δɻ • ϓϥΠϚϦʔͱελϯόΠͷ໰ ͍߹Θͤ݁Ռ͕ಉ͡ʹͳΔɻ • 2୆ߏ੒ͷ৔߹ɺελϯόΠ͕ఀ ࢭ͍ͯ͠ΔͱϓϥΠϚϦʔͷॲ ཧ(ίϛοτ)͕׬ྃ͠ͳ͍ɻ • ϓϥΠϚϦʔͷॲཧ͕ελϯό Πͷॲཧʹࠨӈ͞ΕΔɻ ඇಉظ ελϯόΠଆͷWALॲཧΛ଴ͨͣ ʹϓϥΠϚϦʔ͸ॲཧΛ׬ྃ͢ Δɻ • 2୆ߏ੒ͰϨϓϦέʔγϣϯΛ૊ Ή͜ͱ͕Մೳɻ • ϓϥΠϚϦʔͷॲཧ͕ߴ଎Ͱॲ ཧ͞ΕΔɻ • ωοτϫʔΫ໰୊ͳͲͰWAL͕ ਖ਼͘͠ॲཧ͞Εͳ͍৔߹ɺσʔ λ͕ζϨΔɻ • ߋ৽σʔλ͕େ͖͍৔߹ɺελ ϯόΠଆ΁ͷదԠ͕஗͘ͳΓɺ ໰͍߹Θͤ݁Ռ͕ҟͳΔՄೳੑ ͕͋Δɻ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷઃఆ) ಉظɺඇಉظ͸ɺԼهͷύϥϝʔλʔͰઃఆ͠·͢ɻ • synchronous_commit
 τϥϯβΫγϣϯͷίϛοτ͕ΫϥΠΞϯτʹ“׬ྃ”Λใࠂ͢Δલʹ
 WALϨίʔυ͕σΟεΫ্ʹॻ͖ࠐ·ΕΔ·Ͱ଴͔ͭͲ͏͔ͷઃఆΛ
 ߦ͍·͢ɻ • synchronous_standby_names
 ಉظ͢ΔελϯόΠ໊(ΞϓϦέʔγϣϯ໊)ΛΧϯϚ۠੾ΓͰઃఆ͠·͢ɻ
 “*”Λઃఆ͢ΔͱɺશͯͷελϯόΠ͕ର৅ͱͳΓɺ
 “”ͷ৔߹͸શͯͷελϯόΠ͕ର৅֎ͱͳΓ·͢ɻ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷઃఆ - synchronous_commit) synchronous_commitͷઃఆ஋͸ɺԼهͷ௨Γͱͳͬͯ ͍·͢ɻ 
 σϑΥϧτ஋͸onͰ͢ɻ ઃఆ஋ ಉظ/ඇಉظ ϓϥΠϚϦʔ ελϯόΠ off ඇಉظ ଴ͨͳ͍ ଴ͨͳ͍ local ඇಉظ ଴ͭ ଴ͨͳ͍ remote_write ಉظ ଴ͭ ϝϞϦʔॻ͖ࠐΈ·Ͱ଴ͭ on ಉظ ଴ͭ σΟεΫॻ͖ࠐΈ·Ͱ଴ͭ remote_apply ಉظ ଴ͭ WALదԠ·Ͱ଴ͭ ཁ஫ҙϙΠϯτʂ
 remote_applyͷΈ׬શಉظ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷઃఆ - synchronous_standby_names) synchronous_standby_namesͷઃఆ஋͸ɺԼهͷϑΥʔϚοτͰ͢ɻ 
 σϑΥϧτ஋͸“”Ͱ͢ɻ
 ͜ͷઃఆ஋͸ɺελϯόΠଆͷpostgresql.auto.confͷprimary_conninfoʹɺ application_nameͱͯ͠ΞϓϦέʔγϣϯ໊Λ෇༩ͨ͠஋ͱ߹க͢Δඞཁ͕͋Γ·͢ɻ • ΧϯϚ۠੾Γ (ྫ: ’ελϯόΠ1’, ‘ελϯόΠ2’, ‘ελϯόΠ3’, ‘ελϯόΠ4’)
 ఆٛͨ͠શͯͷελϯόΠΛಉظͱͯ͠ѻ͍·͢ɻ • FIRST N (ྫ: FIRST 2 ’ελϯόΠ1’, ‘ελϯόΠ2’, ‘ελϯόΠ3’, ‘ελϯόΠ4’)
 લ͔ΒN୆ͷελϯόΠΛಉظͱͯ͠ѻ͍·͢ɻ • ANY N (ྫ: ANY 2 ’ελϯόΠ1’, ‘ελϯόΠ2’, ‘ελϯόΠ3’, ‘ελϯόΠ4’)
 ͍ͣΕ͔N୆ͷελϯόΠΛಉظͱͯ͠ѻ͍·͢ɻ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - ϩά) ϨϓϦέʔγϣϯͷ֬ೝ͸ɺαʔόʔϩά΍pg_stat_replicationϏϡʔΛݟΔ͜ ͱͰ֬ೝͰ͖·͢ɻ • αʔόʔϩάͷ֬ೝ
 αʔόʔىಈ࣌ʹϨϓϦέʔγϣϯΛ։࢝ͨ͠ϝοηʔδ͕ग़ྗ͞Ε͍ͯΔ͜ ͱΛ֬ೝ͠·͢ɻ • ϓϥΠϚϦʔ
 • ελϯόΠ
 LOG: standby “<ελϯόΠ໊>” is now a synchronous standby with priority 1 LOG: started streaming WAL from primary at on timeline <λΠϜϥΠϯ஋>

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - ϩά) ϨϓϦέʔγϣϯͷ֬ೝ͸ɺαʔόʔϩά΍pg_stat_replicationϏϡʔΛݟΔ͜ ͱͰ֬ೝͰ͖·͢ɻ • αʔόʔϩάͷ֬ೝ
 αʔόʔىಈ࣌ʹϨϓϦέʔγϣϯΛ։࢝ͨ͠ϝοηʔδ͕ग़ྗ͞Ε͍ͯΔ͜ ͱΛ֬ೝ͠·͢ɻ • ϓϥΠϚϦʔ
 • ελϯόΠ
 LOG: standby “<ελϯόΠ໊>” is now a synchronous standby with priority 1 LOG: started streaming WAL from primary at on timeline <λΠϜϥΠϯ஋> λΠϜϥΠϯID͸ɺϑΣΠϧΦʔόʔ΍ όοΫΞοϓ͔Β෮چͰ൪߸͕੾ΓସΘΔʂ

ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - pg_stat_replication) • pg_stat_replicationϏϡʔͷ֬ೝ
 ϨϓϦέʔγϣϯͷ஗ԆΛ֬ೝ͢Δ͜ͱ͕Ͱ͖·͢ɻ
 =# SELECT application_name, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, 
 -# sync_priority, sync_state FROM pg_stat_replication; -[ RECORD 1 ]----+------------- application_name | walreceiver <= ελϯόΠ໊ state | streaming <= startup: ઀ଓཱ֬த / backup: όοΫΞοϓ(pg_basebackup)࣮ߦத / 
 catchup: ߋ৽ཤྺऔಘத / streaming: ࠷৽৘ใετϦʔϛϯάத / stopping: ఀࢭத sent_lsn | 0/7040E80 <= ϓϥΠϚϦʔ͕ૹग़ͨ͠WALͷϩά൪߸(LSN(Log Sequence Number)) write_lsn | 0/7040E80 <= ελϯόΠଆͷϝϞϦʔʹॻ͖ࠐΈࡁΈͷWALͷϩά൪߸ flush_lsn | 0/7040E80 <= ελϯόΠଆͷσΟεΫʹॻ͖ࠐΈࡁΈͷWALͷϩά൪߸ replay_lsn | 0/7040E80 <= ελϯόΠଆͰదԠͨ͠WALͷϩά൪߸ sync_priority | 0 <= 0: ඇಉظ / 1Ҏ্: synchronous_standby_namesͷઃఆ஋ʹΑΓ࿈൪෇༩ sync_state | async <= sync: ಉظ / async: ඇಉظ / potential: ඇಉظ͕ͩɺಉظʹঢ֨͢ΔՄೳੑ͋Γ

ϩδΧϧ ϨϓϦέʔγϣϯͷઃఆ

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ϩδΧϧϨϓϦέʔγϣϯͷ࢓૊Έ) 1. ϓϥΠϚϦʔ(ύϒϦογϟʔ)͸WALΛղੳ͠ɺߋ৽৘ใΛελϯόΠ(αϒ εΫϥΠόʔ)ʹૹ৴͢Δɻ 2. αϒεΫϥΠόʔ͸ߋ৽৘ใΛݩʹςʔϒϧΛߋ৽͢Δɻ ্هಈ࡞͸ɺύϒϦογϟʔଆͰpgoutputͱwalsenderϓϩηε͕ɺ
 αϒεΫϥΠόʔଆͰbgworkerϓϩηε͕ಈ࡞͢Δ͜ͱͰ࣮ݱ͍ͯ͠·͢ɻ WAL ύϒϦογϟʔ ΫϥΠΞϯτ αϒεΫϥΠόʔ 1. ߋ৽ SQL 3. WAL 
 ॻࠐ 5. σʔλ 
 ૹ৴ 2. ςʔϒϧ 
 ߋ৽ 4. WAL 
 ϩʔυ WAL 7. WAL 
 ॻࠐ 6. ςʔϒϧ 
 ߋ৽

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ) ϩδΧϧϨϓϦέʔγϣϯΛઃఆ͢Δʹ͸ɺύϒϦο γϟʔଆͷpostgresql.confͷमਖ਼͕ඞཁͰ͢ɻ
 ·ͨɺσʔλ࿈ܞΛߦ͏ͨΊɺϨϓϦέʔγϣϯ༻Ϣʔ βʔͷ௥Ճͱ઀ଓઃఆ(pg_hba.conf)͕ඞཁͱͳΓ·͢ɻ


ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ઀ଓઃఆ) ϨϓϦέʔγϣϯΛߦ͏ͨΊʹ͸ɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞੒͢Δඞཁ͕͋ Γ·͢ɻ 1. ύϒϦογϟʔଆͷσʔλϕʔε΁؅ཧऀϢʔβʔͰ઀ଓΛߦ͍ɺREPLICATIONݖ ݶΛ෇༩ͨ͠ϢʔβʔΛ࡞੒͠·͢ɻ
 ·ͨɺσʔλϕʔε΁ͷΞΫηεݖݶΛ෇༩͠·͢ɻ
 
 2. ύϒϦογϟʔଆͷσʔλϕʔεͷpg_hba.conf΁ɺ্هϢʔβʔͷ઀ଓઃఆͷ௥Ճ Λߦ͍·͢ɻ =# CREATE ROLE repl_user LOGIN REPLICATION PASSWORD ‘...'; =# ALTER DEFAULT PRIVILEGES FOR ROLE GRANT ALL PRIVILEGES ON TABLES TO repl_user; host replication repl_user <ελϯόΠଆIPΞυϨε>/<αϒωοτϚεΫ> md5

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ύϒϦογϟʔଆઃఆ) ύϒϦογϟʔଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ى ಈ͠·͢ɻ postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ
 
 PostgreSQLΛ࠶ىಈ͠·͢ɻ listen_addresses = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ max_wal_senders = 2 # ύϒϦογϟʔDBͷ୆਺ + 1 wal_level = logical # ϩδΧϧσίʔσΟϯάͷ༗ޮ # systemctl restart postgresql-13

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ςʔϒϧ࡞੒౳ - ύϒϦογϟʔଆઃఆ) ύϒϦογϟʔଆͰςʔϒϧ࡞੒ɺύϒϦέʔγϣϯͷ࡞੒Λߦ͍·͢ɻ
 ύϒϦέʔγϣϯ࡞੒࣌ɺ؅ཧऀϢʔβʔͰ࣮ߦ͢Δඞཁ͸͋Γ·ͤΜɻ
 ্هઃఆ͸ɺtestςʔϒϧ͚ͩΛϨϓϦέʔγϣϯର৅ͱ͠ɺ௥Ճɾߋ ৽ɾ࡟আ࣌ʹ࿈ܞର৅ͱ͠·͢ɻ
 FOR ALL TABLESΛࢦఆ͢Δͱɺকདྷ࡞੒͞ΕΔςʔϒϧ΋ϨϓϦέʔ γϣϯର৅ͱͳΓ·͢ɻ =# CREATE TABLE test (id integer, name text, primary key (id)); =# CREATE PUBLICATION test FOR TABLE test WITH(publish = ‘insert, update, delete, truncate’); -- ύϒϦέʔγϣϯ໊Λtestͱͯ͠࡞੒

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ςʔϒϧ࡞੒౳ - αϒεΫϥΠόʔଆઃఆ) αϒεΫϥΠόʔଆͰςʔϒϧ࡞੒ɺαϒεΫϦϓγϣϯͷ࡞੒Λߦ͍ ·͢ɻ
 ύϒϦογϟʔଆͷςʔϒϧͱಉ͡ςʔϒϧΛ࡞੒͍ͯͩ͘͠͞ɻ
 αϒεΫϦϓγϣϯ࡞੒࣌ɺ؅ཧऀϢʔβʔͰ࣮ߦ͢Δඞཁ͕͋Γ· ͢ɻ =# CREATE TABLE test (id integer, name text, primary key (id)); =# CREATE SUBSCRIPTION test CONNECTION ‘host=<ύϒϦογϟʔଆϗετ໊> port=<ϙʔτ൪߸> dbname=<σʔλϕʔε໊> user=<ϨϓϦέʔγϣϯϢʔβʔ> password=<ύεϫʔυ>’ publication <ύϒϦέʔγϣϯ໊>; -- αϒεΫϦϓγϣϯ໊Λtestͱͯ͠࡞੒

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ಈ࡞֬ೝ) ύϒϦογϟʔଆͷςʔϒϧʹσʔλΛ௥Ճ͠ɺαϒεΫϥΠόʔଆʹ࿈ܞ ͞Ε͍ͯΔ͔֬ೝͯ͠Έ·͠ΐ͏ɻ
 
 ύϒϦογϟʔଆͰσʔλ௥Ճ
 
 αϒεΫϥΠόʔଆͰσʔλ֬ೝ =# INSERT INTO test VALUES (1, 'test'); =# SELECT name FROM test; -[ RECORD 1 ]-+------------- id | 1 name | test

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ςʔϒϧͷ௥Ճɾ࡟আ) ϨϓϦέʔγϣϯʹςʔϒϧΛ௥Ճɾ࡟আ͍ͨ͠৔߹͸ɺ ALTER PUBLICATIONίϚϯυͰมߋ͕ՄೳͰ͢ɻ
 
 
 
 มߋΛߦͬͨ৔߹ɺαϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷ ߋ৽͕ඞཁͱͳΓ·͢ɻ
 
 
 =# ALTER PUBLICATION <ύϒϦέʔγϣϯ໊> ADD TABLE <௥Ճςʔϒϧ໊>; -- ςʔϒϧ௥Ճ =# ALTER PUBLICATION <ύϒϦέʔγϣϯ໊> DROP TABLE <࡟আςʔϒϧ໊>; -- ςʔϒϧ࡟আ =# ALTER SUBSCRIPTION <αϒεΫϦϓγϣϯ໊> REFRESH PUBLICATION;

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷఀࢭɾ࠶։) ϨϓϦέʔγϣϯΛఀࢭɾ࠶։͢Δ৔߹͸ɺαϒεΫϥΠ όʔଆͰૢ࡞Λߦ͍·͢ɻ
 
 
 
 =# ALTER SUBSCRIPTION <αϒεΫϦϓγϣϯ໊> DISABLE; -- ϨϓϦέʔγϣϯఀࢭ =# ALTER SUBSCRIPTION <αϒεΫϦϓγϣϯ໊> ENABLE; -- ϨϓϦέʔγϣϯ࠶։

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ύϒϦογϟʔଆͷঢ়گ֬ೝ) ύϒϦογϟʔଆͷঢ়ଶ֬ೝ͸ҎԼͷϏϡʔΛ࢖༻͠·͢ɻ
 
 
 
 
 
 
 
 
 =# SELECT pubname, puballtables, pubinsert, pubupdate, pubdelete, pubtruncate FROM pg_publication; -[ RECORD 1 ]+------ pubname | test <= ύϒϦέʔγϣϯ໊ puballtables | f <= কདྷʹ࡞੒͞ΕΔςʔϒϧ΋ϨϓϦέʔγϣϯର৅ͱ͢Δ͔ pubinsert | t <= INSERTΛϨϓϦέʔγϣϯର৅ͱ͢Δ͔ pubupdate | t <= UPDATEΛϨϓϦέʔγϣϯର৅ͱ͢Δ͔ pubdelete | t <= DELETEΛϨϓϦέʔγϣϯର৅ͱ͢Δ͔ pubtruncate | t <= TRUNCATEΛϨϓϦέʔγϣϯର৅ͱ͢Δ͔ =# SELECT pubname, schemaname, tablename FROM pg_publication_tables ; -[ RECORD 1 ]------ pubname | test <= ύϒϦέʔγϣϯ໊ schemaname | public <= ϨϓϦέʔγϣϯର৅ςʔϒϧͷॴଐεΩʔϚ໊ tablename | test <= ϨϓϦέʔγϣϯର৅ςʔϒϧ໊

ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (αϒεΫϥΠόʔଆͷঢ়گ֬ೝ) αϒεΫϥΠόʔଆͷঢ়ଶ֬ೝ͸ҎԼͷϏϡʔΛ࢖༻͠·͢ɻ
 
 
 
 
 
 
 ·ͨϩάϑΝΠϧΛݟΔ͜ͱͰɺϨϓϦέʔγϣϯͰίϯϑϦ Ϋτ͕ൃੜ͍ͯ͠Δ͔Ͳ͏͔͕൑அͰ͖·͢ɻ
 =# SELECT subname, subenabled, subconninfo, subpublications FROM pg_subscription; -[ RECORD 1 ]---+------------- subname | test <= αϒεΫϦϓγϣϯ໊ subenabled | t <= ϨϓϦέʔγϣϯͷ༗ޮঢ়ଶ subconninfo | <઀ଓ৘ใ> <= ύϒϦογϟʔଆ΁ͷ઀ଓ৘ใ subpublications | {test} <= ઀ଓύϒϦέʔγϣϯ໊

ετϦʔϛϯά ϨϓϦέʔγϣϯͷ෮چ

ετϦʔϛϯάϨϓϦέʔγϣϯ ͷ෮چ ετϦʔϛϯάϨϓϦέʔγϣϯͷϓϥΠϚϦʔɾελ ϯόΠͷ੾Γସ͑΍ɺো֐ൃੜ࣌ͷ෮چʹ͍ͭͯ͸ଟذ ʹ౉ΔͨΊɺ͜͜Ͱ͸ҎԼͷ৚݅Ͱૢ࡞Λ࣮ࢪ͠·͢ɻ 1. ϓϥΠϚϦʔΛਖ਼ৗఀࢭͤ͞Δ 2. ελϯόΠΛϓϥΠϚϦʔʹঢ֨ͤ͞Δ 3. چϓϥΠϚϦʔΛελϯόΠʹ߱֨ͤ͞Δ

ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (ϓϥΠϚϦʔͷఀࢭʙελϯόΠͷઃఆมߋ) ϓϥΠϚϦʔΛఀࢭͤ͞·͢ɻ 
 ελϯόΠͷpg_hba.confΛมߋ͠ɺچϓϥΠϚϦʔ͔Β ઀ଓͰ͖ΔΑ͏ʹ͠·͢ɻ
 
 ελϯόΠͷpostgresql.auto.confͷprimary_conninfoΛ ίϝϯτΞ΢τ͠·͢ɻ # systemctl stop postgresql-13 host replication repl_user <چϓϥΠϚϦʔଆIPΞυϨε>/<αϒωοτϚεΫ> md5 #primary_conninfo = '<઀ଓ৘ใ>'

ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (ελϯόΠͷ࠶ىಈʙϓϥΠϚϦʔ΁ঢ֨) ελϯόΠΛ࠶Ҡಈͤ͞·͢ɻ 
 ελϯόΠΛϓϥΠϚϦʔʹঢ֨ͤ͞·͢ɻ(postgresϢʔβʔͰ࣮ߦ)
 
 
 
 pg_walϑΥϧμʔΛ֬ೝ͠ɺλΠϜϥΠϯID͕มΘ͍ͬͯΔ͜ͱΛ֬ೝ ͠·͢ɻ
 
 # systemctl restart postgresql-13 $ #PGDATA=/var/lib/pgsql/13/data/ ͕ઃఆ͞Ε͍ͯΔ΋ͷͱ͢Δɻ $ /usr/pgsql-13/bin/pg_ctl -D ${PGDATA} promote $ #ελϯόΠαʔόʔ΁psqlͳͲͰ઀ଓ͠ɺ”SELECT pg_promote();”Ͱ΋ঢ֨Ͱ͖Δɻ $ ls /var/lib/pgsql/13/data/pg_wal/ 000000010000000000000007 00000002.history 000000020000000000000007 000000020000000000000008 archive_status 1͔Β2ʹ੾ΓସΘ͍ͬͯΔʂ

ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (৽ϓϥΠϚϦʔͷಈ࡞֬ೝ) ৽ϓϥΠϚϦʔͰσʔλΛ௥ՃͰ͖Δ͔֬ೝ͠·͢ɻ =# INSERT INTO test VALUES ('ςετσʔλ2'); =# SELECT * FROM test; test --------------- ςετσʔλ ςετσʔλ2 σʔλ͕௥ՃͰ͖͍ͯΔ

ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (چϓϥΠϚϦʔͷ߱֨) چϓϥΠϚϦʔͷpostgresql.confͷprimary_conninfoʹɺ৽ϓϥΠϚϦʔͷ ઀ଓ৘ใΛઃఆ͠·͢ɻ
 چελϯόΠʹઃఆ͞Ε͍ͯͨprimary_conninfoΛίϐʔ͠ɺhost෦෼Λॻ͖ ׵͑·͠ΐ͏ɻ
 
 
 
 
 postgresql.confͱಉ͡ϑΥϧμʔ্ʹɺۭϑΝΠϧͷstandby.signalΛ࡞੒ͨ͠ ޙɺPostgreSQLΛىಈ͠·͢ɻ 
 primary_conninfo = 'user=repl_user password=repl_user channel_binding=prefer host=<৽ϓϥΠϚϦʔͷϗετ໊> port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any' # touch standby.signal # systemctl restart postgresql-13 ͜ͷϑΝΠϧ͕ແ͍ͱɺ ελϯόΠͰىಈ͠ͳ͍ʂ

ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (چϓϥΠϚϦʔͷಈ࡞֬ೝ) چϓϥΠϚϦʔʹ৽ϓϥΠϚϦʔͷߋ৽৘ใ͕࿈ܞ͞Ε͍ͯΔ͔֬ೝ ͠·͢ɻ
 
 
 
 
 
 
 چϓϥΠϚϦʔ͕ελϯόΠʹͳ͍ͬͯΔ͔ɺσʔλΛ௥Ճͯ֬͠ೝ ͯ͠Έ·͢ɻ =# SELECT * FROM test; test --------------- ςετσʔλ ςετσʔλ2 =# INSERT INTO test VALUES (‘ςετσʔλ’); ERROR: cannot execute INSERT in a read-only transaction σʔλ͕࿈ܞ͞Ε͍ͯΔ

ϩδΧϧ ϨϓϦέʔγϣϯͷ෮چ

ϩδΧϧϨϓϦέʔγϣϯͷ෮چ ετϦʔϛϯάϨϓϦέʔγϣϯͱҟͳΓɺαϒεΫϥΠόʔଆ ͷͰςʔϒϧ͸ߋ৽ՄೳͰ͢ɻ
 ύϒϦογϟʔଆͷมߋͱɺαϒεΫϥΠόʔଆͷߋ৽Ͱίϯϑ ϦΫτ(ओΩʔ੍໿ҧ൓ͳͲ)͕ൃੜ͢ΔͱɺϨϓϦέʔγϣϯ͕ ఀࢭ͠·͢ɻ
 ϩδΧϧϨϓϦέʔγϣϯͷ෮چ͸ɺ͜ͷ໰୊Λղܾ͢Δ͔ɺ ύϒϦέʔγϣϯଆͰ֘౰ςʔϒϧΛϨϓϦέʔγϣϯ͔Β֎ ͠ɺαϒεΫϥΠόʔଆͷσʔλΛ࡟আޙɺϨϓϦέʔγϣϯର ৅ʹ௥Ճ͢Δ͜ͱͰղফͰ͖·͢ɻ

ϩδΧϧϨϓϦέʔγϣϯͷ෮چ (ίϯϑϦΫτൃੜ) αϒεΫϥΠόʔଆͰσʔλΛ௥Ճ͠·͢ɻ
 
 ύϒϦογϟʔଆͰɺಉ͡ओΩʔͷσʔλΛ௥Ճ͠·͢ɻ
 
 
 αϒεΫϥΠόʔଆͷϩάʹΤϥʔ͕ు͖ग़͞Ε·͢ɻ =# INSERT INTO test VALUES (10, 'test1'); =# INSERT INTO test VALUES (10, ‘test2'); ERROR: duplicate key value violates unique constraint "test_pk" 
 DETAIL: Key (id)=(10) already exists.

ϩδΧϧϨϓϦέʔγϣϯͷ෮چ (ίϯϑϦΫτআڈ - ֘౰Ϩίʔυ࡟আ) αϒεΫϥΠόʔଆͰɺݪҼϨίʔυΛ࡟আ͠·͢ɻ
 ࡟আޙɺϨϓϦέʔγϣϯ͕࠶։͞Ε͍ͯΔ͔ςʔϒϧΛ ֬ೝͯ͠Έ·͢ɻ =# DELETE FROM test WHERE id = 10; =# SELECT name FROM test WHERE id = 10; -[ RECORD 1 ]-+------------- name | test2

ϩδΧϧϨϓϦέʔγϣϯͷ෮چ (ϨϓϦέʔγϣϯର৅͔ΒҰ࣌আ֎) ύϒϦογϟʔଆͰର৅ςʔϒϧΛϨϓϦέʔγϣϯ͔Β আ֎͠·͢ɻ
 αϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷߋ৽ͱςʔϒ ϧσʔλ࡟আ͠·͢ɻ
 
 
 =# ALTER PUBLICATION test DEOP TABLE test; =# ALTER SUBSCRIPTION test REFRESH PUBLICATION; =# TRUNCATE test;

ϩδΧϧϨϓϦέʔγϣϯͷ෮چ (ϨϓϦέʔγϣϯର৅͔ΒҰ࣌আ֎) ύϒϦογϟʔଆͰର৅ςʔϒϧΛϨϓϦέʔγϣϯʹ௥ Ճ͠·͢ɻ
 αϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷߋ৽Λߦ͍· ͢ɻ =# ALTER SUBSCRIPTION test REFRESH PUBLICATION; =# SELECT name FROM test WHERE id = 10; -[ RECORD 1 ]-+------------- name | test2 =# ALTER PUBLICATION test ADD TABLE test;