PostgreSQL の管理コマンド一覧（DBA）

PostgreSQL DBA コースで覚える構文の一覧です。共通 SQL コースの構文（SELECT・JOIN・GROUP BY・ウィンドウ関数など）はすべて使えます。

CREATE ROLE 名前 [LOGIN] [PASSWORD 'パスワード'] [CREATEDB] [CREATEROLE];
CREATE USER 名前 …;（LOGIN 付きの CREATE ROLE）
ALTER ROLE 名前 属性;
DROP ROLE 名前;

CREATE ROLE clerk LOGIN PASSWORD 'zou-2026';
ALTER ROLE clerk CREATEDB;
DROP ROLE clerk;

\du
\du 名前
\dg

\du
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname !~ '^pg_';

\c データベース ロール [ホスト] [ポート]
\! psql -h localhost -U ロール -d quest -c "SQL"
\! PGPASSWORD=パスワード psql -h ホスト -U ロール -c "SQL"
\! ssh app01 psql -h db01 -U ロール -c "SQL"

\c quest clerk localhost
\! psql -h localhost -U clerk -c "SELECT current_user"

GRANT SELECT, INSERT ON 表 TO ロール;
GRANT SELECT (列, 列) ON 表 TO ロール;
GRANT UPDATE (列) ON 表 TO ロール;
GRANT SELECT ON ALL TABLES IN SCHEMA スキーマ TO ロール;
GRANT ロール TO ロール;

GRANT SELECT ON scrolls TO clerk;
GRANT UPDATE (stock) ON scrolls TO clerk;

SET ROLE ロール;
RESET ROLE;
SELECT current_user, session_user;

SET ROLE clerk;
SELECT * FROM scrolls;
RESET ROLE;

REVOKE 権限 ON 表 FROM ロール;
REVOKE ALL ON 表 FROM ロール;
REVOKE 権限 ON 表 FROM PUBLIC;
REVOKE GRANT OPTION FOR 権限 ON 表 FROM ロール;

REVOKE INSERT ON scrolls FROM clerk;
REVOKE ALL ON scrolls FROM PUBLIC;

GRANT USAGE ON SCHEMA スキーマ TO ロール;
GRANT CREATE ON SCHEMA スキーマ TO ロール;
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA スキーマ AUTHORIZATION ロール;
\dn+

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA archive TO clerk;

ALTER DEFAULT PRIVILEGES IN SCHEMA スキーマ GRANT SELECT ON TABLES TO ロール;
ALTER DEFAULT PRIVILEGES FOR ROLE 作る人 IN SCHEMA スキーマ GRANT …;
\ddp

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readers;

\dp [表]
\z [表]
SELECT has_table_privilege('ロール', '表', 'SELECT');
SELECT has_column_privilege('ロール', '表', '列', 'UPDATE');

\dp scrolls
SELECT has_table_privilege('clerk', 'scrolls', 'INSERT');

ALTER TABLE 表 ENABLE ROW LEVEL SECURITY;
CREATE POLICY 名前 ON 表 [FOR SELECT|INSERT|UPDATE|DELETE] [TO ロール] USING (条件) [WITH CHECK (条件)];
DROP POLICY 名前 ON 表;

ALTER TABLE letters ENABLE ROW LEVEL SECURITY;
CREATE POLICY own_letters ON letters USING (owner = current_user);

TYPE  DATABASE  USER  ADDRESS  METHOD
local  all  all  peer
host  quest  app  192.168.10.0/24  scram-sha-256
SELECT * FROM pg_hba_file_rules;

\! cat pg_hba.conf
\! echo 'host quest app 192.168.10.21/32 scram-sha-256' >> pg_hba.conf

\! pg_ctl reload
SELECT pg_reload_conf();

\! pg_ctl reload
SELECT pg_reload_conf();

SHOW 名前;
SELECT current_setting('名前');
SELECT name, setting, unit, context, source FROM pg_settings WHERE name = '名前';
SET 名前 = 値;（そのセッションだけ）

SHOW max_connections;
SELECT name, setting, context FROM pg_settings WHERE name = 'work_mem';

名前 = 値    # コメント
\! grep -n 名前 postgresql.conf
\! sed -i "s/^#名前 = .*/名前 = 値/" postgresql.conf
SELECT name, setting, applied, error FROM pg_file_settings;

\! grep -n work_mem postgresql.conf
\! sed -i "s/^#work_mem = 4MB/work_mem = 8MB/" postgresql.conf

ALTER SYSTEM SET 名前 = 値;
ALTER SYSTEM RESET 名前;
ALTER SYSTEM RESET ALL;

ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

\! pg_ctl status
\! pg_ctl stop [-m fast|smart|immediate]
\! pg_ctl start
\! pg_ctl restart [-m fast]

\! pg_ctl status
\! pg_ctl restart

\! tail -n 20 log/postgresql.log
\! grep FATAL log/postgresql.log
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_min_duration_statement = '100ms';

