こわくないPostgreSQLのアップグレード

Transcript

1. ͜Θ͘ͳ͍PostgreSQLͷ
   ΞοϓάϨʔυ
   Mastodonαʔόʔษڧձ
   

   View Slide

2. ͸͡Ίʹ
   ֎෦ʹެ։͍ͯ͠ΔαʔϏε͸ɺ
   ͖ͪΜͱΞοϓσʔτ
   ·ͨ͸ΞοϓάϨʔυ͍ͯ͠·͔͢ʁ
   

   View Slide

3. ͸͡Ίʹ
   ݟ͍͑ͯΔιϑτ͚ͩͰ͸ͳ͘ɺ
   όοΫͰಈ͍͍ͯΔιϑτ΋
   ߋ৽͕ඞཁͰ͢ɻ
   

   View Slide

4. ͸͡Ίʹ
   MastodonͷཪଆͰಈ͍͍ͯΔ
   PostgreSQLʹয఺Λ౰ͯͯɺ
   ΞοϓάϨʔυͷղઆ͠·͢ɻ
   

   View Slide

5. ͸͡Ίʹ
   ຊηογϣϯͰ͸ɺMastodonͰ࢖༻͞Ε͍ͯΔɺ
   PostgreSQL 9.5·ͨ͸9.6͔Β11΁ͷͷΞοϓάϨʔυͷղ
   આΛߦ͍·͢ɻ
   RedHatܥͷඇDockerͳMastodonͰղઆ͠·͢ɻ
   ແఀࢭͰΞοϓάϨʔυ͢Δ͜ͱ͸Ͱ͖·ͤΜɻ

   (ํ๏͸͋Γ·͕͢ɺઆ໌ର৅֎ͱ͠·͢ɻ)
   

   View Slide

6. ΞδΣϯμ
   ࣗݾ঺հ
   PostgreSQLʹ͍ͭͯ
   PostgreSQLͷΞοϓάϨʔυʹ͍ͭͯ
   ΞοϓάϨʔυํ๏
   ऴΘΓʹ
   

   View Slide

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

   ؔ੢ࢧ෦௕
   Twitter/GitHub: @ester41
   ࢓ࣄ: อकɾઃܭɾ։ൃͳͲSE࡞ۀશൠ
   

   View Slide

8. Mastodon಺ͷPostgreSQL
   ग़య: https://handon.hatenablog.jp/entry/2018/12/18/004652
   

   View Slide

9. PostgreSQLʹ͍ͭͯ
   Mastodonͱಉ͡OSS(Φʔϓϯιʔειϑτ΢ΣΞ)Ͱ͋Γɺ

   RDBMS(ؔ܎σʔλϕʔε؅ཧγεςϜ)ͱݺ͹ΕΔϛυϧ΢ΣΞͰ͢ɻ
   େֶͷݚڀࣨੜ·ΕͷͨΊɺඪ४SQL΁ͷ४ڌ͸ൺֱత

   ߴ͘ͳ͍ͬͯ·͢ɻ(MySQLͷΑ͏ͳΫη͕গͳ͍ɻ)
   όʔδϣϯ൪߸ͷॻࣜ͸৽چͷ2छྨଘࡏ͠·͢ɻ

   چ: 9.6.12 => ࠨ͔Β2ͭͷ਺ࣈ͕ϝδϟʔόʔδϣϯͰɺ

   ࢒Γ͕ϚΠφʔόʔδϣϯ

   ৽: 11.2 => ੔਺෦͕ϝδϟʔόʔδϣϯͰɺ

   খ਺෦͕ϚΠφʔόʔδϣϯ
   

   View Slide

10. PostgreSQLͷ

    ΞοϓάϨʔυʹ͍ͭͯ(1/3)
    PostgreSQL͸جຊతʹޙํޓ׵Λେ੾ʹ͍ͯ͠·͕͢ɺ

    ϝδϟʔόʔδϣϯΞοϓͰ಺෦࢓༷͕มߋʹͳΔ৔߹͕͋Γ·͢ɻ

    (SQLߏจͷมߋ͸ɺΊͬͨͳ͜ͱ͕ͳ͍ݶΓมߋ͞Ε·ͤΜɻ)
    ϝδϟʔόʔδϣϯΞοϓΛߦ͏ࡍ͸มߋ఺Λ֬ೝ͠ɺ

    ࢖༻͍ͯ͠ΔπʔϧͷରԠঢ়گΛ֬ೝ͍ͯͩ͘͠͞ɻ
    ΞοϓάϨʔυΛߦ͏৔߹͸ɺσʔλͷίϯόʔτॲཧ͕

    ඞཁͱͳΓ·͢ɻ
    

    View Slide

