D.Horiyama's output

PostgreSQL 高度技術者育成テキスト ch3 (運用管理) (1/2)

OSS-DBPostgreSQL勉強メモ

[asin:B00P4WD4QG:detail]

容量監視

  • 下記領域について見積もり・監視を行う

    • データベース領域
    • WAL領域
    • アーカイブWAL領域

データベース領域

  • 主にリレーションのデータを格納

    • テーブル
    • インデックス
  • 最も増減がある領域

WAL領域

~9.4

  • 昔話
#checkpoint_segments = 3		# in logfile segments, min 1, 16MB each
  • WALセグメントひとつあたり16MB
ls -lAh pg_xlog/
total 16M
-rw-------   1 postgres       postgres  16M 2020-02-28 14:17 000000010000000000000001
drwx------   2 postgres       postgres 4.0k 2020-02-28 14:17 archive_status
  • 頭打ちになるサイズ
16MB * (checkpoint_segments * 3 + 1)
  • デフォルトの checkpoint_segments = 3 だと160MB

9.5~

max_wal_size = 1GB
min_wal_size = 80MB
  • 上限値/下限値をダイレクトに設定できるようになった

アーカイブWAL領域

  • 公式/Disk Full Failure
  • アーカイブWAL領域がディスクフルになっても即座にPostgreSQLは停止しない
  • アーカイブ処理に失敗し続け、WAL領域があふれるとPostgreSQLはPANICする

実測方法

  • データベース領域

  • WAL領域、アーカイブWAL領域

    • df/duコマンド
CREATE TABLE tbl (col int);
INSERT INTO tbl VALUES (generate_series(1,1000000));
CREATE INDEX myindex ON tbl USING BTREE (col);

SELECT pg_database_size('postgres'),
       pg_relation_size('tbl'),
       pg_total_relation_size('tbl');
-[ RECORD 1 ]----------+---------
pg_database_size       | 66904943
pg_relation_size       | 36249600
pg_total_relation_size | 58769408
  • tableとindexとを別々に求めて足した値と4ページずれる…？
SELECT pg_total_relation_size('tbl')
     - (pg_relation_size('tbl') + pg_relation_size('myindex')) as diff;
 diff  
-------
 32768
(1 row)
  • と思ったら、VACUUM FULLしたら差分は無くなった
VACUUM FULL tbl;
SELECT pg_total_relation_size('tbl')
     - (pg_relation_size('tbl') + pg_relation_size('myindex')) as diff;
 diff 
------
    0
(1 row)

テーブル・インデックス容量見積もり

fillfactor

  • データ挿入時のページへのデータの詰め具合
  • Btreeインデックスのleafについては、昔は50%,90%だけ選べたらしい？

  • 今では10から100の任意の値を指定できる

  • rootとinternalは70%固定らしい
CREATE INDEX myindex2 ON tbl USING BTREE (col) WITH (fillfactor=70);
SELECT relname,reltuples,relpages,reloptions FROM pg_class WHERE relname LIKE 'myindex%';
 relname  | reltuples | relpages |   reloptions    
----------+-----------+----------+-----------------
 myindex  |     1e+06 |     2745 | 
 myindex2 |     1e+06 |     3537 | {fillfactor=70}
(2 rows)
  • fillfactorの小さなインデックスはfillfactorがデフォルト(90%)のものに比べてpage数が多い

テーブルデータの見積もり

\d tbl
                Table "public.tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 col    | integer |           |          | 
Indexes:
    "myindex" btree (col)
    "myindex2" btree (col) WITH (fillfactor='70')

  • 1行あたり容量

    • ラインポインタ: 4バイト
    • タプルヘッダ: 23バイト

    • タプルデータ: 4バイト

      • col int
  • ページヘッダ 24バイト
  • ページあたり行数
(8192 - 24) / (4 + 23 + 4) = 263.48
  • 行数1,000,000なのでページ数の見積もりは
1000000 / 263.48 = 3795
  • (実際の値はわりとずれている)
SELECT relname,reltuples,relpages FROM pg_class WHERE relname = 'tbl';
-[ RECORD 1 ]----
relname   | tbl
reltuples | 1e+06
relpages  | 4425
SELECT * FROM pgstattuple('tbl2');
-[ RECORD 1 ]------+---------
table_len          | 36249600
tuple_count        | 1000000
tuple_len          | 28000000
tuple_percent      | 77.24
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 125700
free_percent       | 0.35

インデックスデータの見積もり

  • fillfactorを考慮する

    • leafはデフォルト90%

    • internal,rootは70%固定らしい
  • まず全エントリカバーするのに必要なleaf数を算出する

  • ついで、全リーフをカバーするのに必要なroot,internal数を算出する

    • 木構造

セキュリティ

  • 標準で搭載

    • データ暗号化
    • 通信経路暗号化
    • サーバ認証
    • クライアント認証

  • 標準で搭載していない

    • 監査ログ

      • トリガやルールを使って、自前で実現する

