OSS-DB試験対策 緑本 ch4 標準付属ツール

OSS-DBPostgreSQL勉強メモ

出典: 

共通的なオプション

  • -V (--version)

    • バージョン表示
postgres@00aa48792429:~/data$ pg_ctl --version
pg_ctl (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1)
  • -? (--help)

    • pg_ctl共通、および各サブコマンドのヘルプ表示
    • 【所感】 -hじゃないのね
  • -t (--timeout=SECS)

    • 最大待ち時間
    • 未指定時のデフォルト60秒
    • 時間内に処理が完了しない場合、pg_ctlのexitコードは0以外になる
  • -w --wait

    • 操作の完了を待つ (デフォルト)
  • -W --no-wait

    • 操作の完了を待たない
  • -D (--pgdata)

    • データベースクラスタ指定

pg_ctl

  • PostgreSQLが稼働するホスト上でのみ実行可能
  • PostgreSQLの管理ユーザでのみ実行可能

    • rootはPostgreSQLの管理ユーザになれないので実行不可

pg_ctl initdb

  • pg_ctl initdbinitdbと同じ
  • initdbに渡すオプションを-oでまとめて渡す

    • -D (--pgdata)はpg_ctl共通オプションなので直接渡せる
postgres@00aa48792429:~/data$ pg_ctl initdb -D /home/postgres/data4 -o "--encoding=UTF8 --no-locale"

pg_ctl start

  • pg_ctl共通オプションで最大待ち時間を秒数で指定できる

    • -t(--timeout=SECS)
  • 時間内に処理が完了しない場合でもPostgreSQLサーバの起動自体はバックグラウンドで行われる
postgres@00aa48792429:~/data$ pg_ctl start -t 0 -D /home/postgres/data4 -o "-p 5436"
waiting for server to start... stopped waiting
pg_ctl: server did not start in time
2020-01-18 18:39:52.041 UTC [978] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-18 18:39:52.042 UTC [978] LOG:  listening on IPv4 address "0.0.0.0", port 5436
2020-01-18 18:39:52.042 UTC [978] LOG:  listening on IPv6 address "::", port 5436
2020-01-18 18:39:52.046 UTC [978] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5436"
2020-01-18 18:39:52.059 UTC [979] LOG:  database system was shut down at 2020-01-18 18:39:44 UTC
2020-01-18 18:39:52.062 UTC [978] LOG:  database system is ready to accept connections
postgres@00aa48792429:~/data$ echo $?
  • pg_ctl startは異常終了している
1
  • 【補】サーバ自体は起動している
postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4 
pg_ctl: server is running (PID: 978)
/usr/lib/postgresql/12/bin/postgres "-D" "/home/postgres/data4" "-p" "5436"

pg_ctl stop

  • -m (--mode=MODE)

    • pg_ctl stop特有のオプション
Options for stop or restart:
  -m, --mode=MODE        MODE can be "smart", "fast", or "immediate"
  • 公式
  • 3つのモードがある

    • s, smart

      • すべてのアクティブなクライアントの切断を待つ
      • すべてのオンラインバックアップの完了を待つ
      • サーバーがhot standbyならば、リカバリとストリームレプリケーションの終了を待つ
    • f, fast (デフォルト)

      • アクティブなクライアントの切断を待たない

        • 実行中のトランザクションはロールバックされ、全てのクライアントは強制的に切断される
      • オンラインバックアップの完了を待たない
    • i, immediate

      • 全サーバープロセスを直ちにabortする
      • 次回サーバー起動時にクラッシュリカバリが必要になる
  • kill -9使っちゃだめ

pg_ctl restart

  • PostgreSQL再起動
  • すでに停止している場合は単に起動のみ行う

pg_ctl reload

  • 【補】SIGHUP相当
  • 設定ファイルを再読み込みさせる

    • postgresql.conf
    • pg_hba.conf
  • reloadではなくrestartでないと反映されない設定項目もある

    • 【補】接続とかリソースまわりとか

pg_ctl status

