PostgreSQL10のでパーティショニング

どうも、俺です。

PostgreSQLがv10になってからパーティショニングの設定が超ラクになりました。
これまでは、CREATE TABLE するときにトリガーや関数を逐一設定する必要がありましたが、
PostgreSQL 10からはCREATE TABLE文の中だけでパーティショニングの設定が可能です。

簡単なサンプルで見てみます。

-- 元となる親テーブルの作成
CREATE TABLE test_table (
  id SERIAL,
  user_id INTEGER NOT NULL,
  name VARCHAR(512) NOT NULL,
  created_at TIMESTAMP NOT NULL
)
-- user_idをキーにパーティションする
PARTITION BY LIST (user_id);

-- user_idが1の場合はtest_table_1へ
CREATE TABLE test_table_1 PARTITION OF test_table FOR VALUES IN (1);
-- user_idが2の場合はtest_table_2へ
CREATE TABLE test_table_2 PARTITION OF test_table FOR VALUES IN (2);
-- user_idが3と4の場合はtest_table_3へ
CREATE TABLE test_table_3 PARTITION OF test_table FOR VALUES IN (3,4);
-- user_idが5と6と7の場合はtest_table_4へ
CREATE TABLE test_table_4 PARTITION OF test_table FOR VALUES IN (5,6,7);
.
.
.

これだけでOKです。


ただ、このパーティショニングの方法だと、user_idが増えれば増えるほどパーティショニング子テーブルをいちいち CREATE TABLE する必要があるので、
子テーブルを予め10個作成しておき、user_idを10で割った余りを元にパーティショニングするように変更してみます。

CREATE TABLE test_table (
  id SERIAL,
  user_id INTEGER NOT NULL,
  name VARCHAR(512) NOT NULL,
  created_at TIMESTAMP NOT NULL
)
-- user_idを10で割った余りをキーにパーティションする
PARTITION BY LIST ((user_id%10));

CREATE TABLE test_table_1 PARTITION OF test_table FOR VALUES IN (1);
CREATE TABLE test_table_2 PARTITION OF test_table FOR VALUES IN (2);
CREATE TABLE test_table_3 PARTITION OF test_table FOR VALUES IN (3);
CREATE TABLE test_table_4 PARTITION OF test_table FOR VALUES IN (4);
CREATE TABLE test_table_5 PARTITION OF test_table FOR VALUES IN (5);
CREATE TABLE test_table_6 PARTITION OF test_table FOR VALUES IN (6);
CREATE TABLE test_table_7 PARTITION OF test_table FOR VALUES IN (7);
CREATE TABLE test_table_8 PARTITION OF test_table FOR VALUES IN (8);
CREATE TABLE test_table_9 PARTITION OF test_table FOR VALUES IN (9);
CREATE TABLE test_table_0 PARTITION OF test_table FOR VALUES IN (0);

以上でぇぇぇえ〜す。

PostgreSQL TIMESTAMP型のDEFAULTS値に小数点以下を表示させない

どうも、俺です。

PostgreSQLのテーブル定義で

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

という定義をすると、
created_atカラムに値を指定せずINSERTすると

2018-04-23 16:12:23.13289

秒の後に小数点が入ります。


小数点を入れずに、年月日 時分秒 というフォーマットにするには

created_at TIMESTAMP NOT NULL DEFAULT DATE_TRUNC('second', now() :: timestamp)

という定義にしておけばOKです。


以上でぇぇえぇす。

pgpool-2のオンラインリカバリを試す

どうも、俺です。

pgpool-2でオンラインリカバリを試したのでその方法をメモ。
若干ややこしかった...。

【環境】
・postgresql v9.5.3
・pgpool-2 v3.5.4
・ノード1のあるサーバー 192.168.0.1
・ノード2のあるサーバー 192.168.0.2
・pgpool-2のあるサーバー 192.168.0.1
※PostgreSQLとpgpool-2はすでに稼働中とします。
※pgpool-2の公式HP参照

手順1) C言語関数インストール

各ノードのPostgreSQLデータベースに関数をインストールします。
すべてのノードのあるサーバーで以下を行います。

# su - postgres
% cd /path/to/pgpool-II-3.5.4/src/sql/pgpool-recovery
% make install
% psql -p ポート番号 -f pgpool-recovery.sql template1 

手順2) pgpool.confの設定

オンラインリカバリを行うための設定を追加します。

# vim /path/to/pgpool/etc/pgpool.conf

