PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート

はじめに

この記事では、PostgreSQL を実務で使うときに毎回見直したくなる操作を 1 本にまとめています。Rocky Linux と macOS でのインストール手順、LAN 越しの外部接続設定、pg_dump / pg_restore を使ったバックアップ・リストア、そして psql の便利コマンドや SQL の基本までを順に確認できる構成です。 MySQL について同じ粒度でまとめたものは MySQLと向き合うための 現場で使えるデータベース操作・SQLノート をご覧ください。

Rocky Linuxにインストール

PostgreSQL 13.15 を Rocky Linux にインストールする手順は以下の通りです。

メモ: 記事執筆時は PostgreSQL 13 系を入れています。PostgreSQL 13 はコミュニティサポートが 2025 年 11 月で終了しているため、新規構築では postgresql16-server など現役メジャーバージョンの利用を検討してください。手順の流れ自体はバージョンとリポジトリの番号を読み替えればそのまま使えます。また、Rocky Linux 9 系を使っている場合は EL-8 を EL-9 に置き換えてください。

リポジトリを有効化する

まず、PostgreSQL 13 のリポジトリを追加します。

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

PostgreSQL 13をインストールする

PostgreSQL 13 のインストールを行います。AppStream 側に同梱されている古い postgresql モジュールが優先されないよう、無効化してから入れるのが安全です。

sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql13-server

PostgreSQLの初期化

PostgreSQLのデータベースを初期化します。

sudo /usr/pgsql-13/bin/postgresql-13-setup initdb

サービスの有効化と起動

PostgreSQLサービスを有効化し、起動します。

sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

バージョン確認

インストールが完了したら、バージョンを確認してみてください。

psql --version

これでPostgreSQL 13.15がインストールされ、実行可能な状態になります。

macOSにインストール

macOS に PostgreSQL をセットアップするまでの手順を以下に示します。

Homebrewを使ってPostgreSQLをインストール

メジャーバージョンを固定したい場合は postgresql@14 のように番号付きの formula を指定するのが確実です。

brew install postgresql@14

メモ: postgresql@14 は keg-only formula のため、インストール後にそのまま psql や initdb が PATH に入らない場合があります。command not found になるときは、以下を ~/.zprofile などに追記してシェルを再起動してください。

export PATH="$(brew --prefix)/opt/postgresql@14/bin:$PATH"

バージョンの確認:

$ psql --version
psql (PostgreSQL) 14.12 (Homebrew)

メモ: 上の出力は記事執筆時のものです。brew upgrade 後は表示されるバージョンが変わります。Homebrew の postgresql (バージョン無印) は時期によって指すメジャーバージョンが変わるため、サーバー用途では postgresql@14・postgresql@16 など番号付きを選ぶと安全です。

PostgreSQLサービスの起動と自動起動の設定

インストール後、PostgreSQL サービスを起動し、自動起動を設定します。

brew services start postgresql@14

データディレクトリの場所と手動初期化

Homebrew でインストールした場合、データディレクトリ（クラスタ）は $(brew --prefix)/var/postgresql@14 に作成されます（Apple Silicon Mac なら /opt/homebrew/var/postgresql@14、Intel Mac なら /usr/local/var/postgresql@14）。

brew services start を使えば初期化（initdb）も自動で走るため、通常は手動実行は不要です。クラスタを作り直したい場合は、先にサービスを停止して既存ディレクトリを退避・削除してから initdb を実行します。既存ディレクトリが残ったまま initdb を実行するとエラーになります。

brew services stop postgresql@14
mv "$(brew --prefix)/var/postgresql@14" "$(brew --prefix)/var/[email protected]"
initdb "$(brew --prefix)/var/postgresql@14"
brew services start postgresql@14

メモ: 古い Homebrew 環境では /usr/local/var/postgres というディレクトリ名でしたが、現在は postgresql@<バージョン> 形式に変わっています。Apple Silicon 環境では /usr/local ではなく /opt/homebrew 以下にあるので、パスを直書きせず brew --prefix を使って解決するのが無難です。

psqlコマンドの確認

PostgreSQLのインストールが正常に完了したかを確認するために、psqlコマンドを実行します。

psql postgres

このコマンドでPostgreSQLプロンプトに切り替われば、インストールは成功です。プロンプトを終了するには \q を入力します。

ネットワーク越しのPostgreSQLに接続する

macOS から LAN 内の Rocky Linux で動作している PostgreSQL に接続する方法です。

PostgreSQL の設定ファイル（PGDG パッケージの場合は /var/lib/pgsql/<バージョン>/data/postgresql.conf）を開き、外部からの接続を許可するように設定を変更します。Homebrew で入れた場合の場所は $(brew --prefix)/var/postgresql@14/postgresql.conf のような形になります。

