PostgreSQLデプロイの基礎

PostgreSQLカンファレンス2018のチュートリアル枠の登壇資料です。
https://www.postgresql.jp/jpug-pgcon2018

Transcript

1. PostgreSQL σϓϩΠͷجૅ PostgreSQLΧϯϑΝϨϯε2018
2. ͸͡Ίʹ ຊηογϣϯͰ͸ɺLinux(RedHatܥɺDebianܥ)ɾMacɾ Windowsʹରͯ͠ɺPostgreSQLͷΠϯετʔϧͱ
 ॳظηοτΞοϓʹ͍ͭͯղઆ͠·͢ɻ ύοέʔδͰͷΠϯετʔϧͱɺιʔείʔυ͔Β
 Πϯετʔϧ͢Δ2ύλʔϯΛղઆ͠·͢ɻ ॳظઃఆ΍ύϥʔϝʔλʔ౳Ͱ
 ஫ҙ͢΂͖఺ʹ͍ͭͯ΋ղઆ͠·͢ɻ
3. ΞδΣϯμ ࣗݾ঺հ ύοέʔδͰͷΠϯετʔϧ ιʔείʔυ͔ΒͷΠϯετʔϧ ίϯϑΟάઃఆ ऴΘΓʹ
4. ࣗݾ঺հ ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ)
 ॴଐ: ೔ຊPostgreSQLϢʔβձ
 ؔ੢ࢧ෦௕
 Ұݴ: ࢓ࣄͰPostgreSQLΛ࢖༻ͯ͠ɺ
 ૉ௚ʹSQL͕ಈ͍ͯ͘ΕΔͱ͜Ζʹ
 ऒ͔ΕͯϙεάϨʹ͸·Γ·ͨ͠ɻ
5. ղઆ؀ڥ OS - Linux(RedHat) = CentOS 7 - Linux(Debian) = Ubuntu 18.04 - Mac = macOS Mojave - Windows = Windosw 10 Pro PostgreSQL - PostgreSQL 11
6. PostgreSQLͷσʔλϕʔεߏ଄ postgresϓϩηε σʔλϕʔεΫϥελ ઃఆϑΝΠϧ ɾpostgresql.conf ɾpg_hba.conf ͳͲ σʔλϕʔε εΩʔϚ Ϣʔβʔ ɾpostgres ͳͲ ςʔϒϧ ͳͲ ͜ͷηογϣϯͰ͸ɺPostgreSQLΠϯετʔϧͱ
 σʔλϕʔεΫϥελͷ࡞੒ɺઃఆϑΝΠϧͷઃఆʹ͍ͭͯղઆ͠·͢ɻ
7. σʔλϕʔεΫϥελʹ͍ͭͯ(1/2) PostgreSQLͷϓϩηε͸1ͭͷσʔλϕʔεΫϥελΛ࣋ͪɺ
 ͦͷதʹෳ਺ͷσʔλϕʔεΛ֨ೲ͠·͢ɻ σʔλϕʔεΫϥελ͸ɺϑΥϧμʔͱͳ͍ͬͯ·͢ɻ ϑΥϧμʔ಺ʹઃఆϑΝΠϧ΍σʔλϕʔεͷ࣮ମ͕֨ೲ͞Ε·͢ɻ σʔλϕʔεΫϥελΛ࡞੒͢ΔίϚϯυ͸initdbͰ͢ɻ Πϯετʔϧखॱʹͯ࢖༻͠·͢ɻ initdbίϚϯυ͸ύϥϝʔλʔͰઃఆΛม͑Δ͜ͱ͕Ͱ͖·͕͢ɺ ͜͜Ͱ͸3ͭͷύϥϝʔλʹ͍ͭͯɺ࣍ͷεϥΠυͰ঺հ͠·͢ɻ
8. σʔλϕʔεΫϥελʹ͍ͭͯ(2/2) -D(--pgdata) σʔλϕʔεΫϥελͷ࡞੒৔ॴΛࢦఆ͠·͢ɻ ྫ) -D /var/lib/pgsql/11/data/ -E(--encoding) ςϯϓϨʔτσʔλϕʔεͷූ߸ԽํࣜΛࢦఆ͠·͢ɻ ྫ) -E UTF8 --locale σʔλϕʔεͷσϑΥϧτϩέʔϧΛࢦఆ͠·͢ɻ ϩέʔϧʹ͍ͭͯ͸࣍εϥΠυʹͯઆ໌͠·͢ɻ ྫ) --locale=C ্هҎ֎ʹ΋Φϓγϣϯ͕ଘࡏ͠·͢ɻ initdb --helpͰύϥϝʔλʔΛ֬ೝ͢Δ͔ɺ ެࣜυΩϡϝϯτΛ͝ཡ͍ͩ͘͞ɻ https://www.postgresql.jp/document/10/html/app-initdb.html
9. PostgreSQLͷϩέʔϧʹ͍ͭͯ(1/2) PostgreSQLʹ͸ϩέʔϧ(ࠃࡍԽͱ஍ҬԽ)ͱ͍͏ઃఆ͕ଘࡏ͠·͢ɻ σϑΥϧτͰ͸Πϯετʔϧઌͷจࣈίʔυ͕࢖༻͞Ε·͕͢ɺ
 "INESRT"΍"ORDER BY"࣮ߦ࣌ͷੑೳ͕͋ΔͨΊɺ "C"Λ͓קΊ͠·͢ɻ ೔ຊޠͷιʔτΛࣙॻॱͱ͢Δඞཁ͕͋Δ৔߹͸ɺ
 "CREATE TABLE"࣌ʹ"ja_JP.UTF-8"·ͨ͸"ja-x-icu"(ICUαϙʔτ࣌)ɺ "SJIS"(Windows)Λࢦఆ͢Δͱྑ͍Ͱ͠ΐ͏ɻ CREATE TABLE test_table ( test_col text COLLATE "ja-x-icu" );
