2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

新人プログラマ応援 - みんなで新人を育てよう！
@Choco0602

【備忘録】PostgreSQLのLinuxコマンドまとめ

2
Posted at

はじめに

本記事では、PostgreSQLをLinux環境で使用する際の
コマンドを用途別にまとめます。

1. インストール

Ubuntu / Debian系

sudo apt update
sudo apt install -y postgresql postgresql-contrib

CentOS / RHEL系

sudo dnf install -y postgresql-server postgresql-contrib
sudo postgresql-setup --initdb

postgresql-contribは追加の拡張機能パッケージです。
pg_stat_statementsなどの便利な機能が含まれているため
合わせてインストールしておくことを推奨します。

2. サービスの管理

# 起動
sudo systemctl start postgresql

# 停止
sudo systemctl stop postgresql

# 再起動
sudo systemctl restart postgresql

# 設定リロード（再起動なし・接続を切らずに設定を反映）
sudo systemctl reload postgresql

# 自動起動を有効化
sudo systemctl enable postgresql

# 状態確認
sudo systemctl status postgresql

restartとreloadの違い：

  • restart：プロセスを停止して再起動する。接続中のセッションが切れる。
  • reload：postgresql.confの変更を接続を維持したまま反映できる。
    ただしlisten_addressesなど一部の設定はrestartが必要。

3. PostgreSQLへの接続

# postgresユーザーとして接続（最もよく使う）
sudo -u postgres psql

# データベースを指定して接続
sudo -u postgres psql -d データベース名

# ホスト・ポート・ユーザーを指定して接続
psql -h localhost -p 5432 -U ユーザー名 -d データベース名

# psqlを終了する
\q

sudo -u postgres psqlとは：
PostgreSQLのインストール時にpostgresという
OSユーザーが自動で作成されます。
このユーザーはPostgreSQLのスーパーユーザーと紐づいており、
パスワードなしで接続できます。

4. psql内のメタコマンド一覧

psql起動後に使用できる\コマンドです。
SQLではなくpsql固有のコマンドのため、
末尾に;は不要です。

コマンド 内容
\l データベース一覧を表示
\c データベース名 データベースに切り替え
\dt テーブル一覧を表示
\d テーブル名 テーブルの構造を表示
\du ユーザー（ロール）一覧を表示
\dn スキーマ一覧を表示
\df 関数一覧を表示
\di インデックス一覧を表示
\timing クエリの実行時間を表示/非表示
\x 出力を縦方向に切り替え（列が多い場合に見やすい）
\i ファイルパス SQLファイルを実行
\o ファイルパス 出力先をファイルに変更
\q psqlを終了

\x（拡張表示）が便利な場面：
列数が多いテーブルをSELECT *で表示すると横に広がりすぎて
見づらくなります。\xを実行してからSELECTすると
1列ずつ縦に表示されて見やすくなります。

5. データベースの操作

# データベースを作成（コマンドライン）
sudo -u postgres createdb データベース名

# データベースを削除（コマンドライン）
sudo -u postgres dropdb データベース名

# データベース一覧を表示（コマンドライン）
sudo -u postgres psql -l

psql内で操作する場合

-- データベースを作成
CREATE DATABASE mydb;

-- データベースを削除
DROP DATABASE mydb;

-- データベース一覧
\l

DROP DATABASEは元に戻せません。
実行前に必ずデータベース名を確認してください。
接続中のセッションがある場合はエラーになります。
先に接続を切ってから実行してください。

-- 接続中のセッションを確認
SELECT pid, usename, datname FROM pg_stat_activity WHERE datname = 'mydb';

-- セッションを強制終了してから削除
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'mydb';
DROP DATABASE mydb;

6. ユーザー（ロール）の操作

# ユーザーを作成（対話形式）
sudo -u postgres createuser --interactive ユーザー名

# ユーザーを削除
sudo -u postgres dropuser ユーザー名

psql内で操作する場合

-- ユーザーを作成（パスワードあり）
CREATE USER myuser WITH PASSWORD 'mypassword';