listen_addresses = '*'

pg_hba.confファイル（同じディレクトリにあります）を編集し、外部接続を許可する IP 範囲を設定します。

host    all             all             192.168.1.0/24          md5

ここで192.168.1.0/24は許可するIPアドレスの範囲です。私のAndroidテザリング下のLANの場合、192.168.*.*の範囲で変わるのでを許可するには、以下のように設定しました。

host    all             all             192.168.0.0/16          md5

ここで、192.168.0.0/16は192.168.*.*全体をカバーするCIDR表記です。この設定により、192.168.0.0から192.168.255.255までの範囲のIPアドレスからの接続が許可されます。

以上の設定を反映するために、PostgreSQL サービスを再起動します。PGDG パッケージで入れた場合、サービス名は postgresql-13 のようにバージョン番号付きになります。

sudo systemctl restart postgresql-13

ファイアーウォールを使っている場合は、適宜ポートを解放してください。firewalld を使っているなら次のコマンドで PostgreSQL 既定ポート（5432）を開放できます。

sudo firewall-cmd --add-service=postgresql --permanent
sudo firewall-cmd --reload

これで macOS 上の psql クライアントコマンドや、DataGrip.app などから直接外部 DB へ接続可能になりました。

postgresにパスワードを設定

code -a /opt/homebrew/var/postgresql@14/pg_hba.conf を開いて以下修正。

# "local" is for Unix domain socket connections only
#local   all             all                                     trust # trustにしてしまうとパスワードなしでもログインできてしまう
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

trust にするとパスワードなしでもログインできてしまう。ローカル開発環境であればパスワードなしログインでも問題ないかもしれないが、 Go言語で開発中にpostgresユーザーで接続すると強制的にpostgres DBのテーブルを参照してしまう。 ローカルでもmd5を設定してパスワードなしではログインできないようにしておくとトラブルも少ない。

設定変更したらpostgresを再起動。

brew services restart postgresql@14

既存のユーザーにパスワードを設定する方法

スーパーユーザーでログイン

psql -U スーパーユーザー名 -d postgres

※スーパーユーザーにパスワードを設定していない場合は、先ほどの設定で一度 trust に戻してパスワードなしでログインできるようにしないとならない

ログイン後、以下コマンドを実行。

ALTER USER ユーザー名 WITH PASSWORD '新しいパスワード';

次は、全てのユーザーにパスワードが設定されているかどうか確認するコマンド:

SELECT usename, passwd IS NOT NULL AS has_password FROM pg_shadow;

データベースとユーザーを作成する

PostgreSQLにログイン

まず、postgresユーザーとしてPostgreSQLにログインします。

sudo -i -u postgres
psql

新しいユーザーの作成

新しいデータベースユーザーを作成します。以下のコマンドを実行して、ユーザー名とパスワードを指定してください。

CREATE USER {新しいDBのユーザー名} WITH PASSWORD '{新しいDBのパスワード}';

新しいデータベースの作成

作成したユーザーを所有者として、新しいデータベースを作成します。

CREATE DATABASE {新しいデータベース名} OWNER {新しいDBのユーザー名};

PostgreSQLからログアウト

作業が完了したら、exitでPostgreSQLからログアウトします。

exit

これで、新しいユーザーとデータベースが作成されました。

その他の方法

以下のコマンドでも、新しいデータベースやユーザーを作成することができます。

データベースを作成:

createdb mydatabase

※ デフォルトのユーザー名はログイン中のユーザー名と同じになります。

ユーザーを作成:

createuser aragi

データベースに接続

psql -d mydatabase -U aragi

データベースを丸ごと移行する

PostgreSQLのデータベースを丸ごとバックアップし、別のデータベースに流し込む際にトリガーや関数なども含めて丸ごとコピーする方法について、以下の手順を説明します。

バックアップの取得

pg_dumpコマンドを使用して、データベース全体をバックアップします。この際、スキーマやデータ、トリガーなどすべてが含まれる形式でエクスポートします。

pg_dump -U {元DBのユーザー名} -h {ホスト名} -p {ポート番号} -d {データベース名} -Fc -f backup.dump

• -Fc オプションはカスタム形式でバックアップを作成します。
• {元DBのユーザー名} や {ホスト名} などは環境に合わせて設定してください。

バックアップのリストア

バックアップしたデータを別のデータベースに流し込みます。この際に、別のユーザーで実行するため、適切なオプションを指定します。あらかじめデータベースやユーザーは作成しておいてください。

