PostgreSQLデプロイの基礎

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

Transcript

1. PostgreSQL σϓϩΠͷجૅ
   PostgreSQLΧϯϑΝϨϯε2018
   

   View Slide

2. ͸͡Ίʹ
   ຊηογϣϯͰ͸ɺLinux(RedHatܥɺDebianܥ)ɾMacɾ
   Windowsʹରͯ͠ɺPostgreSQLͷΠϯετʔϧͱ

   ॳظηοτΞοϓʹ͍ͭͯղઆ͠·͢ɻ
   ύοέʔδͰͷΠϯετʔϧͱɺιʔείʔυ͔Β

   Πϯετʔϧ͢Δ2ύλʔϯΛղઆ͠·͢ɻ
   ॳظઃఆ΍ύϥʔϝʔλʔ౳Ͱ

   ஫ҙ͢΂͖఺ʹ͍ͭͯ΋ղઆ͠·͢ɻ
   

   View Slide

3. ΞδΣϯμ
   ࣗݾ঺հ
   ύοέʔδͰͷΠϯετʔϧ
   ιʔείʔυ͔ΒͷΠϯετʔϧ
   ίϯϑΟάઃఆ
   ऴΘΓʹ
   

   View Slide

4. ࣗݾ঺հ
   ໊લ: ࣉ಺ େً(ͯΒ͏ͪ ͍͖ͨ)

   ॴଐ: ೔ຊPostgreSQLϢʔβձ

   ؔ੢ࢧ෦௕

   Ұݴ: ࢓ࣄͰPostgreSQLΛ࢖༻ͯ͠ɺ

   ૉ௚ʹSQL͕ಈ͍ͯ͘ΕΔͱ͜Ζʹ

   ऒ͔ΕͯϙεάϨʹ͸·Γ·ͨ͠ɻ
   

   View Slide

5. ղઆ؀ڥ
   OS
   - Linux(RedHat) = CentOS 7
   - Linux(Debian) = Ubuntu 18.04
   - Mac = macOS Mojave
   - Windows = Windosw 10 Pro
   PostgreSQL
   - PostgreSQL 11
   

   View Slide

6. PostgreSQLͷσʔλϕʔεߏ଄
   postgresϓϩηε
   σʔλϕʔεΫϥελ
   ઃఆϑΝΠϧ
   ɾpostgresql.conf
   ɾpg_hba.conf
   ͳͲ
   σʔλϕʔε
   εΩʔϚ
   Ϣʔβʔ
   ɾpostgres
   ͳͲ
   ςʔϒϧ
   ͳͲ
   ͜ͷηογϣϯͰ͸ɺPostgreSQLΠϯετʔϧͱ

   σʔλϕʔεΫϥελͷ࡞੒ɺઃఆϑΝΠϧͷઃఆʹ͍ͭͯղઆ͠·͢ɻ
   

   View Slide

7. σʔλϕʔεΫϥελʹ͍ͭͯ(1/2)
   PostgreSQLͷϓϩηε͸1ͭͷσʔλϕʔεΫϥελΛ࣋ͪɺ

   ͦͷதʹෳ਺ͷσʔλϕʔεΛ֨ೲ͠·͢ɻ
   σʔλϕʔεΫϥελ͸ɺϑΥϧμʔͱͳ͍ͬͯ·͢ɻ
   ϑΥϧμʔ಺ʹઃఆϑΝΠϧ΍σʔλϕʔεͷ࣮ମ͕֨ೲ͞Ε·͢ɻ
   σʔλϕʔεΫϥελΛ࡞੒͢ΔίϚϯυ͸initdbͰ͢ɻ
   Πϯετʔϧखॱʹͯ࢖༻͠·͢ɻ
   initdbίϚϯυ͸ύϥϝʔλʔͰઃఆΛม͑Δ͜ͱ͕Ͱ͖·͕͢ɺ
   ͜͜Ͱ͸3ͭͷύϥϝʔλʹ͍ͭͯɺ࣍ͷεϥΠυͰ঺հ͠·͢ɻ
   

   View Slide

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
   

   View Slide

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"
   );
   

   View Slide

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
    

    View Slide

11. ύοέʔδͰͷΠϯετʔϧ
    Linux(RedHatܥ) = RPM
    Linux(Debianܥ) = DEB
    MacɾWindows = Πϯετʔϥʔ(EnterpriseDBࣾ੡)
    Ϋϥ΢υ = αʔϏεʹΑΓఏڙ͞Ε͍ͯ·͢ɻ

    (ผ్ΠϯετʔϧΛߦ͏৔߹͸ɺ

    ద߹͢ΔOSͷΠϯετʔϧํ๏Λ͓ࢼ͍ͩ͘͠͞ɻ)
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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 ߦ)
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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 ߦ)
    

    View Slide

