1.PostgreSQL-インストールと基本構成(学習ノート)

20803 ワード
Postgre SQL PostgreSQL
1 PostgreSQLの概要
1.1概要
 PostgreSQLデータベースは現在最も強力なオープンソースデータベースであり、JSONやJSOnbタイプ、配列タイプなどの豊富なデータ型とカスタムタイプをサポートしています.また、Gistフレームワークの下で自分のインデックスタイプを実現できるなど、豊富なインタフェースを提供し、簡単に機能を拡張することができます.また、C言語を使用してカスタム関数、トリガを書くこともサポートしています.流行の言語を使用してカスタム関数を書くこともサポートしています.例えば、その中のPL/Perl言語を使用してカスタム関数を書く機能を提供しています.もちろん、PL/Python、PL/TClもあります.などなど.
1.2 PostgreSQLのメリット
  • PostgreSQLデータベースは現在最も強力なオープンソースデータベースであり、工業標準SQL 92に最も近いクエリー言語であり、最新のSQL標準:SQL 2003と互換性のある新しい機能を実現している.
  • 安定で信頼性が高い:PostgreSQLは、データの損失をゼロにできる唯一のオープンソースデータベースです.海外の一部の銀行でもPostgreSQLデータベースが使われていると報道されています.
  • オープンソース節約:PostgreSQLデータベースはオープンソースで無料であり、BSDプロトコルであり、使用と二次開発にはほとんど制限がありません.
  • は広くサポートされています.PostgreSQLデータベースは、C、C++、Perl、Python、Java、Tcl、PHPなど、多くの主流開発言語をサポートしています.
  • PostgreSQLコミュニティの活躍:PostgreSQLは基本的に3ヶ月ごとにパッチバージョンをリリースしています.これは既知のBUGがすぐに修復され、アプリケーションシーンの需要があればすぐに応答することを意味します.

    • 2 PostgreSQLのインストールと構成
    2.1 yumソースに含まれるPostgreSQLパッケージ
    [root@localhost ~]# yum list | grep postgresql
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
freeradius-postgresql.x86_64               3.0.4-6.el7                 local    
libreoffice-postgresql.x86_64              1:5.0.6.2-3.el7             local    
pcp-pmda-postgresql.x86_64                 3.11.3-4.el7                local    
postgresql.i686                            9.2.18-1.el7                local    
postgresql.x86_64                          9.2.18-1.el7                local    
postgresql-contrib.x86_64                  9.2.18-1.el7                local    
postgresql-devel.i686                      9.2.18-1.el7                local    
postgresql-devel.x86_64                    9.2.18-1.el7                local    
postgresql-docs.x86_64                     9.2.18-1.el7                local    
postgresql-jdbc.noarch                     9.2.1002-5.el7              local    
postgresql-jdbc-javadoc.noarch             9.2.1002-5.el7              local    
postgresql-libs.i686                       9.2.18-1.el7                local    
postgresql-libs.x86_64                     9.2.18-1.el7                local    
postgresql-odbc.x86_64                     09.03.0100-2.el7            local    
postgresql-plperl.x86_64                   9.2.18-1.el7                local    
postgresql-plpython.x86_64                 9.2.18-1.el7                local    
postgresql-pltcl.x86_64                    9.2.18-1.el7                local    
postgresql-server.x86_64                   9.2.18-1.el7                local    
postgresql-test.x86_64                     9.2.18-1.el7                local    
postgresql-upgrade.x86_64                  9.2.18-1.el7                local    
qt-postgresql.i686                         1:4.8.5-13.el7              local    
qt-postgresql.x86_64                       1:4.8.5-13.el7              local    
qt5-qtbase-postgresql.i686                 5.6.1-10.el7                local    
qt5-qtbase-postgresql.x86_64               5.6.1-10.el7                local

    2.2 rpmパッケージインストールPostgreSQL
    [root@localhost ~]# yum install -y postgresql-server postgresql postgresql-libs