postgres@00aa48792429:~/data$ pg_ctl start -D /home/postgres/data4 -o "-p 5555"
waiting for server to start....2020-01-18 20:26:18.830 UTC [1105] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-18 20:26:18.830 UTC [1105] LOG:  listening on IPv4 address "0.0.0.0", port 5555
2020-01-18 20:26:18.830 UTC [1105] LOG:  listening on IPv6 address "::", port 5555
2020-01-18 20:26:18.833 UTC [1105] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5555"
2020-01-18 20:26:18.846 UTC [1106] LOG:  database system was shut down at 2020-01-18 20:25:34 UTC
2020-01-18 20:26:18.850 UTC [1105] LOG:  database system is ready to accept connections
 done
server started
postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4
pg_ctl: server is running (PID: 1105)
/usr/lib/postgresql/12/bin/postgres "-D" "/home/postgres/data4" "-p" "5555"
postgres@00aa48792429:~/data$ pg_ctl stop -m smart  -D /home/postgres/data4
2020-01-18 20:26:40.193 UTC [1105] LOG:  received smart shutdown request
waiting for server to shut down....2020-01-18 20:26:40.198 UTC [1105] LOG:  background worker "logical replication launcher" (PID 1112) exited with exit code 1
2020-01-18 20:26:40.198 UTC [1107] LOG:  shutting down
2020-01-18 20:26:40.213 UTC [1105] LOG:  database system is shut down
 done
server stopped
postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4
pg_ctl status -D /home/postgres/data4
pg_ctl: no server running
postgres@00aa48792429:~/data$ 

pg_ctl kill

  • シグナル送信
  • Windows環境で、killコマンドがない場合に
postgres@00aa48792429:~/data$ pg_ctl start -D /home/postgres/data4 -o "-p 5555"
waiting for server to start....2020-01-18 20:30:22.999 UTC [1127] LOG:  starting PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-01-18 20:30:22.999 UTC [1127] LOG:  listening on IPv4 address "0.0.0.0", port 5555
2020-01-18 20:30:22.999 UTC [1127] LOG:  listening on IPv6 address "::", port 5555
2020-01-18 20:30:23.003 UTC [1127] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5555"
2020-01-18 20:30:23.017 UTC [1128] LOG:  database system was shut down at 2020-01-18 20:26:40 UTC
2020-01-18 20:30:23.021 UTC [1127] LOG:  database system is ready to accept connections
 done
server started
  • プロセス番号はpg_ctl statusで取得できる
postgres@00aa48792429:~/data$ pg_ctl status -D /home/postgres/data4
pg_ctl: server is running (PID: 1127)
/usr/lib/postgresql/12/bin/postgres "-D" "/home/postgres/data4" "-p" "5555"
  • 1127にhangup送信して設定再読込

    • pg_ctl reload相当
postgres@00aa48792429:~/data$ pg_ctl kill HUP 1127
2020-01-18 20:31:05.598 UTC [1127] LOG:  received SIGHUP, reloading configuration files
postgres@00aa48792429:~/data$ 
  • 利用可能なシグナル
Allowed signal names for kill:
  ABRT HUP INT KILL QUIT TERM USR1 USR2
  • pg_ctlコマンドとの対応

    • TERM

      • pg_ctl stop -m smart
      • 【補】killコマンドデフォルト、15。正常終了
    • INT

      • pg_ctl stop -m fast
      • 【補】Ctrl + Cのやつ
    • QUIT

      • pg_ctl stop -m immediate
      • 【補】終了とコアダンプ

ツールからPostgreSQLへの接続

postgres@00aa48792429:~/data$ psql -?
...
Connection options:
  -h, --host=HOSTNAME      database server host or socket directory (default: "local socket")
  -p, --port=PORT          database server port (default: "5432")
  -U, --username=USERNAME  database user name (default: "postgres")
...
  • -h (--host)

    • 接続先ホスト
    • 省略時、PGHOST環境変数
    • それもない場合、ローカルのUnixドメインソケット
  • -p (--port)

    • ポート番号
    • 省略時、PGPORT環境変数
    • それもない場合、コンパイル時に指定したデフォルトポート番号

      • configure時に--with-pgport=NUMBERで指定していなければ5432
  • -U (--username)

    • データベースユーザ名
    • 省略時、PGUSER環境変数
    • それもない場合、実行したOSユーザ名

      • ログイン権限があること

