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

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

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

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

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

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

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

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

PostgreSQLʹ͍ͭͯ Mastodonͱಉ͡OSS(Φʔϓϯιʔειϑτ΢ΣΞ)Ͱ͋Γɺ
 RDBMS(ؔ܎σʔλϕʔε؅ཧγεςϜ)ͱݺ͹ΕΔϛυϧ΢ΣΞͰ͢ɻ େֶͷݚڀࣨੜ·ΕͷͨΊɺඪ४SQL΁ͷ४ڌ͸ൺֱత
 ߴ͘ͳ͍ͬͯ·͢ɻ(MySQLͷΑ͏ͳΫη͕গͳ͍ɻ) όʔδϣϯ൪߸ͷॻࣜ͸৽چͷ2छྨଘࡏ͠·͢ɻ
 چ: 9.6.12 => ࠨ͔Β2ͭͷ਺ࣈ͕ϝδϟʔόʔδϣϯͰɺ
 ࢒Γ͕ϚΠφʔόʔδϣϯ
 ৽: 11.2 => ੔਺෦͕ϝδϟʔόʔδϣϯͰɺ
 খ਺෦͕ϚΠφʔόʔδϣϯ

PostgreSQLͷ
 ΞοϓάϨʔυʹ͍ͭͯ(1/3) PostgreSQL͸جຊతʹޙํޓ׵Λେ੾ʹ͍ͯ͠·͕͢ɺ
 ϝδϟʔόʔδϣϯΞοϓͰ಺෦࢓༷͕มߋʹͳΔ৔߹͕͋Γ·͢ɻ
 (SQLߏจͷมߋ͸ɺΊͬͨͳ͜ͱ͕ͳ͍ݶΓมߋ͞Ε·ͤΜɻ) ϝδϟʔόʔδϣϯΞοϓΛߦ͏ࡍ͸มߋ఺Λ֬ೝ͠ɺ
 ࢖༻͍ͯ͠ΔπʔϧͷରԠঢ়گΛ֬ೝ͍ͯͩ͘͠͞ɻ ΞοϓάϨʔυΛߦ͏৔߹͸ɺσʔλͷίϯόʔτॲཧ͕
 ඞཁͱͳΓ·͢ɻ

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

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

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

ΞοϓάϨʔυํ๏ 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

ΞοϓάϨʔυํ๏ 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

ΞοϓάϨʔυํ๏ 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)

ΞοϓάϨʔυํ๏ 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)

ΞοϓάϨʔυํ๏ 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

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

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

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

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