共通的なオプション
-
-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
- 【補】終了とコアダンプ
-
ツールから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)一覧 |
-
- スキーマメタデータ、内部的な情報(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}