11. PostgreSQLͷ

    ΞοϓάϨʔυʹ͍ͭͯ(2/3)
    PostgreSQLͷΞοϓάϨʔυॲཧʹ͸ɺෳ਺ͷํ๏͕ଘࡏ͠·͢ɻ
    pg_dumpallͰόοΫΞοϓΛऔಘ͠ɺ৽͍͠όʔδϣϯͰϩʔυ͢
    Δɻ
    pg_upgradeͰݹ͍σʔλΛ৽͍͠σʔλʹίϯόʔτ͢Δɻ
    ࠓճ͸ɺͪ͜ΒͰΞοϓάϨʔυ͠·͢ɻ
    Slony-1ͰҟόʔδϣϯؒϨϓϦέʔγϣϯΛ༻͍Δɻ
    

    View Slide

12. PostgreSQLͷ

    ΞοϓάϨʔυʹ͍ͭͯ(3/3)
    ϨϓϦέʔγϣϯΛ૊ΜͰ͍Δ৔߹͸ɺҰׅͯ͠ΞοϓάϨʔυΛߦ
    ͏ඞཁ͕͋Γ·͢ɻ
    ֦ுػೳΛಋೖ͍ͯ͠Δ৔߹ɺΞοϓάϨʔυޙͷPostgreSQLʹ࠶౓
    ಋೖ͢Δඞཁ͕͋Γ·͢ɻ
    ίωΫγϣϯϓʔϥʔ౳ͷιϑτΛಋೖ͍ͯ͠Δ৔߹ɺΞοϓάϨʔ
    υޙͷόʔδϣϯʹରԠ͍ͯ͠Δ͔֬ೝΛߦ͍ͬͯͩ͘͞ɻ
    

    View Slide

13. ΞοϓάϨʔυํ๏
    1. MastodonఀࢭɺσʔλͷόοΫΞοϓ
    σʔλϕʔεͷΞοϓάϨʔυʹࣦഊͨ͠৔߹Λߟྀ͠ɺσʔλϕʔε
    ͷόοΫΞοϓΛऔಘ͠·͢ɻ
    όοΫΞοϓͷऔಘޙʹσʔλߋ৽͕ൃੜ͠ͳ͍Α͏ɺMastodonͷఀ
    ࢭΛߦ͍·͢ɻ
    $ sudo systemctl stop mastodon*
    $ pg_dump -F tar mastodon_production > mastodon_backup.tar
    

    View Slide

14. ΞοϓάϨʔυํ๏
    2. ৽͍͠PostgreSQLͷಋೖ
    ݹ͍PostgreSQLΛఀࢭޙɺ৽͍͠PostgreSQLΛಋೖ͠·͢ɻ
    $ su -
    # systemctl stop postgresql-9.6
    # systemctl disable postgresql-9.6
    # yum install -y https://download.postgresql.org/pub/repos/yum/11/
    redhat/rhel-7-x86_64/pgdg-redhat11-11-2.noarch.rpm
    # yum install -y postgresql11-server postgresql11-contrib
    postgresql11-devel postgresql11-libs
    # cd /usr/pgsql-11/bin/
    # export PGSETUP_INITDB_OPTIONS="-E UTF-8 --locale=C"
    # ./postgresql-11-setup initdb
    

    View Slide

15. ΞοϓάϨʔυํ๏
    3. σʔλͷΞοϓάϨʔυɺىಈ
    ݹ͍PostgreSQLͷσʔλΛɺ৽͍͠PostgreSQLʹҠߦ͠·͢ɻ
    # su - postgres
    $ /usr/pgsql-11/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/
    pgsql-11/bin/ -d /var/lib/pgsql/9.6/data/ -D /var/lib/pgsql/11/data/
    $ vim /var/lib/pgsql/11/data/postgresql.conf pg_hba.conf
    $ exit
    # systemctl start postgresql-11
    # systemctl enable postgresql-11
    # su - postgres
    $ ./analyze_new_cluster.sh
    $ exit
    # exit
    

    View Slide