18. ΠϯετʔϥʔͰͷΠϯετʔϧ(1/5)
    EnterpriseDBࣾύοέʔδ͔ΒΠϯετʔϧΛߦ͍·͢ɻ

    https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
    

    View Slide

19. ΠϯετʔϥʔͰͷΠϯετʔϧ(2/5)
    ్தͰΠϯετʔϧ಺༰ͷબ୒ࢶ͕දࣔ͞Ε·͢ɻ
    PostgreSQL Server

    PostgreSQLຊମͰ͢ɻ
    pgAdmin4

    GUIͷ؅ཧπʔϧͰ͢ɻ
    Stack Builder

    ؔ࿈πʔϧͳͲΛಋೖɾ؅ཧΛ

    αϙʔτ͢ΔπʔϧͰ͢ɻ
    Command LineTools

    PostgreSQLΛίϚϯυϥΠϯ͔Β

    ૢ࡞͢ΔͨΊͷπʔϧͰ͢ɻ
    

    View Slide

20. ΠϯετʔϥʔͰͷΠϯετʔϧ(3/5)
    ్தͰΠϯετʔϧઌɺσʔλϕʔεΫϥελͷอଘઌɺ
    ϙʔτ൪߸ɺύεϫʔυɺϩέʔϧઃఆ͕ೖΓ·͢ɻ
    ΠϯετʔϧઌɺσʔλϕʔεΫϥελͷอଘઌɺϙʔτ൪߸͸σϑΥϧ
    τͱ͠·͢ɻ
    

    View Slide

21. ΠϯετʔϥʔͰͷΠϯετʔϧ(4/5)
    ίϯϑΟάϑΝΠϧ͸Πϯετʔϧ࣌ʹࢦఆͨ͠ɺ

    σʔλϕʔεΫϥελͷอଘઌʹଘࡏ͠·͢ɻ

    ίϯϑΟάઃఆʹ͍ͭͯ͸ผͰઆ໌Λߦ͍·͢ɻ
    # cd /etc/postgresql/11/main/
    # ls *.conf
    pg_hba.conf pg_ident.conf
    postgresql.auto.conf postgresql.conf
    

    View Slide

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)
    

    View Slide

23. ιʔείʔυ͔ΒΠϯετʔϧ(1/4)
    PostgreSQL͸GNU makeʹΑΓίϯύΠϧɺΠϯετʔϧΛߦ͍·͢ɻ
    makeίϚϯυΛ࣮ߦ͢ΔͨΊʹɺMakefileϑΝΠϧΛඞཁͱ͠·͢ɻ
    MakefileϑΝΠϧ͸ɺରԠΦϓγϣϯ΍؀ڥʹґଘ͢Δઃఆ͕ඞཁͱͳΔ
    ͨΊɺιʔείʔυʹ͸ؚ·Ε·ͤΜɻ
    ͦͷͨΊɺconfigureεΫϦϓτͰMakefileϑΝΠϧΛࣗಈੜ੒͠·͢ɻ
    Πϯετʔϧ·ͰͷྲྀΕ
    1. configureͰMakefileͷ࡞੒
    2. makeίϚϯυͰίϯύΠϧ
    3. make installίϚϯυͰΠϯετʔϧ
    

    View Slide

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
    

    View Slide

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ͷ༗ޮԽ "
    

    View Slide

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
    

    View Slide

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
    # make world
    # make world install
    

    View Slide

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ύϥϝʔλʔ͸ҎԼͷ௨ΓͰ͢ɻ
    

    View Slide

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
    $ sudo make world
    $ sudo make world install
    

    View Slide

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ύϥϝʔλʔ͸ҎԼͷ௨ΓͰ͢ɻ
    

    View Slide

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ʹϦωʔϜɺઃఆมߋΛߦ͍·͢ɻ
    

    View Slide

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 <Πϯετʔϧઌ>Λ

    ࣮ߦ͠·͢ɻ
    

    View Slide

33. ίϯϑΟάઃఆ(͸͡Ίʹ)(1/4)
    Πϯετʔϧ௚ޙͷॳظίϯϑΟά͸ɺ

    େ·͔ʹ2ͭʹ෼͚Δ͜ͱ͕ग़དྷ·͢ɻ
    σʔλϕʔεઃఆϑΝΠϧ
    postgresql.conf
    postgresql.auto.conf
    ೝূ༻ઃఆϑΝΠϧ
    pg_hba.conf
    pg_ident.conf
    

    View Slide

