Tihiroの頭を休めるIT教室

少しだけ頭使って後は根性

PostgreSQL

PG_DUMPの圧縮具合

概要 お客さんとお話ししていて、たまに話題になるのが、圧縮するとどれぐらいサイズ減りますかね? っていうこと。なので記録として残しておく。 環境 PostgreSQL 10.2 計測 コマンド pg_dump -Fc postgres > pg.dmp 結果 圧縮前 圧縮後 圧縮率 874MB 52MB …

pg_dumpのCオプションとリストア先データベースの関係

概要 pg_dumpのCオプションについて、調べたメモ 環境 PostgreSQL 10.3 調べたこと dumpの取得 pg_dump -C -U postgres target_db_1 > target_db_1.dmp dumpのリストア psql -U postgres < target_db_1.dmp -> target_db_1に格納される。 psql -U postgres t…

PostgreSQLでログイン中のユーザーを切り替える。

概要 PostgreSQLで、例えばparentという名前のユーザー(ロール?)でログインしている最中に、childというユーザーに切り替えたい。 環境 PostgreSQL 10.3 ※そろそろ10.5にしたい。 変更 SET ROLE child でOK。 確認 SELECT CURRENT_USER; で確認。 使い所 …

リモートホスト上に稼働しているPostgreSQLに対してpg_dumpする。

したいこと リモートホストのPostgreSQLからデータを引っこ抜きたい。 環境 PostgreSQL 10.3 コマンド 自端末から pg_dump -h xxx.xxx.xxx.xxx -U user_name -d database_name > output_dir でOK。-hにリモートホストのIPアドレスを指定する。 ちなみに-Fcオ…

SQLでびっくりしたこと。

概要 タイトルのまんまです。SQLでびっくりしたことです。 環境 PostgreSQL 10.3 びっくりしたこと ORDER BYでNULLの順序を指定できる。 SELECT code, name FROM data_table ORDER BY code NULLS LAST; とかでNULL値のソート順を最後尾にできる。FIRSTを指定…

PostgreSQLでUpdateした結果を確認したい。

概要 タイトルの通り、PostgreSQLでUpdate文を実行した結果を確認したいときのお話です。UPDATE文にRETURNING句を追加することで確認できます。 環境 PostgreSQL 10.3 コード UPDATE test_table SET col_a = 10 WHERE col_key = 999 RETURNING *; ※ RETURNIN…

PostgreSQLの結合のコスト

概要 PostgreSQLの結合処理に必要なコストのお話です。文章力がないのですごく分かりにくいです。すみません。 環境 PostgreSQL 10 結合処理 PostgreSQLでは3つの結合処理がサポートされています。ジョインジョインジョイン。 NestedLoop 2つのテーブルを…

cache lookup failed for relation xxxxでPostgreSQLが起動しない。

概要 PostgreSQLを起動させようと思ったら [5552] FATAL: cache lookup failed for relation 6100 みたいなエラーがでて起動しない。悲しい。 環境 PostgreSQL 10.3 あっ、これOSS-DBでやったやつだ! シングルユーザーモードで起動する。 postgres --single…

PostgreSQLの文字コードを確認する方法。

メタコマンド を使う。 \l で、OK。

PostgreSQLでテーブル名から実ファイルのパスを特定したい。

概要 タイトルの通りなのですが、テーブル名から実ファイルのパスを確認したいときとかってありますよね。知的好奇心を満たしたいときとか。そんなときにはpg_relation_filepath関数を使うわけであります。 環境 PostgreSQL 10.3 やってみる SELECT pg_relat…

PostgreSQLでTruncateのあとにInsertしてSelectする前にAnalyzeする。

伝えたいこと データの入れ替えなんかで、既存のデータをTruncateしてInsertした後は、Selectする前にAnalyzeしましょうというお話。 なぜかというと Truncateによって統計情報がクリアされるから。統計情報がクリアされた状態でSelectしても正しい実行計画…

PostgreSQLでテーブルサイズを確認する。

概要 PostgreSQLですとテーブルにサイズ制限をかけれなかったりします。なので、あまり肥大化しないようにチェックしておく必要があります。AUTOVACUUMがあるから安全だぜ、って考えでもまぁアリっちゃアリなのかもしれません。 確認コマンド メタコマンドに…

テーブルごとにAUTO_VACUUMおよびAUTO_ANALYZEをする閾値を設定する。

概要 PostgreSQLではautovacuumとかautoanalyzeについて、決められた閾値を超えた場合に実行されるという仕組みになっています。 その閾値をテーブルごとに設定してみましょう。というお話です。 確認したのは PostgreSQL 10.3 です。 閾値と計算式 autovacu…

PostgreSQLのロジカルデコーディング

第48章 ロジカルデコーディング ずっと、ロジカルコーディングだと思っていた。ロジカルでコーディング・・。

PostgreSQLをソースコードからビルドとインストール。

概要 PostgreSQLをデバッグしたくなったので、ソースコードからビルドしてインストールして起動してみた。 第17章 Windowsにおけるソースコードからのインストール ↑を参考にした。 環境 PostgreSQL 10.3 Windows 7 VisualStudioExpress 2017 Active State P…

