psql の主要機能（Ubuntu 上）

【概要】

psql は，PostgreSQL データベースを操作するコマンドラインツールである．本ページでは，Ubuntu 上での psql の起動・終了，ロール・テーブル空間・データベース・スキーマの管理，SQL の実行とファイル出力，データの入出力について解説する．Windows 上での操作は，別ページ »を参照．

【目次】

【関連する外部ページ】

【サイト内の関連ページ】

PostgreSQL 活用ガイド

1. PostgreSQL のインストール手順

2. psql の基本操作ガイド

3. psql の起動と終了手順

Ubuntu における psql の起動と終了方法

Ubuntu のサービスアカウント postgres と peer 認証を使い，PostgreSQL の psql を操作する．

  1. 「sudo -u postgres psql」コマンドを実行する． 
    sudo -u postgres psql
  2. 「\c」コマンドで，現在使用中のロール名とデータベース名を確認する． 
    \c
  3. 「\l」コマンドでデータベースの一覧を確認する．利用可能なデータベースが表示されることを確認する． 
    \l
  4. 「\q」コマンドで psql を終了する．

4. PostgreSQL のロール管理

ロール，既定データベース，現在のロールの確認方法

  1. 「sudo -u postgres psql」を実行する．
  2. 次のコマンドでロール一覧と現在の接続情報を確認する． 
    \c
\du

新規ロールの作成とデータベース管理者権限の付与手順

次の手順で，新しいロールを作成し，PostgreSQL データベース管理者権限を付与する．

  1. ロールの作成と確認手順

    参考: https://www.postgresql.org/docs/current/sql-createrole.html 

    create role testuser with superuser createdb createrole login encrypted password 'hoge$#34hoge5';
\du
\q
  2. 新規作成したロールでの動作確認
    Ubuntu 環境では，次の「Ubuntu における追加設定」を確認する． 
    psql -U testuser -d postgres
\q

Ubuntu における追加設定

パスワード認証を有効にするため，pg_hba.conf を編集する．設定ファイルの場所はバージョンにより異なる（例：/etc/postgresql/<バージョン>/main/pg_hba.conf）．次の1行を追加する．現行の PostgreSQL では scram-sha-256 認証が既定であり，これを指定する（md5 を指定した場合も，パスワードが scram 形式で保存されていれば自動的に scram-sha-256 が使われる）． 

local   all             all                                scram-sha-256
この設定を行わないと，peer 認証のままとなり，パスワード認証を使うログイン（psql -U testuser など）で「Peer authentication failed...」というエラーが表示される．

pg_hba.conf の変更を反映するため，PostgreSQL サーバを再起動する．エラーメッセージが表示されないことを確認する（<バージョン>には，導入した PostgreSQL のメジャーバージョンを指定する）． 

sudo pg_ctlcluster <バージョン> main restart
sudo pg_ctlcluster <バージョン> main status

作成したロールで psql を起動し，「\c」コマンドで現在の接続情報を確認する．データベースは「-d postgres」オプションで postgres を指定する． 

psql -U testuser -d postgres
\c
\q

ロールの削除方法

ロールを削除する場合は，psql で「drop role testuser;」コマンドを実行する．

5. テーブル空間の管理

参考: https://www.postgresql.jp/document/18/html/manage-ag-tablespaces.html

テーブル空間の一覧表示

psql で「\db+」コマンドを実行し，テーブル空間の一覧を表示する． 

\db+

Ubuntu でのテーブル空間作成手順

Ubuntu で次の仕様のテーブル空間を作成する．

  1. テーブル空間の作成手順

    次のコマンドを実行する．「sudo chown -R postgres /var/sqltable1」の postgres は，Ubuntu のサービスアカウント名である． 

    sudo mkdir /var/sqltable1
sudo chown -R postgres /var/sqltable1
sudo chmod 700 /var/sqltable1
psql -U testuser -d postgres
create tablespace mytablespace owner testuser location '/var/sqltable1';
\q
  2. 設定の確認 
    psql -U testuser -d postgres