--- 以下pgpool.conf内容 ---

# PCPの設定
pcp_listen_addresses = '*'
pcp_port = 9898
pcp_socket_dir = '/tmp'

# リカバリを行うPostgreSQLのユーザとパスワード。
recovery_user = 'postgres'
recovery_password = 'postgres'
# オンラインリカバリを実行するスクリプト名。
recovery_1st_stage_command = 'recovery_1st_stage.sh'
recovery_2nd_stage_command = 'recovery_2nd_stage.sh'
# オンラインリカバリのタイムアウト秒数。
recovery_timeout = 120 
# リカバリ中にアイドル状態のクライアントを何秒で切断するか。0は無効。-1は即時切断。
client_idle_limit_in_recovery = 1 

手順3) postgresql.confの設定

archive_logを取るように設定します。
既に設定済みであれば不要です。

# vim /path/to/pgsql/data/postgresql.conf

--- 以下postgresql.confの内容 ---

archive_mode = on

# /usr/local/pgsql/data/archive というディレクトリ以下にarchive_logがコピーされる
archive_command = 'cp %p /usr/local/pgsql/data/archive/%f'

手順4) リカバリスクリプトの作成

・recovery_1st_stage.sh
・recovery_2st_stage.sh
・pgpool_remove_start
の3ファイルを作成します。

pgpoolレプリケーションモード | PostgreSQL関連情報のスクリプトを使わせて頂きました。ありがとうございます。

recovery_1st_stage.sh

#!/bin/sh

PSQL="/usr/local/pgsql/bin/psql"
PORT=5432
MASTER_BASEDIR=$1
RECOVERY_HOST=$2
RECOVERY_BASEDIR=$3

$PSQL -p $PORT -c "SELECT pg_start_backup('pgpool-recovery')" postgres

echo "restore_command = 'cp /usr/local/pgsql/data/archive/%f %p'" > $MASTER_BASEDIR/recovery.conf

ssh -l postgres -T $RECOVERY_HOST rm -rf $RECOVERY_BASEDIR.bk
ssh -l postgres -T $RECOVERY_HOST mv -f $RECOVERY_BASEDIR{,.bk}

rsync -az -e ssh -l postgres $MASTER_BASEDIR/ $RECOVERY_HOST:$RECOVERY_BASEDIR/

ssh -l postgres  -T $RECOVERY_HOST cp -f $RECOVERY_BASEDIR.bk/postgresql.conf $RECOVERY_BASEDIR
ssh -l postgres  -T $RECOVERY_HOST rm -f $RECOVERY_BASEDIR/postmaster.pid

rm -f $MASTER_BASEDIR/recovery.conf

$PSQL -p $PORT -c "SELECT pg_stop_backup()" postgres

recovery_2nd_stage.sh

#!/bin/sh

PSQL="/usr/local/pgsql/bin/psql"
PORT=5432
ARCHIVEDIR=/usr/local/pgsql/data/archive/
MASTER_BASEDIR=$1
RECOVERY_HOST=$2
RECOVERY_BASEDIR=$3

$PSQL -p $PORT -c 'SELECT pg_switch_xlog()' postgres

rsync -az -e ssh  -l postgres $ARCHIVEDIR $RECOVERY_HOST:$ARCHIVEDIR 

pgpool_remove_start

#! /bin/sh

PGCTL=/usr/local/pgsql/bin/pg_ctl
RECOVERY_HOST=$1
RECOVERY_BASEDIR=$2

ssh -l postgres -T $RECOVERY_HOST $PGCTL -w -D $RECOVERY_BASEDIR start 2>/dev/null 1> /dev/null < /dev/null &

これら3ファイルに実行権限を付けて、各ノードサーバーのデータディレクトリ以下に配置します。

以上で、オンラインリカバリの準備は完了です。


では実際にオンラインリカバリを試してみます。
オンラインリカバリを行うには、いずれかのノードサーバーがpgpool側で異常検知されている必要があります。
今回は192.168.0.2のPostgreSQLを止めてからオンラインリカバリをしてみます。

% /usr/local/pgsql/bin/pg_ctl -D /path/to/pgsql/data stop

% /usr/local/pgsql/bin/psql -p 9999  // ←pgpoolへ接続
postgres=# show pool_nodes;
 node_id |   hostname   | port | status | lb_weight |  role  | select_cnt 
