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

Transcript

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

   View Slide

2. ࣗݾ঺հ
   ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ
   )
   
   ॴଐ: ೔ຊPostgreSQLϢʔβձ

   ؔ੢ࢧ෦௕
   Twitter/GitHub: @ester4
   1
   
   ొஃࢿྉ: https://speakerdeck.com/ester41
   ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

6. ϨϓϦέʔγϣϯͱ͸
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

10. ϨϓϦέʔγϣϯͱ͸
    (ϨϓϦέʔγϣϯํࣜ)
    ԼهͷΑ͏ͳ͞·͟·ͳϨϓϦέʔγϣϯ͕ଘࡏ͠·͢ɻ

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

    View Slide

11. ϨϓϦέʔγϣϯͱ͸
    (ϨϓϦέʔγϣϯํࣜ)
    ԼهͷΑ͏ͳ͞·͟·ͳϨϓϦέʔγϣϯ͕ଘࡏ͠·͢ɻ

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

    View Slide

12. ϨϓϦέʔγϣϯͱ͸
    (PostgreSQLඪ४ํࣜ)
    PostgreSQL͕ඪ४ͰରԠ͍ͯ͠ΔϨϓϦέʔγϣϯ͸ɺ2छྨଘࡏ͠·͢ɻ
    • ετϦʔϛϯάϨϓϦέʔγϣϯ

    WALΛϑΝΠϧ୯ҐͰ͸ͳ͘มߋ಺༰୯ҐͰૹΓ·͢ɻ

    ϓϥΠϚϦʔͱελϯόΠ͸෺ཧతʹಉ͡σʔλͰ͢ɻ
    • ϩδΧϧ(࿦ཧ)ϨϓϦέʔγϣϯ

    ϩδΧϧσίʔσΟϯά(WALΛɺ֎෦ͷγεςϜ͕ղऍͰ͖Δσʔλʹ

    σίʔυ͢Δϓϩηε)ʹΑΓ࿦ཧతͳมߋ಺༰ΛελϯόΠʹૹΓ·͢ɻ

    ϓϥΠϚϦʔͱελϯόΠ͸෺ཧతʹҟͳΔσʔλͰ͢ɻ
    

    View Slide

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

    View Slide

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

    View Slide

15. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ετϦʔϛϯάϨϓϦέʔγϣϯͷ࢓૊Έ)
    1. ϓϥΠϚϦʔ͸WALΛελϯόΠʹૹ৴͢Δɻ
    2. ελϯόΠ͸సૹ͞ΕͨWALΛϩʔυ(ϦΧόϦʔ)ͯ͠σʔλϕʔεʹదԠ
    ͢Δɻ
    ্هಈ࡞͸ɺϓϥΠϚϦʔଆͰwalsenderϓϩηε͕ɺ

    ελϯόΠଆͰwalreceiverϓϩηε͕ಈ࡞͢Δ͜ͱͰ࣮ݱ͍ͯ͠·͢ɻ
    WAL
    ϓϥΠϚϦʔ
    ΫϥΠΞϯτ ελϯόΠ
    WAL
    1. ߋ৽
    SQL
    2. WAL
    

    ॻࠐ
    5. WAL
    

    ॻࠐ
    3. WAL
    

    సૹ
    4. WAL
    ϩʔυ
    

    View Slide

16. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ)
    ετϦʔϛϯάϨϓϦέʔγϣϯΛઃఆ͢Δʹ͸ɺϓϥΠ
    ϚϦʔଆٴͼελϯόΠଆͷpostgresql.confͷमਖ਼͕ඞ
    ཁͰ͢ɻ

    ·ͨɺσʔλ࿈ܞΛߦ͏ͨΊɺϨϓϦέʔγϣϯ༻Ϣʔ
    βʔͷ௥Ճͱ઀ଓઃఆ(pg_hba.conf)͕ඞཁͱͳΓ·͢ɻ

    ϓϥΠϚϦʔଆ΁ॳظσʔλ౤ೖ࣌ɺϨϓϦέʔγϣϯͷ
    ઃఆΛߦΘͳ͍Ͱߴ଎ʹσʔλ౤ೖΛߦ͏ํ๏͕͋Γ·͢
    ͕ɺࠓճ͸આ໌Λল͖·͢ɻ
    

    View Slide

17. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ - ϓϥΠϚϦʔଆ઀ଓઃఆ)
    ϨϓϦέʔγϣϯΛߦ͏ͨΊʹ͸ɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞
    ੒͢Δඞཁ͕͋Γ·͢ɻ
    ϓϥΠϚϦʔଆͷσʔλϕʔε΁؅ཧऀϢʔβʔͰ઀ଓΛߦ͍ɺ
    REPLICATIONݖݶΛ෇༩ͨ͠ϢʔβʔΛ࡞੒͠·͢ɻ

    ϓϥΠϚϦʔଆͷσʔλϕʔεͷpg_hba.conf΁ɺ্هϢʔβʔͷ઀ଓ
    ઃఆͷ௥ՃΛߦ͍·͢ɻ
    =# CREATE USER repl_user LOGIN REPLICATION PASSWORD '...';
    host replication repl_user / md5
    

    View Slide

18. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ - ϓϥΠϚϦʔଆઃఆ)
    ϓϥΠϚϦʔଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ىಈ
    ͠·͢ɻ
    postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ

    PostgreSQLΛ࠶ىಈ͠·͢ɻ
    listen_addresses = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ
    
    
    max_wal_senders = 2 # ελϯόΠDBͷ୆਺ + 1
    # systemctl restart postgresql-13
    

    View Slide

19. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ - ελϯόΠଆσʔλಉظ)
    ϓϥΠϚϦʔଆͷσʔλΛελϯόΠଆ΁ಉظ͠·͢ɻ

    ॲཧ͸ɺpostgresϢʔβʔͰ࣮ࢪ͠·͢ɻ
    σʔλϕʔεΫϥελΛ࡟আ͠·͢ɻ

    ಉظΛߦ͍·͢ɻ
    $ #PGDATA=/var/lib/pgsql/13/data/ ͕ઃఆ͞Ε͍ͯΔ΋ͷͱ͢Δɻ
    
    
    $ rm -rf ${PDATA}
    # /usr/pgsql-13/bin/pgbasebackup -R -D ${PGDATA} -U repl_user -h
    

    View Slide

20. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ - ελϯόΠଆઃఆ)
    ελϯόΠଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ىಈ͠
    ·͢ɻ
    postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ

    PostgreSQLΛ࠶ىಈ͠·͢ɻ
    listen_addresses = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ
    
    
    #max_wal_senders = 2 # ίϝϯτΞ΢τ͠·͢
    # systemctl restart postgresql-13
    

    View Slide

21. 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
    

    View Slide

22. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ಉظ/ඇಉظͷҧ͍)
    ετϦʔϛϯάϨϓϦέʔγϣϯʹ͸ಉظͱඇಉظͷ2छ
    ྨͷϞʔυ͕ଘࡏ͠·͢ɻ

    (લड़ͷઃఆͰ͸ɺඇಉظઃఆͱͳ͍ͬͯ·͢ɻ)

    2ͭͷҧ͍͸ɺελϯόΠଆͰWAL͕ͲͷΑ͏ͳঢ়ଶʹ
    ͳͬͨΒϓϥΠϚϦʔͰॲཧΛ׬ྃ(ίϛοτ׬ྃΛ௨஌)
    ͤ͞Δ͔ͷҧ͍Ͱ͢ɻ

    

    View Slide

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

    View Slide

24. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ಉظ/ඇಉظͷઃఆ)
    ಉظɺඇಉظ͸ɺԼهͷύϥϝʔλʔͰઃఆ͠·͢ɻ
    • synchronous_commit

    τϥϯβΫγϣϯͷίϛοτ͕ΫϥΠΞϯτʹ“׬ྃ”Λใࠂ͢Δલʹ

    WALϨίʔυ͕σΟεΫ্ʹॻ͖ࠐ·ΕΔ·Ͱ଴͔ͭͲ͏͔ͷઃఆΛ

    ߦ͍·͢ɻ
    • synchronous_standby_names

    ಉظ͢ΔελϯόΠ໊(ΞϓϦέʔγϣϯ໊)ΛΧϯϚ۠੾ΓͰઃఆ͠·͢ɻ

    “*”Λઃఆ͢ΔͱɺશͯͷελϯόΠ͕ର৅ͱͳΓɺ

    “”ͷ৔߹͸શͯͷελϯόΠ͕ର৅֎ͱͳΓ·͢ɻ
    

    View Slide

25. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ಉظ/ඇಉظͷઃఆ - synchronous_commit)
    synchronous_commitͷઃఆ஋͸ɺԼهͷ௨Γͱͳͬͯ
    ͍·͢ɻ 

    σϑΥϧτ஋͸onͰ͢ɻ
    ઃఆ஋ ಉظ/ඇಉظ ϓϥΠϚϦʔ ελϯόΠ
    off ඇಉظ ଴ͨͳ͍ ଴ͨͳ͍
    local ඇಉظ ଴ͭ ଴ͨͳ͍
    remote_write ಉظ ଴ͭ ϝϞϦʔॻ͖ࠐΈ·Ͱ଴ͭ
    on ಉظ ଴ͭ σΟεΫॻ͖ࠐΈ·Ͱ଴ͭ
    remote_apply ಉظ ଴ͭ WALదԠ·Ͱ଴ͭ
    ཁ஫ҙϙΠϯτʂ

    remote_applyͷΈ׬શಉظ
    

    View Slide

26. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ಉظ/ඇಉظͷઃఆ - 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୆ͷελϯόΠΛಉظͱͯ͠ѻ͍·͢ɻ
    

    View Slide

27. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - ϩά)
    ϨϓϦέʔγϣϯͷ֬ೝ͸ɺαʔόʔϩά΍pg_stat_replicationϏϡʔΛݟΔ͜
    ͱͰ֬ೝͰ͖·͢ɻ
    • αʔόʔϩάͷ֬ೝ

    αʔόʔىಈ࣌ʹϨϓϦέʔγϣϯΛ։࢝ͨ͠ϝοηʔδ͕ग़ྗ͞Ε͍ͯΔ͜
    ͱΛ֬ೝ͠·͢ɻ
    • ϓϥΠϚϦʔ

    • ελϯόΠ

    LOG: standby “” is now a synchronous standby with priority 1
    LOG: started streaming WAL from primary at on timeline
    

    View Slide

28. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - ϩά)
    ϨϓϦέʔγϣϯͷ֬ೝ͸ɺαʔόʔϩά΍pg_stat_replicationϏϡʔΛݟΔ͜
    ͱͰ֬ೝͰ͖·͢ɻ
    • αʔόʔϩάͷ֬ೝ

    αʔόʔىಈ࣌ʹϨϓϦέʔγϣϯΛ։࢝ͨ͠ϝοηʔδ͕ग़ྗ͞Ε͍ͯΔ͜
    ͱΛ֬ೝ͠·͢ɻ
    • ϓϥΠϚϦʔ

    • ελϯόΠ

    LOG: standby “” is now a synchronous standby with priority 1
    LOG: started streaming WAL from primary at on timeline
    λΠϜϥΠϯID͸ɺϑΣΠϧΦʔόʔ΍
    όοΫΞοϓ͔Β෮چͰ൪߸͕੾ΓସΘΔʂ
    

    View Slide

29. ετϦʔϛϯάϨϓϦέʔγϣϯͷઃఆ
    (ϨϓϦέʔγϣϯͷঢ়گ֬ೝ - 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: ඇಉظ͕ͩɺಉظʹঢ֨͢ΔՄೳੑ͋Γ
    

    View Slide

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

    View Slide

31. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ϩδΧϧϨϓϦέʔγϣϯͷ࢓૊Έ)
    1. ϓϥΠϚϦʔ(ύϒϦογϟʔ)͸WALΛղੳ͠ɺߋ৽৘ใΛελϯόΠ(αϒ
    εΫϥΠόʔ)ʹૹ৴͢Δɻ
    2. αϒεΫϥΠόʔ͸ߋ৽৘ใΛݩʹςʔϒϧΛߋ৽͢Δɻ
    ্هಈ࡞͸ɺύϒϦογϟʔଆͰpgoutputͱwalsenderϓϩηε͕ɺ

    αϒεΫϥΠόʔଆͰbgworkerϓϩηε͕ಈ࡞͢Δ͜ͱͰ࣮ݱ͍ͯ͠·͢ɻ
    WAL
    ύϒϦογϟʔ
    ΫϥΠΞϯτ αϒεΫϥΠόʔ
    1. ߋ৽
    SQL
    3. WAL
    

    ॻࠐ
    5. σʔλ
    

    ૹ৴
    2. ςʔϒϧ
    

    ߋ৽
    4. WAL
    

    ϩʔυ
    WAL
    7. WAL
    

    ॻࠐ
    6. ςʔϒϧ
    

    ߋ৽
    

    View Slide

32. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ)
    ϩδΧϧϨϓϦέʔγϣϯΛઃఆ͢Δʹ͸ɺύϒϦο
    γϟʔଆͷpostgresql.confͷमਖ਼͕ඞཁͰ͢ɻ

    ·ͨɺσʔλ࿈ܞΛߦ͏ͨΊɺϨϓϦέʔγϣϯ༻Ϣʔ
    βʔͷ௥Ճͱ઀ଓઃఆ(pg_hba.conf)͕ඞཁͱͳΓ·͢ɻ

    

    View Slide

33. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ - ઀ଓઃఆ)
    ϨϓϦέʔγϣϯΛߦ͏ͨΊʹ͸ɺϨϓϦέʔγϣϯઐ༻ϢʔβʔΛ࡞੒͢Δඞཁ͕͋
    Γ·͢ɻ
    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 / md5
    

    View Slide

34. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (جຊతͳઃఆ - ύϒϦογϟʔଆઃఆ)
    ύϒϦογϟʔଆͷઃఆΛ׬ྃͤ͞ɺPostgreSQLΛ࠶ى
    ಈ͠·͢ɻ
    postgresql.confͷҎԼͷ஋Λมߋ͠·͢ɻ

    

    PostgreSQLΛ࠶ىಈ͠·͢ɻ
    listen_addresses = '*' # ઀ଓՄೳΞυϨεΛແ੍ݶʹઃఆ
    
    
    max_wal_senders = 2 # ύϒϦογϟʔDBͷ୆਺ + 1
    
    
    wal_level = logical # ϩδΧϧσίʔσΟϯάͷ༗ޮ
    # systemctl restart postgresql-13
    

    View Slide

35. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ςʔϒϧ࡞੒౳ - ύϒϦογϟʔଆઃఆ)
    ύϒϦογϟʔଆͰςʔϒϧ࡞੒ɺύϒϦέʔγϣϯͷ࡞੒Λߦ͍·͢ɻ

    ύϒϦέʔγϣϯ࡞੒࣌ɺ؅ཧऀϢʔβʔͰ࣮ߦ͢Δඞཁ͸͋Γ·ͤΜɻ

    ্هઃఆ͸ɺ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ͱͯ͠࡞੒
    

    View Slide

36. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ςʔϒϧ࡞੒౳ - αϒεΫϥΠόʔଆઃఆ)
    αϒεΫϥΠόʔଆͰςʔϒϧ࡞੒ɺαϒεΫϦϓγϣϯͷ࡞੒Λߦ͍
    ·͢ɻ

    ύϒϦογϟʔଆͷςʔϒϧͱಉ͡ςʔϒϧΛ࡞੒͍ͯͩ͘͠͞ɻ

    αϒεΫϦϓγϣϯ࡞੒࣌ɺ؅ཧऀϢʔβʔͰ࣮ߦ͢Δඞཁ͕͋Γ·
    ͢ɻ
    =# CREATE TABLE test (id integer, name text, primary key (id));
    
    
    =# CREATE SUBSCRIPTION test CONNECTION ‘host= port=
    
    
    dbname= user= password=’
    
    
    publication ;
    
    
    -- αϒεΫϦϓγϣϯ໊Λtestͱͯ͠࡞੒
    

    View Slide

37. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ಈ࡞֬ೝ)
    ύϒϦογϟʔଆͷςʔϒϧʹσʔλΛ௥Ճ͠ɺαϒεΫϥΠόʔଆʹ࿈ܞ
    ͞Ε͍ͯΔ͔֬ೝͯ͠Έ·͠ΐ͏ɻ

    

    ύϒϦογϟʔଆͰσʔλ௥Ճ

    

    αϒεΫϥΠόʔଆͰσʔλ֬ೝ
    =# INSERT INTO test VALUES (1, 'test');
    
    
    =# SELECT name FROM test;
    
    
    -[ RECORD 1 ]-+-------------
    
    
    id | 1
    
    
    name | test
    

    View Slide

38. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ςʔϒϧͷ௥Ճɾ࡟আ)
    ϨϓϦέʔγϣϯʹςʔϒϧΛ௥Ճɾ࡟আ͍ͨ͠৔߹͸ɺ
    ALTER PUBLICATIONίϚϯυͰมߋ͕ՄೳͰ͢ɻ

    

    

    

    มߋΛߦͬͨ৔߹ɺαϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷ
    ߋ৽͕ඞཁͱͳΓ·͢ɻ

    

    

    =# ALTER PUBLICATION ADD TABLE ; -- ςʔϒϧ௥Ճ
    
    
    =# ALTER PUBLICATION DROP TABLE ; -- ςʔϒϧ࡟আ
    =# ALTER SUBSCRIPTION REFRESH PUBLICATION;
    

    View Slide

39. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ϨϓϦέʔγϣϯͷఀࢭɾ࠶։)
    ϨϓϦέʔγϣϯΛఀࢭɾ࠶։͢Δ৔߹͸ɺαϒεΫϥΠ
    όʔଆͰૢ࡞Λߦ͍·͢ɻ

    

    

    

    =# ALTER SUBSCRIPTION DISABLE; -- ϨϓϦέʔγϣϯఀࢭ
    
    
    =# ALTER SUBSCRIPTION ENABLE; -- ϨϓϦέʔγϣϯ࠶։
    

    View Slide

40. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (ύϒϦογϟʔଆͷঢ়گ֬ೝ)
    ύϒϦογϟʔଆͷঢ়ଶ֬ೝ͸ҎԼͷϏϡʔΛ࢖༻͠·͢ɻ

    

    

    

    

    

    

    

    

    =# 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 <= ϨϓϦέʔγϣϯର৅ςʔϒϧ໊
    
    
    

    View Slide

41. ϩδΧϧϨϓϦέʔγϣϯͷઃఆ
    (αϒεΫϥΠόʔଆͷঢ়گ֬ೝ)
    αϒεΫϥΠόʔଆͷঢ়ଶ֬ೝ͸ҎԼͷϏϡʔΛ࢖༻͠·͢ɻ

    

    

    

    

    

    

    ·ͨϩάϑΝΠϧΛݟΔ͜ͱͰɺϨϓϦέʔγϣϯͰίϯϑϦ
    Ϋτ͕ൃੜ͍ͯ͠Δ͔Ͳ͏͔͕൑அͰ͖·͢ɻ

    =# SELECT subname, subenabled, subconninfo, subpublications FROM pg_subscription;
    
    
    -[ RECORD 1 ]---+-------------
    
    
    subname | test <= αϒεΫϦϓγϣϯ໊
    
    
    subenabled | t <= ϨϓϦέʔγϣϯͷ༗ޮঢ়ଶ
    
    
    subconninfo | <= ύϒϦογϟʔଆ΁ͷ઀ଓ৘ใ
    
    
    subpublications | {test} <= ઀ଓύϒϦέʔγϣϯ໊
    

    View Slide

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

    View Slide

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

    View Slide

44. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ
    (ϓϥΠϚϦʔͷఀࢭʙελϯόΠͷઃఆมߋ)
    ϓϥΠϚϦʔΛఀࢭͤ͞·͢ɻ
    

    ελϯόΠͷpg_hba.confΛมߋ͠ɺچϓϥΠϚϦʔ͔Β
    ઀ଓͰ͖ΔΑ͏ʹ͠·͢ɻ

    

    ελϯόΠͷpostgresql.auto.confͷprimary_conninfoΛ
    ίϝϯτΞ΢τ͠·͢ɻ
    # systemctl stop postgresql-13
    host replication repl_user / md5
    #primary_conninfo = ''
    

    View Slide

45. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ
    (ελϯόΠͷ࠶ىಈʙϓϥΠϚϦʔ΁ঢ֨)
    ελϯόΠΛ࠶Ҡಈͤ͞·͢ɻ
    

    ελϯόΠΛϓϥΠϚϦʔʹঢ֨ͤ͞·͢ɻ(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ʹ੾ΓସΘ͍ͬͯΔʂ
    

    View Slide

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

    View Slide

47. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ
    (چϓϥΠϚϦʔͷ߱֨)
    چϓϥΠϚϦʔͷ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
    ͜ͷϑΝΠϧ͕ແ͍ͱɺ
    ελϯόΠͰىಈ͠ͳ͍ʂ
    

    View Slide

48. ετϦʔϛϯάϨϓϦέʔγϣϯͷ෮چ
    (چϓϥΠϚϦʔͷಈ࡞֬ೝ)
    چϓϥΠϚϦʔʹ৽ϓϥΠϚϦʔͷߋ৽৘ใ͕࿈ܞ͞Ε͍ͯΔ͔֬ೝ
    ͠·͢ɻ

    

    

    

    

    

    

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

    View Slide

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

    View Slide

50. ϩδΧϧϨϓϦέʔγϣϯͷ෮چ
    ετϦʔϛϯάϨϓϦέʔγϣϯͱҟͳΓɺαϒεΫϥΠόʔଆ
    ͷͰςʔϒϧ͸ߋ৽ՄೳͰ͢ɻ

    ύϒϦογϟʔଆͷมߋͱɺαϒεΫϥΠόʔଆͷߋ৽Ͱίϯϑ
    ϦΫτ(ओΩʔ੍໿ҧ൓ͳͲ)͕ൃੜ͢ΔͱɺϨϓϦέʔγϣϯ͕
    ఀࢭ͠·͢ɻ

    ϩδΧϧϨϓϦέʔγϣϯͷ෮چ͸ɺ͜ͷ໰୊Λղܾ͢Δ͔ɺ
    ύϒϦέʔγϣϯଆͰ֘౰ςʔϒϧΛϨϓϦέʔγϣϯ͔Β֎
    ͠ɺαϒεΫϥΠόʔଆͷσʔλΛ࡟আޙɺϨϓϦέʔγϣϯର
    ৅ʹ௥Ճ͢Δ͜ͱͰղফͰ͖·͢ɻ
    

    View Slide

51. ϩδΧϧϨϓϦέʔγϣϯͷ෮چ
    (ίϯϑϦΫτൃੜ)
    αϒεΫϥΠόʔଆͰσʔλΛ௥Ճ͠·͢ɻ

    

    ύϒϦογϟʔଆͰɺಉ͡ओΩʔͷσʔλΛ௥Ճ͠·͢ɻ

    

    

    αϒεΫϥΠόʔଆͷϩάʹΤϥʔ͕ు͖ग़͞Ε·͢ɻ
    =# 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.
    

    View Slide

52. ϩδΧϧϨϓϦέʔγϣϯͷ෮چ
    (ίϯϑϦΫτআڈ - ֘౰Ϩίʔυ࡟আ)
    αϒεΫϥΠόʔଆͰɺݪҼϨίʔυΛ࡟আ͠·͢ɻ

    ࡟আޙɺϨϓϦέʔγϣϯ͕࠶։͞Ε͍ͯΔ͔ςʔϒϧΛ
    ֬ೝͯ͠Έ·͢ɻ
    =# DELETE FROM test WHERE id = 10;
    =# SELECT name FROM test WHERE id = 10;
    
    
    -[ RECORD 1 ]-+-------------
    
    
    name | test2
    

    View Slide

53. ϩδΧϧϨϓϦέʔγϣϯͷ෮چ
    (ϨϓϦέʔγϣϯର৅͔ΒҰ࣌আ֎)
    ύϒϦογϟʔଆͰର৅ςʔϒϧΛϨϓϦέʔγϣϯ͔Β
    আ֎͠·͢ɻ

    αϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷߋ৽ͱςʔϒ
    ϧσʔλ࡟আ͠·͢ɻ

    

    

    =# ALTER PUBLICATION test DEOP TABLE test;
    =# ALTER SUBSCRIPTION test REFRESH PUBLICATION;
    
    
    =# TRUNCATE test;
    

    View Slide

54. ϩδΧϧϨϓϦέʔγϣϯͷ෮چ
    (ϨϓϦέʔγϣϯର৅͔ΒҰ࣌আ֎)
    ύϒϦογϟʔଆͰର৅ςʔϒϧΛϨϓϦέʔγϣϯʹ௥
    Ճ͠·͢ɻ

    αϒεΫϥΠόʔଆͰαϒεΫϦϓγϣϯͷߋ৽Λߦ͍·
    ͢ɻ
    =# ALTER SUBSCRIPTION test REFRESH PUBLICATION;
    
    
    =# SELECT name FROM test WHERE id = 10;
    
    
    -[ RECORD 1 ]-+-------------
    
    
    name | test2
    =# ALTER PUBLICATION test ADD TABLE test;
    

    View Slide