10. PostgreSQLͷϩέʔϧʹ͍ͭͯ(2/2) ϩέʔϧʹ͸5ͭͷઃఆ͕ଘࡏ͠·͢ɻ ɾจࣈछͷѻ͍(lc_ctype) ɾฒͼॱ(lc_collate) ɾϝοηʔδ(lc_messages) ɾ௨՟(lc_monetary) ɾ਺஋ͷॻࣜ(lc_numeric) ɾ೔෇ͷॻࣜ(lc_time) ্2ͭ͸ɺσʔλϕʔεΫϥελ࡞੒࣌ͷΈઃఆͰ͖·͢ɻ ͦͷଞ͸೔ຊޠ؀ڥʹ߹ΘͤΔͨΊɺ ೔ຊޠϩέʔϧΛઃఆ͓ͯ͘͠ͱྑ͍Ͱ͠ΐ͏ɻ name | setting | context -------------+-------------+----------- lc_collate | C | internal lc_ctype | C | internal lc_messages | ja_JP.UTF-8 | superuser lc_monetary | ja_JP.UTF-8 | user lc_numeric | ja_JP.UTF-8 | user lc_time | ja_JP.UTF-8 | user
11. ύοέʔδͰͷΠϯετʔϧ Linux(RedHatܥ) = RPM Linux(Debianܥ) = DEB MacɾWindows = Πϯετʔϥʔ(EnterpriseDBࣾ੡) Ϋϥ΢υ = αʔϏεʹΑΓఏڙ͞Ε͍ͯ·͢ɻ
 (ผ్ΠϯετʔϧΛߦ͏৔߹͸ɺ
 ద߹͢ΔOSͷΠϯετʔϧํ๏Λ͓ࢼ͍ͩ͘͠͞ɻ)
12. RPMͰͷΠϯετʔϧ(1/3) PostgreSQLެࣜαΠτͷϨϙδτϦ͔ΒΠϯετʔϧΛߦ͍·͢ɻ # yum install -y https://download.postgresql.org/pub/ repos/yum/11/redhat/rhel-7-x86_64/pgdg- centos11-11-2.noarch.rpm # yum install -y postgresql11-server postgresql11- contrib postgresql11-devel postgresql11-libs PostgreSQL11ͷ৽ػೳͰ͋ΔJIT͸ɺ্هͷखॱͰ͸ೖ͍ͬͯ·ͤΜɻ
 LLVM5ͷಋೖ͕ඞཁͱͳΓ·͢ɻ # yum install -y epel-release # yum install -y llvm5.0 llvm5.0-libs # yum install -y postgresql11-llvmjit
13. RPMͰͷΠϯετʔϧ(2/3) σʔλϕʔεΫϥελΛ࡞੒͠·͢ɻ ΤϯίʔσΟϯάΛ"UTF-8"ɺσϑΥϧτϩέʔϧΛ"C"ʹઃఆ͠·͢ɻ # cd /usr/pgsql-11/bin/ # export PGSETUP_INITDB_OPTIONS="-E UTF-8 --locale=C" # ./postgresql-11-setup initdb ίϯϑΟάϑΝΠϧ͸"/var/lib/pgsql/11/data/"ʹଘࡏ͠·͢ɻ ίϯϑΟάઃఆʹ͍ͭͯ͸ผͰઆ໌Λߦ͍·͢ɻ # cd /var/lib/pgsql/11/data/ # ls *.conf pg_hba.conf pg_ident.conf postgresql.auto.conf postgresql.conf
14. RPMͰͷΠϯετʔϧ(3/3) αʔόʔىಈ࣌ʹɺPostgreSQL͕ࣗಈىಈ͢ΔΑ͏ʹઃఆ͠ɺ ىಈΛߦ͍·͢ɻ # systemctl enable postgresql-11 # systemctl start postgresql-11 # su postgres -c "psql -c 'select version();'" version ———————————————————————————————————————————————— PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit (1 ߦ)
15. DEBͰͷΠϯετʔϧ(1/3) PostgreSQLެࣜαΠτͷϨϙδτϦ͔ΒΠϯετʔϧΛߦ͍·͢ɻ $ sudo bash -c 'echo "deb http://apt.postgresql.org/ pub/repos/apt/ bionic-pgdg main" > /etc/apt/ sources.list.d/pgdg.list' $ wget --quiet -O - https://www.postgresql.org/media/ keys/ACCC4CF8.asc | sudo apt-key add - $ sudo apt-get update $ sudo apt-get install -y postgresql-11
16. DEBͰͷΠϯετʔϧ(2/3) σʔλϕʔεΫϥελ͸ύοέʔδΛಋೖ͢Δͱࣗಈతʹ࡞੒͞Ε·͢ɻ ͔͠͠ɺϩέʔϧͷઃఆ͸αʔόʔͷLANG؀ڥม਺ʹґଘ͢ΔͨΊɺ ΤϯίʔσΟϯάΛ"UTF-8"ɺ σϑΥϧτϩέʔϧΛ"C"ʹઃఆͯ͠࠶࡞੒Λߦ͍·͢ɻ $ sudo pg_dropcluster --stop 11 main $ sudo pg_createcluster -e UTF-8 --locale C --start 11 main ίϯϑΟάϑΝΠϧ͸"/etc/postgresql/11/main/"ʹଘࡏ͠·͢ɻ ίϯϑΟάઃఆʹ͍ͭͯ͸ผͰઆ໌Λߦ͍·͢ɻ # cd /etc/postgresql/11/main/ # ls *.conf pg_ctl.conf pg_hba.conf pg_ident.conf postgresql.conf start.conf
17. DEBͰͷΠϯετʔϧ(3/3) αʔόʔىಈ࣌ʹɺPostgreSQL͕ࣗಈىಈ͢ΔΑ͏ʹઃఆΛߦ͍·͢ɻ $ sudo systemctl enable postgresql $ sudo systemctl restart postgresql $ sudo su postgres -c "psql -c 'select version();'" version ———————————————————————————————————————————————— PostgreSQL 11.1 (Ubuntu 11.0-1.pgdg18.04+2) on x86_64- pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit (1 ߦ)
18. ΠϯετʔϥʔͰͷΠϯετʔϧ(1/5) EnterpriseDBࣾύοέʔδ͔ΒΠϯετʔϧΛߦ͍·͢ɻ
 https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
