LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト

作者:河原翔
発売日: 2014/10/27
メディア: オンデマンド (ペーパーバック)

容量監視
セキュリティ
バックアップ/リストア
- 分類
  - pg_basebackupコマンド

容量監視

下記領域について見積もり・監視を行う
- データベース領域
- 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の導入

公式/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本必要

勉強日記

チラ裏

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

容量監視

データベース領域

WAL領域

~9.4

9.5~

アーカイブWAL領域

実測方法

データベース領域

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

fillfactor

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

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

セキュリティ

データ暗号化

pgcryptoの導入

汎用ハッシュ関数

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

PGP暗号化関数/対称鍵

PGP暗号化関数/公開鍵

通信経路暗号化

監査ログ

バックアップ/リストア

分類

pg_basebackupコマンド