文書の過去の版を表示しています。
目次
PostgreSQL
概要
読み方は、ポストグレス・キューエル。Ingresの次版(Post)としてPostgresとなった。QLはQuery Languageの略。
PostgreSQLは、追記型アーキテクチャを採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。
不要領域を再利用可能な状態にするには、VACUUMコマンドを使います。
将来的には追記型アーキテクチャを捨てて、EnterpriseDBが率先して開発している「zHeap」の実装して他のデータベースのようにUNDOログをもつようにしたい。
データベースとして,オープンソースとして,コミュニティとして ―石井達夫氏が語るPostgreSQLの強さと課題
接続ドライバー
PostgreSQL OLDDBドライバーは、遅い上にPostgreSQLのnumeric型フィールド値がうまく取得できないなど問題があるため、ODBCドライバーを使用すること。
ちなみに別のOLDDBドライバーであるPM PostgreSQL OLEDBを使用してみたが、速度も遅くClassic.ASP(VB)のCIntエラーが出るなど使えなかった。
OLDDBドライバーを使用してシステムでは、遅いということで調査したところ1つのSQLで10msの処理が100ms程度かかっていた。ODBCドライバーに変更したところ正常な速度となった。
ODBCドライバー設定
psqlodbc_09_00_0200.zip を解凍して、指示通りにインストールする。
管理ツールの「ODBCデータソースアドミニストレータ」にてシステムDNS
PostgreSQL35W PostgreSQL Unicode
サーバー名 localhost
データベース名 db_test
ユーザー名 fuga
パスワード hoge
接続文字列変更
test\Global.asa の接続をODBC用に変更する。
- Global.asa
'Session("ConnectionString") = "Provider=PostgreSQL OLE DB Provider;Data Source=localhost;Location=db_test;User ID=fuga;Password=hoge" ↓ Session("ConnectionString") = "DSN=PostgreSQL35W;Server=localhost;Database=db_test;UID=fuga;PWD=hoge;Port=5432;"
NPGSQL ver3にした場合
接続文字列の「Preload Reader=true」が不要になりました。あると例外エラーになる。
バージョン
PostgreSQL wiki
現在の9.6の最新版は9.6.17(リリース日:2020-02-13)
https://www.postgresql.org/docs/9.6/release-9-6-17.html
バージョン | リリース | 主な変更点 |
---|---|---|
9.2 | 2012/09/10 | インデックスオンリースキャン, カスケードレプリケーション, JSON型, 範囲型 |
9.3 | 2013/09/09 | マテリアライズドビュー, 外部テーブルへの書き出し, イベントトリガ, データページ・チェックサム, LATERAL句 |
9.4 | 2014/12/18 | JSONB型, SQLからのサーバー設定の変更(ALTER SYSTEM), レプリケーションスロット |
9.5 | 2016/01/07 | UPSERT機能, ALTER TABLE tablename ENABLE ROW LEVEL SECURITYコマンド, ブロックレンジインデックス(BRIN) |
9.6 | 2016/09/29 | 同期レプリケーション機能の強化(「remote_apply」モード), PostgreSQL間のデータ連携ドライバー(「postgres_fdw」)の強化(リモート下にあるサーバーにおいても実行可能となる |
10.0 | 2017/10/05 | postgresql10情報 |
11.0 | 2018/10/18 | postgresql11情報 |
12.0 | 2019/10/03 | postgresql12情報 |
Postgres10情報
2017-10-05にリリース
主な新機能
- IDカラム
行のIDを保存するカラム、一意かつ自動でカウントアップされます。 - ネイティブのパーティショニング機能
ひとつのテーブルの実体を分割して扱える - 複数列統計
Oracle11gで採用された複数列統計相当なもの - 並列性の強化
パラレルクエリの改善 - JSONとJSONBの全文検索
JSON型やJSONB型のカラムで全文検索をサポート - 論理レプリケーション
特定のテーブルの情報だけをレプリケーションする
参照
PostgreSQL11情報
2018-10-18にリリース
主な新機能
- JITコンパイルの追加 [性能]
- パーティショニングの様々な改善[機能+性能]
- 並列実行の様々な改善[性能]
- プロシージャ内でのトランザクション制御をサポート [機能]
- 認証でSCRAMチャンネルバインドをサポート[セキュリティ]
- ALTER TABLE .. ADD COLUMN の性能改善 [性能]
- ウィンドウ関数の拡張[機能]
PostgreSQL12情報
2019-10-03にリリース
主な新機能
PostgreSQL 12 プレスキット
「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる
PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など
- JSON Pathに対応
- 生成列(式で計算される列)に対応
- 各種インデックスの機能追加、性能改善
- パーティショニングの機能追加、性能改善
- テーブルアクセスメソッドに対応
設定について
他ホストから接続するための設定変更
コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。
他ホストから接続するための設定
- postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on; port = 5432 # (change requires restart)
※変更後は、PostgreSQLサービス再起動
接続できるクライアントを設定する
pg_hda.conf に設定追加する。
全てのクライアントPCからの接続を許可
- pg_hda.conf
# IPv4 local connections: host all all 0.0.0.0/0 md5
クライアントPCからの接続に制限を付ける
- pg_hda.conf
# IPv4 local connections: host all all 192.168.64.0/24 md5 host all all 192.168.71.0/24 md5
※変更後は、PostgreSQLサービス再起動は不要
※認証方式(https://www.postgresql.jp/document/9.4/html/auth-methods.html)
- trust
任意のロール名でパスワードなしで接続可能 - md5
パスワード認証
ログ出力設定変更
logging_collectorを「off」から「on」にして、pg_logフォルダにログ出力させる。
障害発生に備えて設定すべき3つのログ関連パラメーター
- postgresql.conf
logging_collector = on # Enable capturing of stderr and csvlog log_line_prefix='[%t] %u %d %p[%l] ' # special values:
※変更後は、PostgreSQLサービス再起動
- ログ出力例
[2019-04-25 14:37:08 JST] 7496[1] LOG: database system was shut down at 2019-04-25 14:37:06 JST [2019-04-25 14:37:08 JST] 7496[2] LOG: MultiXact member wraparound protections are now enabled [2019-04-25 14:37:08 JST] 6592[3] LOG: database system is ready to accept connections [2019-04-25 14:37:08 JST] 3240[1] LOG: autovacuum launcher started
log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a '
postgresql.conf エラーログの設定 Windows
log_line_prefix | ||
---|---|---|
エスケープ | 効果 | セッションのみ |
%a | アプリケーション名 | ○ |
%u | ユーザ名 | ○ |
%d | データベース名 | ○ |
%r | 遠隔ホスト名、またはIPアドレス、およびポート番号 | ○ |
%h | 遠隔ホスト名、またはIPアドレス | ○ |
%p | プロセス識別子 | × |
%t | ミリ秒無しのタイムスタンプ | × |
%m | ミリ秒付きタイムスタンプ | × |
%i | コマンドタグ。セッションの現在のコマンド種類 | ○ |
%e | SQLSTATE エラーコード | × |
%c | セッションID。下記参照 | × |
%l | 各セッションまたは各プロセスのログ行の番号。1から始まります。 | × |
%s | プロセスの開始タイムスタンプ | × |
%v | 仮想トランザクションID(backendID/localXID) | × |
%x | トランザクションID (未割り当ての場合は0) | × |
%q | 何も出力しません。 非セッションプロセスではこのエスケープ以降の出力を停止します。 セッションプロセスでは無視されます。 | × |
クライアントからの接続・切断を記録
ひとつのデータベースを複数のアプリケーションで参照する場合、以下の設定を行っておくと接続元のIPが記録されるため、どこからの接続が多いかがログから判断できます。→システム監査用途
デフォルトでは off となっています。
有効にする場合は on に設定してください。
- postgresql.conf
log_connections = on #log_disconnections = off
- ログ出力例
[2020-03-28 15:45:06 JST] [unknown] [unknown] 8512[1] LOG: connection received: host=10.55.3.3 port=62717 [2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG: connection authorized: user=postgres database=postgres
接続子の優先設定変更
postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。
- postgresql.conf 530
#search_path = '"$user", public' # schema names
search_pathに設定されている値の左から順に選択されるので「test」スキーマを自動的に選択するためには以下のように設定する。
- postgresql.conf 530
search_path = 'test,"$user", public' # schema names
psqlのパスワード入力省略
パスワード入力なしでpsqlを実行するには、下記フォルダの「pgpass.conf」にパスワードを設定しておく。 %APPDATA%\postgresql\pgpass.conf
- pgpass.conf
localhost:5432:*:postgres:(パスワード)
もう1つのやり方として、セキュリティ上は非推奨であるがバッチ内に環境変数(PGPASSWORD)を設定した上で実行する。
SET PGPASSWORD=(パスワード) psql -U (ユーザー) -f xxxxx.sql
psqlのスクリプトエラー無視
postgresqlの特性上、1度エラーが出ると以降のSQLは全てエラー(commit/rollbackするまで)になります。
エラーは無視して、OKなSQLだけ実行したい場合、ファイルの先頭に下記を記述する。
\set ON_ERROR_ROLLBACK on
エクスポート
全体
pg_dumpall
スキーマ単位
pg_dump -h ホスト名 -p ポート番号 DB名
リストア
pg_restoreは、pg_dumpによってアーカイブされた平文形式以外のアーカイブファイルを使って、PostgreSQLデータベースをリストアするためのユーティリティ
テーブル単位
tオプションを指定する。
pg_dump -t (テーブル名)
逆に除外する場合は、英大文字のTオプションを指定する。
pg_dump -T (テーブル名)
スキーマ単位
sオプションを指定する。
pg_dump -s (スキーマ名)
条件指定
COPYコマンドを使用する。
TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。
COPY (SELECT * FROM sample_table WHERE id > 10000) TO 'E:/work/tmp/sample_dump';
TOをFROMにするとインポートできる。
インポート
PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む。
その際には、UTF8 のテキストファイルは BOM 無しとする。
※BOM有りだと1行目でvarchar(n)で長すぎます。「先頭列名:データ」でエラーとなる。
- NULLは、CSVモードでは引用符のない空値を指定する。
- 空白をNULL扱いにする場合、「NULL AS ''」を追加する。
- 先頭行にヘッダーがある場合、「HEADER」を追加する。
- CSV側とPostgre側の列順が違う場合、列順を指定する。
- 引用符がデータ内に含まれている場合、データの中に発生しそうにないオプションでバックスペース(x08)を引用符として指定する。quote e'\x08'
// ファイル名は絶対パスで、'\', '/' または '\\' に置換する SET CLIENT_ENCODING TO 'UTF8'; DELETE FROM tablename; COPY tablename FROM pathName WITH CSV;
- カラム指定なしの例
copy tablename FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';
Oracleは後で列追加すると後ろに付く。PostgreSQLは最初から正しい順序でテーブルを作成した場合。
- カラム指定ありの例
copy tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';
注意点
null のところが空欄でセット
CSVによるデータ移行でCOPY句に「NULL AS ''」オプションを付けていたが、null のところが空欄でセットされてしまっていた。
原因は、単に文字列が空の場合に二重引用符で囲まれていたため。
対応は、空欄は二重引用符を除去する置換処理を追加する。
COPYで読み込みエラー
「読み取り用にオープンできませんでした: Permission denied」が出た場合、参照フォルダのプロパティのセキュリティタブにて“Network Service”アカウント、または、“Everyone”アカウントを追加する。
参照
高速化
インデックスを張らない状態でロードする
初期ロードの際には、インデックスを張ってからロードするよりも、張らずにロードして後から CREATE INDEX したほうが速い場合が多いようです。 このとき、主キー (PRIMARY KEY)、一意性制約 (UNIQUE)、排他制約 (EXCLUDE) も暗黙的にインデックスが追加されることに注意してください. 外部キーの追加もロード後のほうが良いでしょう。
バッチ化
-f オプションでファイル名を指定する。
psqlのパスワード入力省略
- xxxxx.bat
psql -U hostname -d databasename -f xxxxx.sql
- xxxxx.sql
DELETE FROM tablename; COPY tablename FROM pathname WITH encoding 'UTF8' CSV;
Tips
全テーブル件数の取得
統計情報から取得しているので、おおよその件数が取得できる。
統計情報を手動で行うコマンド「VACUUM ANALYZE」を実行してからなら、正確な件数が取得できる。
SELECT relname, n_live_tup FROM pg_stat_user_tables WHERE schemaname='wh_kousei'
文字列結合
SELECT句で Null文字列を含むカラムを連結すると、全体が Null になります。
これを回避するためには、カラムごとに Null の場合の置換を指定することで対処可能です。
[PostgreSQL] PostgreSQL の SELECT句で Null文字列を連結する方法
SELECT ok_column || '-' || COALESCE((null_column, '') FROM foo;
シーケンス作成
serial を使ったカラムの作成すると、自動でシーケンスが作成される。
serial ではないカラムで別途作成したシーケンスを適用するには、DEFAULTにシーケンスをセットする。
id INTEGER DEFAULT NEXTVAL('xxxxx_id_seq') NOT NULL,
データベース作成
デフォルトの設定が「C」となっている場合に違う照合順序「Japanese_Japan.932」に指定するには、「TEMPLATE = template0」を追記する。
- CreateDatabase.sql
CREATE DATABASE "FUGA" WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'Japanese_Japan.932' LC_CTYPE = 'Japanese_Japan.932' CONNECTION LIMIT = -1;
照合順序
照合順序については下記サイトを参照
運用としては、全て「C」で統一していく。
lc_collateとlc_ctypeは、デフォルトで「C」、lc_messages, lc_monetary, lc_numeric, lc_timeに関してはpostgresql.confファイル内で指定することができる。変更したらPostgresサービスを再起動して反映させる。
※弊害としては、pg_logに出力されるログのメッセージは日本語ではなく英語になる。
lc_collate | C |
---|---|
lc_ctype | C |
lc_messages | C |
lc_monetary | C |
lc_numeric | C |
lc_time | C |
- 現在設定確認SQL
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%'
- 照合順序確認SQL
WITH a AS ( SELECT * FROM generate_series(ascii('A'), ascii('Z')) ascii UNION ALL SELECT * FROM generate_series(ascii('A'), ascii('Z')) UNION ALL SELECT * FROM generate_series(ascii('a'), ascii('z')) ), b AS ( SELECT chr(ascii) FROM a ) SELECT array_agg(chr ORDER BY chr) FROM b ;
- 照合順序「C」(文字コード順)
{A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z} 文字コード順 は(清音) ば(濁音) ぱ(半濁音) ハ(清音) バ(濁音) パ(半濁音)
- 照合順序「Japanese_Japan.932」
{a,A,A,b,B,B,c,C,C,d,D,D,e,E,E,f,F,F,g,G,G,h,H,H,i,I,I,j,J,J,k,K,K,l,L,L,m,M,M,n,N,N,o,O,O,p,P,P,q,Q,Q,r,R,R,s,S,S,t,T,T,u,U,U,v,V,V,w,W,W,x,X,X,y,Y,Y,z,Z,Z} アイウエオ一つずつ「カタカナ → ひらがな」の順に並びます。 ハ(清音) は(清音) バ(濁音) ば(濁音) パ(半濁音) ぱ(半濁音)
他端末から接続できない場合
エラー「no pg_hba.conf entry for host <IPアドレス> … SSL off」などが出た場合
- Windowsファイアウォールで、Postgresのポート5432を許可するようにする。
- dataフォルダ(PostgreSQLフォルダ配下に無い場合、データ専用のドライブ側にある)の pg_hda.conf ファイルを修正する。
バッチからCOPYの絶対パスを渡す
COPYのFROM/TOは、絶対パスのみで相対パスは「relative path not allowed for COPY to file」エラーになる。
バインド変数として絶対パスを渡す。
- Test.bat
REM PostgreSQLのインストールパスのbinディレクトリ set PGPATH=D:\Program Files\PostgreSQL\9.6\bin\ psql -f "Test.sql" -v id='1234' -v inputpath="'E:/Work/Test/tmp/sample_dump'"
- Test.sql
SET CLIENT_ENCODING TO 'UTF8'; COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath;
その他
- postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。
- PostgreSQLの場合、テーブルのカラム名に予約語(例 “DO”)があるとそのままでは使えない。二重引用符“do”を付与すれば使える。
- PostgreSQLのCSVインポートする場合、COPY コマンドで取り込む UTF8 のテキストファイルは BOM 無しにする。
- 名前付きパラメータクエリーは、OLEDB版は使えた(プレースホルダ :xxxx)がODBC版では使えない。パラメータクエリーのプレースホルダは“?”である。
- DECODE文は無いため、CASE文に変更する必要がある。
- FROM句 or WHERE句内で使用する場合、PostgreSQLではエイリアスが必要となる。
- PostgreSQLの場合、SELECTで別名にしたカラム名をORDER BY句で指定できないため、一段上にSELECTを追加する必要があった。
- PostgreSQLの場合、1桁のvarchar型にシングルクォーテーションを付けないで指定するとエラーになる。修正例 AA=1 → AA='1'
- PostgreSQLの場合、Numeric型にシングルクォーテーションを付けて指定するとエラーになる。修正例 AA='1' → AA=1
- PostgreSQLの場合、MAX値取得でORDER BY句があるとエラーになる。そもそもORDER BY句が不要。
- FORMAT関数ではパーセント(%)をエスケープで2つ(%%)にする必要がある。
- PostgreSQLの場合、日付型を文字型にした場合に、例「ERROR: 日付が範囲外です: “20150406”」となる。
修正例 TO_DATE(MAX(DE),'YYYY/MM/DD') AS DE FROM TEST → TO_CHAR(TO_DATE(MAX(DE),'YYYYMMDD'), 'YYYY/MM/DD') AS DE FROM TEST - FROMとテーブル名の区切りが全角空白だとエラーになるので半角空白にする。
エラーコード一覧
パスワードは英大小文字を区別する。
例 28P01:invalid_password