トップページデータ処理PostgreSQLpsql の主要機能(Ubuntu を使用)

psql の主要機能(Ubuntu を使用)

psql は,PostgreSQL に関する各種操作を,簡単に行えるツール.

目次

  1. PostgreSQL のインストール
  2. psql の基本操作
  3. psql の起動と終了
  4. PostgreSQL のロール
  5. テーブル空間
  6. データベース
  7. スキーマ
  8. Ubuntu で既定(デフォルト)で使用するロール名とデータベース名,既定(デフォルト)で使用するテーブル空間,テンポラリテーブルが作成されるテーブル空間,カレントスキーマの設定
  9. SQL の実行,SQL 実行結果のファイル出力
  10. psql のオプション
  11. テーブルのインポート,エクスポート

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

参考Webページ:

1. PostgreSQL のインストール

2. psql の基本操作

3. psql の起動と終了

Windows での psql の起動と終了

  1. Windows のコマンドプロンプトの起動
  2. psql -U postgresの実行

    postgres」はPostgres データベース管理者のユーザ名

    psql -U postgres
    

    [image]
  3. Postgres データベース管理者のパスワード

    プロンプトが出るので, PostgreSQL データベース管理者 (postgres) のパスワード を入力する.

    画面にパスワードが表示されないのは正常動作.

    [image]
  4. 「\c」により,使用されている PostgreSQL のロール名と,オープンされているデータベース名を確認.
    \c
    

    [image]
  5. 「\l」 により,データベースの確認

    postgres, template0, template1 の 3 つのデータベースが表示されることを確認.

    \l
    

    [image]
  6. \q」で psql を終了する

    [image]

Ubuntu での psql の起動と終了

Ubuntu のサービスアカウント postgres と peer 認証により,PostgreSQL の psql を使ってみる.

  1. 「sudo -u postgres psql」の実行

    sudo -u postgres psql
    

    [image]
  2. 「\c」により,使用されている PostgreSQL のロール名と,オープンされているデータベース名を確認.

    \c
    

    [image]
  3. 「\l」 により,データベースの確認.

    データベースが表示されることを確認.

    \l
    

    [image]
  4. 「\q」により,psql の終了

    [image]

4. PostgreSQL のロール

ロール,既定(デフォルト)で使用されるデータベース名,使用中のロール名の確認

参考 Web ページ: https://www.postgresql.jp/document/12/html/database-roles.html

Windows の場合

  1. 「psql -U postgres」を実行したとする
  2. ロールの一覧,現在使用中のデータベース名とロール名の確認

    \c
    \du
    

    [image]

Ubuntu の場合

  1. 「sudo -u postgres psql」を実行したとする
  2. ロールの一覧,現在使用中のデータベース名とロール名の確認

    \c
    \du
    

    [image]

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

次のように,ロールを新規作成し,PostgreSQL データベース管理者権限の付与を行う.

  1. ロールの新規作成と確認

    参考 Web ページ: https://www.postgresql.org/docs/12/sql-createrole.html

    create role testuser with superuser createdb createrole login encrypted password 'hoge$#34hoge5';
    \du
    \q
    

    [image]
  2. 確認のため,新規作成したロールで psql を使ってみる

    Ubuntu の場合には,下に記載の「Ubuntu での追加設定」を確認すること.

    psql -U testuser -d postgres
    \q
    

    [image]

Ubuntu での追加設定

  1. 全ユーザ の md5 認証を有効にする.

    /etc/postgresql/12/main/pg_hba.conf を書き換えて,全ユーザである all の md5 認証を有効にする.

    そのために,次の1行を追加する

    local   all             all                                md5
    

    [image]

    上の設定を行わないと,「Peer authentication failed...」というメッセージが出る.このメッセージから,md5 認証ができていないことになる.

    [image]
  2. pg_hda.conf を書き換えたので,PostgreSQL サーバを再起動する.

    エラーメッセージが出ていないことを確認すること.

    sudo pg_ctlcluster 12 main restart
    sudo pg_ctlcluster 12 main status
    

    [image]
  3. 新規作成されたロールの確認

    確認のため,いま作成したロールで,psql を起動してみる. その後「\c」で,現在使用中のデータベース名とロール名の確認を行う. 使用するデータベース名としては,「-d postgres」で,postgres を指定する.

    psql -U testuser -d postgres
    \c
    \q
    

    [image]

