ユーザ用ツール

サイト用ツール


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

設定について

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

コメントを外す。「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コマンドタグ。セッションの現在のコマンド種類
%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だけ実行したい場合、ファイルの先頭に下記を記述する。

\set ON_ERROR_ROLLBACK on

エクスポート

全体

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で読み込みエラー

「読み取り用にオープンできませんでした: 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='wh_kousei'

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.1585453854.txt.gz · 最終更新: 2020/03/29 12:50 by yajuadmin