OSS-DB試験対策 緑本 ch4 標準付属ツール
共通的なオプション
-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 initdb
はinitdb
と同じ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
- 【補】終了とコアダンプ
- TERM
ツールから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
)-p
(--port
)-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)
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
- その他
メタコマンド | 説明 |
---|---|
\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}