34. ίϯϑΟάઃఆ(͸͡Ίʹ)(2/4)
    σʔλϕʔεઃఆϑΝΠϧͷ2ͭͷϑΝΠϧ͸ɺ
    ͸͡Ίʹpostgresql.conf͕ಡΈࠐ·Εͨޙʹ
    postgresql.auto.conf͕ಡΈࠐ·Ε·͢ɻ
    postgresql.auto.conf͸ɺϑΝΠϧࣗମΛ௚઀৮Δ͜ͱ͸ग़དྷ·ͤΜɻ
    "ALTER SYSTEM"Ͱมߋͨ͠৔߹ʹॻ͖ࠐΈ͞Ε·͢ɻ
    ྫ) ALTER SYSTEM SET wal_level = hot_standby;
    

    View Slide

35. ίϯϑΟάઃఆ(͸͡Ίʹ)(3/4)
    ೝূ༻ઃఆϑΝΠϧ͸ɺ௚઀ฤूΛߦ͍·͢ɻ
    pg_hba.confʹ͸جຊతͳઃఆΛߦ͍ɺ
    pg_ident.confʹ͸identೝূ౳ͷઃఆΛߦ͍·͢ɻ
    ίϯϑΟάϑΝΠϧ͸ɺߦ಄͕"#"ͷ৔߹͸ίϝϯτͱͯ͠ѻ͍·͢ɻ
    

    View Slide

36. ίϯϑΟάઃఆ(͸͡Ίʹ)(4/4)
    postgresql.confͷҰ෦ͷ
    ஋͸ɺPGTune
    ( https://pgtune.leopard.in.ua/ )
    ɹ
    Ͱαʔόʔ؀ڥʹ߹Θͤ
    ͨࢀߟ஋Λੜ੒͢Δ͜ͱ
    ͕ग़དྷ·͢ɻ
    

    View Slide

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ૢ࡞ͷ਺Λࢦఆ͠·͢ɻ
    υϥΠϒͷ਺Λࢦఆ͢Δͱɺߴ଎Խ͢Δ

    Մೳੑ͕͋Γ·͢ɻ
    

    View Slide

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' ඞཁʹԠͯ͡ ϩάϑΝΠϧ͕࡞੒͞ΕΔσΟϨΫτϦΛࢦఆ͠·͢ɻ
    

    View Slide

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͕༗ޮʹͳΓ·͢ɻ
    

    View Slide

40. ίϯϑΟάઃఆ(pg_hba.conf)(1/7)
    ͜ͷεϥΠυͰ͸pg_hba.confͷॻࣜٴͼࢀߟྫΛઆ໌͠·͢ɻ
    ্͔Βॱ൪ʹઃఆΛಡΈऔΓɺ઀ଓΛࢼΈ·͢ɻ
    ॻࣜ

    ࠨ͔Βॱʹεϖʔε۠੾ΓͰྻڍ͠·͢ɻ
    # TYPE DATABASE USER ⏎
    ADDRESS METHOD OPTIONS
    # ઃఆλΠϓ σʔλϕʔε໊ Ϣʔβʔ໊ ⏎
    IPΞυϨε/CIDR ೝূํࣜ Φϓγϣϯ
    

    View Slide

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Φϓγϣϯ͕ඞཁͱͳΓ·͢ɻ)
    

    View Slide

42. ίϯϑΟάઃఆ(pg_hba.conf)(3/7)
    ઃఆλΠϓͷछྨ
    local UnixυϝΠϯιέοτΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ
    host
    ҉߸Խɾඇ҉߸Խڞ௨ͷɺ
    TCP/IPΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ
    hostssl
    SSL҉߸Խ͞Ε͍ͯΔɺ
    TCP/IPΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ
    hostnossl
    SSL҉߸Խ͞Ε͍ͯͳ͍ɺ
    TCP/IPΛ࢖༻ͯ͠઀ଓ͢Δ৔߹ͷઃఆ
    

    View Slide

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ೝূ͔Βมߋ͢Δ৔߹͸ɺύεϫʔυͷมߋ͕ඞཁͱͳΓ·͢ɻ
    

    View Slide

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ʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ
    

    View Slide