データ暗号化

pgcryptoの導入

CREATE EXTENSION pgcrypto;
SELECT extname,extversion FROM pg_extension;
   extname   | extversion 
-------------+------------
 plpgsql     | 1.0
 pgstattuple | 1.5
 pgcrypto    | 1.3
(3 rows)

汎用ハッシュ関数

  • digest
  • ハッシュ値を得るやつ
SELECT digest('testdata', 'md5');
               digest               
------------------------------------
 \xef654c40ab4f1747fc699915d4f70902
(1 row)
  • hmac
  • hashed MAC(Message Authentication Code)を算出するやつ
  • メッセージ、秘密鍵、ハッシュ関数をもとに算出する
SELECT hmac('testdata', 'testkey', 'md5');
                hmac                
------------------------------------
 \xe66a491c05c3f2a921ba32c6650369cc
(1 row)

パスワードハッシュ化関数

  • ソルトつき暗号化
gen_salt(type text [, iter_count integer ]) returns text
crypt(password text, salt text) returns text
  • つかう
SELECT gen_salt('md5');
  gen_salt   
-------------
 $1$HUbvn/DB
(1 row)
  • 毎回異なるソルトを得る
SELECT gen_salt('md5');
  gen_salt   
-------------
 $1$1Q92f5mK
(1 row)
  • ので、crypt()に渡す用と照会用の値が同じになるよう、サブクエリを用いる
CREATE TABLE users(account text, password text, salt text);
INSERT INTO users (account, password, salt)
SELECT 'testaccount',
       crypt('testpass', salt),
       salt
  FROM (SELECT gen_salt('md5') AS salt) tmp_salt;
  
TABLE users;
-[ RECORD 1 ]--------------------------------
account  | testaccount
password | $1$.IKYM42v$z6ZXpPR78pXxHM7FIm/lN.
salt     | $1$.IKYM42v

PGP暗号化関数/対称鍵

  • 対称鍵暗号化/復号化
pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
  • つかう
SELECT pgp_sym_encrypt('testdata', 'testpass');
                                                                    pgp_sym_encrypt                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------
 \xc30d04070302889ab5f26b967ad17dd239010dd9c10a1e442c5eafd7f68c111656e737cbaf5055bc8a899a9a3c5349de8a9a58382aac6a14ce70749c979704662f86e569c37bef8c08be
(1 row)
SELECT pgp_sym_decrypt('\xc30d040703028df075445a4beff276d23901185f131dcc1d0c5aea1fde529722fbab438b1f30b0de53c059c6c85dabc1c52ff2af30a7e4397cfb152381c7d9769a20fb432296032d4af0', 'testpass');
 pgp_sym_decrypt 
-----------------
 testdata
(1 row)
  • 共通鍵を間違えてみる
SELECT pgp_sym_decrypt('\xc30d040703028df075445a4beff276d23901185f131dcc1d0c5aea1fde529722fbab438b1f30b0de53c059c6c85dabc1c52ff2af30a7e4397cfb152381c7d9769a20fb432296032d4af0', 'ngpass');
ERROR:  Wrong key or corrupt data

PGP暗号化関数/公開鍵

pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea

通信経路暗号化

  • 先の「データ暗号化」は、通信経路のデータは平文筒抜け

  • SSLを使う

    • 公式/SSL-TCP

      • 証明書の作り方を教えてくれてたりする
  • DBサーバのような、接続するクライアントが限定されている場合はオレオレ証明書でも問題ない
  • SSL有効化方法
  • configureオプション--with-opensslでビルド

  • postgresql.confでssl=on

    • reloadで反映
SELECT name,setting,context FROM pg_settings WHERE name='ssl';
 name | setting | context 
------+---------+---------
 ssl  | off     | sighup
(1 row)

  • pg_hba.confでhostsslルール設定

    • これもreloadで反映

監査ログ

  • 発行したSQL文をログ出力するやつ

    • 特定のテーブルのみ、とか器用なことはできない
#log_statement = 'none'			# none, ddl, mod, all

  • 特定のテーブルについて何かしたい場合はトリガプロシージャを自分で作り込む

バックアップ/リストア

分類

  • オンラインバックアップ

    • 論理

      • pg_dump, pg_dumpall/psql, pg_restore

    • 物理

      • pg_basebackup

  • オフラインバックアップ

    • コールドバックアップ

  • レプリケーション as バックアップ

    • 操作ミスによるデータ損失の復旧は不可能

pg_basebackupコマンド

  -X, --wal-method=none|fetch|stream
                         include required WAL files with specified method

  • f/fetch

    • バックアップ取得中はWALファイルを転送せず、最後にWALファイルをまとめて転送

    • ベースバックアップ中に書き出されるWALファイルがWAL領域から削除される可能性がある

      • wal_keep_segmentsの値を大きくする等の対策を講じること

  • s/stream

    • WALファイルを随時転送

      • 転送用の接続が1本必要