server ：postgresql-server
client ：postgresql
   ：  postgresql-libs

    1.インストールが完了した後、直接データベースを起動することはできません.まず手動でデータベースを初期化する必要があります.
    [root@localhost ~]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK

    2.データベースの再起動:
    [root@localhost ~]# systemctl start postgresql

[root@localhost ~]# systemctl status postgresql
● postgresql.service - PostgreSQL database server
  Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled)
  Active: active (running) since   2018-02-19 22:02:57 CST; 3min 37s ago
 Process: 1286 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
 Process: 1281 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 1290 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─1290 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─1291 postgres: logger process   
           ├─1293 postgres: checkpointer process   
           ├─1294 postgres: writer process   
           ├─1295 postgres: wal writer process   
           ├─1296 postgres: autovacuum launcher process   
           ├─1297 postgres: stats collector process   
           └─1391 postgres: postgres postgres [local] idle

2  19 22:02:56 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
2  19 22:02:57 localhost.localdomain systemd[1]: Started PostgreSQL database server.

    3.オペレーティングシステムの「postgres」ユーザーに切り替え、データベースにログインする:
    [root@localhost ~]# su - postgres
-bash-4.2$ psql
psql (9.2.18)
   "help"        .

postgres=# \l
                                          
         |       |      |         |    Ctype    |                   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3    )

postgres=#

    4.rpmパッケージインストールのPostgreSQLのデータディレクトリ、デフォルトは/var/lib/pgsql/data
    -bash-4.2$ ls -l /var/lib/pgsql/data/
    48
drwx------ 5 postgres postgres    41 2   19 22:01 base
drwx------ 2 postgres postgres  4096 2   19 22:02 global
drwx------ 2 postgres postgres    18 2   19 22:01 pg_clog
-rw------- 1 postgres postgres  4232 2   19 22:01 pg_hba.conf
-rw------- 1 postgres postgres  1636 2   19 22:01 pg_ident.conf
drwx------ 2 postgres postgres    32 2   19 22:02 pg_log
drwx------ 4 postgres postgres    36 2   19 22:01 pg_multixact
drwx------ 2 postgres postgres    18 2   19 22:02 pg_notify
drwx------ 2 postgres postgres     6 2   19 22:01 pg_serial
drwx------ 2 postgres postgres     6 2   19 22:01 pg_snapshots
drwx------ 2 postgres postgres    25 2   19 22:08 pg_stat_tmp
drwx------ 2 postgres postgres    18 2   19 22:01 pg_subtrans
drwx------ 2 postgres postgres     6 2   19 22:01 pg_tblspc
drwx------ 2 postgres postgres     6 2   19 22:01 pg_twophase
-rw------- 1 postgres postgres     4 2   19 22:01 PG_VERSION
drwx------ 3 postgres postgres    60 2   19 22:01 pg_xlog
-rw------- 1 postgres postgres 19816 2   19 22:01 postgresql.conf
-rw------- 1 postgres postgres    57 2   19 22:02 postmaster.opts
-rw------- 1 postgres postgres    91 2   19 22:02 postmaster.pid

    rpmパッケージのpostgresql 10インストール、データベースの初期化、および起動方法(centos-7):
    https://www.postgresql.org/download/linux/redhat/
    yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

yum install postgresql10

yum install postgresql10-server

/usr/pgsql-10/bin/postgresql-10-setup initdb
systemctl enable postgresql-10
systemctl start postgresql-10

    2.3ソースパッケージインストールPostgreSQL
  • ダウンロードソース
    • [root@localhost ~]# wget https://ftp.postgresql.org/pub/source/v10.2/postgresql-10.2.tar.gz
  • インストール依存パッケージ
    • [root@localhost ~]# yum install -y zlib-devel readline-devel gcc
  • コンパイルインストールPostgreSQL 
    • [root@localhost ~]# tar -zxvf postgresql-10.2.tar.gz
[root@localhost ~]# cd postgresql-10.2

[root@localhost postgresql-10.2]# ./configure --prefix=/usr/local/pgsql10.2 --with-python --with-perl
[root@localhost postgresql-10.2]# make && make install