19. ΠϯετʔϥʔͰͷΠϯετʔϧ(2/5) ్தͰΠϯετʔϧ಺༰ͷબ୒ࢶ͕දࣔ͞Ε·͢ɻ PostgreSQL Server
 PostgreSQLຊମͰ͢ɻ pgAdmin4
 GUIͷ؅ཧπʔϧͰ͢ɻ Stack Builder
 ؔ࿈πʔϧͳͲΛಋೖɾ؅ཧΛ
 αϙʔτ͢ΔπʔϧͰ͢ɻ Command LineTools
 PostgreSQLΛίϚϯυϥΠϯ͔Β
 ૢ࡞͢ΔͨΊͷπʔϧͰ͢ɻ
20. ΠϯετʔϥʔͰͷΠϯετʔϧ(3/5) ్தͰΠϯετʔϧઌɺσʔλϕʔεΫϥελͷอଘઌɺ ϙʔτ൪߸ɺύεϫʔυɺϩέʔϧઃఆ͕ೖΓ·͢ɻ ΠϯετʔϧઌɺσʔλϕʔεΫϥελͷอଘઌɺϙʔτ൪߸͸σϑΥϧ τͱ͠·͢ɻ
21. ΠϯετʔϥʔͰͷΠϯετʔϧ(4/5) ίϯϑΟάϑΝΠϧ͸Πϯετʔϧ࣌ʹࢦఆͨ͠ɺ
 σʔλϕʔεΫϥελͷอଘઌʹଘࡏ͠·͢ɻ
 ίϯϑΟάઃఆʹ͍ͭͯ͸ผͰઆ໌Λߦ͍·͢ɻ # cd /etc/postgresql/11/main/ # ls *.conf pg_hba.conf pg_ident.conf postgresql.auto.conf postgresql.conf
