OSS-DB試験対策 緑本 ch3 インストール

OSS-DBPostgreSQL勉強メモ

出典: 

※インストール方法そのものは試験範囲外

PostgreSQLのインストール

事前準備

  • postgresユーザ作っとく
  • ソースコードからビルドする場合に必要なもの

    • build-essential

    • readline

      • libreadline6-devとか
      • psqlでコマンドライン編集・ヒストリ機能を利用するのに必要
      • configureの --without-readline オプションで無効化可能
    • zlib

      • zlib1g-devとか
      • pg_dump, pg_restore機能に必要
      • configureの --without-zlib オプションで無効化可能

ソースコードの取得

  • tarball拾ってくる

インストール

  1. ./configure <オプション>

    • --prefixでインストール先を指定
  2. make

    • make allとおなじ
    • 【補】contribも利用したければmake world
  3. make install

インストール後の設定

  • パス通す

    • PATH/usr/local/bin/pgsql/bin
    • LD_LIBRARY_PATH/usr/local/bin/pgsql/lib

起動と停止

  • 公式
  • データベースクラスタの作成: initdb
$ initdb --help
 [-D, --pgdata=]DATADIR     location for this database cluster
 ...
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
 ...
$ initdb --pgdata=/home/postgres/data --locale=C
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default database encoding has accordingly been set to "SQL_ASCII".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory /home/postgres/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /home/postgres/data -l logfile start
  • 例: ポート5433でサーバ起動
pg_ctl start --pgdata /home/postgres/data -w -o "-p 5433"
  • psqlでサーバに接続
postgres@00aa48792429:/$ psql -p 5433


psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# SELECT now();

              now              
-------------------------------
 2020-01-18 16:26:41.277869+00
(1 row)

postgres=# \q

postgres@00aa48792429:/$ 
  • 【補】同一のデータベースクラスタに対して複数のPostgreSQLサーバーを起動しようとするとエラー

    • postmaster.pidファイルで判別している
postgres@00aa48792429:~/data$ pg_ctl start --pgdata /home/postgres/data -w -o "-p 5434"
pg_ctl: another server might be running; trying to start server anyway
waiting for server to start....2020-01-18 17:46:34.325 UTC [844] FATAL:  lock file "postmaster.pid" already exists
2020-01-18 17:46:34.325 UTC [844] HINT:  Is another postmaster (PID 833) running in data directory "/home/postgres/data"?
 stopped waiting
pg_ctl: could not start server
Examine the log output.
  • 停止
postgres@00aa48792429:~/data$ pg_ctl stop --pgdata /home/postgres/data
2020-01-18 17:48:47.202 UTC [833] LOG:  received fast shutdown request
waiting for server to shut down....2020-01-18 17:48:47.205 UTC [833] LOG:  aborting any active transactions
2020-01-18 17:48:47.205 UTC [833] LOG:  background worker "logical replication launcher" (PID 840) exited with exit code 1
2020-01-18 17:48:47.206 UTC [835] LOG:  shutting down
2020-01-18 17:48:47.221 UTC [833] LOG:  database system is shut down
 done
server stopped
  • 【補】指定のデータベースクラスタに対してPostgreSQLサーバが起動していない場合、エラー
postgres@00aa48792429:~/data$ pg_ctl stop --pgdata /home/postgres/data
pg_ctl: PID file "/home/postgres/data/postmaster.pid" does not exist
Is server running?
postgres@00aa48792429:~/data$ 

アンインストール

  • ソースコードのディレクトリで
$ make uninstall
  • 【補】なかみ

    • config/Makefile
...
uninstall:
	rm -f '$(DESTDIR)$(pgxsdir)/config/install-sh'
	rm -f '$(DESTDIR)$(pgxsdir)/config/missing'
  • けしてるだけ

データベースクラスタ