[root@localhost postgresql-10.2]# cd /usr/local/
[root@localhost local]# ln -s pgsql10.2 pgsql

    コメント:
    PostgreSQL 8.Xでは、コンパイルコマンドに「--enable-thread-safety」オプションが必要ですが、PostgreSQL 9.X以降のバージョンではこのオプションは必要ありません.  日常的な使用ではクライアントがスレッドセキュリティであることが一般的に要求されているため、PostgreSQL 9.X以降のバージョンではこの問題を考慮してデフォルトスレッドはセキュリティである.  --with-perl:perl言語のPL/Perlプロシージャ言語を使用してカスタム関数を書くには、このオプションを追加します.一般的に必要です.このオプションを使用するには、perl-ExtUtils-Embedとperl-develをインストールする必要があります. --with-python:perl言語のPL/Pythonプロシージャ言語を使用してカスタム関数を書くには、このオプションを追加します.一般的に必要です.このオプションを使用するにはpython-develをインストールする必要があります.  公式文書の要求に従って、makeコマンドを使用する場合、makeのバージョンはgmake 3.8以上であり、現在、多くのLinuxリリース版が要求を満たしている.(チェック方法:make--version) --prefixオプションは指定されていません.デフォルトのパスは/usr/localです.
    例外処理:
          --with-python
configure: error: header file  is required for Python
    ：
yum install python-devel
          --with-perl
configure: error: could not determine flags for linking embedded Perl.
This probably means that ExtUtils::Embed or ExtUtils::MakeMaker is notinstalled.
    ：
yum install perl-ExtUtils-Embed

    4.PostgreSQL環境変数の構成
    [root@localhost local]# vim /etc/profile
# PostgreSQL       
export PATH=/usr/local/pgsql/bin:$PATH
# PostgreSQL     
export LD_LIBRARY_PATH=/usr/local/pgsql/lib

[root@localhost local]# source /etc/profile

    5.データベース・クラスタの作成
    [root@localhost local]# useradd postgres

[root@localhost local]# mkdir -pv /mydata/pgdata
mkdir:       "/mydata"
mkdir:       "/mydata/pgdata"

[root@localhost local]# chown -R postgres.postgres /mydata/pgdata/

[root@localhost local]# su - postgres
[postgres@localhost ~]$ /usr/local/pgsql/bin/initdb -D /mydata/pgdata/

    6.PostgreSQLの起動
    [postgres@localhost pgsql]$ pg_ctl start -D /mydata/pgdata/
waiting for server to start....2018-02-20 00:52:03.616 CST [38915] LOG:  listening on IPv6 address "::1", port 5432
2018-02-20 00:52:03.616 CST [38915] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2018-02-20 00:52:03.619 CST [38915] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2018-02-20 00:52:03.689 CST [38916] LOG:  database system was shut down at 2018-02-20 00:44:56 CST
2018-02-20 00:52:03.692 CST [38915] LOG:  database system is ready to accept connections
 doneserver started

    7.PostgreSQLの停止
    [postgres@localhost pgsql]$ pg_ctl stop -D /mydata/pgdata/  ：
             [-m SHUTDOWN-MODE]，            ；SHUTDOWN-MODE     ：
        smart：         ，     。          ，        。
        fast (PostgreSQL10.X  )：       ，        ，        ，         。
                                      Oracle       immediate(adj.    )  。
        immediate：         ，   kill     ，             。   Oracle       abort  。
      ，      fast  。

    8.PostgreSQL起動スクリプトサービスコマンドを使用してPostgreSQLを起動するには、PostgreSQLの起動スクリプトを/etc/init.d/にコピーし、実行権限を追加する必要があります
    [root@localhost start-scripts]# pwd/root/postgresql-10.2/contrib/start-scripts

[root@localhost start-scripts]# lsfreebsd  linux  macos  osx

[root@localhost start-scripts]# cp linux /etc/init.d/postgresql
[root@localhost ~]# cd /etc/init.d
[root@localhost init.d]# chmod 755 postgresql

    起動スクリプトのパラメータをいくつか変更する必要があります.
    [root@localhost init.d]# vim postgresql 
