ユーザ用ツール

サイト用ツール


サイドバー

サイドバー

スタートページ

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何も出力しません。 非セッションプロセスではこのエスケープ以降の出力を停止します。 セッションプロセスでは無視されます。×

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