---------+--------------+------+--------+-----------+--------+------------
 0       | 192.168.0.1  | 5432 | 2      | 0.500000  | master | 1200
 1       | 192.168.0.2  | 5432 | 3      | 0.500000  | slave  | 884
(2 rows)

192.168.0.2が停止しています。

この状態で、pcp_recovery_nodeコマンドでオンラインリカバリを行います。

% /path/to/pgpool/bin/pcp_recovery_node -h 192.168.0.1 -p 9898 -U postgres -n 1

-h ... PCP(pgpool Communication Manager Connection)の稼働ホスト?
-p ... PCPの稼働ポート
-U ... pgpoo.confに記したrecovery_user名
-n ... 何番のノードを起動させるか。今回はnode_id=1の192.168.0.2

以上で、問題なければオンラインリカバリが成功するはずです。
ただ、オンラインリカバリを行うのは意外と時間かかる場合があるので気長に待つ必要も大切です。

【実際に試してみた】

サービス稼働中を模して、
無限ループでINSERTさせながらオンラインリカバリをやってみました。
...が、ターミナル上はSuccess!と出たのですが、
実際にデータを確認してみると2件レコードのズレがありました...。
やはり、データ誤差が出た場合は一旦サービス止めてdata/ディレクトリ丸ごとコピーの方が安全なのでしょうか。。？


以上でぇぇぇぇす！

PostgreSQLでスキーマ(テーブル定義)を取得する方法

どうも、俺です。

 PostgreSQLでターミナル上からスキーマ情報を取得したい場合。

$ pg_dump --schema-only DATABASE_NAME > schema.sql

以上でぇぇぇぇす。

PostgreSQLのロック確認方法

どうも、俺です。

PostgreSQLで発生しているロックの確認の方法です。

PostgreSQL：ロックの確認と解除方法

上記にありますが、

SELECT l.pid, db.datname, c.relname, l.locktype, l.mode
FROM pg_locks l
        LEFT JOIN pg_class c ON l.relation=c.relfilenode
        LEFT JOIN pg_database db ON l.database = db.oid
  WHERE datname='{DATABASE_NAME}'
ORDER BY l.pid;

です。

9.6. ロックとテーブル
にあるように、クエリ実行中は数種類のロックがかかっています。

実際に測ってみると、
SELECT文でも AccessShareLockがかかりますが、
これはAccessExclusiveLockモードとのみ競合するとのことなので、
ALTER TALBE, DROP TABLE, VACUUM FULL, LOCK TABLE
以外のクエリに対しては何ら問題ありません。


以上でぇぇぇぇす。

PostgreSQLのキャッシュヒット率計算

どうも、俺です。

PostgreSQLのキャッシュヒット率の計算をググって出てきたのでメモ。

以下のサイトに書いてありました。
稼動統計情報を活用しよう(2)

・テーブルへのキャッシュヒット率の計算

SELECT relname,
   round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2)
   AS cache_hit_ratio FROM pg_statio_user_tables
     WHERE heap_blks_read > 0 ORDER BY cache_hit_ratio;

・インデックスのキャッシュヒット率の計算

SELECT relname, indexrelname,
   round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2)
   AS cache_hit_ratio FROM pg_statio_user_indexes
     WHERE idx_blks_read > 0 ORDER BY cache_hit_ratio;

以上でぇぇぇぇす。


※2015.6.25 追記

postgresql_トラブルシュート
を参考にテーブルキャッシュヒット率とインデックスヒット率を同時に計算できるんじゃないかと考えました。
利用するテーブルはpg_statio_user_tablesです。

SELECT *,
(heap_blks_hit*100) / (heap_blks_read+heap_blks_hit) AS disk_ratio,
(idx_blks_hit*100) / (idx_blks_read+idx_blks_hit) AS idx_ratio
FROM pg_statio_user_tables
WHERE heap_blks_hit >= 1
and schemaname = 'public' ORDER BY idx_ratio;

統計情報を持つテーブルについてはこちら(統計情報コレクタ)を参考に。

ただ、なぜかこのクエリだといくつかのインデックスヒット率は、上述したクエリで算出したものと異なる場合がある。。
なので、修正しないといけない...。


※2015.6.26 追記
キャッシュヒット率の合計平均を出すクエリを作りました。

・テーブルへのキャッシュヒット率平均