22. ΠϯετʔϥʔͰͷΠϯετʔϧ(5/5) αʔόʔىಈ࣌ʹɺPostgreSQL͕ࣗಈىಈ͢ΔΑ͏ʹࣗಈతʹઃఆ͞Εͯ ͍·͢ͷͰɺͦͷ··࢖༻͕ՄೳͰ͢ɻ $ sudo su postgres -c "/Library/PostgreSQL/11/bin/ pg_ctl restart -D /Library/PostgreSQL/11/data/“ $ sudo su postgres -c "/Library/PostgreSQL/11/bin/psql -c 'select version();'" version ———————————————————————————————————————————————— PostgreSQL 11.1 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit (1 row)
23. ιʔείʔυ͔ΒΠϯετʔϧ(1/4) PostgreSQL͸GNU makeʹΑΓίϯύΠϧɺΠϯετʔϧΛߦ͍·͢ɻ makeίϚϯυΛ࣮ߦ͢ΔͨΊʹɺMakefileϑΝΠϧΛඞཁͱ͠·͢ɻ MakefileϑΝΠϧ͸ɺରԠΦϓγϣϯ΍؀ڥʹґଘ͢Δઃఆ͕ඞཁͱͳΔ ͨΊɺιʔείʔυʹ͸ؚ·Ε·ͤΜɻ ͦͷͨΊɺconfigureεΫϦϓτͰMakefileϑΝΠϧΛࣗಈੜ੒͠·͢ɻ Πϯετʔϧ·ͰͷྲྀΕ 1. configureͰMakefileͷ࡞੒ 2. makeίϚϯυͰίϯύΠϧ 3. make installίϚϯυͰΠϯετʔϧ
24. ιʔείʔυ͔ΒΠϯετʔϧ(2/4) ஫ҙࣄ߲ ͜ͷεϥΠυͰղઆ͍ͯ͠Δํ๏͸ɺ ίϯύΠϧ͓ΑͼΠϯετʔϧͷྫͱͳΓ·͢ɻ postgresϢʔβʔͷ࡞੒౳ͷղઆ͸ߦ͍·ͤΜɻ ύοέʔδͱಉ͡ঢ়ଶʹ͢Δ৔߹͸ɺύοέʔδੜ੒Λߦͳ͍ͬͯΔ εΫϦϓτ΍ύονϑΝΠϧΛ֬ೝ͢Δඞཁ͕͋Γ·͢ɻ RPM https://git.postgresql.org/gitweb/?p=pgrpms.git;a=tree;f=rpm/ redhat/11/postgresql/EL-7 DEB https://salsa.debian.org/postgresql/postgresql/tree/11/debian
25. ιʔείʔυ͔ΒΠϯετʔϧ(3/4) "./configure"ͷύϥϝʔλʔͰ༷ʑͳػೳ͕༗ޮʹͰ͖·͢ɻ ͜͜Ͱ͸ɺ֤ύοέʔδͷύϥϝʔλʔΛҰ෦ྻڍ͠·͢ɻ (ਖ਼֬ͳ஋͸ɺpg_configίϚϯυͰ֬ೝͰ͖·͢ɻ) ҙຯ RPM DEB Mac Windows --prefix Πϯετʔϧύε /usr/pgsql-11/ /usr/ /Library/PostgreSQL/11/ --enable-debug σόοάͷ༗ޮԽ " " --enable-dtrace DTraceͷ༗ޮԽ " " --enable-integer-datetimes 64bit ੔਺ܕ೔෇ͷ༗ޮԽ " --enable-nls ֤ࠃޠαϙʔτͷ༗ޮԽ " " " --enable-tap-tests TAPςετͷ༗ޮԽ " --enable-thread-safety εϨουηʔϑͷ༗ޮԽ " " " --with-bonjour BonjourೝূαϙʔτΛ༗ޮԽ " --with-gnu-ld GNU linkerͷ༗ޮԽ "
26. ιʔείʔυ͔ΒΠϯετʔϧ(4/4) આ໌ RPM DEB Mac Windows --with-gssapi Kerberosೝূαϙʔτͷ༗ޮԽ " " " --with-icu ICU(International Components for Unicode)α ϙʔτͷ༗ޮԽ " " " " --with-ldap LDAPೝূͷ༗ޮԽ " " " " --with-libedit-preferred Editline Libraryͷ༗ޮԽ " --with-libxml SQL/XMLαϙʔτͷ༗ޮԽ " " " " --with-libxslt xml2Ϟδϡʔϧαϙʔτͷ༗ޮԽ " " " " --with-llvm JITαϙʔτͷ༗ޮԽ " " " --with-openssl SSL઀ଓαϙʔτͷ༗ޮԽ " " " " --with-pam ϓϥΨϒϧೝূϞδϡʔϧαϙʔτͷ༗ޮԽ " " " --with-perl PL/PerlαʔόαΠυݴޠαϙʔτͷ༗ޮԽ " " " " --with-python PL/PythonαʔόαΠυݴޠαϙʔτͷ༗ޮԽ " " " " --with-selinux SELinuxαϙʔτͷ༗ޮԽ " " --with-systemd systemdαʔϏε௨஌αϙʔτͷ༗ޮԽ " --with-tcl PL/TclαʔόαΠυݴޠαϙʔτͷ༗ޮԽ " " " " --with-uuid uuid-osspϞδϡʔϧαϙʔτͷ༗ޮԽ e2fs e2fs e2fs ossp
27. ιʔείʔυ͔ΒΠϯετʔϧ (RedHat)(1/2) ίϯύΠϧʹඞཁͳύοέʔδΛಋೖ͠ɺίϯύΠϧɾΠϯ ετʔϧΛߦ͍·͢ɻ # cd /opt/ # wget https://ftp.postgresql.org/pub/source/v11.1/postgresql-11.1.tar.gz # yum install -y centos-release-scl epel-release # yum install -y gcc make perl perl-ExtUtils-Embed bison flex zlib-devel krb5-devel openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel libuuid-devel libicu-devel gettext-devel tcl-devel python- devel clang llvm5.0 llvm5.0-libs llvm5.0-devel llvm-toolset-7-clang-devel systemd-devel # export PATH=/opt/rh/devtoolset-7/root/usr/bin:$PATH # tar xvf postgresql-11.1.tar.gz # cd postgresql-11.1/ # ./configure <configureύϥϝʔλʔ> # make world # make world install
28. ιʔείʔυ͔ΒΠϯετʔϧ (RedHat)(2/2) '--enable-rpath' '--prefix=/usr/pgsql-11' '--includedir=/usr/pgsql-11/include' '--mandir=/ usr/pgsql-11/share/man' '--datadir=/usr/pgsql-11/share' '--with-icu' 'CLANG=/opt/rh/ llvm-toolset-7/root/usr/bin/clang' 'LLVM_CONFIG=/usr/lib64/llvm5.0/bin/llvm-config' '--with-llvm' '--with-perl' '--with-python' '--with-tcl' '--with-tclconfig=/usr/lib64' '--with- openssl' '--with-pam' '--with-gssapi' '--with-includes=/usr/include' '--with-libraries=/usr/ lib64' '--enable-nls' '--enable-dtrace' '--with-uuid=e2fs' '--with-libxml' '--with-libxslt' '-- with-ldap' '--with-selinux' '--with-systemd' '--with-system-tzdata=/usr/share/zoneinfo' '--sysconfdir=/etc/sysconfig/pgsql' '--docdir=/usr/pgsql-11/doc' '--htmldir=/usr/ pgsql-11/doc/html' 'CFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 - fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc- switches -m64 -mtune=generic' 'LDFLAGS=-Wl,--as-needed' 'PKG_CONFIG_PATH=:/usr/lib64/pkgconfig:/usr/share/pkgconfig' configureύϥϝʔλʔ͸ҎԼͷ௨ΓͰ͢ɻ
29. ιʔείʔυ͔ΒΠϯετʔϧ (Debian)(1/2) PostgreSQLެࣜαΠτͷϨϙδτϦ͔Β
 ΠϯετʔϧΛߦ͍·͢ɻ $ sudo wget https://ftp.postgresql.org/pub/source/v11.1/ postgresql-11.1.tar.gz $ sudo apt install -y systemtap-sdt-dev clang-6.0 libicu-dev libreadline-dev libkrb5-dev libssl-dev bison flex libpam0g-dev libxslt1-dev libldap2-dev libselinux1-dev uuid-dev libsystemd- dev tclcl-dev tcl-dev libperl-dev libpython-dev $ sudo tar xvf postgresql-11.1.tar.gz $ cd postgresql-11.1/ $ sudo ./configure <configureύϥϝʔλʔ> $ sudo make world $ sudo make world install
