サイトロゴ

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

著者画像
Toshihiko Arai
投稿日: 2024-07-04

はじめに

ここでは現場でよく使うPostgreSQLの基本操作を忘備録として残しておきます。 MySQLに関してはこちらの記事をご覧ください。

Rocky Linuxにインストール

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

リポジトリを有効化する

まず、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のインストールを行います。

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をインストール

brew install postgresql

バージョンの確認:

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

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

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

brew services start postgresql

デフォルトのデータベースを作成

まず、/usr/local/varディレクトリを作成し、適切な権限を設定します。

sudo mkdir -p /usr/local/var/postgres
sudo chown -R $(whoami) /usr/local/var/postgres

デフォルトのデータベースを作成します。

initdb /usr/local/var/postgres

psqlコマンドの確認

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

psql postgres

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

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

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

PostgreSQLの設定ファイル（通常は/var/lib/pgsql/バージョン/data/postgresql.conf）を開き、外部からの接続を許可するように設定を変更します。

listen_addresses = '*'

pg_hba.confファイル（通常は/var/lib/pgsql/バージョン/data/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サービスを再起動します。

sudo systemctl restart postgresql

ファイアーウォールを使っている場合は、適宜ポートを解放してください。 これでmacOS上のpsqlクライアントコマンドや、DataGrip.appなどから直接外部DBへ接続可能になりました。

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

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でよく使う便利なコマンドをマークダウンのテーブル形式でまとめたものです。

コマンド 説明
\l データベース一覧の表示
\dt 現在のデータベース内のテーブル一覧の表示
\d <テーブル名> テーブルの構造を表示
\i <ファイルパス> SQLファイルを実行
\x 結果表示の拡張モードを有効化
\q psqlを終了
SELECT * FROM <テーブル名>; テーブル内の全データを取得
INSERT INTO <テーブル名> (カラム1, カラム2, ...) VALUES (値1, 値2, ...); データを挿入
UPDATE <テーブル名> SET カラム1 = 値1, カラム2 = 値2 WHERE 条件; データを更新
DELETE FROM <テーブル名> WHERE 条件; データを削除
SELECT COUNT(*) FROM <テーブル名>; テーブルの行数をカウント
SELECT DISTINCT <カラム名> FROM <テーブル名>; 重複を排除したデータを取得
\copy <テーブル名> TO '<ファイルパス>' DELIMITER ',' CSV HEADER; テーブルデータをCSVファイルにエクスポート
\copy <テーブル名> FROM '<ファイルパス>' DELIMITER ',' CSV HEADER; CSVファイルからテーブルにデータをインポート
\g [(OPTIONS)] [FILE] クエリを実行し（結果をファイルやパイプに送信）；引数なしの
\watch [SEC] 指定秒毎にクエリを実行

ページャを無効化

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';

関連記事