16. ΞοϓάϨʔυํ๏
    4. Ruby༻PostgreSQL઀ଓϥΠϒϥϦͷߋ৽(1/2)
    MastodonͷPostgreSQL઀ଓ༻ϥΠϒϥϦpg͸ɺlibpqͱݺ͹ΕΔϥΠϒ
    ϥϦΛϦϯΫ͍ͯ͠·͢ɻ
    libpqͱ͸ɺCݴޠͰॻ͔ΕͨPostgreSQLͷΠϯλʔϑΣʔεϥΠϒϥϦ
    ͱͳΓ·͢ɻ( https://www.postgresql.jp/document/10/html/ )
    ͜ͷ··چPostgreSQLΛ࡟আͯ͠͠·͏ͱɺMastodon͔ΒPostgreSQL
    ʹ઀ଓͰ͖ͳ͘ͳͬͯ͠·͍·͢ɻ
    ͦͷͨΊɺϥΠϒϥϦͷ࠶ϦϯΫ(࠶ίϯύΠϧ)͕ඞཁͱͳΓ·͢ɻ
    $ ldd vendor/bundle/ruby/2.6.0/extensions/x86_64-linux/2.6.0-
    static/pg-1.1.4/pg_ext.so | grep libpq
    libpq.so.5 => /usr/pgsql-9.6/lib/libpq.so.5 (0x00007fc0598e4000)
    

    View Slide

17. ΞοϓάϨʔυํ๏
    4. Ruby༻PostgreSQL઀ଓϥΠϒϥϦͷߋ৽(2/2)
    Gemͷ࠶ίϯύΠϧΛߦ͍·͢ɻ
    (RubyͷίϚϯυʹ͍ͭͯ଄ܮ͕ਂ͋͘Γ·ͤΜͷͰɺશϥΠϒϥϦͷ
    ࠶ίϯύΠϧ͕૸Γ·͢ɻ΋ͬͱ͍͍ํ๏͕͋Ε͹ڭ͑ͯͩ͘͞
    ͍ɻɻɻ)
    $ bundle config build.pg --with-pg-config=/usr/pgsql-11/bin/
    pg_config
    $ bundle install --force
    $ ldd vendor/bundle/ruby/2.6.0/extensions/x86_64-linux/2.6.0-
    static/pg-1.1.4/pg_ext.so | grep libpq
    libpq.so.5 => /usr/pgsql-11/lib/libpq.so.5 (0x00007f0fea6b5000)
    

    View Slide

18. ΞοϓάϨʔυํ๏
    5. Mastodonͷىಈɺಈ࡞֬ೝɺچσʔλ࡟আ
    MastodonΛىಈ͠ɺਖ਼ৗʹಈ࡞͢Δ͔֬ೝ͠·͢ɻ
    ໰୊ͳ͚Ε͹ɺچPostgreSQLΛ࡟আ͠·͢ɻ
    $ su -
    # systemctl start mastodon-web mastodon-sidekiq mastodon-streaming
    # su - postgresql
    $ ./delete_old_cluster.sh
    $ rm -f analyze_new_cluster.sh delete_old_cluster.sh
    $ exit
    # yum remove postgresql96-contrib postgresql96-libs postgresql96-
    devel postgresql96-server postgresql96
    # exit
    

    View Slide

19. Φεεϝͷॻ੶
    PostgreSQLʹ͍͍͔ͭͯͪΒֶ΂Δॻ੶Ͱ͢ʂ
    

    View Slide

20. Φεεϝͷॻ੶
    PostgreSQLΛ҆ఆͯ͠ಈ͔ͨ͢ΊʹࢀߟͱͳΔຊͰ͢ʂ
    

    View Slide

21. ऴΘΓʹ
    MastodonͰ࢖༻͞Ε͍ͯΔPostgreSQLͷαϙʔτظݶ͸
    2021೥·Ͱ࢒͍ͬͯ·͕͢ɺݹ͍σʔλϕʔεΛ࢖͍ଓ͚Δ
    ͷ͸ϦεΫͰ͔͋͠Γ·ͤΜɻ
    ৽͍͠όʔδϣϯ͸ηΩϡϦςΟͷվળ΍ύϑΥʔϚϯεͷ
    վળΛߦͳ͍ͬͯͨΓ͍ͯ͠·͢ɻ
    όοΫΞοϓΛऔ͔ͬͯΒͥͻɺ
    օ͞Μ΋ΞοϓάϨʔυΛࢼ͍ͯͩ͘͠͞ʂ
    

    View Slide

22. ྑ͍PostgreSQL MastodonϥΠΫΛʂ
    ͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ
    

    View Slide