SELECT avg (cache_hit_ratio)
FROM
(SELECT relname,
   round(heap_blks_hit*100/(heap_blks_hit+heap_blks_read), 2) AS cache_hit_ratio 
FROM pg_statio_user_tables
     WHERE heap_blks_read > 0) AS foo;

・インデックスヒット率の平均

SELECT avg(cache_hit_ratio)
FROM
(SELECT relname, indexrelname,
   round(idx_blks_hit*100/(idx_blks_hit+idx_blks_read), 2) AS cache_hit_ratio 
FROM pg_statio_user_indexes
WHERE idx_blks_read > 0) AS foo;

統計情報をリセットする場合は

SELECT pg_stat_reset();

を叩けばリセットされます。

設定を変更して統計を取り直したい場合などに使います。

postgresqlでSELECTした結果でUPDATEをかける

どうも、俺＠仕事中です。

postgresqlでSELECTした結果を利用してUPDATE（更新）をかける方法についてメモメモ。

dbname=# select * from table_a;
 id | name | ref_id 
----+------+--------
  1 | xxx  |     10
  2 | xxx  |     11
  3 | xxx  |     12
(3 rows)

dbname=#
dbname=# select * from table_b;
 id | name 
----+------
 10 | aaa
 11 | bbb
 12 | ccc
(3 rows)

テーブルはこういう状態だとします。
期待する結果は

dbname=# select * from table_a;
 id | name | ref_id 
----+------+--------
  1 | aaa  |     10
  2 | bbb  |     11
  3 | ccc  |     12
(3 rows)

table_bのnameでtable_aのnameを更新したい場合。

dbname=# UPDATE table_a
 SET name = foo.name
 FROM (SELECT * FROM table_b) AS foo
WHERE a.ref_id = foo.id;

これで出来ます！


以上でぇぇぇぇぇぇぇえぇす。

PHPとpostgresqlでテーブル一覧を取得

どうも、オレ＠始業しましたです。

忘れないうちにメモメモ。
DBがpostgreSQLでphpからテーブル一覧を取得するためのクエリについて。
postgreSQLのバージョンは7.4.12ですが、おそらく他のバージョンでも大丈夫かな？（未確認）
PHPのバージョンは5以上です。

<?php
$pdo = new PDO('pgsql:host=localhost;port=5432;dbname=dbname;user=username;password=password');
$sql = 'select pg_statio_user_tables.relname
from pg_catalog.pg_class,pg_catalog.pg_statio_user_tables
where relkind='r'
and pg_catalog.pg_statio_user_tables.relid=pg_catalog.pg_class.relfilenode';
$stmt = $pdo->query($sql);
var_dump($stmt->fetchAll());

連想配列（fetchAll()した結果）でテーブル一覧が取得できます。
ただし、作った覚えのないテーブルも含まれているので要注意。

ちなみに、MySQLだと簡単で

<?php
$sql = 'show tables';
$stmt = $pdo->query($sql);
var_dump($stmt->fetchAll());

です。

以上でぇぇぇす。

postgresqlのクエリログ（スロウログ）を取得

どうも、俺＠仕事中です。

以前にもpostgresqlのslow-log::オデの日記と題してpostgresqlでSlowログを取得する方法を書いたのですが、
この方法はsyslogを利用する方法だったので連携が難しいというか面倒というか、syslogの設定も変更する必要があったので、今回はpostgresqlだけでログを取得する方法についてメモします。

PostgreSQLのバージョンは8.4.xです。（8.3～であれば共通なはず）

# vim postgresql.conf
# 以下を追加

log_destination = 'stderr'
logging_collector = 'on'
# ログの出力先ディレクトリ（postgresユーザで書込み権限あるように）
log_directory = '/usr/local/pgsql/data'
# 出力ファイル名 (
log_filename = 'postgresql_%Y%m%d%H%M_log'
# ログが100KBになったらローテートする
log_rotation_size = 100
# ログを1日おきにローテート
#log_rotation_age = 86400
# 1000ミリ秒以上のクエリを出力（0の場合はすべてのクエリ）
log_min_duration_statement = 1000

です。

あとは

postgres $ pg_ctl restart

と再起動します。

以上どえぇぇぇす。

postgrsql.8.4.xでcreatedb実行時に文字コードエラー

どうも、俺＠残業中です。

postgresql.8.4系で

$ createdb hoge_db -E UTF8
createdb: database creation failed: ERROR: new encoding (UTF8) is incompatible with the encoding of the template database (EUC_JP)
HINT: Use the same encoding as in the template database, or use template0 as template.