\! tail -n 5 log/postgresql.log
\! grep DETAIL log/postgresql.log

SELECT ctid, xmin, xmax, * FROM 表;
SELECT relname, n_live_tup, n_dead_tup FROM pg_stat_user_tables;

SELECT ctid, xmin, xmax, id FROM treasure WHERE id = 1;
SELECT relname, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;

VACUUM 表;
VACUUM (VERBOSE) 表;
VACUUM;（すべての表）

VACUUM (VERBOSE) treasure;
SELECT last_vacuum, vacuum_count FROM pg_stat_user_tables WHERE relname = 'treasure';

ANALYZE 表;
VACUUM ANALYZE 表;
SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = '表';
ALTER TABLE 表 ALTER COLUMN 列 SET STATISTICS 数;

ANALYZE orders;
EXPLAIN SELECT * FROM orders WHERE status = 'new';

SHOW autovacuum;
SELECT name, setting FROM pg_settings WHERE name LIKE 'autovacuum%';
ALTER TABLE 表 SET (autovacuum_vacuum_scale_factor = 0.01);
ALTER TABLE 表 RESET (autovacuum_enabled);

ALTER TABLE big_log SET (autovacuum_vacuum_scale_factor = 0.01);
\! sleep 60

SELECT pg_size_pretty(pg_relation_size('表'));
SELECT pg_size_pretty(pg_total_relation_size('表'));
SELECT pg_size_pretty(pg_indexes_size('表'));

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;

VACUUM FULL 表;
VACUUM (FULL, VERBOSE) 表;
\! df -h $PGDATA

VACUUM FULL treasure;
SELECT relfilenode FROM pg_class WHERE relname = 'treasure';

REINDEX INDEX インデックス;
REINDEX TABLE 表;
REINDEX INDEX CONCURRENTLY インデックス;

REINDEX INDEX CONCURRENTLY orders_updated_idx;
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

SELECT datname, age(datfrozenxid) FROM pg_database;
SELECT relname, age(relfrozenxid) FROM pg_class WHERE relkind = 'r';
VACUUM FREEZE 表;

VACUUM FREEZE old_ledger;
SELECT age(datfrozenxid) FROM pg_database WHERE datname = 'quest';

\! vacuumdb -z quest
\! vacuumdb -Z quest
\! vacuumdb -t 表 -v quest
\! vacuumdb -F quest

\! vacuumdb -z quest

\! pg_dump -f ファイル.sql quest
\! pg_dump -Fc -f ファイル.dump quest
\! pg_dump -t 表 -f ファイル.sql quest
\! pg_dump -n スキーマ -s -f ファイル.sql quest

\! pg_dump -Fc -f /backup/quest.dump quest
\! grep COPY /backup/quest.sql

\! pg_restore -l ファイル.dump
\! pg_restore -d quest ファイル.dump
\! pg_restore -d quest -t 表 ファイル.dump
\! pg_restore --clean -d quest ファイル.dump
\! psql -f ファイル.sql

\! pg_restore -d quest -t treasure /backup/quest.dump
\! psql -f /backup/quest.sql

\! pg_dumpall --globals-only -f ファイル.sql
\! pg_dumpall -f ファイル.sql
\! psql -f ファイル.sql

\! pg_dumpall -g -f /backup/globals.sql
\! psql -f /backup/globals.sql

SELECT pg_current_wal_lsn();
SELECT pg_walfile_name(pg_current_wal_lsn());
SELECT pg_wal_lsn_diff(LSN, LSN);
SELECT pg_switch_wal();
SELECT name, size FROM pg_ls_waldir();

SELECT pg_current_wal_lsn(), pg_walfile_name(pg_current_wal_lsn());
\! ls pg_wal

ALTER SYSTEM SET archive_mode = on;（再起動が要る）
ALTER SYSTEM SET archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f';
SELECT * FROM pg_stat_archiver;

SELECT archived_count, last_archived_wal, failed_count FROM pg_stat_archiver;

\! pg_basebackup -D ディレクトリ -Ft -X stream -P
\! PGPASSWORD=… pg_basebackup -h localhost -U ロール -D ディレクトリ
\! pg_basebackup -D ディレクトリ -Fp -R

\! pg_basebackup -D /backup/base -Ft -X stream -P

CREATE ROLE 名前 LOGIN REPLICATION PASSWORD '…';
ALTER ROLE 名前 REPLICATION;

CREATE ROLE backup LOGIN REPLICATION PASSWORD 'shelf-key';

\! tar -xf /backup/base/base.tar -C $PGDATA
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2026-11-04 22:14:00'
recovery_target_name = '名前'（pg_create_restore_point）
\! touch $PGDATA/recovery.signal
SELECT pg_wal_replay_resume();

\! touch recovery.signal
SELECT pg_is_in_recovery();
SELECT pg_wal_replay_resume();

\! pg_verifybackup /backup/base
SELECT timeline_id FROM pg_control_checkpoint();
\! cat /backup/wal/00000002.history