30. ιʔείʔυ͔ΒΠϯετʔϧ (Debian)(2/2) '--build=x86_64-linux-gnu' '--prefix=/usr' '--includedir=/usr/include' '--mandir=/usr/share/man' '--infodir=/usr/ share/info' '--sysconfdir=/etc' '--localstatedir=/var' '--disable-silent-rules' '--libdir=/usr/lib/x86_64-linux-gnu' '-- libexecdir=/usr/lib/x86_64-linux-gnu' '--disable-maintainer-mode' '--disable-dependency-tracking' '--with-icu' '--with-tcl' '--with-perl' '--with-python' '--with-pam' '--with-openssl' '--with-libxml' '--with-libxslt' '--with-tclconfig=/ usr/lib/x86_64-linux-gnu/tcl8.6' '--with-includes=/usr/include/tcl8.6' 'PYTHON=/usr/bin/python' '--mandir=/usr/ share/postgresql/11/man' '--docdir=/usr/share/doc/postgresql-doc-11' '--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' '--datadir=/usr/share/postgresql/11' '--bindir=/usr/lib/postgresql/11/bin' '--libdir=/usr/ lib/x86_64-linux-gnu/' '--libexecdir=/usr/lib/postgresql/' '--includedir=/usr/include/postgresql/' '--with-extra- version= (Ubuntu 11.0-1.pgdg18.04+2)' '--enable-nls' '--enable-integer-datetimes' '--enable-thread-safety' '-- enable-tap-tests' '--enable-debug' '--enable-dtrace' '--disable-rpath' '--with-uuid=e2fs' '--with-gnu-ld' '--with- pgport=5432' '--with-system-tzdata=/usr/share/zoneinfo' '--with-llvm' '--with-systemd' '--with-selinux' 'MKDIR_P=/bin/mkdir -p' 'TAR=/bin/tar' 'CFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format- security -fno-omit-frame-pointer' 'LDFLAGS=-Wl,-Bsymbolic-functions -Wl,-z,relro -Wl,-z,now' '--with-gssapi' '--with-ldap' '--with-includes=/usr/include/mit-krb5' '--with-libs=/usr/lib/mit-krb5' '--with-libs=/usr/lib/x86_64- linux-gnu/mit-krb5' 'build_alias=x86_64-linux-gnu' 'CPPFLAGS=-Wdate-time -D_FORTIFY_SOURCE=2' 'CXXFLAGS=-g -O2 -fstack-protector-strong -Wformat -Werror=format-security' configureύϥϝʔλʔ͸ҎԼͷ௨ΓͰ͢ɻ
31. ιʔείʔυ͔ΒΠϯετʔϧ (Windows)(1/2) 1. ίϯύΠϧʹඞཁͳιϑτΛΠϯετʔϧ͠·͢ɻ Visual C++ 2015 Build Tools(Windows SDK΋ಉ࣌ʹΠϯετʔϧ)
 (Visual Studio 2017 CommunityͰΠϯετʔϧ) StrawberryPerl tar.gzΛղౚͰ͖Διϑτ(7-zip΍ExplzhͳͲ) 2. ιʔείʔυΛμ΢ϯϩʔυɾղౚ͠·͢ɻ 3. ղౚͨ͠ϑΥϧμʔͷsrc\tools\msvcʹ͋Δconfig_default.plΛ
 ίϐʔͯ͠config.plʹϦωʔϜɺઃఆมߋΛߦ͍·͢ɻ
32. ιʔείʔυ͔ΒΠϯετʔϧ (Windows)(2/2) 4. VS 2017༻ x64 Native Tools ίϚϯυϓϩϯϓτ
 Λىಈ͠ɺPerlͷϞδϡʔϧಡΈࠐΈઌΛ௥Ճ͠·͢ɻ
 set PERL5LIB=<ղౚϑΥϧμʔ>\src\tools\msvc\
 (Perl 5.26ҎԼͷ৔߹͸ɺPERL5LIBͷઃఆ͸ෆཁͰ͢ɻ) 5. <ղౚϑΥϧμʔ>\src\tools\msvc\build.batΛ࣮ߦ͠·͢ɻ 6. <ղౚϑΥϧμʔ>\src\tools\msvc\install.bat <Πϯετʔϧઌ>Λ
 ࣮ߦ͠·͢ɻ
33. ίϯϑΟάઃఆ(͸͡Ίʹ)(1/4) Πϯετʔϧ௚ޙͷॳظίϯϑΟά͸ɺ
 େ·͔ʹ2ͭʹ෼͚Δ͜ͱ͕ग़དྷ·͢ɻ σʔλϕʔεઃఆϑΝΠϧ postgresql.conf postgresql.auto.conf ೝূ༻ઃఆϑΝΠϧ pg_hba.conf pg_ident.conf