のエラーが出ました。

これはinitdb時に文字コードをEUC-JPで作成したためUTF-8なデータベースが作成できないよ！というエラーです。ちなみにpostgresql8.4系から出るエラーだそうです。

こんな時は、「Use the same encoding as in the template database, or use template0 as template」しましょう。
つまり、template0のテンプレートデータベースを使ってcreatedbしましょう。

$ createdb hoge_db -E UTF8 -T template0

これで出来るはずです。

テンプレートデータベースとはなんじゃらほい？ですが、
PostgreSQL テンプレートデータベースに説明があります。
template1というデータベースはデータベース作成時に元となるテンプレートで、initdbなどした初期設定の情報などが入っています。
template0というデータベースは、初期設定のないテンプレートだそうです。


ちゃんちゃん。

PostgreSQL8.xで外部サーバから接続 めも

お久し、俺＠仕事中です。

postgreSQL8.4.1を使ってて少しハマったのでメモφ(｀д´)ﾒﾓﾒﾓ...

タイトルの通り、外部サーバ<192.168.0.11>から自サーバ<192.168.0.10>（ローカル接続）のpostgreSQLへ接続しようとしてできませんでした。

 $ telnet 192.168.0.10 5432
Trying 192.168.0.10...
telnet: connect to address 192.168.0.10: Connection refused

これはpostgreSQLのバージョン8以降からpostgresql.confに設定漏れがあったことが原因でした。

# vim $PGDATA/postgresql.conf
------------------------------------------
#listen_addresses = 'localhost'     # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost', '*' = all

これは、どのネットワークインターフェースアドレスで接続を許可するか、を設定する項目だそうです。
デフォルトでは「localhost」になっているので、自サーバからの接続のみ許可となっているので

listen_addresses = 'localhost,192.168.0.10'

とするとローカル接続の外部サーバから接続できます。
全て許可する場合は

listen_addresses = '*'

とします。

インターフェースのアドレスを確認するのは、おなじみの

# /sbin/ifconfig

でできますね。


以上でえええす。

PostgreSQLでコマンドラインから直接クエリ実行

おはようございます、俺＠朝礼後です。

今日はPostgreSQLでコマンドラインで直接クエリを実行する方法をメモります。
説明が難しいのですが、「コマンドラインから直接」というのは

$ psql foo_database
psql> SELECT * FROM bar;

というのではなく、
postgresqlサーバへログインせずにクエリ発行、結果取得という意味です。
（※内部的にはpostgresqlログインしてるのかもやけど、、、）


やり方は、

$ psql -c 'SELECT * FROM bar' foo_database

です。
ちなみに

$ psql -c 'SELECT * FROM bar' -o output_file foo_database

とやると、
output_fileへクエリ結果を出力します。

ついでにMySQLでは

$ mysql -e 'SELECT * FROM bar' foo_database

で直接クエリを実行できます。



以上どぇぇぇぇぇす。

postgresqlのslow-log

基本的なことなのかも知れませんが、postgresql用スローログの取得方法についてメモ。
僕のテストサーバーでのPostgresqlバージョンは7.4.12です。

１）postgresql.confの設定（ソースからインストールした場合は/usr/local/pgsql/data下にあります）

syslog = 2 # range 0-2; 0=stdout; 1=both; 2=syslog
log_min_duration_statement = 100 # milliseconds
syslog_facility = 'LOCAL0'　# この設定はなくてもOK?

を追加。（クエリ実行に100ミリ秒以上かかったクエリをsyslogへ出力。facilityはlocal0に設定）

２）/etc/syslog.confの設定

local0.* /var/log/postgres

を追加。（facility0を/var/log/postgresへログ出力）

*.info;mail.none;authpriv.none;cron.none /var/log/messages

をコメントアウトして

*.info;mail.none;authpriv.none;cron.none;local0.none /var/log/messages

を追加。（これがないと/var/log/messagesと/var/log/postgresの両方にログが出てしまう）

３）postgresqlとsyslogを再起動

# /sbin/service postgres reload
# /sbin/service syslog restart

以上で、実行に時間のかかるクエリのログが取得できます。
他にもpostgresql.confでいろいろ設定できるようなので、試してみる価値ありそうです。

これ試してみて、サーバーが重くなったら即時に設定を元に戻してpostgresqlとsyslogを再起動してください。

以上｡･ﾟ･(ﾉε`)･ﾟ･｡