-- スーパーユーザーとして作成
CREATE USER myuser WITH SUPERUSER PASSWORD 'mypassword';

-- パスワードを変更
ALTER USER myuser WITH PASSWORD 'newpassword';

-- ユーザーを削除
DROP USER myuser;

-- ユーザー一覧
\du

DROP USERは依存関係があるとエラーになります。
そのユーザーが所有するオブジェクト（テーブル等）がある場合は
先にオブジェクトを削除するか所有者を変更してください。

-- 所有オブジェクトを別ユーザーに移す
REASSIGN OWNED BY myuser TO postgres;
-- 残った権限を削除
DROP OWNED BY myuser;
-- ユーザーを削除
DROP USER myuser;

7. 権限の付与・取り消し

-- データベースへの接続権限を付与
GRANT CONNECT ON DATABASE mydb TO myuser;

-- スキーマの使用権限を付与
GRANT USAGE ON SCHEMA public TO myuser;

-- テーブルの全権限を付与
GRANT ALL PRIVILEGES ON TABLE mytable TO myuser;

-- SELECT権限のみ付与
GRANT SELECT ON TABLE mytable TO myuser;

-- スキーマ内の全テーブルに権限を付与
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- 権限を取り消す
REVOKE ALL PRIVILEGES ON TABLE mytable FROM myuser;

権限付与の順番：
ユーザーがテーブルにアクセスするには
CONNECT→USAGE→テーブル権限の順で付与が必要です。
テーブル権限だけ付与してもスキーマのUSAGEがないと
アクセスできないため注意してください。

8. バックアップと復元

# データベースをSQLファイルにバックアップ
pg_dump -U postgres データベース名 > backup.sql

# 圧縮形式でバックアップ（復元が高速）
pg_dump -U postgres -Fc データベース名 > backup.dump

# 全データベースをバックアップ
pg_dumpall -U postgres > all_backup.sql

# SQLファイルから復元
psql -U postgres データベース名 < backup.sql

# 圧縮形式から復元
pg_restore -U postgres -d データベース名 backup.dump

-Fc（カスタム形式）のメリット：
pg_restoreでの復元時に並列処理が使えるため
大規模なDBでは復元速度が大幅に向上します。
また特定のテーブルだけを選択して復元することもできます。

# 特定のテーブルだけ復元
pg_restore -U postgres -d データベース名 -t テーブル名 backup.dump

9. ログとパスの確認

# PostgreSQLのログをリアルタイムで確認
sudo tail -f /var/log/postgresql/postgresql-*.log

# 設定ファイルの場所を確認
sudo -u postgres psql -c "SHOW config_file;"

# データディレクトリの場所を確認
sudo -u postgres psql -c "SHOW data_directory;"

# 接続中のセッション数を確認
sudo -u postgres psql -c "SELECT count(*) FROM pg_stat_activity;"

ログの場所はディストリビューションによって異なる場合があります。
場所が分からない場合は以下で確認できます。

sudo -u postgres psql -c "SHOW log_directory;"
sudo -u postgres psql -c "SHOW log_filename;"

10. よく使うSQL（確認系）

-- 現在接続中のデータベースを確認
SELECT current_database();

-- 現在のユーザーを確認
SELECT current_user;

-- PostgreSQLのバージョンを確認
SELECT version();

-- テーブルのサイズを確認
SELECT pg_size_pretty(pg_total_relation_size('テーブル名'));

-- データベースのサイズを確認
SELECT pg_size_pretty(pg_database_size('データベース名'));

-- 実行中のクエリを確認
SELECT pid, usename, state, query FROM pg_stat_activity;

-- 実行中のクエリを強制終了
SELECT pg_terminate_backend(pid);

まとめ

カテゴリ 主なコマンド
サービス管理 systemctl start/stop/restart/reload postgresql
接続 sudo -u postgres psql
DB操作 createdb / dropdb / \l
ユーザー操作 createuser / \du / GRANT
バックアップ pg_dump / pg_restore
ログ確認 tail -f /var/log/postgresql/...
2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?