ロールの削除

ロールを削除したいときは,psql で,「drop role testuser;」のように操作する.

5. テーブル空間

参考 Web ページ: https://www.postgresql.jp/document/12/html/manage-ag-tablespaces.html

テーブル空間の一覧

psql で次のように操作する.「\db+」はテーブル空間の一覧.

\db+

[image]

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
    

    [image]
  2. 確認表示
    psql -U testuser -d postgres
    \db+
    \q
    

    [image]

既定(デフォルト)で使用されるテーブル空間の確認

表示が空のときは,「pg_default」

show default_tablespace;

[image]

既定(デフォルト)で使用されるテーブル空間の変更

この設定は,psql の終了により消えるものである.

show default_tablespace;
set default_tablespace to mytablespace;
show default_tablespace;

[image]

テンポラリテーブルが作成されるテーブル空間の確認

参考 Web ページ: https://www.postgresql.jp/document/12/html/manage-ag-tablespaces.html

show temp_tablespaces;

[image]

テンポラリテーブルが作成されるテーブル空間の変更

ここでは,テンポラリテーブルが作成されるテーブル空間を次のように設定する.

「set temp_tablespaces to ・・・;」により,テンポラリテーブルが作成されるテーブル空間を変更している. 「show temp_tablespaces;」により確認している.

この設定は,psql の終了により消えるものである.

set temp_tablespaces to mytablespace;
show temp_tablespaces;

[image]

データベース

データベースの一覧

\l+」ですべてのデータベースの詳細情報を確認.

\l+

[image]

[image]

データベースの新規作成

参考 Web ページ: https://www.postgresql.jp/document/12/html/manage-ag-createdb.html

次のように,データベースを新規作成する.

  1. データベースの新規作成

    psql で次のように操作する.

    create database mydb owner testuser tablespace mytablespace encoding 'UTF8';
    

    [image]
  2. 確認表示

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

    psql -U testuser -d mydb
    \l+
    

    [image]

    [image]

現在使用中のデータベースの確認

\c」は,現在使用中のデータベース名とロール名の確認.

\c

[image]

使用するデータベースの変更

\c mydb」は,使用するデータベースを mydb に変更.

\c
\c mydb
\c

[image]

スキーマ

スキーマの一覧

\dn+」はすべてのスキーマの詳細情報の表示.

\dn+

[image]

スキーマの新規作成

次のように,スキーマを新規作成する.

  1. スキーマの新規作成

    次のように操作する.

    create schema myschema;
    

    [image]
  2. 確認表示

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

    \dn+
    

    [image]

カレントスキーマの確認

show search_path;」は,カレントスキーマの表示.

show search_path;

[image]

カレントスキーマの変更

to myschema;」は,カレントスキーマを, myschemaに変更.

show search_path;」は,カレントスキーマの表示.

この設定は,psql の終了により消えるものである.

show search_path;
set search_path to myschema;
show search_path;

[image]

8. 既定(デフォルト)で使用するロール名とデータベース名,既定(デフォルト)で使用するテーブル空間,テンポラリテーブルが作成されるテーブル空間,カレントスキーマの設定

9. SQL の実行,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+

[image]

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

以下は,動作画面.

[image]

10. psql のオプション

データベースの一覧表示

データベースの一覧表示は,「psql -U testuser -l」でも行うことができる.

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

psql の起動において,「-L <ファイル名>」を付ける.

ファイルに入った SQL の実行

psql の起動において,「-f <ファイル名>」を付ける.

11. テーブルのインポート,エクスポート

テーブルのインポート,エクスポートには,PostgreSQL の copy コマンドを使う. Windows マシンでファイルを使う場合には,2つ注意点がある