ユーザ用ツール

サイト用ツール


サイドバー

サイドバー

スタートページ

it技術:データベース:postgresql


PostgreSQL

概要

読み方は、ポストグレス・キューエル。Ingresの次版(Post)としてPostgresとなった。QLはQuery Languageの略。

PostgreSQLは、追記型アーキテクチャを採用している。データの変更があっても元のレコードを物理的に消さずに、新しい行を追加して、元のレコードを無効マークとします。
不要領域を再利用可能な状態にするには、VACUUMコマンドを使います。

PostgreSQLのお勉強

将来的には追記型アーキテクチャを捨てて、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.22012/09/10インデックスオンリースキャン, カスケードレプリケーション, JSON型, 範囲型
9.32013/09/09マテリアライズドビュー, 外部テーブルへの書き出し, イベントトリガ, データページ・チェックサム, LATERAL句
9.42014/12/18JSONB型, SQLからのサーバー設定の変更(ALTER SYSTEM), レプリケーションスロット
9.52016/01/07UPSERT機能, ALTER TABLE tablename ENABLE ROW LEVEL SECURITYコマンド, ブロックレンジインデックス(BRIN)
9.62016/09/29同期レプリケーション機能の強化(「remote_apply」モード), PostgreSQL間のデータ連携ドライバー(「postgres_fdw」)の強化(リモート下にあるサーバーにおいても実行可能となる
10.02017/10/05postgresql10情報
11.02018/10/18postgresql11情報
12.02019/10/03postgresql12情報

Postgres10情報

2017-10-05にリリース

主な新機能

  1. IDカラム
    行のIDを保存するカラム、一意かつ自動でカウントアップされます。
  2. ネイティブのパーティショニング機能
    ひとつのテーブルの実体を分割して扱える
  3. 複数列統計
    Oracle11gで採用された複数列統計相当なもの
  4. 並列性の強化
    パラレルクエリの改善
  5. JSONとJSONBの全文検索
    JSON型やJSONB型のカラムで全文検索をサポート
  6. 論理レプリケーション
    特定のテーブルの情報だけをレプリケーションする

参照

PostgreSQL11情報

2018-10-18にリリース

主な新機能

PostgreSQL 11 検証報告

  • JITコンパイルの追加 [性能]
  • パーティショニングの様々な改善[機能+性能]
  • 並列実行の様々な改善[性能]
  • プロシージャ内でのトランザクション制御をサポート [機能]
  • 認証でSCRAMチャンネルバインドをサポート[セキュリティ]
  • ALTER TABLE .. ADD COLUMN の性能改善 [性能]
  • ウィンドウ関数の拡張[機能]

PostgreSQL12情報

2019-10-03にリリース

主な新機能

PostgreSQL 12 プレスキット
「PostgreSQL 12」リリース、性能面でさまざまな強化が行われる
PostgreSQL 12が正式リリース。Bツリー周りの改善による性能向上、JSONパスによる抽出可能など

  • JSON Pathに対応
  • 生成列(式で計算される列)に対応
  • 各種インデックスの機能追加、性能改善
  • パーティショニングの機能追加、性能改善
  • テーブルアクセスメソッドに対応

設定について

postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。

-- postgresユーザーで実行
SELECT name, context FROM pg_settings;
設定反映のタイミング
context説明
不可(internal)ユーザー設定変更が出来ない
起動(postmaster)PostgreSQLの再起動で反映される
再読み込み(signup)pg_ctl reload でも反映できる
スーパーユーザーのみ(suset)スーパーユーザーによるSET文でも反映できる
いつでも(user)一般ユーザーによるSET文でも反映できる

サーバー上の管理者権限で「pg_ctl reload」コマンドでpostgresql.confの設定値を反映

>pg_ctl reload -D "F:\Program Files\PostgreSQL\9.6\data"
サーバにシグナルを送信しました

他ホストから接続するための設定変更

コメントを外す。「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サービス再起動またはpg_ctl reloadで反映

ログ出力例
[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

postgresql.conf エラーログの設定 Windowsの場合

log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a '
log_line_prefix
エスケープ効果セッションのみ
%aアプリケーション名 例 psql または [unknown]
%uユーザ名
%dデータベース名
%r遠隔ホスト名、またはIPアドレス、およびポート番号
%h遠隔ホスト名、またはIPアドレス
%pプロセス識別子×
%tミリ秒無しのタイムスタンプ×
%mミリ秒付きタイムスタンプ×
%iコマンドタグ。セッションの現在のコマンド種類
%eSQLSTATE エラーコード×
%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だけ実行したい場合、ファイルの先頭に下記を記述する。

※COPY句のCSV登録時のエラーには関係しない

\set ON_ERROR_ROLLBACK on

ビュー定義

自動変換

ビュー作成時に整形したりコメントを付けたとしても、ビューに登録された段階で自動変換されて型変換が付いたり整形が崩れたりコメントが消去されてしまう。

型変換エラー

ビューで「cannot change data type of view column」で間違っていないのに登録できない場合、ビューを削除して再作成すると登録できるようになる。

ユーザ定義関数

動的クエリ

「EXECUTE sql;」として、動的クエリを実行させる。また、「RETURN QUERY」としてテーブルを返す。
PostgreSQL めも - ストアドプロシージャ

SQLの文字列の値セットにformat関数で指定している。
9.4.1. format
format指示子の出力を生成するのに使用されるフォーマット変換の型。 以下の型がサポートされています。

  • 「%s」は引数の値を単純文字列にフォーマットします。 NULL値は空文字列として扱われます。
  • 「%I」は、必要とされれば二重括弧で括られたSQL識別子として引数値を取り扱います。 NULL値はエラーです。
  • 「%L」は引数値をSQLリテラルとして引用します。NULL値は引用符無しでNULL文字列として表示されます。
CREATE OR REPLACE FUNCTION wiremodule(_carline TEXT, _part TEXT, _module TEXT) 
RETURNS TABLE(drawingno text, wirename text) AS $$
DECLARE
  i INTEGER;
  SQL TEXT;
  cat TEXT; 
BEGIN
  SQL := format('SELECT drawingno::text, wirename::text FROM wire WHERE carline=%L AND part=%L AND (' , _carline, _part);
 
  cat := '';
  FOR i IN 1..4 LOOP
     SQL := SQL || cat || format('functionmodule%s=%L', i , _module);
     cat := ' OR ';
  END LOOP;
  SQL := SQL || ')';
 
  RETURN QUERY EXECUTE SQL;
 
END;
$$ LANGUAGE 'plpgsql';

デバッグ

デバッガ画面でトレースは出来るが、不安定である(ステップアウトで応答が返らない)。

従来のメッセージ出力によるデバッグ方法もある。

エクスポート

全体

pg_dumpall

スキーマ単位

pg_dump -h ホスト名 -p ポート番号 DB名

リストア

pg_restoreは、pg_dumpによってアーカイブされた平文形式以外のアーカイブファイルを使って、PostgreSQLデータベースをリストアするためのユーティリティ

テーブル単位

tオプションを指定する。
pg_dump -t (テーブル名)

pg_dumpで複数のテーブルのダンプを取る方法

逆に除外する場合は、英大文字の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句でFORCE_NULLを指定すれば、2重引用符のみをNULLで登録できる。 https://www.postgresql.jp/document/11/html/sql-copy.html

WITHの書き方をこれまでと変える必要があるのと、FORCE_NULLの時にカラムを指定する必要がある。

\COPY tablename(col1,col3,col4,col2) FROM pathName WITH encoding 'UTF8' CSV HEADER NULL AS '';
                                                     ↓
\COPY tablename(col1,col3,col4,col2) FROM pathName WITH(ENCODING 'UTF8',FORMAT CSV,HEADER ON,NULL '',FORCE_NULL(col1,col3,col4,col2));

COPYで読み込みエラー

「読み取り用にオープンできませんでした: Permission denied」が出た場合、参照フォルダのプロパティのセキュリティタブにて“Network Service”アカウント、または、“Everyone”アカウントを追加する。

参照

高速化

インデックスを張らない状態でロードする
初期ロードの際には、インデックスを張ってからロードするよりも、張らずにロードして後から CREATE INDEX したほうが速い場合が多いようです。 このとき、主キー (PRIMARY KEY)、一意性制約 (UNIQUE)、排他制約 (EXCLUDE) も暗黙的にインデックスが追加されることに注意してください. 外部キーの追加もロード後のほうが良いでしょう。

バッチ化

-f オプションでファイル名を指定する。
psqlのパスワード入力省略

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='test'

PostgreSQLでテーブルの行数を速く取得する方法

文字列結合

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,

[PostgreSQL]serial と 手動でのシーケンス作成の違い

データベース作成

デフォルトの設定が「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_collateC
lc_ctypeC
lc_messagesC
lc_monetaryC
lc_numericC
lc_timeC
現在設定確認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

PostgreSQLエラーコード

参照


it技術/データベース/postgresql.txt · 最終更新: 2020/05/21 15:15 by yajuadmin