データベースユーザ

  • OSとは別にユーザの概念がある
  • データベースクラスタで共通
  • ユーザには権限を設定できる

    • GRANT/REVOKE文で設定する、テーブル単位のものとは別
  • 【補】role
  • 【補】\duメタコマンドで見れるやつ
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  • PostgreSQLにはグループ、ユーザの概念がある
  • 現在ではすべてRoleに統合されている

    • ユーザに関する操作を行った際にroleと出力されるのはそのため

createuser

  • 公式
  • CREATE ROLE文のラッパ
Usage:
  createuser [OPTION]... [ROLENAME]
  • ROLENAME

    • ユーザ名

      • 先述の通り、ユーザとグループは「ロール」に統合されている
      • 【所感】Composite Patternみたいな
  • OPTION

    • 接続オプション、createuser固有オプション
  • ロールに関するオプション
ロール 付与する 付与しない
Superuser -s, --superuser -S, --no-superuser (default)
Create DB -d, --createdb -D, --no-createdb (default)
Create role -r, --createrole -R, --no-createrle (default)
Login -l, --login (default) -L, --no-login
  • それ以外のオプション

    • -P (--pwprompt)

      • パスワードを設定する
      • 【補】-pじゃないのはportに使われてるから
    • --interactive

      • 対話的に設定する場合に指定
    • -e (--echo)

      • createuserが実行したSQLを出力
postgres@00aa48792429:~/data$ createuser -S -d -R -l -e aoba
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE aoba NOSUPERUSER CREATEDB NOCREATEROLE INHERIT LOGIN;
postgres@00aa48792429:~/data$ psql 

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 aoba      | Create DB                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  • 【補】no-loginはグループ等で使用する
postgres@00aa48792429:~/data$ createuser -L gochiusa
postgres@00aa48792429:~/data$ createuser syaro -g gochiusa
postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \du
                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create DB                                                  | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}

dropuser

Usage:
  dropuser [OPTION]... [ROLENAME]
  • 削除するには、Create role権限を持っていること

    • Superuserの削除にはSuperuser権限も必要
postgres@00aa48792429:~/data$ dropuser -i -e
Enter name of role to drop: aoba

Role "aoba" will be permanently removed.
Are you sure? (y/n) y

SELECT pg_catalog.set_config('search_path', '', false);
DROP ROLE aoba;

createdb

  • 公式
  • createdb固有のオプション
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  • -O, --owner=OWNER

    • データベースの所有者
    • スーパーユーザでのみ指定可能
  • -T, --template=TEMPLATE

    • template0とか指定するやつ
  • DB作れるユーザを作る
postgres@00aa48792429:~/data$ createuser -r -d -l -S  aoba
postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \du

                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create role, Create DB                                     | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}
  • 青葉ちゃんでDBつくる
postgres@00aa48792429:~/data$ createdb -U aoba --template=template0
  • データベース名を省略するとOSユーザ名と同名のデータベースが作成される

    • postgresは作成済なのでエラー
createdb: error: database creation failed: ERROR:  database "postgres" already exists
postgres@00aa48792429:~/data$ createdb -U aoba -e --template=template0 aoba
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE aoba TEMPLATE template0;
  • 作成したデータベースを確認
postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \l

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

postgres=# \q
  • aobaはスーパーユーザではないので、他の人のデータベースは作成できない
postgres@00aa48792429:~/data$ createdb -U aoba -O syaro -e syaro
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE syaro OWNER syaro;
createdb: error: database creation failed: ERROR:  must be member of role "syaro"
  • スーパーユーザでなら作れる
postgres@00aa48792429:~/data$  createdb -O syaro -e syaro
SELECT pg_catalog.set_config('search_path', '', false);
CREATE DATABASE syaro OWNER syaro;

dropdb

  • DB消すやつ
postgres@00aa48792429:~/data$ dropdb --interactive -e syaro
Database "syaro" will be permanently removed.
Are you sure? (y/n) y
y
SELECT pg_catalog.set_config('search_path', '', false);
DROP DATABASE syaro;

psql

データベースへの接続と切断

  • PostgreSQLへの接続、SQLコマンドの発行、結果の表示などを行うコマンド
  • -l, --list

    • \lメタコマンドと同じ
postgres@00aa48792429:~/data$ psql -l

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)
  • -c, --command

    • 指定したコマンド(メタコマンド、SQL)を実行して終了

      • メタコマンドとSQL混在は不可能