34. ίϯϑΟάઃఆ(͸͡Ίʹ)(2/4) σʔλϕʔεઃఆϑΝΠϧͷ2ͭͷϑΝΠϧ͸ɺ ͸͡Ίʹpostgresql.conf͕ಡΈࠐ·Εͨޙʹ postgresql.auto.conf͕ಡΈࠐ·Ε·͢ɻ postgresql.auto.conf͸ɺϑΝΠϧࣗମΛ௚઀৮Δ͜ͱ͸ग़དྷ·ͤΜɻ "ALTER SYSTEM"Ͱมߋͨ͠৔߹ʹॻ͖ࠐΈ͞Ε·͢ɻ ྫ) ALTER SYSTEM SET wal_level = hot_standby;
35. ίϯϑΟάઃఆ(͸͡Ίʹ)(3/4) ೝূ༻ઃఆϑΝΠϧ͸ɺ௚઀ฤूΛߦ͍·͢ɻ pg_hba.confʹ͸جຊతͳઃఆΛߦ͍ɺ pg_ident.confʹ͸identೝূ౳ͷઃఆΛߦ͍·͢ɻ ίϯϑΟάϑΝΠϧ͸ɺߦ಄͕"#"ͷ৔߹͸ίϝϯτͱͯ͠ѻ͍·͢ɻ
36. ίϯϑΟάઃఆ(͸͡Ίʹ)(4/4) postgresql.confͷҰ෦ͷ ஋͸ɺPGTune ( https://pgtune.leopard.in.ua/ ) ɹ Ͱαʔόʔ؀ڥʹ߹Θͤ ͨࢀߟ஋Λੜ੒͢Δ͜ͱ ͕ग़དྷ·͢ɻ
37. ίϯϑΟάઃఆ(postgresql.conf)(1/3) ͜ͷεϥΠυͰ͸͓קΊͷมߋՕॴΛઆ໌͠·͢ɻ JIT͸σϑΥϧτͰແޮͱͳ͍ͬͯΔͨΊɺ࢖༻͢Δ৔߹͸ඞͣมߋ͕ඞཁͰ͢ɻ ύϥϝʔλʔ ॳظ஋ ਪ঑஋ ղઆ listen_addresses 'localhost' '*' ઀ଓΛड͚෇͚ΔIPΞυϨεΛࢦఆ͠·͢ɻ
 *͸શͯΛड͚෇͚·͢ɻ pg_hba.confͰৄࡉͳ઀ଓઃఆΛߦ͍·͢ɻ max_connections 100 PGTuneͷ஋ ಉ࣌઀ଓ͢Δ࠷େ਺Λࢦఆ͠·͢ɻ shared_buffers 32MB PGTuneͷ஋ ڞ༗ϝϞϦόοϑΝͷϝϞϦྔΛࢦఆ͠·͢ɻ temp_buffers 8MB ඞཁʹԠͯ͡ Ұ࣌ςʔϒϧͳͲʹ࢖༻͞ΕΔྖҬͰ͢ɻ work_mem 4MB PGTuneͷ஋ ϋογϡςʔϒϧૢ࡞࣌΍ฒͼସ͑ͳͲʹ
 ࢖༻͞ΕΔྖҬͰ͢ɻ maintenance_work_mem 64MB PGTuneͷ஋ VACUUMɺCREATE INDEXͳͲอक࡞ۀͰ
 ࢖༻͞ΕΔྖҬͰ͢ɻ effective_io_concurrency 1 ඞཁʹԠͯ͡ ಉ࣌σΟεΫI/Oૢ࡞ͷ਺Λࢦఆ͠·͢ɻ υϥΠϒͷ਺Λࢦఆ͢Δͱɺߴ଎Խ͢Δ
 Մೳੑ͕͋Γ·͢ɻ
38. ίϯϑΟάઃఆ(postgresql.conf)(2/3) ύϥϝʔλʔ ॳظ஋ ਪ঑஋ ղઆ max_worker_processes 8 PGTuneͷ஋ όοΫάϥ΢ϯυϓϩηεͷ࠷େ਺Λࢦఆ͠·͢ɻ max_parallel_workers_per_gather 2 PGTuneͷ஋ ύϥϨϧΫΤϦಈ࡞࣌ͷ࠷େϫʔΧʔ਺Λࢦఆ͠·͢ɻ max_parallel_workers 8 PGTuneͷ஋ γεςϜશମͷύϥϨϧΫΤϦͷ࠷େϫʔΧʔ਺Λ
 ࢦఆ͠·͢ɻ wal_buffers -1 PGTuneͷ஋ ڞ༗ϝϞϦ಺ʹ֬อ͞ΕΔɺWALσʔλ༻ϝϞϦྔΛ
 ࢦఆ͠·͢ɻ checkpoint_timeout 5min ඞཁʹԠͯ͡ νΣοΫϙΠϯτͷִؒΛඵ୯ҐͰࢦఆ͠·͢ɻ ୹͗͢ΔͱେྔͷI/O͕ൃੜ͠·͢ɻ30ʙ60min͕
 ྑ͍ͱݴΘΕ͍ͯ·͢ɻ max_wal_size 1GB PGTuneͷ஋ WALϑΝΠϧͷ߹ܭαΠζͷ্ݶ஋Λࢦఆ͠·͢ɻ min_wal_size 80MB PGTuneͷ஋ WALϑΝΠϧͷ߹ܭαΠζͷԼݶ஋Λࢦఆ͠·͢ɻ checkpoint_completion_target 0.5 PGTuneͷ஋ νΣοΫϙΠϯτͷ׬ྃ໨ඪΛɺ νΣοΫϙΠϯτؒͷ૯࣌ؒͷׂ߹ͱͯ͠ࢦఆ͠·͢ɻ effective_cache_size 4GB PGTuneͷ஋ σΟεΫΩϟογϡʹͲΕ͚ͩϝϞϦΛ࢖༻Ͱ͖Δ͔
 ࢦఆ͠·͢ɻ ࣮ࡍʹϝϞϦ͸࢖༻͞Ε·ͤΜɻ log_directory 'log' ඞཁʹԠͯ͡ ϩάϑΝΠϧ͕࡞੒͞ΕΔσΟϨΫτϦΛࢦఆ͠·͢ɻ
39. ίϯϑΟάઃఆ(postgresql.conf)(3/3) ύϥϝʔλʔ ॳظ஋ ਪ঑஋ ղઆ log_filename 'postgresql-%a.log' ඞཁʹԠͯ͡ ϩάϑΝΠϧͷϑΝΠϧ໊Λࢦఆ͠·͢ɻ ྫ) 'postgresql%Y%m%d.log' log_line_prefix '%m [%p] ' ඞཁʹԠͯ͡ ϩάͷߦ಄ϝοηʔδΛࢦఆ͠·͢ɻ ྫ) '[%m] [%p] [%u] [%d] [%h] ' log_temp_files -1 ඞཁʹԠͯ͡ Ұ࣌ϑΝΠϧͷ࠷େϑΝΠϧαΠζΛࢦఆ͠·͢ɻ Ұ࣌ϑΝΠϧ͸ιʔτॲཧ΍ϋογϡॲཧͳͲͰ
 ੜ੒͞Ε·͢ɻ log_timezone 'GMT' 'Japan' αʔόϩάͷλΠϜκʔϯΛࢦఆ͠·͢ɻ timezone 'GMT' 'Japan' ग़ྗ΍λΠϜελϯϓղऍͷͨΊͷλΠϜκʔϯΛࢦఆ͠ ·͢ɻ lc_messages 'C' 'ja_JP.UTF-8' ग़ྗ͞ΕΔϝοηʔδͷݴޠΛࢦఆ͠·͢ɻ lc_monetary 'C' 'ja_JP.UTF-8' ௨՟ॻࣜͷϩέʔϧΛࢦఆ͠·͢ɻ lc_numeric 'C' 'ja_JP.UTF-8' ਺஋ॻࣜͷϩέʔϧΛࢦఆ͠·͢ɻ lc_time 'C' 'ja_JP.UTF-8' ೔෇࣌ؒॻࣜͷϩέʔϧΛࢦఆ͠·͢ɻ jit off ඞཁʹԠͯ͡ JITͷ੍ޚΛߦ͍·͢ɻonͰJIT͕༗ޮʹͳΓ·͢ɻ