psqlでCTRL+CするとPostgreSQLが落ちる。

概要 psqlで接続中のPostgreSQLから抜けようと、CTRL+CしたらPostgreSQLごと終了した。 環境 PostgreSQL 10.3 Windows7 SP1 内容 ERROR: canceling statement due to user request LOG: received fast shutdown request LOG: aborting any active transactio…

PG_MODULE_MAGICを記述しているのにPG_MODULE_MAGICが見つからない。というエラー。

概要 PostgreSQLの拡張ライブラリを自作した時のお話。 Cプログラムのソース内に「PG_MODULE_MAGIC」を記述しているのに、記述が見つからないとエラーが出る。 環境 PostgreSQL 10.3 内容 PostgreSQLサーバー起動時に サーバの起動完了を待っています.... FA…

PostgreSQLでヒット率が知りたい。

ヒット率を確認するSELECT文 テーブル SELECT relname AS "テーブル名" , CASE WHEN heap_blks_hit + heap_blks_read = 0 THEN 0 ELSE heap_blks_hit * 100 / (heap_blks_hit + heap_blks_read) END AS "ヒット率" FROM pg_statio_user_tables ORDER BY reln…

第1回PGECons勉強会の振り返り。

概要 PostgreSQL エンタープライズ・コンソーシアム : 2017年11月17日 第1回PGECons勉強会 に参加してきましたので、その振り返りです。が、時間が経つと結構うろ覚えになっていました。細かいところは結構違うかもなのでご注意くださいませ。 ツイッターの…

関数実行時にnextval(sequence)がエラーとなる。

概要 PL/pgSQLの関数を実行しようとした際に発生するエラーについて。 環境は PostgreSQL 9.6 です。 エラー内容 ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: P…

PostgreSQLのUPDATE文でWITH句とFROM句が使えることにびっくりした。

概要 タイトルの通りなのですが、PostgreSQLではUPDATE文でWITH句とFROM句が使える模様。 Oracleでは無理だと思います。 環境は PostgreSQL 9.6 です。 実践 WITH with_table AS (SELECT 1 AS no) UPDATE update_table SET update_data = 'xxx' FROM with_ta…

PostgreSQLでテーブルの制約を確認する。

概要 前回(PostgreSQLでテーブルの定義を確認する。 - Tihiroの頭を休めるIT教室)はPostgreSQLでテーブル定義を確認する、ということでした。 今回は、テーブルの制約を確認したいと思います。 例によって環境は PostgreSQL9.6 です。

PostgreSQLでテーブルの定義を確認する。

概要 タイトルの通りなのですが、PostgreSQLでテーブルの定義を確認する方法です。 要するにOracleでいうDESCコマンドみたいなのを実行したいわけなんです。 主キー(プライマリーキー)とか確認したい場合は↓で。 PostgreSQLでテーブルの制約を確認する。 -…

CREATE EXTENSION dblinkに失敗するので解決した。

概要 タイトルの通り CREATE EXTENSION dblink; の実行が ERROR: could not access file "$libdir/dblink": No such file or directory みたいなエラーで失敗する。ときの解決方法です。 環境は PostgreSQL9.6.3 CentOS7 です。

PostgreSQLで実行中のSQLを確認する方法について。

概要 タイトルの通り、PostgreSQLで実行中のSQLを確認する方法についてです。 SELECT文を使って、実行中のSQLと状態を確認してみます。 対象はPostgreSQL9.6です。

PostgreSQLのデッドロックについて。

概要 PostgreSQLのデッドロックについての解説記事です。 行レベルロック、テーブルレベルロックと解説しましたので、ついでというわけではないですがデッドロックについても少しだけ。 検証したバージョンはPostgreSQL9.6.3です。

PostgreSQLのテーブルレベルロックについて

概要 PostgreSQL 9.6のお話です。 前回の行レベルロックの解説(PostgreSQLの行レベルロックについて) に引き続き、テーブルレベルロック(表レベルロック)についてのお話です。 ロックの使い方を間違えるとデッドロック多発なんてことになりますので、しっ…

PostgreSQLの行レベルロックについて

記事の概要 PostgreSQL 9.6のお話です。 PostgreSQLでは(でも?)テーブル内のデータへの同時アクセスを制御するため、テーブルレベルロック(表レベルロック)と行レベルロックという仕組みがあります。 今回は、行レベルロックについて確認したことを解説…

PostgreSQLのテーブルに対応するデータファイルパスをSELECT文で確認する方法について

記事の概要 PostgreSQL 9.6のお話です。 現在のデータベース内に存在するテーブルのデータファイルがどこに格納されているかを確認する方法を解説します。 残念ながら全てのデータベース内のテーブルに対する方法は見つけきれませんでした。

PostgreSQLのデータベースクラスタ(ファイルやディレクトリ)のお話し

記事の概要 PostgreSQLのデータファイルや、自動的に作成されるディレクトリの解説です。 要は、PostgreSQLの「データベースクラスタ」と呼ばれるものについての解説です。 利用バージョンはPostgreSQL 9.6.3。 「データベースクラスタ」以外の解説について…