Postgresのスニペット集！よく忘れてしまうことをまとめたリファレンスページ。

• pg_ctl init -D path - pathに新しいデータベースと設定を初期化
• pg_hba.conf - ホストベース認証の設定
• pg_ident.conf - システムユーザーをデータベースユーザーにマッピング
• postgresql.conf - その他の設定変更全般

学んだこと

• インデックスなしでテーブルを作成し、データをコピーしてからインデックスを追加する方が速い
• SSDベースのデータベースではrandom_page_cost=1.1を使うとパフォーマンスが大幅に向上する

スニペット

コマンド

• \l データベース一覧を表示
• \c dbname 現在のユーザーとしてデータベースに接続

別のテーブルのコピーとしてテーブルを作成

create table new_table as table old_table;

注意: これですべてのデータはコピーされるが、インデックスや制約はコピーされない

データなしで作成する場合

create table new_table as table old_table with no data;

クエリやフィルタをかけたい場合:

create table new_table as (select * from old_table where some_condition);

待機中のロックを確認する

select relation::regclass, * from pg_locks where not granted;

データベースのサイズを取得する

SELECT pg_size_pretty(pg_database_size('database name'));

テーブルのサイズを取得する

SELECT pg_size_pretty(pg_relation_size('records'));

レプリケーションスロットの監視

SELECT * FROM pg_replication_slots;

レプリケーションラグの監視

SELECT extract(epoch from now() - pg_last_xact_replay_timestamp()) AS replica_lag

データベースのスキーマのみをダンプする

pg_dump --schema-only databasename

便利なツール

• オブジェクトストレージ経由のWALストリーミングとバックアップ