it技術:データベース:postgresql
差分
このページの2つのバージョン間の差分を表示します。
両方とも前のリビジョン前のリビジョン次のリビジョン | 前のリビジョン次のリビジョン両方とも次のリビジョン | ||
it技術:データベース:postgresql [2020/03/29 12:50] – [ログ出力設定変更] yajuadmin | it技術:データベース:postgresql [2021/07/29 16:49] – [Postgres10情報] yajuadmin | ||
---|---|---|---|
行 54: | 行 54: | ||
|11.0|2018/ | |11.0|2018/ | ||
|12.0|2019/ | |12.0|2019/ | ||
+ | |13.0|2020/ | ||
+ | |||
+ | ==== サポート終了期限(EOL=End Of Life) ==== | ||
+ | ^バージョン^初期リリース日^サポート終了期限^ | ||
+ | |13|2020年09月|2025年11月| | ||
+ | |12|2019年10月|2024年11月| | ||
+ | |11|2018年10月|2023年11月| | ||
+ | |10|2017年10月|2022年11月| | ||
+ | |9.6|2016年09月|2021年11月| | ||
==== Postgres10情報 ==== | ==== Postgres10情報 ==== | ||
2017-10-05にリリース | 2017-10-05にリリース | ||
行 64: | 行 73: | ||
- JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート | - JSONとJSONBの全文検索 \\ JSON型やJSONB型のカラムで全文検索をサポート | ||
- 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする | - 論理レプリケーション \\ 特定のテーブルの情報だけをレプリケーションする | ||
+ | |||
+ | === 名称変更 === | ||
+ | PostgreSQL10ではいくつかのディレクトリ名や関数名などが変更されています。メンテナンス系のシェルスクリプトや監視ツールで下記ディレクトリ名や関数名などをハードコードされている場合は、PostgreSQL10以降の名称に修正する必要があります。 | ||
+ | |||
+ | * ログファイル出力先のディレクトリ名がpg_logからlogへ変更 | ||
+ | * WALに関連するディレクトリ/関数/コマンドなどでxlogがwal、locationがlsnに変更 | ||
+ | * コミットログの出力先ディレクトリ名がpg_clogからpg_xactへ変更 | ||
+ | |||
+ | ^PostgreSQL10以降で変更されたディレクトリ/関数名などの例^^ | ||
+ | ^PostgreSQL9.6以前の名称^PostgreSQL10以降の名称^ | ||
+ | |pg_log|log| | ||
+ | |pg_xlog|pg_wal| | ||
+ | |pg_clog|pg_xact| | ||
+ | |pg_current_xlog_location|pg_current_wal_lsn| | ||
+ | |pg_xlogdump|pg_waldump| | ||
+ | |pg_receivexlog|pg_receivewal| | ||
+ | |||
=== 参照 === | === 参照 === | ||
行 92: | 行 118: | ||
[[https:// | [[https:// | ||
[[https:// | [[https:// | ||
- | [[https:// | + | [[https:// |
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
行 100: | 行 129: | ||
* パーティショニングの機能追加、性能改善 | * パーティショニングの機能追加、性能改善 | ||
* テーブルアクセスメソッドに対応 | * テーブルアクセスメソッドに対応 | ||
+ | * REINDEX CONCURRENTLYが追加、重いロックなしでインデックスを再構築可能 | ||
+ | === 注意 === | ||
+ | * WITH OIDが使えなくなった。WITH OIDSがあるテーブルがあるとpg_upgradeが実行できない | ||
+ | * recovery.confはなくなりpostgresql.confに統合 | ||
+ | |||
+ | === 対処方法 === | ||
+ | [[https:// | ||
+ | |||
+ | * WITH OIDSを使わなくても良い設計を検討する | ||
+ | * ALTER TABLE ... SET WITHOUT OIDSでOIDSを取り除く | ||
+ | * recovery.confに書いていたパラメータをpostgresql.confに書くようにする | ||
+ | * リカバリ時はrecovery.signal、スタンバイ時はstandby.signal を置くようにする | ||
+ | * recovery_target_XXXは設定ファイルに必ず一つになるようにすること | ||
+ | ==== PostgreSQL13情報 ==== | ||
+ | 2020-09-24にリリース | ||
+ | |||
+ | === 主な新機能 === | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | [[https:// | ||
+ | |||
+ | * B-Treeインデックスの性能向上 | ||
+ | * 新しい実行プラン | ||
+ | * パーティションテーブル機能の拡張 | ||
+ | * パラレルVACUUM | ||
+ | * 進捗レポートビューの拡張 | ||
+ | * pgbenchの拡張 | ||
+ | * pg_rewindの拡張 | ||
+ | * SQL機能の追加 | ||
===== 設定について ===== | ===== 設定について ===== | ||
+ | postgresql.confの設定値は、下記SQLで context の値により設定反映のタイミングが違う。 | ||
+ | <code sql> | ||
+ | -- postgresユーザーで実行 | ||
+ | select name, context from pg_settings; | ||
+ | </ | ||
+ | |||
+ | ^設定反映のタイミング^^ | ||
+ | ^context^説明^ | ||
+ | |不可(internal)|ユーザー設定変更が出来ない| | ||
+ | |起動(postmaster)|PostgreSQLの再起動で反映される| | ||
+ | |再読み込み(signup)|pg_ctl reload でも反映できる| | ||
+ | |スーパーユーザーのみ(suset)|スーパーユーザーによるSET文でも反映できる| | ||
+ | |いつでも(user)|一般ユーザーによるSET文でも反映できる| | ||
+ | |||
+ | サーバー上の管理者権限で「pg_ctl reload」コマンドでpostgresql.confの設定値を反映 | ||
+ | <code bash> | ||
+ | >pg_ctl reload -D " | ||
+ | サーバにシグナルを送信しました | ||
+ | </ | ||
+ | |||
+ | |||
==== 他ホストから接続するための設定変更 ==== | ==== 他ホストから接続するための設定変更 ==== | ||
コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。\\ | コメントを外す。「localhost」から「*」に変更する。これをしないとlocalhostしか接続できない。\\ | ||
行 141: | 行 224: | ||
log_line_prefix=' | log_line_prefix=' | ||
</ | </ | ||
- | ※変更後は、PostgreSQLサービス再起動 | + | ※変更後は、PostgreSQLサービス再起動またはpg_ctl reloadで反映 |
行 150: | 行 233: | ||
[2019-04-25 14:37:08 JST] 3240[1] LOG: autovacuum launcher started | [2019-04-25 14:37:08 JST] 3240[1] LOG: autovacuum launcher started | ||
</ | </ | ||
+ | |||
+ | [[https:// | ||
< | < | ||
log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a ' | log_line_prefix = '%e: %t [%p]: [%l-1] user = %u,db =%d,remote = %r app = %a ' | ||
</ | </ | ||
- | |||
- | [[https:// | ||
^log_line_prefix^^^ | ^log_line_prefix^^^ | ||
^エスケープ^効果^セッションのみ^ | ^エスケープ^効果^セッションのみ^ | ||
- | |%a|アプリケーション名|○| | + | |%a|アプリケーション名 |
|%u|ユーザ名|○| | |%u|ユーザ名|○| | ||
|%d|データベース名|○| | |%d|データベース名|○| | ||
行 191: | 行 274: | ||
[2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG: connection authorized: user=postgres database=postgres | [2020-03-28 15:45:06 JST] postgres postgres 8512[2] LOG: connection authorized: user=postgres database=postgres | ||
</ | </ | ||
+ | |||
+ | === 実行されたクエリを記録 === | ||
+ | 調査の時に行うくらい。実行するSQLが多いので運用時はコメントアウトにしている。\\ | ||
+ | 但し、バインド変数の値までは取れない。 | ||
+ | |||
+ | <code .conf postgresql.conf> | ||
+ | log_statement = ‘all’ | ||
+ | </ | ||
+ | |||
+ | [[https:// | ||
==== 接続子の優先設定変更 ==== | ==== 接続子の優先設定変更 ==== | ||
postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。 | postgresql.confの下記設定を変更することによって、スキーマ名未設定でSQL実行時に選択されるスキーマの優先度を変更できる。 | ||
行 218: | 行 311: | ||
postgresqlの特性上、1度エラーが出ると以降のSQLは全てエラー(commit/ | postgresqlの特性上、1度エラーが出ると以降のSQLは全てエラー(commit/ | ||
エラーは無視して、OKなSQLだけ実行したい場合、ファイルの先頭に下記を記述する。 | エラーは無視して、OKなSQLだけ実行したい場合、ファイルの先頭に下記を記述する。 | ||
+ | |||
+ | <wrap em> | ||
< | < | ||
行 223: | 行 318: | ||
</ | </ | ||
+ | * [[https:// | ||
* [[https:// | * [[https:// | ||
* [[https:// | * [[https:// | ||
+ | ===== ビュー定義 ===== | ||
+ | ==== 自動変換 ==== | ||
+ | ビュー作成時に整形したりコメントを付けたとしても、ビューに登録された段階で自動変換されて型変換が付いたり整形が崩れたりコメントが消去されてしまう。 | ||
+ | |||
+ | ==== 型変換エラー ==== | ||
+ | ビューで「cannot change data type of view column」で間違っていないのに登録できない場合、ビューを削除して再作成すると登録できるようになる。 | ||
+ | |||
+ | ===== ユーザ定義関数 ===== | ||
+ | ==== 動的クエリ ==== | ||
+ | 「EXECUTE sql; | ||
+ | [[http:// | ||
+ | |||
+ | SQLの文字列の値セットにformat関数で指定している。\\ | ||
+ | [[https:// | ||
+ | format指示子の出力を生成するのに使用されるフォーマット変換の型。 以下の型がサポートされています。 | ||
+ | * 「%s」は引数の値を単純文字列にフォーマットします。 NULL値は空文字列として扱われます。 | ||
+ | * 「%I」は、必要とされれば二重括弧で括られたSQL識別子として引数値を取り扱います。 NULL値はエラーです。 | ||
+ | * 「%L」は引数値をSQLリテラルとして引用します。NULL値は引用符無しでNULL文字列として表示されます。 | ||
+ | |||
+ | <code sql> | ||
+ | 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(' | ||
+ | |||
+ | cat := ''; | ||
+ | FOR i IN 1..4 LOOP | ||
+ | sql := sql || cat || format(' | ||
+ | cat := ' OR '; | ||
+ | END LOOP; | ||
+ | sql := sql || ' | ||
+ | |||
+ | RETURN QUERY EXECUTE sql; | ||
+ | |||
+ | END; | ||
+ | $$ LANGUAGE ' | ||
+ | </ | ||
+ | |||
+ | ==== デバッグ ==== | ||
+ | デバッガ画面でトレースは出来るが、不安定である(ステップアウトで応答が返らない)。\\ | ||
+ | * [[http:// | ||
+ | * [[http:// | ||
+ | 従来のメッセージ出力によるデバッグ方法もある。 | ||
+ | * [[http:// | ||
===== エクスポート ===== | ===== エクスポート ===== | ||
行 299: | 行 443: | ||
対応は、空欄は二重引用符を除去する置換処理を追加する。 | 対応は、空欄は二重引用符を除去する置換処理を追加する。 | ||
+ | COPY句でFORCE_NULLを指定すれば、2重引用符のみをNULLで登録できる。 | ||
+ | https:// | ||
+ | |||
+ | WITHの書き方をこれまでと変える必要があるのと、FORCE_NULLの時にカラムを指定する必要がある。 | ||
+ | |||
+ | <code sql> | ||
+ | \COPY tablename(col1, | ||
+ | ↓ | ||
+ | \COPY tablename(col1, | ||
+ | </ | ||
=== COPYで読み込みエラー === | === COPYで読み込みエラー === | ||
「読み取り用にオープンできませんでした: | 「読み取り用にオープンできませんでした: | ||
行 339: | 行 493: | ||
<code sql> | <code sql> | ||
- | select relname, n_live_tup from pg_stat_user_tables where schemaname=' | + | select relname, n_live_tup from pg_stat_user_tables where schemaname=' |
</ | </ | ||
[[http:// | [[http:// | ||
行 456: | 行 610: | ||
COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath; | COPY (SELECT * FROM fuga WHERE id = :id) TO :inputpath; | ||
</ | </ | ||
+ | ==== パフォーマンス向上 ==== | ||
+ | * [[https:// | ||
+ | * [[https:// | ||
+ | |||
==== その他 ==== | ==== その他 ==== | ||
* postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。 | * postgresql.confのlc_messagesを「Japanese_Japan.932」から「C」にすることでエラーメッセージが文字化けしなくなる。 |
it技術/データベース/postgresql.txt · 最終更新: 2024/04/24 16:37 by yajuadmin