45. ίϯϑΟάઃఆ(pg_hba.conf)(6/7)
    peer
    αʔόʔͷϢʔβʔ໊Λऔಘ͠ɺσʔλϕʔεͷ

    Ϣʔβʔ໊ͱͯ͠࢖༻͠·͢ɻ
    ઃఆλΠϓ͕localͷΈͰ࢖༻ՄೳͰ͢ɻ
    pg_hba.confʹΦϓγϣϯΛઃఆ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    ldap
    LDAPೝূͰೝূΛߦ͍·͢ɻ
    LDAPαʔόʔ΁͸ɺϢʔβʔ໊ͱύεϫʔυͷ

    ૊Έ߹ΘͤͷݕূͷΈߦ͍·͢ɻ
    pg_hba.confʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ
    radius
    RADIUSೝূͰೝূΛߦ͍·͢ɻ
    LDAPೝূͱͷҧ͍͸ɺೝূํࣜͷҧ͍ͷΈͱ

    ͳΓ·͢ɻ
    

    View Slide

46. ίϯϑΟάઃఆ(pg_hba.conf)(7/7)
    cert
    SSLূ໌ॻʹΑΔೝূΛߦ͍·͢ɻ
    αʔόʔ͓ΑͼΫϥΠΞϯτͰൿີ伴ͱূ໌ॻΛ

    ࡞੒ͯ͠ަ׵͢Δඞཁ͕͋Γ·͢ɻ
    ผ్ɺpostgresql.confͷઃఆ͕ඞཁͱͳΓ·͢ɻ
    pg_hba.confʹΦϓγϣϯΛઃఆ͢Δ͜ͱ͕ՄೳͰ͢ɻ
    pam
    PAM(Pluggable Authentication Modules)ʹΑΔೝূΛ

    ߦ͍·͢ɻ
    pg_hba.confʹΦϓγϣϯ͕ඞཁͱͳΓ·͢ɻ
    bsd
    BSDೝূͰೝূΛߦ͍·͢ɻ
    OpenBSDͰͷΈ࢖༻͕ՄೳͰ͢ɻ
    Ϣʔβʔ໊ͱύεϫʔυͷ૊Έ߹ΘͤͷݕূͷΈ

    ߦ͍·͢ɻ
    

    View Slide

47. ίϯϑΟάઃఆ(pg_ident.conf)(1/2)
    ͜ͷεϥΠυͰ͸pg_ident.confͷॻࣜٴͼࢀߟྫΛઆ໌͠·͢ɻ
    ઃఆ͸্͔Βॱ൪ʹνΣοΫ͞Εɺ߹கͨ͠ՕॴͰ࣍ͷॲཧ(ೝূ)ͱͳΓ
    ·͢ɻ
    ॻࣜ

    ࠨ͔Βॱʹεϖʔε۠੾ΓͰྻڍ͠·͢ɻ
    # MAPNAME SYSTEM-USERNAME PG-USERNAME
    # map໊ Ϣʔβʔ໊(γεςϜ) Ϣʔβʔ໊(PostgreSQL)
    

    View Slide

48. ίϯϑΟάઃఆ(pg_ident.conf)(2/2)
    ֤ϑΟʔϧυͷҙຯ
    MAPNAME
    pg_hba.confͰࢦఆͨ͠map໊Λࢦఆ͠·͢ɻ
    ର৅ೝূํࣜ: gss, sspi, ident, peer, cert
    SYSTEM-USERNAME ΫϥΠΞϯτͷϢʔβʔ໊
    PG-USERNAME PostgreSQLͷϢʔβʔ໊
    

    View Slide

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)ͷ઀ଓ͸ڐՄ͠ͳ͍ɻ
    

    View Slide

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)
    

    View Slide

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
    

    View Slide

52. ऴΘΓʹ
    ͜ͷηογϣϯͰ͸Πϯετʔϧํ๏΍ઃఆํ๏ʹ͍ͭͯ
    ղઆΛߦ͍·ͨ͠ɻ
    ͜͜Ͱઆ໌ͨ͠ઃఆ஋͸ɺ͋͘·Ͱࢀߟ஋ͱͳΓ·͢ɻ
    ࣮ࡍͷӡ༻Ͱ͸αʔόʔߏ੒΍౤ೖ͞ΕΔσʔλྔɺ
    ൃߦ͞ΕΔSQLʹ߹Θͤͯɺσʔλϕʔεͷ
    νϡʔχϯά౳͕ඞཁͱͳΓ·͢ɻ
    ͜·ΊʹPostgreSQLͷঢ়ଶΛ֬ೝ͠ɺ
    ҆ఆՔಇΛ໨ࢦ͍ͯͩ͘͠͞ɻ
    

    View Slide

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

    View Slide