Share
Embed
Transcript
54 slides
-
ࣗݾհ ໊લ: ࣉ େً(ͯΒ͏ͪ ͍͖ͨ ) ॴଐ: ຊPostgreSQLϢʔβձ ؔࢧ෦ Twitter/GitHub:
@ester4 1 ొஃࢿྉ: https://speakerdeck.com/ester41 ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ -
ϨϓϦέʔγϣϯͱ ߴՄ༻ੑͱෛՙࢄΛߦ͏͜ͱ͕ՄೳͰ͢ɻ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ
ࢀর ߋ৽ ߴՄ༻ੑ ෛՙࢄ ࢀরͷΈՄೳ -
ϨϓϦέʔγϣϯͱ ߴՄ༻ੑͱෛՙࢄΛߦ͏͜ͱ͕ՄೳͰ͢ɻ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ
ࢀর ߋ৽ ߴՄ༻ੑ ෛՙࢄ ෛՙ͕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 ϩʔυ -
ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ϓϥΠϚϦʔଆଓઃఆ) ϨϓϦέʔγϣϯΛߦ͏ͨΊʹɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞ ͢Δඞཁ͕͋Γ·͢ɻ ϓϥΠϚϦʔଆͷσʔλϕʔεཧऀϢʔβʔͰଓΛߦ͍ɺ 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 -
ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷҧ͍) ಉظɺඇಉظͷҧ͍Լهͷ௨ΓͰ͢ɻ ಛ ϝϦοτ σϝϦοτ ಉظ ελϯόΠͰ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 <WALҐஔ> on timeline <λΠϜϥΠϯ> -
ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - ϩά) ϨϓϦέʔγϣϯͷ֬ೝɺαʔόʔϩάpg_stat_replicationϏϡʔΛݟΔ͜ ͱͰ֬ೝͰ͖·͢ɻ • αʔόʔϩάͷ֬ೝ αʔόʔىಈ࣌ʹϨϓϦέʔγϣϯΛ։࢝ͨ͠ϝοηʔδ͕ग़ྗ͞Ε͍ͯΔ͜ ͱΛ֬ೝ͠·͢ɻ
• ϓϥΠϚϦʔ • ελϯόΠ LOG: standby “<ελϯόΠ໊>” is now a synchronous standby with priority 1 LOG: started streaming WAL from primary at <WALҐஔ> 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. ςʔϒϧ ߋ৽ -
ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ଓઃఆ) ϨϓϦέʔγϣϯΛߦ͏ͨΊʹɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞͢Δඞཁ͕͋ Γ·͢ɻ 1. ύϒϦογϟʔଆͷσʔλϕʔεཧऀϢʔβʔͰଓΛߦ͍ɺREPLICATIONݖ ݶΛ༩ͨ͠ϢʔβʔΛ࡞͠·͢ɻ ·ͨɺσʔλϕʔεͷΞΫηεݖݶΛ༩͠·͢ɻ
2. ύϒϦογϟʔଆͷσʔλϕʔεͷpg_hba.confɺ্هϢʔβʔͷଓઃఆͷՃ Λߦ͍·͢ɻ =# CREATE ROLE repl_user LOGIN REPLICATION PASSWORD ‘...'; =# ALTER DEFAULT PRIVILEGES FOR ROLE <DBॴ༗Ϣʔβʔ> 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} <= ଓύϒϦέʔγϣϯ໊ -
ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (ϓϥΠϚϦʔͷఀࢭʙελϯόΠͷઃఆมߋ) ϓϥΠϚϦʔΛఀࢭͤ͞·͢ɻ ελϯόΠͷ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;