40. ίϯϑΟάઃఆ(pg_hba.conf)(1/7) ͜ͷεϥΠυͰ͸pg_hba.confͷॻࣜٴͼࢀߟྫΛઆ໌͠·͢ɻ ্͔Βॱ൪ʹઃఆΛಡΈऔΓɺ઀ଓΛࢼΈ·͢ɻ ॻࣜ
 ࠨ͔Βॱʹεϖʔε۠੾ΓͰྻڍ͠·͢ɻ # TYPE DATABASE USER ⏎ ADDRESS METHOD OPTIONS # ઃఆλΠϓ σʔλϕʔε໊ Ϣʔβʔ໊ ⏎ IPΞυϨε/CIDR ೝূํࣜ Φϓγϣϯ
41. ίϯϑΟάઃఆ(pg_hba.conf)(2/7) ֤ϑΟʔϧυͷҙຯ TYPE ઃఆͷλΠϓΛࢦఆ͠·͢ɻࢦఆͰ͖Δ஋͸ҎԼͷ௨ΓͰ͢ɻ local, host, hostssl, hostnossl DATABASE ର৅ͱͳΔσʔλϕʔεΛจࣈྻͰࢦఆ͠·͢ɻ "all"͸͢΂ͯͷσʔλϕʔε͕ର৅ͱͳΓ·͢ɻ USER ର৅ͱͳΔϢʔβʔΛจࣈྻͰࢦఆ͠·͢ɻ "all"͸͢΂ͯͷϢʔβʔ͕ର৅ͱͳΓ·͢ɻ ADDRESS ର৅ͱͳΔIPΞυϨεΛࢦఆ͠·͢ɻ IPΞυϨεͷޙΖʹɺ"/"ͱCIDRΛࢦఆ͠·͢ɻ METHOD ೝূํࣜΛࢦఆ͠·͢ɻࢦఆͰ͖Δ஋͸ҎԼͷ௨ΓͰ͢ɻ trust, reject, scram-sha-256, md5, password, gss, sspi, ident, peer, ldap, radius, cert, pam, bsd OPTIONS ೝূํࣜʹΑͬͯΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ (ྫ: identೝূͷ৔߹͸ɺmapΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ)
42. ίϯϑΟάઃఆ(pg_hba.conf)(3/7) ઃఆλΠϓͷछྨ local UnixυϝΠϯιέοτΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ host ҉߸Խɾඇ҉߸Խڞ௨ͷɺ TCP/IPΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ hostssl SSL҉߸Խ͞Ε͍ͯΔɺ TCP/IPΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ hostnossl SSL҉߸Խ͞Ε͍ͯͳ͍ɺ TCP/IPΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ
43. ίϯϑΟάઃఆ(pg_hba.conf)(4/7) ೝূํࣜͷछྨ trust ύεϫʔυͳͲͷೝূͳ͠Ͱɺ ઀ଓΛແ৚݅ʹڐՄ͠·͢ɻ reject ઀ଓΛະ৚݅ʹڋ൱͠·͢ɻ scram-sha-256 SASLೝূ(SCRAM-SHA-256)ͰೝূΛߦ͍·͢ɻ (ৄࡉ͸RFC 7677͓ΑͼRFC 5802Λࢀরɻ) md5 ύεϫʔυΛMD5ͰϋογϡԽͯ͠ೝূΛߦ͍·͢ɻ password ύεϫʔυΛฏจͷ··ͰೝূΛߦ͍·͢ɻ SSL઀ଓͷ৔߹͸ɺ҆શʹ࢖༻͢Δ͜ͱ͕Ͱ͖·͢ɻ ※ ύεϫʔυ͸ɺΦϖϨʔςΟϯάγεςϜͷϢʔβʔύεϫʔυͱҟͳΓ·͢ɻ
 pg_authidγεςϜΧλϩάʹొ࿥͞Ε͍ͯ·͢ɻ ※ scram-sha-256Λ࢖༻͢Δ৔߹͸ɺ͢΂ͯͷΫϥΠΞϯτγεςϜ͕SASLೝূʹରԠ͍ͯ͠Δඞཁ͕͋Γ·͢ɻ
 ·ͨɺmd5ೝূ͔Βมߋ͢Δ৔߹͸ɺύεϫʔυͷมߋ͕ඞཁͱͳΓ·͢ɻ
44. ίϯϑΟάઃఆ(pg_hba.conf)(5/7) gss GSSAPIೝূ(Kerberosೝূ)ͰೝূΛߦ͍·͢ɻ ผ్ɺpostgresql.confͷkrb_server_keyfile
 ύϥϝʔλʔͷઃఆ͕ඞཁͱͳΓ·͢ɻ ·ͨɺpg_hba.confʹ΋Φϓγϣϯ͕ඞཁͱͳΓ·͢ɻ sspi SSPIೝূ(Kerberosೝূ·ͨ͸NTMLೝূ)Ͱ ೝূΛߦ͍·͢ɻ pg_hba.confʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ ident ΫϥΠΞϯτͷϢʔβʔ໊Λɺ identαʔόʔʹ໰͍߹ΘͤͯೝূΛߦ͍·͢ɻ ઃఆλΠϓ͕localͷ৔߹͸ɺpeerೝূͱͳΓ·͢ɻ pg_hba.confʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ
