Upgrade to PRO for Only $50/Year—Limited-Time Offer! 🔥

PostgreSQLのレプリケーションを使ってみよう / PostgreSQL 13 Rep...

ester41
February 06, 2021

PostgreSQLのレプリケーションを使ってみよう / PostgreSQL 13 Replication

ester41

February 06, 2021
Tweet

More Decks by ester41

Other Decks in Technology

Transcript

  1. ࣗݾ঺հ ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ ) ॴଐ: ೔ຊPostgreSQLϢʔβձ
 ؔ੢ࢧ෦௕ Twitter/GitHub:

    @ester4 1 ొஃࢿྉ: https://speakerdeck.com/ester41 ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ
  2. ϨϓϦέʔγϣϯͱ͸ ߴՄ༻ੑͱෛՙ෼ࢄΛߦ͏͜ͱ͕ՄೳͰ͢ɻ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡ ߋ৽ ϓϥΠϚϦʔ ελϯόΠ ෳ੡

    ࢀর ߋ৽ ߴՄ༻ੑ ෛՙ෼ࢄ ෛՙ͕1఺ʹूத͠ͳ͍ͨΊɺ γεςϜશମͱͯ͠ੑೳ͕޲্ʂ 1୆͕ނোͯ͠΋ผαʔόʔͰ ॲཧ͕Մೳʂ
  3. ϨϓϦέʔγϣϯͱ͸ (ϨϓϦέʔγϣϯํࣜ) ԼهͷΑ͏ͳ͞·͟·ͳϨϓϦέʔγϣϯ͕ଘࡏ͠·͢ɻ
 ·ͨɺελϯόΠ΁ͷΞΫηεՄ൱͕ଘࡏ͠·͢ɻ ํࣜ ಛ௃ ελϯόΠํࣜ ڞ༗σΟεΫ ϓϥΠϚϦʔͱελϯόΠͰσʔλϕʔ εΫϥελΛڞ༗͢Δɻ

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

    ΢ΥʔϜελϯόΠ ϑΝΠϧγεςϜϨϓϦέʔγϣϯ ڞ༗σΟεΫ૬౰ͷػೳΛιϑτ΢ΣΞ ϨϕϧͰ࣮૷͠ɺϓϥΠϚϦʔͱελϯ όΠͰσʔλϕʔεΫϥελΛڞ༗͢ Δɻ ϩάγοϐϯά WALϕʔεͷϨϓϦέʔγϣϯɻ ϗοτελϯόΠ τϦΨʔϕʔεϨϓϦέʔγϣϯ ϓϦϚϦʔ΁ͷߋ৽ΛτϦΨʔͱͯ͠ε λϯόΠʹಉ͡ߋ৽Λ఻೻͢Δɻ SQLϕʔεϨϓϦέʔγϣϯ ϓϥΠϚϦʔͱελϯόΠʹಉ͡SQLΛ ૹΔϛυϧ΢ΣΞΛ஥հͤ͞Δɻ PostgreSQLඪ४Ͱ࢖༻Մೳͳ ϩάγοϐϯάํࣜͷϨϓϦέʔ γϣϯʹ͍ͭͯղઆ͠·͢ɻ ※΢ΥʔϜελϯόΠ: ঢ֨·Ͱ࢖༻ෆՄ / ϗοτελϯόΠ: ঢ֨͠ͳͯ͘΋࢖༻Մ / WAL: τϥϯβΫγϣϯϩά(Write Ahead Logging)
  5. ͦΕͧΕͷϨϓϦέʔγϣϯͷϝϦοτɾσϝϦοτ͸ҎԼͷ௨ΓͰ͢ɻ ϨϓϦέʔγϣϯͱ͸ (PostgreSQLඪ४ํࣜ) ϝϦοτ σϝϦοτ ετϦʔϛϯά ϨϓϦέʔγϣϯ • ෺ཧతʹಉ͡σʔλϕʔεΛෳ੡͢Δ͜ͱ͕ Ͱ͖Δɻ

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

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

    ϓϥΠϚϦʔ ελϯόΠ off ඇಉظ ଴ͨͳ͍ ଴ͨͳ͍ local ඇಉظ ଴ͭ ଴ͨͳ͍ remote_write ಉظ ଴ͭ ϝϞϦʔॻ͖ࠐΈ·Ͱ଴ͭ on ಉظ ଴ͭ σΟεΫॻ͖ࠐΈ·Ͱ଴ͭ remote_apply ಉظ ଴ͭ WALదԠ·Ͱ଴ͭ ཁ஫ҙϙΠϯτʂ
 remote_applyͷΈ׬શಉظ
  8. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ಉظ/ඇಉظͷઃఆ - 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୆ͷελϯόΠΛಉظͱͯ͠ѻ͍·͢ɻ
  9. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - ϩά) ϨϓϦέʔγϣϯͷ֬ೝ͸ɺαʔόʔϩά΍pg_stat_replicationϏϡʔΛݟΔ͜ ͱͰ֬ೝͰ͖·͢ɻ • αʔόʔϩάͷ֬ೝ
 αʔόʔىಈ࣌ʹϨϓϦέʔγϣϯΛ։࢝ͨ͠ϝοηʔδ͕ग़ྗ͞Ε͍ͯΔ͜ ͱΛ֬ೝ͠·͢ɻ

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

    • ϓϥΠϚϦʔ
 • ελϯόΠ
 LOG: standby “<ελϯόΠ໊>” is now a synchronous standby with priority 1 LOG: started streaming WAL from primary at <WALҐஔ> on timeline <λΠϜϥΠϯ஋> λΠϜϥΠϯID͸ɺϑΣΠϧΦʔόʔ΍ όοΫΞοϓ͔Β෮چͰ൪߸͕੾ΓସΘΔʂ
  11. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - 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: ඇಉظ͕ͩɺಉظʹঢ֨͢ΔՄೳੑ͋Γ
  12. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ઀ଓઃఆ) ϨϓϦέʔγϣϯΛߦ͏ͨΊʹ͸ɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞੒͢Δඞཁ͕͋ Γ·͢ɻ 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
  13. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (جຊతͳઃఆ - ύϒϦογϟʔଆઃఆ) ύϒϦογϟʔଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ى ಈ͠·͢ɻ postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ
 
 PostgreSQLΛ࠶ىಈ͠·͢ɻ listen_addresses

    = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ max_wal_senders = 2 # ύϒϦογϟʔDBͷ୆਺ + 1 wal_level = logical # ϩδΧϧσίʔσΟϯάͷ༗ޮ # systemctl restart postgresql-13
  14. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ςʔϒϧ࡞੒౳ - ύϒϦογϟʔଆઃఆ) ύϒϦογϟʔଆͰςʔϒϧ࡞੒ɺύϒϦέʔγϣϯͷ࡞੒Λߦ͍·͢ɻ
 ύϒϦέʔγϣϯ࡞੒࣌ɺ؅ཧऀϢʔβʔͰ࣮ߦ͢Δඞཁ͸͋Γ·ͤΜɻ
 ্هઃఆ͸ɺ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ͱͯ͠࡞੒
  15. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ςʔϒϧ࡞੒౳ - αϒεΫϥΠόʔଆઃఆ) αϒεΫϥΠόʔଆͰςʔϒϧ࡞੒ɺαϒεΫϦϓγϣϯͷ࡞੒Λߦ͍ ·͢ɻ
 ύϒϦογϟʔଆͷςʔϒϧͱಉ͡ςʔϒϧΛ࡞੒͍ͯͩ͘͠͞ɻ
 αϒεΫϦϓγϣϯ࡞੒࣌ɺ؅ཧऀϢʔβʔͰ࣮ߦ͢Δඞཁ͕͋Γ· ͢ɻ =#

    CREATE TABLE test (id integer, name text, primary key (id)); =# CREATE SUBSCRIPTION test CONNECTION ‘host=<ύϒϦογϟʔଆϗετ໊> port=<ϙʔτ൪߸> dbname=<σʔλϕʔε໊> user=<ϨϓϦέʔγϣϯϢʔβʔ> password=<ύεϫʔυ>’ publication <ύϒϦέʔγϣϯ໊>; -- αϒεΫϦϓγϣϯ໊Λtestͱͯ͠࡞੒
  16. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ςʔϒϧͷ௥Ճɾ࡟আ) ϨϓϦέʔγϣϯʹςʔϒϧΛ௥Ճɾ࡟আ͍ͨ͠৔߹͸ɺ ALTER PUBLICATIONίϚϯυͰมߋ͕ՄೳͰ͢ɻ
 
 
 
 มߋΛߦͬͨ৔߹ɺαϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷ ߋ৽͕ඞཁͱͳΓ·͢ɻ


    
 
 =# ALTER PUBLICATION <ύϒϦέʔγϣϯ໊> ADD TABLE <௥Ճςʔϒϧ໊>; -- ςʔϒϧ௥Ճ =# ALTER PUBLICATION <ύϒϦέʔγϣϯ໊> DROP TABLE <࡟আςʔϒϧ໊>; -- ςʔϒϧ࡟আ =# ALTER SUBSCRIPTION <αϒεΫϦϓγϣϯ໊> REFRESH PUBLICATION;
  17. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ϨϓϦέʔγϣϯͷఀࢭɾ࠶։) ϨϓϦέʔγϣϯΛఀࢭɾ࠶։͢Δ৔߹͸ɺαϒεΫϥΠ όʔଆͰૢ࡞Λߦ͍·͢ɻ
 
 
 
 =# ALTER SUBSCRIPTION

    <αϒεΫϦϓγϣϯ໊> DISABLE; -- ϨϓϦέʔγϣϯఀࢭ =# ALTER SUBSCRIPTION <αϒεΫϦϓγϣϯ໊> ENABLE; -- ϨϓϦέʔγϣϯ࠶։
  18. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (ύϒϦογϟʔଆͷঢ়گ֬ೝ) ύϒϦογϟʔଆͷঢ়ଶ֬ೝ͸ҎԼͷϏϡʔΛ࢖༻͠·͢ɻ
 
 
 
 
 
 
 


    
 =# 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 <= ϨϓϦέʔγϣϯର৅ςʔϒϧ໊
  19. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ (αϒεΫϥΠόʔଆͷঢ়گ֬ೝ) αϒεΫϥΠόʔଆͷঢ়ଶ֬ೝ͸ҎԼͷϏϡʔΛ࢖༻͠·͢ɻ
 
 
 
 
 
 
 ·ͨϩάϑΝΠϧΛݟΔ͜ͱͰɺϨϓϦέʔγϣϯͰίϯϑϦ

    Ϋτ͕ൃੜ͍ͯ͠Δ͔Ͳ͏͔͕൑அͰ͖·͢ɻ
 =# SELECT subname, subenabled, subconninfo, subpublications FROM pg_subscription; -[ RECORD 1 ]---+------------- subname | test <= αϒεΫϦϓγϣϯ໊ subenabled | t <= ϨϓϦέʔγϣϯͷ༗ޮঢ়ଶ subconninfo | <઀ଓ৘ใ> <= ύϒϦογϟʔଆ΁ͷ઀ଓ৘ใ subpublications | {test} <= ઀ଓύϒϦέʔγϣϯ໊
  20. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (ελϯόΠͷ࠶ىಈʙϓϥΠϚϦʔ΁ঢ֨) ελϯόΠΛ࠶Ҡಈͤ͞·͢ɻ 
 ελϯόΠΛϓϥΠϚϦʔʹঢ֨ͤ͞·͢ɻ(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ʹ੾ΓସΘ͍ͬͯΔʂ
  21. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (چϓϥΠϚϦʔͷ߱֨) چϓϥΠϚϦʔͷ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 ͜ͷϑΝΠϧ͕ແ͍ͱɺ ελϯόΠͰىಈ͠ͳ͍ʂ
  22. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ (چϓϥΠϚϦʔͷಈ࡞֬ೝ) چϓϥΠϚϦʔʹ৽ϓϥΠϚϦʔͷߋ৽৘ใ͕࿈ܞ͞Ε͍ͯΔ͔֬ೝ ͠·͢ɻ
 
 
 
 
 
 


    چϓϥΠϚϦʔ͕ελϯόΠʹͳ͍ͬͯΔ͔ɺσʔλΛ௥Ճͯ֬͠ೝ ͯ͠Έ·͢ɻ =# SELECT * FROM test; test --------------- ςετσʔλ ςετσʔλ2 =# INSERT INTO test VALUES (‘ςετσʔλ’); ERROR: cannot execute INSERT in a read-only transaction σʔλ͕࿈ܞ͞Ε͍ͯΔ
  23. ϩδΧϧϨϓϦέʔγϣϯͷ෮چ (ίϯϑϦΫτൃੜ) αϒεΫϥΠόʔଆͰσʔλΛ௥Ճ͠·͢ɻ
 
 ύϒϦογϟʔଆͰɺಉ͡ओΩʔͷσʔλΛ௥Ճ͠·͢ɻ
 
 
 αϒεΫϥΠόʔଆͷϩάʹΤϥʔ͕ు͖ग़͞Ε·͢ɻ =# 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.