ls -lA $PGDATA
total 132
drwx------  5 postgres postgres  4096 Jan 18 16:05 base
drwx------  2 postgres postgres  4096 Jan 18 16:26 global
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_commit_ts
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_dynshmem
-rw-------  1 postgres postgres  4537 Jan 18 16:05 pg_hba.conf
-rw-------  1 postgres postgres  1636 Jan 18 16:05 pg_ident.conf
drwx------  4 postgres postgres  4096 Jan 18 16:10 pg_logical
drwx------  4 postgres postgres  4096 Jan 18 16:05 pg_multixact
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_notify
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_replslot
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_serial
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_snapshots
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_stat
drwx------  2 postgres postgres  4096 Jan 18 16:35 pg_stat_tmp
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_subtrans
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_tblspc
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_twophase
-rw-------  1 postgres postgres     3 Jan 18 16:05 PG_VERSION
drwx------  3 postgres postgres  4096 Jan 18 16:05 pg_wal
drwx------  2 postgres postgres  4096 Jan 18 16:05 pg_xact
-rw-------  1 postgres postgres    88 Jan 18 16:05 postgresql.auto.conf
-rw-------  1 postgres postgres 26588 Jan 18 16:05 postgresql.conf
-rw-------  1 postgres postgres    36 Jan 18 16:05 postmaster.opts
-rw-------  1 postgres postgres    94 Jan 18 16:05 postmaster.pid

PG_VERSION

postgres@00aa48792429:~/data$ cat PG_VERSION
12
  • メジャーバージョンが格納されている

    • 【補】メジャーバージョンが異なるとファイル形式が異なり、互換性がないため

base/

  • 各データベースのサブディレクトリを格納
postgres@00aa48792429:~/data$ ls -lA  ${PGDATA}/base
total 20
drwx------ 2 postgres postgres  4096 Jan 18 16:05 1
drwx------ 2 postgres postgres  4096 Jan 18 16:05 13407
drwx------ 2 postgres postgres 12288 Jan 18 16:26 13408

global/

  • クラスタ内のデータベース間で共有するデータ

    • ユーザ情報等

log/

  • ログ

pg_wal/

  • WAL
  • Write Ahead Log

    • トランザクションログ

postgresql.conf

pg_hba.conf

  • 公式
  • クライアント認証情報を設定するファイル
postgres@00aa48792429:~/data$ cat $PGDATA/pg_hba.conf
  • 【補】postgresコンテナのデフォルト
...

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

host all all all trust

postmaster.pid

  • 起動中のPostgreSQLサーバのPIDなどを記録
postgres@00aa48792429:~/data$ cat postmaster.pid
cat postmaster.pid
1
/var/lib/postgresql/data
1579363515
5432
/var/run/postgresql
*
  5432001    163840
ready   
  • 【補】同じデータベースクラスタに対して複数のPostgreSQLを起動できないようにする役割がある

データベース

postgres=# SELECT oid, datname FROM pg_database;

  oid  |  datname  
-------+-----------
 13408 | postgres
     1 | template1
 13407 | template0
(3 rows)
  • 【補】oid: Object ID

    • base/ディレクトリ内のものに対応しているっぽい
postgres@00aa48792429:~/data$ ls -lA  ${PGDATA}/base
total 20
drwx------ 2 postgres postgres  4096 Jan 18 16:05 1
drwx------ 2 postgres postgres  4096 Jan 18 16:05 13407
drwx------ 2 postgres postgres 12288 Jan 18 16:26 13408
  • 【補】メタコマンドでも出せる
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

テンプレートデータベース

  • データベースを作成する際、テンプレートを元にコピーする

    • デフォルトはtemplate1
  • template1: 変更可能、デフォルトはtemplate0と同じ内容
  • template0: 変更不可

    • template1に登録済のデータベースオブジェクトをコピーしたくないときに選択する
    • 【補】Null Object Patternみたいなかんじ

      • 「テンプレートを選択しない」のではなく、「空のテンプレート」を選択することで設計を単純化しているものと思われる

initdb

initdb --help
initdb initializes a PostgreSQL database cluster.

Usage:
  initdb [OPTION]... [DATADIR]