45. ίϯϑΟάઃఆ(pg_hba.conf)(6/7) peer αʔόʔͷϢʔβʔ໊Λऔಘ͠ɺσʔλϕʔεͷ
 Ϣʔβʔ໊ͱͯ͠࢖༻͠·͢ɻ ઃఆλΠϓ͕localͷΈͰ࢖༻ՄೳͰ͢ɻ pg_hba.confʹΦϓγϣϯΛઃఆ͢Δ͜ͱ͕ՄೳͰ͢ɻ ldap LDAPೝূͰೝূΛߦ͍·͢ɻ LDAPαʔόʔ΁͸ɺϢʔβʔ໊ͱύεϫʔυͷ
 ૊Έ߹ΘͤͷݕূͷΈߦ͍·͢ɻ pg_hba.confʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ radius RADIUSೝূͰೝূΛߦ͍·͢ɻ LDAPೝূͱͷҧ͍͸ɺೝূํࣜͷҧ͍ͷΈͱ
 ͳΓ·͢ɻ
46. ίϯϑΟάઃఆ(pg_hba.conf)(7/7) cert SSLূ໌ॻʹΑΔೝূΛߦ͍·͢ɻ αʔόʔ͓ΑͼΫϥΠΞϯτͰൿີ伴ͱূ໌ॻΛ
 ࡞੒ͯ͠ަ׵͢Δඞཁ͕͋Γ·͢ɻ ผ్ɺpostgresql.confͷઃఆ͕ඞཁͱͳΓ·͢ɻ pg_hba.confʹΦϓγϣϯΛઃఆ͢Δ͜ͱ͕ՄೳͰ͢ɻ pam PAM(Pluggable Authentication Modules)ʹΑΔೝূΛ
 ߦ͍·͢ɻ pg_hba.confʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ bsd BSDೝূͰೝূΛߦ͍·͢ɻ OpenBSDͰͷΈ࢖༻͕ՄೳͰ͢ɻ Ϣʔβʔ໊ͱύεϫʔυͷ૊Έ߹ΘͤͷݕূͷΈ
 ߦ͍·͢ɻ
47. ίϯϑΟάઃఆ(pg_ident.conf)(1/2) ͜ͷεϥΠυͰ͸pg_ident.confͷॻࣜٴͼࢀߟྫΛઆ໌͠·͢ɻ ઃఆ͸্͔Βॱ൪ʹνΣοΫ͞Εɺ߹கͨ͠ՕॴͰ࣍ͷॲཧ(ೝূ)ͱͳΓ ·͢ɻ ॻࣜ
 ࠨ͔Βॱʹεϖʔε۠੾ΓͰྻڍ͠·͢ɻ # MAPNAME SYSTEM-USERNAME PG-USERNAME # map໊ Ϣʔβʔ໊(γεςϜ) Ϣʔβʔ໊(PostgreSQL)
48. ίϯϑΟάઃఆ(pg_ident.conf)(2/2) ֤ϑΟʔϧυͷҙຯ MAPNAME pg_hba.confͰࢦఆͨ͠map໊Λࢦఆ͠·͢ɻ ର৅ೝূํࣜ: gss, sspi, ident, peer, cert SYSTEM-USERNAME ΫϥΠΞϯτͷϢʔβʔ໊ PG-USERNAME PostgreSQLͷϢʔβʔ໊
49. ίϯϑΟάઃఆ(ೝূαϯϓϧ)(1/3) ཁ݅ - 192.168.2.0/24 ͔Βtest1σʔλϕʔεʹରͯ͠
 ͢΂ͯͷϢʔβʔͰmd5ೝূΛߦ͏ɻ - 192.168.3.0/24͔Β͸͢΂ͯͷσʔλϕʔεʹରͯ͠
 testϢʔβʔͰidentೝূΛߦ͏ɻmapΦϓγϣϯ͸"test1"ͱ͢Δɻ - ΫϥΠΞϯτϢʔβʔ໊͸"foo"ɺ
 PostgreSQLϢʔβʔ໊͸"user1"ͱ͢Δɻ - 192.168.2.8/30(192.168.2.9ʙ192.168.2.10)ͷ઀ଓ͸ڐՄ͠ͳ͍ɻ
50. 192.168.2.2 192.168.3.3 ೝূαʔόʔ 192.168.2.3 192.168.2.5 192.168.2.9 192.168.2.10 192.168.3.5 192.168.3.10 PostgreSQL ίϯϑΟάઃఆ(ೝূαϯϓϧ)(2/3) ωοτϫʔΫਤ શϢʔβʔɾ"test"DB΁ͷ ΞΫηεڐՄ(md5) 192.168.2.8/30͔Βͷ ΞΫηε٫Լ "test"ϢʔβʔɾશDB΁ͷ ΞΫηεڐՄ(ident)
51. ίϯϑΟάઃఆ(ೝূαϯϓϧ)(3/3) ઃఆϑΝΠϧ - pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD OPTIONS local all all peer host all all 127.0.0.1/32 ident host test1 all 192.168.2.8/30 reject host test1 all 192.168.2.0/24 md5 host all all 192.168.3.0/24 ident map=test1 - pg_ident.conf # MAPNAME SYSTEM-USERNAME PG-USERNAME test1 foo user1
52. ऴΘΓʹ ͜ͷηογϣϯͰ͸Πϯετʔϧํ๏΍ઃఆํ๏ʹ͍ͭͯ ղઆΛߦ͍·ͨ͠ɻ ͜͜Ͱઆ໌ͨ͠ઃఆ஋͸ɺ͋͘·Ͱࢀߟ஋ͱͳΓ·͢ɻ ࣮ࡍͷӡ༻Ͱ͸αʔόʔߏ੒΍౤ೖ͞ΕΔσʔλྔɺ ൃߦ͞ΕΔSQLʹ߹Θͤͯɺσʔλϕʔεͷ νϡʔχϯά౳͕ඞཁͱͳΓ·͢ɻ ͜·ΊʹPostgreSQLͷঢ়ଶΛ֬ೝ͠ɺ ҆ఆՔಇΛ໨ࢦ͍ͯͩ͘͠͞ɻ
53. ྑ͍PostgreSQLϥΠΫΛʂ ͝੩ௌ͋Γ͕ͱ͏͍͟͝·ͨ͠ɻ