Techarm BlogPostgreSQL CLI完全チートシート：現場開発でよく使うコマンド集
2025/09/07に公開
PostgreSQL開発において、「あのコマンドなんだっけ？」と思う瞬間は誰にでもあります。本記事では、現場開発で実際によく使用するPostgreSQLコマンドを実用性重視でまとめました。
初心者エンジニアから中級者まで、開発現場ですぐに使える実践的なチートシートとして活用してください。
PostgreSQLへの接続・切断
▶基本的な接続
ターミナル
# ローカルのpostgresユーザーで接続
psql -U postgres
# 特定のデータベースに接続
psql -U username -d database_name
# リモートサーバーに接続
psql -h hostname -U username -d database_name -p 5432
# 接続URLを使用した接続
psql "postgresql://username:password@hostname:5432/database_name"▶環境変数を利用した接続
ターミナル
# 環境変数設定
export PGHOST=localhost
export PGPORT=5432
export PGUSER=myuser
export PGDATABASE=mydb
# 環境変数を使って接続
psql▶接続オプション
ターミナル
# パスワードプロンプトを表示
psql -U username -W
# SSL接続を強制
psql "sslmode=require host=hostname user=username dbname=database_name"
# 接続タイムアウトを設定
psql -c "SET statement_timeout = 30000;" -U username database_name▶切断
-- psqlからの切断
\q
-- または
\quit
-- 他のセッションを強制終了
SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE usename = 'username' AND pid <> pg_backend_pid();データベースとスキーマの操作
▶データベース一覧と詳細
-- データベース一覧
\l
\list
-- 詳細情報付きでデータベース一覧
\l+
-- 現在のデータベース情報
SELECT current_database();
-- データベースのサイズ確認
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;▶データベース作成・削除
-- データベース作成
CREATE DATABASE myapp_development;
-- エンコーディング指定でデータベース作成
CREATE DATABASE myapp_production
WITH ENCODING 'UTF8'
LC_COLLATE='ja_JP.UTF-8'
LC_CTYPE='ja_JP.UTF-8';
-- テンプレートを指定してデータベース作成
CREATE DATABASE new_db WITH TEMPLATE template0;
-- データベース削除（接続を強制切断）
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'database_to_drop';
DROP DATABASE database_to_drop;▶スキーマ操作
-- スキーマ一覧
\dn
\dn+
-- スキーマ作成
CREATE SCHEMA app_schema;
-- スキーマ削除（CASCADE注意）
DROP SCHEMA app_schema CASCADE;
-- 検索パス確認・設定
SHOW search_path;
SET search_path TO app_schema, public;
-- デフォルトスキーマ設定
ALTER DATABASE myapp SET search_path = app_schema, public;▶データベース切り替え
-- データベース切り替え
\c database_name
\connect database_name
-- 別のユーザーで切り替え
\c database_name usernameテーブル操作（構造確認・作成・削除）
▶テーブル一覧と構造確認
-- テーブル一覧
\dt
\dt *.* -- 全スキーマのテーブル
-- テーブル詳細情報
\dt+
-- 特定テーブルの構造確認
\d table_name
\d+ table_name -- 詳細情報付き
-- テーブルのカラム情報のみ
\d+ table_name
-- インデックス情報
\di table_name▶テーブル作成
-- 基本的なテーブル作成
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 制約付きテーブル作成
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
amount DECIMAL(10,2) CHECK (amount > 0),
status VARCHAR(20) DEFAULT 'pending',
order_date DATE NOT NULL,
CONSTRAINT valid_status CHECK (status IN ('pending', 'completed', 'cancelled'))
);
-- パーティションテーブル
CREATE TABLE sales (
id SERIAL,
sale_date DATE,
amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);▶テーブル変更
-- カラム追加
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- カラム削除
ALTER TABLE users DROP COLUMN phone;
-- カラム名変更
ALTER TABLE users RENAME COLUMN name TO full_name;
-- カラムデータ型変更
ALTER TABLE users ALTER COLUMN email TYPE TEXT;
-- NOT NULL制約追加
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- デフォルト値設定
ALTER TABLE users ALTER COLUMN created_at SET DEFAULT NOW();
-- 制約追加
ALTER TABLE users ADD CONSTRAINT email_check
CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+[.][A-Za-z]+$');▶テーブル削除・切り捨て
-- テーブル削除
DROP TABLE table_name;
DROP TABLE IF EXISTS table_name;
-- 外部キー制約無視して削除
DROP TABLE table_name CASCADE;
-- テーブルデータのみ削除
TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name RESTART IDENTITY CASCADE;データ操作（CRUD）
▶データ挿入（INSERT）
-- 基本的な挿入
INSERT INTO users (email, name) VALUES ('user@example.com', 'John Doe');
-- 複数行一括挿入
INSERT INTO users (email, name) VALUES
('user1@example.com', 'User One'),
('user2@example.com', 'User Two'),
('user3@example.com', 'User Three');
-- SELECT結果から挿入
INSERT INTO user_backup
SELECT * FROM users WHERE created_at < '2024-01-01';
-- 競合時の処理（PostgreSQL 9.5+）
INSERT INTO users (email, name) VALUES ('user@example.com', 'John Doe')
ON CONFLICT (email) DO NOTHING;
INSERT INTO users (email, name) VALUES ('user@example.com', 'John Updated')
ON CONFLICT (email) DO UPDATE SET
name = EXCLUDED.name,
updated_at = CURRENT_TIMESTAMP;▶データ取得（SELECT）
-- 基本的な取得
SELECT * FROM users;
SELECT id, email, name FROM users WHERE id = 1;
-- 条件付き取得
SELECT * FROM users WHERE created_at >= '2024-01-01';
SELECT * FROM users WHERE name LIKE '%John%';
SELECT * FROM users WHERE email ~ '@gmail\.com$';
-- ソート・制限
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
SELECT * FROM users ORDER BY name OFFSET 20 LIMIT 10;
-- 集計
SELECT COUNT(*) FROM users;
SELECT DATE(created_at), COUNT(*) FROM users
GROUP BY DATE(created_at) ORDER BY DATE(created_at);
-- 結合
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;▶データ更新（UPDATE）
-- 基本的な更新
UPDATE users SET name = 'Updated Name' WHERE id = 1;
-- 複数カラム更新
UPDATE users SET
name = 'New Name',
updated_at = CURRENT_TIMESTAMP
WHERE id = 1;
-- 条件付き更新
UPDATE users SET name = 'VIP User'
WHERE id IN (SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > 10);
-- JOINを使った更新
UPDATE users SET status = 'active'
FROM orders
WHERE users.id = orders.user_id
AND orders.created_at >= '2024-01-01';▶データ削除（DELETE）
-- 基本的な削除
DELETE FROM users WHERE id = 1;
-- 条件付き削除
DELETE FROM users WHERE created_at < '2024-01-01';
-- JOINを使った削除
DELETE FROM users
USING orders
WHERE users.id = orders.user_id
AND orders.status = 'cancelled';ユーザーと権限管理
▶ユーザー一覧と詳細
-- ユーザー一覧
\du
\du+
-- 詳細なユーザー情報
SELECT usename, usesuper, usecreatedb, useconnlimit
FROM pg_user;
-- 現在のユーザー確認
SELECT current_user;
SELECT session_user;▶ユーザー作成・削除
-- 基本的なユーザー作成
CREATE USER app_user WITH PASSWORD 'secure_password';
-- 権限付きユーザー作成
CREATE USER admin_user WITH PASSWORD 'admin_pass' CREATEDB CREATEROLE;
-- ロール作成（ログイン不可）
CREATE ROLE app_role;
-- ユーザー削除
DROP USER app_user;
-- 権限の有効期限設定
CREATE USER temp_user WITH PASSWORD 'temp_pass' VALID UNTIL '2024-12-31';▶権限管理
-- データベースへのアクセス権付与
GRANT CONNECT ON DATABASE myapp TO app_user;
-- スキーマへのアクセス権
GRANT USAGE ON SCHEMA public TO app_user;
-- テーブルへの権限付与
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- 新しく作成されるテーブルへの権限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
-- 権限取り消し
REVOKE DELETE ON users FROM app_user;
-- 権限確認
\dp table_name
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='users';▶ロール管理
-- ロール作成
CREATE ROLE readonly;
CREATE ROLE readwrite;
-- ロールに権限付与
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
-- ユーザーにロール付与
GRANT readonly TO app_user;
-- ロール確認
SELECT rolname FROM pg_roles WHERE pg_has_role('app_user', oid, 'member');バックアップとリストア
▶pg_dumpを使ったバックアップ
ターミナル
# データベース全体のバックアップ
pg_dump -U username -h hostname database_name > backup.sql
# 圧縮バックアップ
pg_dump -U username -h hostname database_name | gzip > backup.sql.gz
# カスタムフォーマットでバックアップ
pg_dump -U username -h hostname -Fc database_name > backup.dump
# 特定テーブルのみバックアップ
pg_dump -U username -h hostname -t table_name database_name > table_backup.sql
# スキーマのみバックアップ
pg_dump -U username -h hostname -s database_name > schema_backup.sql
# データのみバックアップ
pg_dump -U username -h hostname -a database_name > data_backup.sql▶pg_dumpallを使った全データベースバックアップ
ターミナル
# 全データベースのバックアップ
pg_dumpall -U postgres > all_databases.sql
# ロールとテーブル空間のみ
pg_dumpall -U postgres -r > roles.sql▶リストア
ターミナル
# SQLファイルからリストア
psql -U username -h hostname database_name < backup.sql
# 圧縮ファイルからリストア
gunzip -c backup.sql.gz | psql -U username -h hostname database_name
# カスタムフォーマットからリストア
pg_restore -U username -h hostname -d database_name backup.dump
# 並列リストア（高速化）
pg_restore -U username -h hostname -d database_name -j 4 backup.dump
# 特定テーブルのみリストア
pg_restore -U username -h hostname -d database_name -t table_name backup.dump▶継続的バックアップ（WAL）
ターミナル
# WALアーカイブの設定確認
SHOW archive_mode;
SHOW archive_command;
# ベースバックアップ作成
pg_basebackup -D /backup/base -Ft -z -P
# PITR（Point-in-Time Recovery）用設定
SELECT pg_start_backup('backup_label');
-- ファイルシステムレベルでコピー
SELECT pg_stop_backup();パフォーマンス監視
▶実行中のクエリ監視
-- 実行中のクエリ一覧
SELECT
pid,
now() - pg_stat_activity.query_start AS duration,
query
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';
-- 長時間実行中のクエリ強制終了
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE pid = <process_id>;
-- アクティブな接続数
SELECT count(*) FROM pg_stat_activity;
-- データベース別接続数
SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname;▶テーブル統計情報
-- テーブルのアクセス統計
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins,
n_tup_upd,
n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC;
-- インデックス使用状況
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- 使用されていないインデックス
SELECT
schemaname,
tablename,
indexname,
idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;▶パフォーマンス分析
-- スロークエリ分析（pg_stat_statements拡張必要）
SELECT
query,
calls,
total_time,
mean_time,
rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
-- クエリ実行計画
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';
-- バッファキャッシュヒット率
SELECT
sum(heap_blks_read) as heap_read,
sum(heap_blks_hit) as heap_hit,
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;▶システム情報
-- データベースサイズ
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;
-- テーブルサイズ
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;
-- 設定値確認
SHOW all;
SHOW shared_buffers;
SHOW effective_cache_size;便利なメタコマンド（\dシリーズ）
▶基本的なメタコマンド
-- ヘルプ
\? -- メタコマンドヘルプ
\h -- SQLコマンドヘルプ
\h SELECT -- 特定SQLのヘルプ
-- 接続情報
\conninfo -- 現在の接続情報
\c -- データベース切り替え
-- 終了
\q -- 終了▶構造確認系メタコマンド
-- データベースとスキーマ
\l -- データベース一覧
\dn -- スキーマ一覧
\du -- ユーザー一覧
-- テーブルとビュー
\dt -- テーブル一覧
\dt+ -- テーブル詳細一覧
\dv -- ビュー一覧
\dm -- マテリアライズドビュー一覧
\ds -- シーケンス一覧
-- インデックスと制約
\di -- インデックス一覧
\di+ -- インデックス詳細一覧
-- 関数とプロシージャ
\df -- 関数一覧
\df+ -- 関数詳細
\dp -- 権限確認▶特定オブジェクトの詳細
-- テーブル構造
\d table_name -- テーブル構造
\d+ table_name -- 詳細なテーブル構造
\dt *table_name* -- テーブル名を部分検索
-- インデックス詳細
\di table_name -- 特定テーブルのインデックス
-- 関数詳細
\df function_name -- 関数詳細
\sf function_name -- 関数のソースコード表示▶出力制御とフォーマット
-- 出力形式変更
\x -- 拡張表示切り替え
\x on -- 拡張表示ON
\x off -- 拡張表示OFF
-- ページング制御
\pset pager off -- ページング無効化
\pset pager on -- ページング有効化
-- 出力フォーマット
\pset format aligned -- 整列形式
\pset format unaligned -- 非整列形式
\pset format csv -- CSV形式
-- NULL表示設定
\pset null 'NULL'トラブルシューティング
▶接続問題の解決
-- 接続数上限確認
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;
-- 接続を強制終了
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'problematic_user';
-- デッドロック確認
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.usename AS blocking_user,
blocked_activity.query AS blocked_statement,
blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;▶パフォーマンス問題の診断
-- インデックス不足の確認
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
seq_tup_read / seq_scan AS avg_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC;
-- 統計情報の更新
ANALYZE;
ANALYZE table_name;
-- VACUUM実行状況確認
SELECT
schemaname,
tablename,
last_vacuum,
last_autovacuum,
n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000;
-- 手動VACUUM実行
VACUUM table_name;
VACUUM ANALYZE table_name;
VACUUM FULL table_name; -- 注意：ロックが発生▶ログ分析
-- エラーログ確認（設定により場所が異なる）
SHOW log_directory;
SHOW log_filename;
-- pg_logのサンプルクエリ（csvログ形式の場合）
-- ログファイルを直接確認する必要があります
-- 現在の設定確認
SHOW logging_collector;
SHOW log_statement;
SHOW log_min_duration_statement;▶データ整合性チェック
-- テーブルの整合性チェック
SELECT * FROM pg_stat_user_tables WHERE n_dead_tup > n_live_tup;
-- 外部キー制約チェック
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint
WHERE contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_trigger
WHERE tgconstraint = pg_constraint.oid
);
-- 重複データチェック
SELECT column_name, count(*)
FROM table_name
GROUP BY column_name
HAVING count(*) > 1;開発現場でよくあるワンライナー
▶データ調査系
-- テーブル行数カウント（全テーブル）
SELECT
schemaname,
tablename,
n_live_tup AS row_count
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
-- カラム一覧取得（情報スキーマ使用）
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'users';
-- 最近作成されたテーブル一覧
SELECT tablename, tableowner
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY tablename;
-- NULL値の多いカラム特定
SELECT
column_name,
(SELECT count(*) FROM table_name WHERE column_name IS NULL) as null_count,
(SELECT count(*) FROM table_name) as total_count
FROM information_schema.columns
WHERE table_name = 'your_table';▶データメンテナンス系
-- 重複データ削除（最新を残す）
DELETE FROM table_name a USING (
SELECT MIN(ctid) as ctid, column_name
FROM table_name
GROUP BY column_name HAVING COUNT(*) > 1
) b
WHERE a.column_name = b.column_name
AND a.ctid <> b.ctid;
-- ランダムサンプルデータ取得
SELECT * FROM users TABLESAMPLE SYSTEM (1); -- 1%サンプリング
-- 日付範囲でのデータ集計
SELECT
DATE_TRUNC('day', created_at) as day,
count(*)
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY day;▶システム運用系
ターミナル
# データベースサイズ監視
psql -d database_name -c "SELECT pg_size_pretty(pg_database_size('database_name'));"
# テーブルサイズTOP10
psql -d database_name -c "
SELECT
tablename,
pg_size_pretty(pg_total_relation_size(tablename::regclass)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC
LIMIT 10;"
# 接続数監視
psql -d database_name -c "SELECT count(*), usename FROM pg_stat_activity GROUP BY usename;"▶開発効率化
-- 開発用データリセット（危険：注意して使用）
TRUNCATE TABLE orders RESTART IDENTITY CASCADE;
-- 外部キー制約を一時無効化
SET session_replication_role = replica;
-- データ操作
SET session_replication_role = DEFAULT;
-- トランザクション内での安全なテスト
BEGIN;
-- テスト用の操作
SELECT * FROM users WHERE test_condition;
ROLLBACK; -- 変更を破棄▶JSON操作（PostgreSQL 9.3+）
-- JSONデータの抽出
SELECT data->>'name' as name FROM json_table WHERE data ? 'name';
-- JSON配列の操作
SELECT jsonb_array_elements(data->'items') FROM json_table;
-- JSON値での検索
SELECT * FROM json_table WHERE data @> '{"status": "active"}';まとめ
PostgreSQL CLIコマンドは開発現場で毎日使用するツールです。本チートシートで紹介したコマンドを活用することで、データベース操作が格段に効率化されます。
重要なポイント:
- 本番環境では必ずバックアップを取ってから操作する
- 権限管理は最小権限の原則に従う
- パフォーマンス監視は定期的に実施する
- メタコマンドを活用して作業効率を向上させる
開発現場では、基本的なCRUD操作から高度なパフォーマンスチューニングまで幅広い知識が求められます。このチートシートを手元に置いて、PostgreSQLを使った開発をより効率的に進めてください。
継続的な学習として、PostgreSQL公式ドキュメントや\hコマンドでのヘルプ機能も積極的に活用することをお勧めします。
この記事はいかがでしたか？
もしこの記事が参考になりましたら、
高評価をいただけると大変嬉しいです！
--
皆様からの応援が励みになります。ありがとうございます！ ✨