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

ここでは現場でよく使うPostgreSQLの基本操作を忘備録として残しておきます。

▼ MySQLに関しては下記の記事をご覧ください。

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

  1. PostgreSQLをインストール
    1. Rocky Linuxにインストール
    1. macOSにインストール
  2. ネットワーク越しのPostgreSQLに接続する
  3. データベースとユーザーを作成する
  4. データベースを丸ごと移行する
  5. PostgreSQLのコマンド
    1. 便利なコマンド一覧
    1. ページャを無効化
  6. SQLファイルをデータベースに流し込む
  7. 特定のテーブルのカラム構成を確認したい

PostgreSQLをインストール

Rocky Linuxにインストール

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

リポジトリを有効化する

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

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

bash
sudo dnf install -y postgresql13-server

PostgreSQLの初期化

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

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

サービスの有効化と起動

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

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

バージョン確認

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

bash
psql --version

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

macOSにインストール

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

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

zsh
brew install postgresql

バージョンの確認:

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

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

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

zsh
brew services start postgresql

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

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

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

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

zsh
initdb /usr/local/var/postgres

psqlコマンドの確認

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

zsh
psql postgres

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

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

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

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

postgresql.conf
listen_addresses = '*'
pg_hba.confファイル（通常は/var/lib/pgsql/バージョン/data/pg_hba.conf）を編集し、外部接続を許可するIP範囲を設定します。
pg_hba.conf
host    all             all             192.168.1.0/24          md5

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

pg_hba.conf
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サービスを再起動します。

shell
sudo systemctl restart postgresql

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

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

PostgreSQLにログイン

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

bash
sudo -i -u postgres
psql

新しいユーザーの作成

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

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

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

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

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

PostgreSQLからログアウト

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

bash
exit

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

その他の方法

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

データベースを作成:

zsh
createdb mydatabase

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

ユーザーを作成:

zsh
createuser aragi

データベースに接続

zsh
psql -d mydatabase -U aragi

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

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

バックアップの取得

pg_dumpコマンドを使用して、データベース全体をバックアップします。この際、スキーマやデータ、トリガーなどすべてが含まれる形式でエクスポートします。
bash
pg_dump -U {元DBのユーザー名} -h {ホスト名} -p {ポート番号} -d {データベース名} -Fc -f backup.dump

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

バックアップのリストア

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

bash
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結果表示の拡張モードを有効化
\qpsqlを終了
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]クエリを実行し（結果をファイルやパイプに送信）；引数なしの\gはセミコロンと同じ
\watch [SEC]指定秒毎にクエリを実行

ページャを無効化

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

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

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

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

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

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

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

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

関連記事

アイデアノート > 開発関連
最後までご覧いただきありがとうございます！

▼ 記事に関するご質問やお仕事のご相談は以下よりお願いいたします。
お問い合わせフォーム

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