prefix=/usr/local/pgsql     #                   PostgereSQL
PGDATA="/mydata/pgdata"     #    5

    コメント:
      centos 7では、ソースコードがインストールされているPostgreSQLはシステムctlに必要なスクリプトを提供していません.サービスとchkconfigを使用して管理を継続できます.
    2.4 crontribディレクトリのインストールツール
    crontribの下にはいくつかのツールがあります.インストールできます.方法は以下の通りです.
    [root@localhost postgresql-10.2]# pwd/root/postgresql-10.2
[root@localhost postgresql-10.2]# cd contrib/
[root@localhost contrib]# make && make install

    /usr/local/pgsql/bin        ：oid2name pg_standby vacuumlo

    2.5大きなブロックを使用してI/O性能を向上
      大きなデータブロックを使用してI/O性能を向上させるには、ソースコードのコンパイルインストールで解決するしかありません../configureコマンドを実行するときに大きなデータブロックを指定するとともに、大きなWALログブロックとWALログファイルのサイズを指定する必要があります. 32 KBデータブロック、32 KBのWALログブロック、64 MBのWALログファイルを指定したい場合、./configureのコマンドは次のとおりです.
    ./configure --prefix=/usr/local/pgsql10.2 --with-python --with-perl --with-blocksize=32 --with-wal-blocksize=32 --with-wal-segsize=64
  ：
    --with-blocksize            1-32KB，  8KB
    --with-wal-blocksize        1-64KB，  8KB
    --with-wal-segsize          1-1024MB，  16MB    
  ：
           PostgreSQL   PostgreSQL   ，          PostgreSQL    。

    2.6 PostgreSQLの簡単な構成
    2.6.1 PostgreSQLリスニングIPとポートの変更
    IPとポートをリスニングするには、データディレクトリのpostgresql.confファイルで変更する必要があります.
    [root@localhost pgdata]# pwd
/mydata/pgdata
[root@localhost pgdata]# vim postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)

     リスニングIPがlocalhostを使用する場合、127.0.0.1でしかデータベースにアクセスできません. 他のネットワークを介してPostgreSQLにリモートアクセスする必要がある場合は、「,」を区切り記号として使用してlisten_にIPアドレスを追加できます.addresses後、または「*」を使用して、すべてのIPがデータベースにアクセスできるようにします.
    2.6.2データベースlogの構成
    1.ログスイッチおよび保存ディレクトリ名
    logging_collector = on      #      ，   off，      
log_directory = 'log'       #         ，          log   ，       ，

    2.ログのローテーション構成では、次のようなシナリオを使用できます.
  • 毎日新しいログ
    • が生成されます.
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'     #     
log_truncate_on_rotation = off      #         
log_rotation_age = 1d       #        
log_rotation_size = 0       # 
  • ログが一定のサイズ(例えば10 MB)を満たすたびに、ログの回転
    • が行われる.
    log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = offlog_rotation_age = 0
log_rotation_size = 10M
  • は7日間のログのみを保持し、
    • をループオーバーライドする.
    log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1dlog_rotation_size = 0

    説明:%aは週を表します.たとえば、火曜日にpostgresql-Toe.logというログが生成されます.  同じ名前のログに遭遇するたびに、古いログファイルに追加するのではなく、古いログファイルを上書きします.
    2.6.3メモリパラメータ構成
    PostgreSQLのインストールが完了すると、次の2つの主要メモリパラメータを主に変更できます.
  • shared_buffer:共有メモリのサイズで、主に共有データブロックに使用され、デフォルトは128 MBです.サーバのメモリに余裕がある場合は、このパラメータを適切に大きくすることで、データベースはより多くのデータブロックをキャッシュすることができ、データを読み出すと、ファイルの読み取りを必要とせずに共有メモリから読み取ることができます.
  • work_mem:単一SQL実行時、ソート、hash joinで使用したメモリは、SQL実行が完了するとメモリが解放され、デフォルトは4 MBです.このパラメータを大きくすると、ソート操作の速度が向上します.
    • ExcelVBA怖いよ
    Javaベース-コレクション