Tihiroのストレスフリーな生活

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

PostgreSQL

パラメータを変更したのに変更されない(PostgreSQLのお話し)

概要 パラメータを変更したのに変わっていなかった。ということに関するお話し。 SETコマンド SETコマンドでパラメータを変更したのに、次に接続してみたらパラメータの変更が元に戻っていた。そんなお悩み、ありますよね。それはSETコマンドがそのセッショ…

VACUUM ANALYZE VERBOSEでエラーが発生する。

概要 いつも通りにVACUUM ANALYZE VERBOSEを実行してみたらエラーになった。ので調べてみた。けど原因は不明。 環境 PostgreSQL 11とか9とか色々 実行内容 コマンド VACUUM ANALYZE VERBOSE; これを実行すると エラー内容 ERROR: syntax error at or near "V…

PostgreSQLでデータの並び順を確認する。のと、並べる方法。

概要 基本的にデータベースではデータの並び順は保証されていません。データはインデックス通りに並んでいる訳でもなく、また、入れた順番に入っているわけでもなく、データベースの思うままに格納されています。そして勝手に並べ替えられたりすることもあっ…

PostgreSQLでテーブル指定でロックを調べるSELECT文

概要 テーブルの定義を変更しようとしたら、誰かがSELECT文を投げていて待たされる。そんな経験、誰もがお持ちですよね? そこでどんな処理でロックがかかっているのかが分かればストレスも多少は軽減されます。 環境 PostgreSQL 10です。 調べる SELECT loc…

PostgreSQLでテーブル一覧を取得する。

環境 多分、PostgreSQL 10ぐらい 取得する テーブルと外部テーブルとパーティションテーブル(親)をSELECTする。 SELECT nam.nspname , cls.relname , cls.relkind FROM pg_class cls INNER JOIN pg_namespace nam ON cls.relnamespace = nam.oid WHERE cls…

PostgreSQLで関数を確認したい。

いっつも忘れるのでメモ。 SELECT * FROM pg_proc 定義されているスキーマを知りたい場合は SELECT * FROM pg_proc INNER JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid でOK。

PostgreSQLに接続しようとしたらタイムアウトになる

逆に考えて繋がらなくってもいいさ、って考えられたらいいんですけど、そうも言っていられないのが現実。 タイムアウトの場合は大抵ファイアウォールが閉じていたりします。 firewall-cmd --all-list で該当のIPアドレスからの5432ポートへのTCP接続が空いて…

PostgreSQLでの権限エラーへの対応

概要 タイトルの通り、権限エラーが発生したときのお話です。 環境 PostgreSQL 10.3ぐらい 内容 user_nameっていうユーザで、新しく作成したデータベースにログインして SELECT * FROM table_name; ってすると ERROR: リレーション"table_name"は存在しませ…

PostgreSQLでスキーマに対する全ての権限をユーザーに設定するときの注意点

概要 いわゆる、GRANTさんの話。スキーマに対する権限を付与するときの注意点です。 環境 PostgreSQL 10.3ぐらい 内容 スキーマに対する全ての権限を付与しようと思って GRANT ALL ON SCHEMA schema_name TO role_name; としても、スキーマ内のテーブルへの…

OracleでいうTNSPINGみたいなのをPostgreSQLでいうとPG_ISREADY

OracleでいうTNSPINGみたいなのをPostgreSQLでいうとPG_ISREADYになります。 使い方は↓で pg_isready

pg_dumpとEXTENSION

概要 pg_dumpに-nをつけてスキーマ単位でのダンプを取得した際に、拡張機能がダンプに含まれないことについての脳内放出。 環境 PostgreSQL 10.5ぐらい 実際にやってみる 対象 項目 値 データベース名 test スキーマ名 public やってみた CREATE EXTENSION t…

pg_restoreのオプションで-dとか-cとか-Cとかが難しい。

概要 pg_restoreのオプションの挙動に理解が追いつかないので、脳内アウトプット。ので、内容が不正確な可能性が大いにあります。 環境 PostgreSQL 10.5ぐらい 難しいの -d オプション リストア先のデータベースの名前を指定する。 つまりは、-d db_nameとし…

PostgreSQLでのLENGTH関数で文字列の長さを測ってみる(空白は取り除かれちゃうよ)

概要 タイトルが全てを物語っているので多くは語りません。 環境 PostgreSQL 10.5ぐらい 測ってみた CREATE TABLE test_table ( val CHAR(12) ); っていうテーブルがあったとして INERT INTO test_table VALUES('test'); とした後に SELECT val, LENGTH(val)…

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…