\db+
\q

デフォルトテーブル空間の確認

空の場合は「pg_default」が使用される． 

show default_tablespace;

デフォルトテーブル空間の変更方法

この設定は psql の終了時にリセットされる． 

show default_tablespace;
set default_tablespace to mytablespace;
show default_tablespace;

一時テーブル用テーブル空間の確認

参考: https://www.postgresql.jp/document/18/html/manage-ag-tablespaces.html 

show temp_tablespaces;

一時テーブル用テーブル空間の変更

一時テーブル用テーブル空間を次のように設定する．

「set temp_tablespaces to ...;」で一時テーブル用テーブル空間を変更し，「show temp_tablespaces;」で設定を確認する．この設定は psql の終了時にリセットされる． 

set temp_tablespaces to mytablespace;
show temp_tablespaces;

6. データベースの管理

データベース一覧の表示

「\l+」コマンドですべてのデータベースの詳細情報を確認できる． 

\l+

新規データベースの作成手順

参考: https://www.postgresql.jp/document/18/html/manage-ag-createdb.html

次の仕様でデータベースを作成する．

  1. データベースの作成 
    create database mydb owner testuser tablespace mytablespace encoding 'UTF8';
  2. 設定の確認

    「-d mydb」で新規作成したデータベースを指定し，「\l+」ですべてのデータベースの詳細情報を確認する． 

    psql -U testuser -d mydb
\l+

現在の接続データベースの確認

「\c」コマンドで，現在使用中のデータベース名とロール名を確認できる． 

\c

接続データベースの切り替え

「\c mydb」コマンドで，使用するデータベースを mydb に切り替える． 

\c
\c mydb
\c

7. スキーマの管理

スキーマ一覧の表示

「\dn+」コマンドですべてのスキーマの詳細情報を表示する． 

\dn+

新規スキーマの作成手順

次の仕様でスキーマを作成する．

  1. スキーマの作成 
    create schema myschema;
  2. 設定の確認

    「\dn+」ですべてのスキーマの詳細情報を確認する． 

    \dn+

現在のスキーマの確認

「show search_path;」コマンドで，現在のスキーマを表示する． 

show search_path;

現在のスキーマの変更

「set search_path to myschema;」コマンドで，現在のスキーマを myschema に変更する．「show search_path;」コマンドで設定を確認する．この設定は psql の終了時にリセットされる． 

show search_path;
set search_path to myschema;
show search_path;

8. デフォルト設定の構成

9. SQL の実行とファイル出力

テーブルの定義とデータ登録

「\dt+」コマンドで，すべてのテーブルの詳細情報を表示する． 

create table commodity (
    type integer primary key not null,
    name text not null,
    price integer);
insert into commodity values( 1, 'apple', 50 );
insert into commodity values( 2, 'orange', 20 );
insert into commodity values( 3, 'strawberry', 100 );
insert into commodity values( 4, 'watermelon', 150 );
insert into commodity values( 5, 'melon', 200 );
insert into commodity values( 6, 'banana', 100 );
\dt+

SQL クエリの実行 

select * from commodity;

実行結果の例：

 type  name        price
 ----  ----------  -----
    1  apple          50
    2  orange         20
    3  strawberry    100
    4  watermelon    150
    5  melon         200
    6  banana        100

実行画面：

10. psql の拡張オプション

データベース一覧の取得

「psql -U testuser -l」コマンドでもデータベース一覧を表示できる．

SQL 実行結果のファイル出力

psql 起動時に「-L ＜ファイル名＞」オプションを指定する．

SQL ファイルの実行

psql 起動時に「-f ＜ファイル名＞」オプションを指定する．

11. テーブルデータの入出力

テーブルデータのインポート・エクスポートには，PostgreSQL の copy コマンドを使用する．Windows 環境でファイルを扱う際は，パス中のバックスラッシュに注意する．