Options:
  -A, --auth=METHOD         default authentication method for local connections
      --auth-host=METHOD    default authentication method for local TCP/IP connections
      --auth-local=METHOD   default authentication method for local-socket connections
 [-D, --pgdata=]DATADIR     location for this database cluster
  -E, --encoding=ENCODING   set default encoding for new databases
  -g, --allow-group-access  allow group read/execute on data directory
      --locale=LOCALE       set default locale for new databases
      --lc-collate=, --lc-ctype=, --lc-messages=LOCALE
      --lc-monetary=, --lc-numeric=, --lc-time=LOCALE
                            set default locale in the respective category for
                            new databases (default taken from environment)
      --no-locale           equivalent to --locale=C
      --pwfile=FILE         read password for the new superuser from file
  -T, --text-search-config=CFG
                            default text search configuration
  -U, --username=NAME       database superuser name
  -W, --pwprompt            prompt for a password for the new superuser
  -X, --waldir=WALDIR       location for the write-ahead log directory
      --wal-segsize=SIZE    size of WAL segments, in megabytes

Less commonly used options:
  -d, --debug               generate lots of debugging output
  -k, --data-checksums      use data page checksums
  -L DIRECTORY              where to find the input files
  -n, --no-clean            do not clean up after errors
  -N, --no-sync             do not wait for changes to be written safely to disk
  -s, --show                show internal settings
  -S, --sync-only           only sync data directory

Other options:
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

If the data directory is not specified, the environment variable PGDATA
is used.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
  • rootでは実行できない
root@00aa48792429:/# initdb
initdb: error: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
  • (PostgreSQLの)管理ユーザ

    • initdbを実行してデータベースクラスタを作成したOSユーザ
    • postgresで実行したならpostgres氏
  • 管理ユーザだけが$PGDATAディレクトリへのアクセス権を有する

    • 【補】 postgres:postgresの700
postgres@00aa48792429:~/data$ ls -ld $PGDATA
drwx------ 19 postgres postgres 4096 Jan 18 16:05 /var/lib/postgresql/data
  • PostgreSQLの起動/停止などを行えるのも管理ユーザのみ
  • デフォルトで、管理ユーザと同名のデータベースユーザがスーパーユーザ権限で作成される
postgres=# \du
\du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  • 別名のスーパーユーザを作成したい場合は-U (--username)で指定

    • 【補】スーパーユーザ名によらず、デフォルトのデータベースはpostgres
postgres@00aa48792429:~/data$ initdb /home/postgres/data2 -U aoba
postgres@00aa48792429:~/data$ psql -p 5434 -U aoba -d postgres

ロケール

      --locale=LOCALE       set default locale for new databases
      --no-locale           equivalent to --locale=C
  • ロケールとは

    • 文字の並び順

      • 例: 全角数字を半角数字と同様に数値順でソートしたい場合

        • 通常は辞書順になる
    • 文字の分類
    • ログメッセージの言語
    • 通貨や数字
    • 日時の書式
  • 使用可能なロケール一覧
postgres@00aa48792429:~/data$ locale -a
C
C.UTF-8
en_US.utf8
POSIX
  • ロケールは無効が推奨

    • 検索性能が低くなる等、問題が発生しうるため
    • --no-locale または --locale=C
  • ロケールはデータベース単位で指定できる

    • 【補】createdbコマンドの--localeオプションで指定できる

エンコーディング

  -E, --encoding=ENCODING   set default encoding for new databases
  • データベース単位
  • エンコーディングとロケールとは互換性があること

    • さもないとエラーの原因になる
    • ロケール無効時は考慮する必要なし
  • C/S両サイドでのエンコーディングがある

    • データベースエンコーディング(サーバエンコーディング)

      • 文字をバイト列としてデータベースに格納する際のエンコーディング
    • クライアントエンコーディング

      • 文字の自動変換とか
      • 公式

client_encoding (string)

Sets the client-side encoding (character set). The default is to use the database encoding. The character sets supported by the PostgreSQL server are described in Section 23.3.1.