\! pg_verifybackup /backup/base

\! pg_archivecleanup アーカイブの場所 一番古く残すファイル
\! pg_archivecleanup -n /backup/wal 000000010000000000000005

\! cat /backup/wal/000000010000000000000005.00000028.backup

SELECT pid, usename, state, wait_event_type, wait_event, query FROM pg_stat_activity;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

SELECT pid, now() - xact_start AS xact_age, query FROM pg_stat_activity WHERE state = 'idle in transaction';

SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';

SELECT pg_cancel_backend(5103);
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction';

SELECT pid, relation::regclass, mode, granted FROM pg_locks WHERE NOT granted;
SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE wait_event_type = 'Lock';
SET lock_timeout = '3s';

SELECT pid, state, query FROM pg_stat_activity WHERE pid = ANY(pg_blocking_pids(5202));

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';（再起動が要る）
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
SELECT pg_stat_statements_reset();

SELECT query, calls, round(mean_exec_time::numeric, 2) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;

EXPLAIN (ANALYZE, BUFFERS) SELECT …;
BEGIN; EXPLAIN (ANALYZE) UPDATE …; ROLLBACK;

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan;
DROP INDEX CONCURRENTLY インデックス;

SELECT s.indexrelname FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid WHERE s.idx_scan = 0 AND NOT i.indisunique;

EXPLAIN (ANALYZE) SELECT … ORDER BY …;
SELECT temp_files, temp_bytes FROM pg_stat_database WHERE datname = 'quest';
ALTER SYSTEM SET log_temp_files = 0;
\! free -h

SET work_mem = '64MB';
ALTER ROLE report SET work_mem = '64MB';

SELECT blks_hit, blks_read, round(100.0 * blks_hit / (blks_hit + blks_read), 2) FROM pg_stat_database WHERE datname = 'quest';
SELECT relname, heap_blks_hit, heap_blks_read FROM pg_statio_user_tables;

SELECT relname, round(100.0 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 1) FROM pg_statio_user_tables;

SELECT num_timed, num_requested FROM pg_stat_checkpointer;
ALTER SYSTEM SET max_wal_size = '16GB';
ALTER SYSTEM SET log_checkpoints = on;
SELECT pg_stat_reset_shared('checkpointer');

SELECT num_timed, num_requested FROM pg_stat_checkpointer;

SHOW wal_level;
ALTER SYSTEM SET wal_level = replica;（再起動が要る）
ALTER SYSTEM SET max_wal_senders = 10;

SELECT name, setting, context FROM pg_settings WHERE name IN ('wal_level', 'max_wal_senders');

\! pg_basebackup -h localhost -U replicator -D /var/lib/pgsql/18/standby -R -X stream
\! pg_ctl -D /var/lib/pgsql/18/standby -o "-p 5433" start
\! psql -p 5433 -c "SELECT …"

\! psql -p 5433 -c "SELECT pg_is_in_recovery()"

SELECT application_name, state, sent_lsn, replay_lsn, replay_lag, sync_state FROM pg_stat_replication;
SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) FROM pg_stat_replication;
\! psql -p 5433 -c "SELECT status FROM pg_stat_wal_receiver"

SELECT application_name, pg_wal_lsn_diff(sent_lsn, replay_lsn) AS lag_bytes FROM pg_stat_replication;

SELECT * FROM pg_create_physical_replication_slot('名前');
primary_slot_name = '名前'（スタンバイの設定）
SELECT slot_name, active, restart_lsn, wal_status FROM pg_replication_slots;
SELECT pg_drop_replication_slot('名前');

SELECT slot_name, active, pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) FROM pg_replication_slots;

ALTER SYSTEM SET synchronous_standby_names = '名前';
ALTER SYSTEM SET synchronous_standby_names = 'ANY 1 (a, b)';
SET synchronous_commit = local;

SELECT application_name, sync_state FROM pg_stat_replication;

\! pg_ctl -D /var/lib/pgsql/18/standby promote
\! psql -p 5433 -c "SELECT pg_promote()"

\! pg_ctl -D /var/lib/pgsql/18/standby promote

\! pg_rewind --target-pgdata=$PGDATA --source-server="host=localhost port=5433 user=postgres dbname=quest" -R

\! pg_rewind --target-pgdata=$PGDATA --source-server="host=localhost port=5433 user=postgres dbname=quest" -R

ALTER SYSTEM SET wal_level = logical;（再起動が要る）
CREATE PUBLICATION 名前 FOR TABLE 表;
CREATE SUBSCRIPTION 名前 CONNECTION 'host=… dbname=… user=… password=…' PUBLICATION 名前;（受け取る側）
ALTER SUBSCRIPTION 名前 REFRESH PUBLICATION;
SELECT * FROM pg_stat_subscription;

CREATE PUBLICATION shop_pub FOR TABLE orders;
\! psql -p 5434 -c "SELECT subname, received_lsn FROM pg_stat_subscription"