pg_restore -U {新しいDBのユーザー名} -h {ホスト名} -p {ポート番号} -d {新しいデータベース名} --no-owner --role={新しいDBのユーザー名} backup.dump

• --no-owner オプションは、バックアップに含まれるオブジェクトの所有者情報を無視します。
• --role オプションで新しいユーザー名を指定します。

以上で、データベースを丸ごと移行できます。例えば、Rocky Linuxで動いているデータベースをmacOSのPostgreSQLに移行することもこの方法で可能です。

PostgreSQLのコマンド

PostgreSQLプロンプト上で、 \? を使ってコマンドの説明を参照してみます。

便利なコマンド一覧

以下は、PostgreSQLでよく使う便利なコマンドをマークダウンのテーブル形式でまとめたものです。

ページャを無効化

psqlのプロンプトで検索結果をlessで表示している際に、一気に最後まで表示するには、以下のコマンドを使用します。

\pset pager off

これでページャが無効化され、一気に全ての結果が表示されるようになります。

SQLファイルをデータベースに流し込む

以下のコマンドを使用して、PostgreSQLデータベースにSQLファイルを流し込むことができます。

psql -U ユーザー名 -d データベース名 -f ファイルパス.sql

特定のテーブルのカラム構成を確認したい

以下のSQLクエリを使用して、特定のテーブルのカラム構成を確認できます。

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'テーブル名';

特定のテーブルが占有しているハードディスク容量を確認する

pg_total_relation_size() や pg_relation_size() 関数を使って占有しているハードディスク容量を確認できます。

テーブル単体のサイズ（インデックス含まず）

これは テーブル本体のみのサイズを返します（インデックスやTOASTデータは含まれません）。

SELECT pg_size_pretty(pg_relation_size('your_table_name'));

テーブル＋インデックス＋TOAST（実質すべて）

これは、テーブル本体＋インデックス＋TOAST領域（大きなテキストなど）も含めた、そのテーブルが使用する全体容量を見積もってくれます。

SELECT pg_size_pretty(pg_total_relation_size('your_table_name'));

次は、上記のコマンドを詳細内訳つきで一覧表示する例です。

SELECT
  c.relname AS "テーブル名",
  pg_size_pretty(pg_relation_size(c.oid)) AS "本体サイズ",
  pg_size_pretty(pg_indexes_size(c.oid)) AS "インデックスサイズ",
  pg_size_pretty(pg_relation_size(c.reltoastrelid)) AS "TOAST本体サイズ",
  pg_size_pretty(pg_total_relation_size(c.reltoastrelid)) AS "TOAST合計サイズ（インデックス含む）",
  pg_size_pretty(
    pg_relation_size(c.oid)
    + pg_indexes_size(c.oid)
    + pg_total_relation_size(c.reltoastrelid)
  ) AS "手動計算の合計",
  pg_size_pretty(pg_total_relation_size(c.oid)) AS "PostgreSQL合計"
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE c.relname = 'your_table_name'
  AND n.nspname = 'public';

COALESCE

COALESCE は「合体する」の意味で、左から順に評価し最初のNULLでない値を返します。

-- 基本
SELECT COALESCE(NULL, 'A', 'B');         -- 'A'
SELECT COALESCE(NULL, NULL, 123);        -- 123
SELECT COALESCE(NULL, NULL, NULL);       -- NULL

-- カラムにデフォルト表示を当てる
SELECT id, COALESCE(name, '(未設定)') AS name FROM users;

-- 集計がNULLになるのを0で埋める
SELECT COALESCE(SUM(amount), 0) AS total FROM payments WHERE user_id = $1;

-- 日時のフォールバック（例: 表示用タイムスタンプ）
SELECT COALESCE(view_created_at, CAST(EXTRACT(EPOCH FROM created_at) AS int)) AS ts
FROM z_t_article;

関連記事

• MySQLと向き合うための 現場で使えるデータベース操作・SQLノート （MySQL 側の CLI / SQL の早引きに）
• シェルコマンド・チートシート （psql と組み合わせて使う grep・xargs・パイプ処理の参考に）
• Ubuntuでnginxを導入して最初の公開設定をする手順 （PostgreSQL を載せた VPS で Web を公開するときに）
• GitHub CI/CD で VPS へ自動デプロイするまで （DB を伴うアプリの VPS デプロイ運用に）

SQL / PostgreSQL を深掘りするための書籍

CLI と SQL の基本を押さえたら、業務向けのチューニングやモデリングは書籍でまとめて入れておくのが結局早いです。

• PostgreSQL 書籍を Amazon で検索
• SQL アンチパターン・SQL 入門書を Amazon で検索
• 達人に学ぶ DB 設計の書籍を Amazon で検索