PostgreSQL
概要 いつも通りタイトルの通りなのですが、PostgreSQLで現在のトランザクションID、つまりはxidを確認する方法についてす。 確認方法 SELECT txid_current(); で確認することができます。 ただし、psqlで実行する場合は暗黙的にトランザクションが開始され…
やりたいこと テーブル内に格納されているレコードのブロック番号を調べたいってときありますよね? 環境は PostgreSQL 12.3 です。が、多分どのバージョンでも大丈夫です。 調べる pg_visibilityという拡張モジュールを使うと確認することができます。 post…
PostgreSQLの話です。 パラメータを変更する方法 postgresql.confを変更する。 ALTER SYSTEM SET コマンドで変更する。 SET コマンドで変更する。 の3通りでしょうか。 1のpostgresql.confの変更と、ALTER SYSTEM SET コマンドの変更では永続的に変更されま…
環境 CentOS7 PostgreSQL のバージョンは問いません。 CentOSの設定 localectl set-locale LANG=ja_JP.UTF-8 localectl set-keymap jp106 timedatectl set-timezone Asia/Tokyo ロケール(文字コード? エンコード? エンコーディング?)を日本語でUTF8にし…
PostgreSQLの話をしていて、知らなかったのですがPINとかいうものがあるそうで、色々と確認してみました。 が、ドキュメントの類には全くPINの解説などがでてこず、よくよく話を聞いてみると"src/backend/storage/buffer/README"に書かれているそうです。 な…
前回のチェックポイント実行から checkpoint_timeout 秒経過した時 max_wal_size に指定したサイズ分のWALログレコードがWALセグメントファイルに書き込まれたとき CHECKPOINTコマンドを実行したとき PostgreSQL が正常に停止されたとき pg_basebackup 関数…
VACUUMを実行した場合にいつまで経っても完了しない場合、どれだけ進んでいるのかとか、実はフリーズしているんじゃないかとか色々考えてしまいますよね。 そんなときに"pg_stat_progress_vacuum"を確認すれば進捗状況が分かるそうです。 www.postgresql.jp …
PostgreSQLでのdump/restore(バックアップリストア)が個人的にすんごいややこしい。特にアーカイブ形式がややこしい。その中でも何がややこしいかってpg_restoreがややこしい。 平文形式とアーカイブ形式でリストアの方法が違うのがややこしい 平文系式 →…
タイトルの通り、PostgreSQLがバージョン12から追記型アーキテクチャではなくなってVACUUMとかが要らなくなる、というお話をチラチラ聞いたことがあるのですが、色々ググってもそんな話は全く無く不思議に思っていました。 で、最近見つけたのが PostgreSQL …
概要 タイトルの通り、CentOS 5にPostgreSQL9.2をインストールしたい。ということです。正直、大変でした。 環境 Vagrant 2.2.7 CentOS 5.11 手順 yumのリポジトリURLを変更する zlib-develをyumでインストールする readline-develをyumでインストールする P…
確認する 書き込み対象のWALファイルの名前を確認する(WALログ書き込み位置 → WALファイル名)。 PostgreSQL 10以降 pg_walfile_nameとpg_current_wal_lsnを組み合わせて確認します。 SELECT pg_walfile_name( pg_current_wal_lsn() ); な感じです。 Postgr…
-tオプションをつけてテーブル名を指定してリストアしたところ、実行が空振り(すぐに終了)する。もちろんテーブルはリストアされていない。 何故かと色々検証してみたところ、テーブル名が大文字小文字を判別している模様。 つまりはTB_NAMEっていうテーブ…
概要 パラメータを変更したのに変わっていなかった。ということに関するお話し。 SETコマンド SETコマンドでパラメータを変更したのに、次に接続してみたらパラメータの変更が元に戻っていた。そんなお悩み、ありますよね。それはSETコマンドがそのセッショ…
概要 いつも通りにVACUUM ANALYZE VERBOSEを実行してみたらエラーになった。ので調べてみた。けど原因は不明。 環境 PostgreSQL 11とか9とか色々 実行内容 コマンド VACUUM ANALYZE VERBOSE; これを実行すると エラー内容 ERROR: syntax error at or near "V…
概要 基本的にデータベースではデータの並び順は保証されていません。データはインデックス通りに並んでいる訳でもなく、また、入れた順番に入っているわけでもなく、データベースの思うままに格納されています。そして勝手に並べ替えられたりすることもあっ…
概要 テーブルの定義を変更しようとしたら、誰かがSELECT文を投げていて待たされる。そんな経験、誰もがお持ちですよね? そこでどんな処理でロックがかかっているのかが分かればストレスも多少は軽減されます。 環境 PostgreSQL 10です。 調べる SELECT loc…
環境 多分、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…
いっつも忘れるのでメモ。 SELECT * FROM pg_proc 定義されているスキーマを知りたい場合は SELECT * FROM pg_proc INNER JOIN pg_namespace ON pg_proc.pronamespace = pg_namespace.oid でOK。
逆に考えて繋がらなくってもいいさ、って考えられたらいいんですけど、そうも言っていられないのが現実。 タイムアウトの場合は大抵ファイアウォールが閉じていたりします。 firewall-cmd --all-list で該当のIPアドレスからの5432ポートへのTCP接続が空いて…
概要 タイトルの通り、権限エラーが発生したときのお話です。 環境 PostgreSQL 10.3ぐらい 内容 user_nameっていうユーザで、新しく作成したデータベースにログインして SELECT * FROM table_name; ってすると ERROR: リレーション"table_name"は存在しませ…
概要 いわゆる、GRANTさんの話。スキーマに対する権限を付与するときの注意点です。 環境 PostgreSQL 10.3ぐらい 内容 スキーマに対する全ての権限を付与しようと思って GRANT ALL ON SCHEMA schema_name TO role_name; としても、スキーマ内のテーブルへの…
OracleでいうTNSPINGみたいなのをPostgreSQLでいうとPG_ISREADYになります。 使い方は↓で pg_isready
概要 pg_dumpに-nをつけてスキーマ単位でのダンプを取得した際に、拡張機能がダンプに含まれないことについての脳内放出。 環境 PostgreSQL 10.5ぐらい 実際にやってみる 対象 項目 値 データベース名 test スキーマ名 public やってみた CREATE EXTENSION t…
概要 pg_restoreのオプションの挙動に理解が追いつかないので、脳内アウトプット。ので、内容が不正確な可能性が大いにあります。 環境 PostgreSQL 10.5ぐらい 難しいの -d オプション リストア先のデータベースの名前を指定する。 つまりは、-d db_nameとし…
概要 タイトルが全てを物語っているので多くは語りません。 環境 PostgreSQL 10.5ぐらい 測ってみた CREATE TABLE test_table ( val CHAR(12) ); っていうテーブルがあったとして INERT INTO test_table VALUES('test'); とした後に SELECT val, LENGTH(val)…
概要 お客さんとお話ししていて、たまに話題になるのが、pg_dumpで圧縮(-Fc)するとどれぐらいサイズ減りますかね? っていうこと。なので記録として残しておく。 pg_dumpやpg_dumpallで出力されるファイルには、不要領域とか不要タプルとかデッドタプルと…
概要 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で、例えばparentという名前のユーザー(ロール?)でログインしている最中に、childというユーザーに切り替えたい。 環境 PostgreSQL 10.3 ※そろそろ10.5にしたい。 変更 SET ROLE child でOK。 確認 SELECT CURRENT_USER; で確認。 使い所 …
したいこと リモートホストのPostgreSQLからデータを引っこ抜きたい。 環境 PostgreSQL 10.3 コマンド 自端末から pg_dump -h xxx.xxx.xxx.xxx -U user_name -d database_name > output_dir でOK。-hにリモートホストのIPアドレスを指定する。 ちなみに-Fcオ…
概要 タイトルのまんまです。SQLでびっくりしたことです。 環境 PostgreSQL 10.3 びっくりしたこと ORDER BYでNULLの順序を指定できる。 SELECT code, name FROM data_table ORDER BY code NULLS LAST; とかでNULL値のソート順を最後尾にできる。FIRSTを指定…