postgres@00aa48792429:~/data$ psql -c "\l"

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)
  • -f, --file

    • ファイルからコマンドを流し込む
    • メタコマンドとSQLコマンド混在可能
postgres@00aa48792429:~/data$ echo '\l' > commands && psql -f commands && rm commands

                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 aoba      | aoba     | UTF8     | en_US.utf8 | en_US.utf8 | 
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)
  • -U, --username

    • 接続ユーザ名 (共通オプション)
    • スーパーユーザだとプロンプトが=#
    • それ以外だとプロンプトが=>
postgres@00aa48792429:~/data$ psql

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

postgres=# \q
postgres@00aa48792429:~/data$ psql -U aoba -d aoba

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

aoba=> \q
  • -d, --dbname

    • 接続するデータベース名
    • -dを省略すると、最初の引数がデータベース名として解釈される
postgres@00aa48792429:~/data$ psql aoba

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

aoba=# \q

SQLコマンドの入力

postgres@00aa48792429:~/data$ psql -U aoba

psql (12.1 (Debian 12.1-1.pgdg100+1))
Type "help" for help.

aoba=> SELECT current_user, current_date;
SELECT current_user, current_date;
 current_user | current_date 
--------------+--------------
 aoba         | 2020-01-19
(1 row)
  • セミコロンまでで1文
aoba=> SELECT
SELECT
aoba-> current_user
current_user
aoba-> ,
,
aoba-> current_date
current_date
aoba-> ;
;
 current_user | current_date 
--------------+--------------
 aoba         | 2020-01-19
(1 row)

aoba=> \q
  • 2行目以降、=>から->に変わる

    • SQLコマンドが最後まで入力されていないことを意味する

メタコマンド

  • こちらは改行で終了を解釈
  • 規則的な名前の一覧系コマンド
メタコマンド 説明
\du データベースユーザ一覧
\dt テーブル一覧
\di インデックス一覧
\dv ビュー一覧
\ds シーケンス一覧
\dS システムカタログ一覧
\df 関数一覧
\dp 権限(privileges)一覧
  • system catalog

    • スキーマメタデータ、内部的な情報(internal bookkeeping information)を格納する
  • その他
メタコマンド 説明
\l (\list) データベース一覧
\d PATTERN テーブル、インデックス、ビュー、シーケンスの構成情報一覧
PATTERNには*とか?とかが使える
\d テーブル、ビュー、シーケンス一覧
\z \dpのエイリアス
\copy PostgreSQLとpsqlの間でテーブルデータをコピー
\password [USERNAME] 指定のユーザ(省略時は現在のユーザ)のパスワード変更
\c (\connect) [DBNAME] 指定のデータベース(省略時は現在接続中のデータベース)に再接続
\x 拡張表示モードのトグル
? メタコマンドのヘルプ
\h SQLコマンドのヘルプ
  • 拡張表示モード

    • 結果の1行を複数行に分けて表示するやつ
    • 【補】MySQLの\Gみたいなやつ
aoba=# \x

Expanded display is on.

aoba=# \du

List of roles
-[ RECORD 1 ]----------------------------------------------------------
Role name  | aoba
Attributes | Create role, Create DB
Member of  | {}
-[ RECORD 2 ]----------------------------------------------------------
Role name  | gochiusa
Attributes | Cannot login
Member of  | {}
-[ RECORD 3 ]----------------------------------------------------------
Role name  | postgres
Attributes | Superuser, Create role, Create DB, Replication, Bypass RLS
Member of  | {}
-[ RECORD 4 ]----------------------------------------------------------
Role name  | syaro
Attributes | 
Member of  | {gochiusa}
  • cf. 拡張表示オフ
aoba=# \x

Expanded display is off.

aoba=# \du

                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create role, Create DB                                     | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}

コマンドのバッチ実行

  • psql-fコマンドでファイルを流し込む
postgres@00aa48792429:~$ cat sample.sql
select current_user, current_date;
\du
postgres@00aa48792429:~$ psql -f sample.sql

 current_user | current_date 
--------------+--------------
 postgres     | 2020-01-19
(1 row)

                                    List of roles
 Role name |                         Attributes                         | Member of  
-----------+------------------------------------------------------------+------------
 aoba      | Create role, Create DB                                     | {}
 gochiusa  | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 syaro     |                                                            | {gochiusa}