*

postgresql

公開日: : Other

root@ubuntu-VirtualBox:~# su - postgres
postgres@ubuntu-VirtualBox:~$ psql
psql (9.5.6)
Type "help" for help.

postgres=# \du
postgres=# CREATE ROLE hellosql LOGIN CREATEDB PASSWORD 'hellosql';
/usr/lib/postgresql/9.5/bin/initdb --help
/usr/lib/postgresql/9.5/bin/pg_ctl
postgres@ubuntu-VirtualBox:/usr/lib/postgresql/9.5/bin$ /usr/bin/pg_lsclusters
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log
postgres@ubuntu-VirtualBox:/usr/lib/postgresql/9.5/bin$ pg_ctl stop -D /var/lib/postgresql/9.5/main/ -m s
waiting for server to shut down.... done
server stopped
postgres@ubuntu-VirtualBox:/usr/lib/postgresql/9.5/bin$ pg_ctl start -D /home/postgres/data -w
waiting for server to start....LOG:  database system was shut down at 2017-05-29 00:10:17 JST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
 done
server started
postgres@ubuntu-VirtualBox:/usr/lib/postgresql/9.5/bin$ 

pg_archivecleanup
pg_basebackup
pg_config
pg_conftool
pg_createcluster
pg_ctlcluster
pg_dropcluster
pg_dump
pg_dumpall
pg_isready
pg_lsclusters
pg_receivexlog
pg_recvlogical
pg_renamecluster
pg_restore
pg_upgradecluster
pg_virtualenv

$ initdb -D /home/postgres/data/ --no-locale
#OK
$ initdb -E UTF8 --no-locale /home/postgres/data/
#NG
$ initdb --encording=UTF8 --no-locale /home/postgres/data/
$ pg_ctl start -D /home/postgres/data -w
$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 online postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

initdbコマンドはデータベースクラスタを作成する。
initdbを実行することでユーザーデータベースの雛形となるテンプレートデータベースとデフォルトの接続先データベースとなるpostgresというデータベースが作成される。initdbのオプションとしてデータベースクラスタを作成する場所、データベースのデフォルトのエンコーディング、データベースのデフォルトのロケールなどを指定できる。
initdbを実行するときデータベースのデフォルトのロケール指定は省略しても構わない。
initdbを実行することで2つのテンプレートデータベース(template0,template1)が作成される。
データベースクラスタを作成する場所を省略した場合は、環境変数PGDATAで指定される場所にデータベースクラスタが作成される。
–usernameオプションによってinitdbが作成するデータベースユーザの名前を指定する。
データベースで日本語を使用する場合は、エンコーディングとしてEUC_JP,UTF-8などを指定できる。

テンプレートデータベースはユーザが新しくデータベースを作成するときにテンプレートとして使用される。テンプレートに定義されている情報をすべて引き継いでユーザのデータベースが作成されるため、かくデータベースに共通となるテーブルを事前にテンプレートデータベースに登録しておくと新しく作成したデータベースには共通のテーブルが含まれ李こととなる。
テンプレートデータベース。template1データベースはカスタマイズしてユーザデータベースの雛形として使用する。template0データベースは更新ができずテンプレートデータベースの初期状態を保持するデータベース。
2つのテンプレートデータベースの初期状態はすべて同じ。

データベースクラスタはPostgresqlにおけるデータベースの格納領域。Postgresqlが管理するデータはすべてデータベースクラスタ内で管理される。データベースクラスタの実態はファイルシステム上の1つのディレクトリとなっていて、当該ディレクトリの配下にPostgresqlの動作に必要なデータが保存される。データベースクラスタのディレクトリの配置場所はユーザが自由に決めることができる。

1つのデータベースクラスタは1つのPostgresqlのみが利用できる。
データベースクラスタにはユーザ情報などのグローバルデータが含まれる。データベースの雛形であるテンプレートデータベースもデータベースクラスタに含まれる。
ソースコードからPostgresqlをインストールした場合、データベースクラスタが存在しないのでinitdbコマンドによってデータベースクラスタを作成する。

initdbコマンドにはデータベースの環境を指定する様々なオプションがある。initdbコマンドによってユーザデータベースの雛形となるテンプレートデータベースが作成される。
データベースのエンコーディングはデータベースごとに異なるものにすることができる、エンコーディングにSJISを指定することはできない。サーバのロケールはクライアントのロケールに依存しない。
データベースエンコーディングとクライアントエンコーディングは 一致させる必要はない
initdbの対象とするディレクトリにすでにデータベースクラスタが作成されている場合は、initdbは失敗する。ディレクトリを消さないといけない。initdbコマンドはデータベースクラスタを新しく作成する。データベースクラスタが作成されたディレクトリは、一般ユーザが容易にアクセスできないようにセキュリティ制御される。ロケールとエンコーディングは利用者に大きな影響を与えるので利用環境を踏まえて適切に設定する必要がある。
initdbコマンドはローカルホストでのみ実行できる。
ロケールを明示的に指定しないとOSのロケールが使用される。
エンコーディングを明示的に設定しないとOSのロケールからエンコーディングが決定される。
initdbコマンドによってデータベースクラスタを作成したユーザはPostgresqlの管理ユーザと呼ばれる
ロケールを無効にしても日本語のデータを取り扱うことができる

標準付属ツール
pg_ctl
createuser
dropuser
createdb
dropdb
createlang
droplang
psql

pg_ctl initdb -D /pgdata -o "--encording=UTF8 --no-locale"
pg_ctl start -D /pgdata -w -t 120
#-D>データベースクラスタは/pgdata、-w>起動完了まで待つ、-t 120>起動するまで120秒まつ
pg_ctl stop -D /pgdata -m smart
#/pgdataのデータベースクラスタ上で稼働しているPostgresqlをスマートシャットダウンで停止している
#-m>シャットダウンモード 
# s[mart]:スマートシャットダウン、クライアントの接続がすべて切れてから停止する、デフォルトのシャットダウンモード
# f[ast]:高速シャットダウン、クライアントの接続をすべて強制的に切断してから停止。実行中のトランザクションはすべてロールバックされる。
# i[mmediate]:即時シャットダウン、緊急停止する、次回の起動時には復旧処理が必要になる
#-W>停止が完了するまで待たない
postgres@ubuntu-VirtualBox:~$ pg_ctl stop -D /home/postgres/data -m fast
LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down
LOG:  shutting down
waiting for server to shut down....LOG:  database system is shut down
 done
server stopped
postgres@ubuntu-VirtualBox:~$ 


pg_ctl restart -D /pgdata -m f
#-m:シャットダウンモード
#-w:起動が完了するまで待つ
#-t:最大待ち時間、デフォルトは60秒
pg_ctl reload -D /pgdata
 pg_ctl status -D /pgdata
pg_ctl: server is running (PID: 10634)
/usr/psql-9.0/bin/postgres "-D" "/pgdata"
pg_ctl kill シグナル名 プロセスID
#TERM:スマートシャットダウンpg_ctl stop -m smart
#INT:高速シャットダウンpg_ctl stop -m fast
#QUIT:即時シャットダウンpg_ctl stop -m immdiate
#HUP:設定ファイルを再読み込みさせるpg_ctl reload

4-3
psql -h 192.168.0.10 -p 5432 -U user1
ホスト名は環境変数PGHOSTが使われ、省略した場合ツールを実行したホストが接続先になる。

createuser -P -l -d user1
-l:ログイン権限を与える
-P:パスワードを設定
-s:スーパーユーザー
-d:データベースの作成権限を与える
-r:ユーザの作成権限を与える

dropuser
-i:ユーザを削除して良いか確認する

createdb
-E:エンコーディング
-l:ロケール
-O:データベースの所有者となるユーザーの指定
-T:テンプレートのDBを指定

createdb -U user1 tempdb1
所有者はuse1、テンプレートデータベースはtemplate1、エンコーディングとロケールはtemplate1に準じる

create -E EUC_JP -l C -T template0 tempdb2
所有者はcreatedbを実行したユーザ、ロケールはC、エンコーディングはEUC_JP

cat << EOF > exam.sql
SELECT current_user , current_date;
\du
EOF

psql -f exam.sql tempdb2

psql -c “\du” -U postgres tempdb2

pg_ctlは、Postgresqlの起動、停止、状態確認などに使用する管理するツール。pg_ctlでは、サブコマンドによってどのような管理操作を行うのかを指定する。サブコマンドにはinitdb start stop restart reloadがある。サブコマンドごとに設定できるオプションが違う。pq_ctl initdbはデータベースクラスタを作成する。pg_ctl startはPostgresqlをバックグラウンドで起動する。
pg_ctlはローカルホストからのみ実行できる。リモートにあるホストからは実行できない。
pg_ctlを実行するユーザーはinitdbを実行してデータベースクラスタを作成したPostgresql管理ユーザに限定される。
pg_ctl startはデフォルトの環境において実際にPostgresqlが起動を完了するまで待たない。
pg_ctl startにおいて起動するデータベースクラスタを指定しないと、環境変数PGDATAが使用される
pg_ctl stop でスマートシャットダウンモードを指定すると接続が残っている間データベースはシャットダウンしない

pg_ctl stop はデータベースをシャットダウンする。しゃっとだうんもーどを指定することで方法を指定できる。
シャットダウン処理中はクライアントからの新しい接続は禁止される。
pg_ctl stop -m fast を実行するとクライアントからの接続は強制的に接続される。実行中のトランザクションはすべてロールバックされる。データベースの再起動時にはロールバックされたトランザクションを手動で実行する必要がある。
pg_ctl stop -m immdiateを実行すると、すべての処理が即時に終了される。Postgresがクラッシュした場合と同じ状況になるためデータベースの再起動後に復旧処理が必要となる
-W オプションを使用するとPostgresqlが実際に停止していないにもかかわらずpg_ctlの処理が終了する。
pg_ctl stop はPostgreslが停止するまでデフォルトで60秒まつ、待ち時間は-tオプションで指定できる。

pg_ctl kill は、プロセスにシグナルを送信する。送信できるシグナルはHUP INT QUIT ABRT TERM USR1 USR2がある。pg_crl kill はkillコマンドをサポートしていないWindowsなどの環境でシグナルを送信するのに役立つ。pg_ctl kill によってPostgresqlのマスタプロセスにシグナルを送信することでpg_ctlの一部コマンドいと同じ効果が得られる。
pg_ctl kill HUP > pg_ctl reload
pg_ctl kill INT > pg_ctl stop -m fast
pg_ctl kill QUIT > pg_ctl stop -m immediate
pg_ctl kill TERM > pg_ctl stop -m smart
pg_ctl kill USR > ユーザ指定のシグナルを送ることに相当する

Postgresql のツールを使用する場合、稼働中のPostgresqlに接続する。接続にはTCP/IPもしくはUNIXドメインに夜通信が使用できる。Postgresqlのツールは、Postgresqlに接続できるのであれば、どのホストからでも実行できる。ツールがPostgresqlに接続するための情報は-h -p -Uなどのオプションで指定する。
Postgresqlに接続するデータベースユーザはログイン権限を持っている必要があります。
接続先ホストとしてIPアドレスもしくはホスト名を指定できる。
接続先ホストを省略した場合は環境変数のPGHOSTが使用される。
接続先のポート番号を省略し環境変数のPGPORTも設定されていない場合はデフォルトのポート番号5432が使用される。
Postgresqlの管理ユーザでもあるOSユーザだけでなくそれ以外のOSユーザもpsqlをp実行してデータベースへ接続できる。
PostgresqlにはOSと同様にユーザの概念がある。データベースに接続(ログイン)するには、適切な権限を持つデータベースアカウントが必要になる。PostgresqlのユーザはOSのユーザとは別のユーザで、PostgresqlとOSでユーザ名を一致させる必要はない。
ユーザはデータベースクラスタ内で一意にしなければならない。同じ名前の複数のユーザを1つのデータベースクラスタ内に作成することはできない。
ユーザを作成する権限とデータベースを作成する権限を別に設定できる。ユーザを作成する権限はCREATEROLE権限、データベースを作成する権限はCREATEDB権限という。
データベースユーザのパスワードとOSパスワードは別のもの。
データベースに接続できる権限をLOGIN権限という。
SUPERUSER権限をもつユーザは、データベースに対するあらゆる操作を行える。

createuserはデータベースユーザを作成するコマンド。ユーザを作成する際にデータベース全体を管理できるSUPERUSER権限、データベースを作成できるCREATEDB権限、ユーザを作成できるCREATEROLE権限を与えることができる。
-Lオプションでユーザの作成時にログイン権限を与えないことが可能となる。
createuserコマンドの入力時にオプション指定を省略した場合に、必要なオプションについて対話的に処理が行われる。createuserコマンドによってコマンドで指定した名前のユーザがデータベースのオブジェクトとしてデータベースクラスタに作成される。
スーパーユーザーを作成する場合、SUPERUSER権限が必要。CREATEDB権限だけではスーパーユーザを作成できない。
データベースクラスタに作成される初期ユーザはSUPERUSER権限を有している。

createdbは指定した名前のデータベースを作成する。createdbのオプションには、データベース所有者名、テンプレートデータベース、エンコーディングなどを指定することができる。
-O:データベースの所有者を指定
-T:テンプレートデータベースを指定
-E:エンコーディングを指定
最大同時接続数はcreatedbのオプションでは指定できない
データベース領域の最大サイズはcreatedbのオプションで指定できない

dropuserは、指定した名前のデータベースユーザを削除する。dropdbは指定した名前のデータベースを削除する。droplangは指定した名前の手続き言語を削除する。どのコマンドもデータベースのオブジェクトを削除するために使用する。Postgresqlは標準でPL/pgSQL、PL/Tcl、PL/Perl、PL/Pythonという4つの手続き言語を配布している。手続き言語はデータベースで登録する必要がある。
ユーザを削除するためにはSUPERUSER権限もしくはCREATEROLE権限が必要。データベースを削除できるのはスーパーユーザもしくはデータベースの所有者のみ。データベースの所有者であれば、データベースの作成権限がなくともデータベースを削除できる。

psqlは、Postgresqlに付属する対話型インターフェースのツールでPostgresqlへの接続、SQLコマンドの発行、結果の表示などを行う。psqlでは、事前にファイルに記述しておいたコマンドをまとめて実行できる。
psqlの接続を切断し、psqlを終了させるためには、\q か\quitコマンドを使用する。
psqlでは長いSQLコマンドの行を分けて入力できる。SQLコマンドの終了はセミコロンで識別する。末尾にセミコロンのある行を入力するとSQLコマンドを実行できる。
標準のpsqlのコマンドプロンプトから入力したメタコマンドは一部のメタコマンドを除き大半が`Postgresqlによって実行される。
標準のpsqlのコマンドプロンプトはスーパーユーザが=#、一般ユーザが=>。
psqlのメタコマンドは1行で入力しなければならない。SQLコマンドのように行を分割して入力する方法はない。

postgresql.confは、Postgresqlのパラメーたを設定する。ファイルはinitdbで作成される。$PGDATAディレクトリないに配置される。
設定反映のタイミング
○不可(internal):ユーザが設定変更できないパラメータ。SHOW文で設定値を確認できる。
○起動(postmaster):設定の反映にPostgresqlの起動を必要とするパラメータ。Postgresqlの起動中に設定値を変更する場合は、postgresql.confを編集した後、Postgresqlを再起動しなければならない。postgresql.confで”#(change require restart)”とコメントされている。
○再読み込み(sighup)Postgresqlの起動に加えて、pg_ctl reloadを使ったpostgresql.confの再読み込み似寄り設定反映できるパラメータ。Postgresqlの起動中に設定変更する場合は、postgresql.confを編集した後pg_ctl reloadでpostgresql.confを再読み込みさせれば良い。Postgresqlサービスを停止させることなく設定値を変更できる。
○スーパーユーザのみ(suset):Postgresqlの起動、pg_ctl reloadによる設定値の変更に加え、スーパユーザによるSET文で設定値を変更できるパラメータ。
○いつでも(user):Postgresqlの起動、pg_ctl reloadによる設定値の変更に加え、スーパーユーザ及び一般ユーザによるSET文で設定値を変更できるパラメータ。

psql -c "SHOW max_connections"
postgres@ubuntu-VirtualBox:~$ vim /home/postgres/data1/postgresql.conf
max_connections = 100 => max_connections = 10
postgres@ubuntu-VirtualBox:~$ pg_ctl reload -D /home/postgres/data1
LOG:  received SIGHUP, reloading configuration files
LOG:  parameter "max_connections" cannot be changed without restarting the server
LOG:  configuration file "/home/postgres/data1/postgresql.conf" contains errors; unaffected changes were applied
server signaled
postgres@ubuntu-VirtualBox:~$ psql -c "SHOW max_connections"
 max_connections 
-----------------
 100
(1 row)

-接続と認証
listen_address:locahost、起動、文字列
port:5432、起動、整数
max_connections:100、起動、整数

設定例
listen_address = ‘192.168.0.10, 192.168.0.20’
port = 9999
max_cnnections = 64

-クライアント接続デフォルト
search_path: ‘”$user”,public’、いつでも、文字列
default_transaction_isolation:read commited、いつでも、
client_encording:’SQL_ASCII’、いつでも、文字列
クライアントからのSQLをSJISとして扱い、SQLの結果をSJISに変換してから返却する
client_encording = ‘SJIS’

-エラー報告とログ取得
log_destination:再読み込み
設定値:
stderr:サーバログを平文で標準エラー出力に出力する
csvlog:サーバログをcsv形式で標準エラー出力に出力する。この設定では、logging_collectorをonに設定しなければならない。
syslog:サーバログをsyslogに出力する
logging_collector:off、起動
サーバログは、Postgresqlの稼働状態を確認したり、問題の原因を解析したりするのに必要不可つな情報。
確実にサーバログを残すように設定する。
log_destination = stderr
logging_collector = off
はサーバログは標準エラー出力に流れるだけでファイルには残らない

log_directory
logging_collectorがonで、サーバログを記録するときだけ意味のある設定。

log_filename
デフォルト値:postgresql-%Y-%m-%d_%H%m%S.log
再読み込みで設定変更

サーバログとsyslogとログファイルに出力する設定
log_destination = ‘syslog, stderr’
logging_collector = on
log_directory = ‘examlog’
log_filename = ‘postgresql-%Y%m%d.log’

log_min_message
どのレベルのメッセージをサーバログに出力するか
デフォルト値:WARNING
設定変更のタイミング:スーパーユーザのみ
ログメッセージのレベル
PANIC:
 致命的なエラーは発生したことを示す。すべてのセッション(クライアントとPostgresqlサーバ間の接続)が強制的に切断されPostgresqlは停止する
FATAL:
 特定のセッションで問題が発生したことを示す。そのセッションだけ切断され、他のセッションは影響を受けない
LOG:
 データベースの管理者が着目すべきPostgresqlの動作ログを意味する
ERROR:
 特定のトランザクションで問題が発生したことを示す。そのトランザクションだけアボートされ、他のセッションやトランザクションへの影響はない
WARNING:
 想定外の動作に対する警告メッセージを意味する

log_line_prefix
サーバログの行頭に出力する内容の設定
デフォルト値:空文字列
設定変更のタイミング:再読み込み
%から始まる文字は置き換えできる
主な%文字
%u:データベースユーザ名
%d:データベース名
%p:プロセスID
%t:タイムスタンプ(年月日時分秒)
%%:%の文字そのもの

log_line_prefix = ‘%t [%p] ‘

SET パラメータ名 { TO | = } { 設定値 | DEFAULT }
設定変更のタイミングが「スーパーユーザのみ」もしくは「いつでも」のパラメータについて、Postgresqlの起動中に設定値を変更する(「スーパーユーザのみ」のパラメータを設定変更するには、スーパーユーザでSET文を実行する必要がある)。SET文により変更された設定値は、そのSET文を実行したセッションのみに効果がある。pg_ctl reloadのようにPostgresql全体に設定変更が反映されることはない。
設定値としてDEFAULTを指定することでデフォルト地に設定できる。
SET文により設定値を変更できるのは一部のパラメータだけである。

SHOW文
指定したパラメータについて、現在の設定値を表示する。
表示されるのは、SHOW文を実行したセッションの設定値。SET文で設定値を変更した場合、そのセッションと他のセッションとでは、SHOW文の結果が異なる可能性がある。パラメータ名としてALLを指定することで、すべての
パラメータについて設定値を表示できる。次の例では、SET文でsearch_pathに新しいスキーマを設定している。
その際、設定値をSHOW文で確認している。

postgres@vbox:~$ psql
psql (9.5.6)
Type "help" for help.

postgres=# SHOW search_path
postgres-# ;
   search_path   
-----------------
 "$user", public
(1 row)

postgres=# CREATE SCHEMA exam_schema;
CREATE SCHEMA
postgres=# SET search_path TO 'exam_schema';
SET
postgres=# SHOW search_path
postgres-# ;
 search_path 
-------------
 exam_schema
(1 row)

-pg_hba.conf
pg_hba.confはクライアント認証を設定するファイル。postgresql.confと同様にinitdbで作成され、
$PGDATAディレクトリ内に配置される。クライアント認証の設定とは、どのホスト上のクライアントがどの
データベースユーザを使って、どのデータベースに接続する時に、どの方法で認証するかを指定するもの。
host-based authentication

# TYPE DATABASE USER ADDRESS METHOD

# “local” is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local replication postgres trust
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust

local データベース名 ユーザ名 認証方式
host データベース名 ユーザ名 CIDRアドレス 認証方式
host データベース名 ユーザ名 IPアドレス ネットマスク 認証方式

クライアント認証は、1行に1つずつ設定する。複数行に分けて1つのクライアント認証を設定することができない。
クライアント認証は、複数のフィールドで構成され、それらは少なくとも1つのタブもしくは空白で区切られている必要がある。
フィールド内に空白を含めるときは、設定値をダブルクォート(”)で囲む
空白の行は無視され、シャープ(#)から行末まではコメントと扱われる。
#は行末まではコメントとして行末までは扱われる。#は行頭にある必要はなく、行の途中にもおくことができる

Postgresqlは、クライアントからの接続を受け付けると、その接続に対して、接続方法、接続先のデータベース、接続に使われている

pg_hba.confでは、一致する行のうち、より先頭にあるものが評価される

5-1
postgresql.conf はpostgresqlのパラメータを設定するファイル。
initdbで作成され、$PGDATAディレクトリ内に配置される。
パラメータは1行に1つずつ設定する。
パラメータ名と設定値の間のイコールは省略できる。
パラメータ名と設定値の間には少なくとも1つの空白が必要
シャープ#から行末まではコメントとして扱われる
文字列はシングルクォートで囲むpostgresql.confのパラメータについて論理値として設定できるのは、true false on off yes no 1 0とこれらの設定値の先頭の文字t f y n です。
大文字と小文字の区別はない

postgresql.conf の設定値は設定値によってPostgresqlに反映させるタイミングが異なる。具体的には、Postgresqlの起動時、pg_ctl reload コマンドによる設定ファイルの再読み込み、SET文によるオンデマンドの反映がある。
設定値によってはユーザが変更できないものもある。
データベースの作成時に限って有効な設定値はない。
ユーザが変更できないものを覗き、Postgresqlのパラメータは起動時に有効になる。
pg_ctl reload コマンドによって設定ファイルを再読み込みした時に有効になる設定値がある。
しかし、すべての設定値が有効になることはない
スーパーユーザがSET文で有効にできる設定値がある
一般ユーザでもSET文で有効にできる設定値はある

listen_address にはpostgresqlがクライアントからの接続を監視するIPアドレスもしくはホスト名を設定する。
設定する値はPostgresqlにおいてlisten_addressを設定しないとクライアントからのTCP/IP接続を受け付けない。
listen_address ではPostgresqlサーバのIPアドレスを設定する。
設定値が空の場合、クライアントからのTCP/IP接続を受け付けない。複数のIPアドレスを間まで区切って指定できる。
IPアドレス意外にホスト名を指定できる。
変更内容はPostgresqlの再起動によって有効になる

port はクライアントからの接続を受け付けるポート番号、maxconnectionsは同時接続可能な最大数、default_transaction_isolation はデフォルトの
トランザクション分離レベル、client_encordingはクライアントエンコーディングを指定するパラメータ。
設定反映のタイミングはパラメータによって異なるので運用時には注意が必要。
portはPostgresqlの起動(再起動)のタイミングで有効になる。デフォルト値は5432。
max_connectionのデフォルト値は100。100以上でも100未満でも指定できる。
max_connectionsを超えた接続をしようとすると接続エラーとなる
トランザクションに分離レベルとしてread uncommitted, read committed, repeatable read, serializableを指定できる。default_transaction_isolationのデフォルトの値はread commited。
clirent_encordingがSQL_ASCIIの場合、クライアントとデータベース間の自動的な変換が行われない

PostgresqlのサーバログはPostgresqlの稼働状況を確認したり問題の原因を解析したりするのに必要不可欠な情報。
Postgresqlの運用に際しては確実にサーバログを残すように設定する。デフォルトの設定ではサーバログが標準エラーにながれるだけでファイルには残らないため注意する
ログの出力先はstderr,csvlog,syslogから選択できる
ログをファイルに出力する場合は、logging_collectorwponにする必要がある
ログファイル名に出力年月日を含めることは可能でlog_filenameをせっていすることで書式をカスタマイズできる
ログメッセージの出力レベルlog_min_messageを指定することでサーバログに出力するメッセージを選択できる。log_min_messageをPANICに設定することでFATALメッセージを出力させないことが可能。
ログはデフォルトでは標準エラー出力stderrへ出力されるだけでファイルへはリダイレクトされない。

postgresqlには多くの設定値がある。基本的にはデフォルト値が決められているのですべての設定値を手作業で設定する必要はない。
ログファイルの出力先を変更した場合、pg_ctl reloadコマンドによる設定ファイルの再読み混みをすることでも変更内容を有効にできる。
SET文によって変更した設定値は、当該SET文を実行したセッションないでのみ有効となります。
SET文において設定値としてDEFAULTを指定し設定値をデフォルトに戻すことができる。
log_line_prefixを指定することでサーバログの行頭にタイムスタンプを出力させることができる。タイムスタンプ意外にも、データベースユーザ名、データベース名、プロセスIDなども出力させることができる
Postgresqlのパラメータの設定内容はSHOW文によって出力することもできる

pg_hba.confはクライアント認証を設定するファイル。postgresql.confと同様にinitdbで作成され$PGDATAディレクトリ内に配置される。クライアント認証の設定とはどのクライアントがどのデータベースユーザを使ってどのデータベースに接続するときにどの方法で接続するかを指定するもの。
Postgresqlは、クライアントからの接続要求を受け付けるとその接続要求に対して、接続方法、接続先のデータベース、接続に使われているデータベースユーザー、接続元のアドレスが一致するクライアント認証の行をpg_hba.confから探す。一致する場合はその行で指定されている方法で接続を認証する。一致する行がない場合はその接続を拒否する。
接続方法としてUNIXドメイン接続に対応するときはlocal、TCP/IP接続に対応するときはhostを指定する。
pg_hba.confはpg_ctl reloadコマンドによって設定変更を反映できる。
接続先のデータベースはカンマ区切りで複数指定できる。
接続するクライアントを1つのIPアドレスのみに限定させる場合はIPアドレスのマスク長を32にする。
すべてのIPアドレスからの接続を許可する場合はIPアドレスとして0.0.0.0/0
を指定する。

SET文はパラメータを設定変更するSQL。Postgresqlの起動中に設定値を変更できる。SET文により変更された設定値は、そのSET文を実行したセッションのみに効果があることに注意する。pg_ctl reloadのようにPostgresql全体に設定変更が反映されることはない。設定値としてDEFAULTを指定することでデフォルト値に戻すことができる。
スキーマをserach_pathに登録するSET文の書式は次の通り。
SET serach_path TO スキーマ名

pg_hba.conf の書式は次のようになっている。
local データベース名 ユーザ名 認証方式
host データベース名 ユーザ名 CIDRアドレス 認証方式
host データベース名 ユーザ名 IPアドレス ネットマスク 認証方式

最初のフィールドは接続方式の指定でUNIXドメイン接続に対応するときはlocal、TCP/IP接続に対応するときはhostを指定する。認証方式にはtrust reject md5 password を指定できる。trustは接続を常に許可する、rejectは接続を常に拒否する、md5は接続に対してパスワード認証を行い、MD5で暗号化されたパスワードを認証時にクライアントに要求する。passwordは接続に対してパスワード認証を行い平文のパスワードを認証時にクライアントに要求する。
Postgresqlはクライアント空の接続を受け付けるとその接続に対して接続方法、接続先のデータベース、接続に使われているデータベースユーザ、接続元のアドレスが一致するクラアンとの行をpg_hba.confから探す。一致する行がある場合はその行で指定されている方法で接続を認証する。一致する行がない場合は接続を拒否する。pg_hba.confの検索は先頭から行われ最初に条件に合致した行が認証に使用される。
ホスト192.168.16.8からデータベースユーザuser1によるexamdbへの接続は上から3行目に最初に合致するので接続がrejectされる。ホスト192.168.16.16からデータベースユーザuser1によるexamdbへの接続は上から4行目に最初に合致するのでmd5認証に成功した場合に許可される。

6
Postgresql稼働中にバックアップを行う方法にはpg_dumpコマンド、pg_dumpallコマンドがある。
リストアの方法はバックアップ形式によって異なり、平文形式ではpsqlコマンドを、平文形式以外ではpg_restoreコマンドを使ってリストアできる。
バックアップコマンド リストアコマンド
平文形式 pg_dump pg_dumpall psql
平文形式以外 pg_dump pg_restore

pg_dumpallはすべてのデータベースをバックアップする。

pg_dump pg_dumpallコマンドのオプション
-Fp 出力形式として平文plainを指定する
-Fc 出力形式としてカスタムcustomを指定する
-Ft 出力形式としてtar形式を選択する
-f バックアップ先のファイル名を指定する。省略した場合は標準出力となる

pg_dump

リストア:平文の場合
psql -f db.sql
リストア:カスタム形式
pg_restore -d データベース名

pg_restore -d examdb examdb.dump
-dオプションでデータベース名を指定しなかった場合は、平文形式のSQLコマンドが標準形式に出力される。
-dオプションでデータベース名を指定しなかった場合の例

pg_restore examdb.dump

pg_dump -Fc examdb -f examdb.dump
drop examdb
create examdb
pg_restore -d examdb examdb.dump

pg_ctl stop

cd $PGDATA/..
tar cvf backup.tar data

PITRでのバックアップ&リカバリの流れ
前準備/設定 WALアーカイブの準備、postgresql.confの設定
ベースバックアップ pg_start_backup()関数/pg_stop_backup()関数によるバックアップ
リカバリ 1ベースバックアップのリストア
2未アーカイブのWALファイルをコピー
3recovery.confの設定、Postgresqlの起動

PITRでは、ある時点でデータベース全体のバックアップを取得する。これをベースバックアップという。Postgresqlには、データベースに対して行われたすべての変更を記録する機構が備わっており、内容はログに出力される。このログのことを先行書き込みログあるいはWAL(write ahead logging)とよぶ。
WALはWALファイルと呼ばれる16メガバイトのファイルに記録される。WALファイルにはデータベースに変更があるたびにデータが書き込まれていく。WALのファイルの数が一定以上に達すると再利用され、古いWALは捨てられるようになる、PITRではWALファイルが再利用されてWALが失われる前にWALを別な場所に保存するようにする。これをWALアーカイブという。
このように、運用中はベースバックアップに加え、WALアーカイブをデータベースの更新情報として蓄積していきます。リカバリ時はベースバックアップに対し、WALアーカイブに記録されたデータベースの変更を再実行していく。これはロールフォワードリカバリと呼ばれる。リカバリ時は、アーカイブが完了していないWALも使用して障害発生時に近い状態までデータベースを回復できる。

PITRでのバックアップの前準備/設定
postgresql.confファイルの各種パラメータを設定する。PITRに関連する主なパラメータ
postgresql.conf の設定項目
wal_level :
WALに書き込まれる情報の度合いを指定する。デフォルト値はminimal。PITR使用時はarchiveまたはhot_stanbyを指定する。
archive_mode :
WALアーカイブを有効にするかどうかの指定。デフォルトはoff。PITR使用時はonに設定する。
archive_command :
WALファイルをWALアーカイブとしてコピーするためのシェルコマンドを指定する。

postgresql.conf
wal_level = hot_stanby
archive_mode = on
archive_command = ‘cp %p /mnt/archivedir/%f’

PITRによるリカバリ時に必要な情報がWALに書き込まれるようhot_stanbyを指定する。
archive_commandパラメータに指定されたコマンドは、Postgresqlの運用中にWALファイルがデータで満杯になると実行される。運用中にWALアーカイブのコピー先ディレクトリを確認すると$PGDATA/pg_xlogディレクトリ下のWALファイルがコピーされていることがわかる。

PITRによるベースバックアップ
PITRで使用するベースバックアップを取得する際はPostgresqlを停止させる必要はない。スーパーユーザーで稼働中のPostgresqlに接続してpg_start_backup()関数を呼び出したあと、データベーすクラスタのディレクトリを丸ごとバックアップし、pg_stop_backup()関数を呼び出しする。スーパーユーザーで操作する必要がある。

postgres@vbox:~$ echo $PGDATA
/home/postgres/data
postgres@vbox:/home/postgres/data$ psql -c “SELECT pg_start_backup(‘label_test’)”
pg_start_backup
—————–
0/2000028
(1 row)

postgres@vbox:/home/postgres/data$ ls
PG_VERSION pg_commit_ts pg_multixact pg_stat pg_xlog
backup_label pg_dynshmem pg_notify pg_stat_tmp postgresql.auto.conf
base pg_hba.conf pg_replslot pg_subtrans postgresql.conf
global pg_ident.conf pg_serial pg_tblspc postmaster.opts
pg_clog pg_logical pg_snapshots pg_twophase postmaster.pid
postgres@vbox:/home/postgres/data$ tar czvf /home/postgres/backup.tar.gz ./

postgres@vbox:/home/postgres/data$ pg_ctl stop -D $PGDATA
LOG: received fast shutdown request
LOG: aborting any active transactions
LOG: autovacuum launcher shutting down
LOG: shutting down
waiting for server to shut down….LOG: database system is shut down
done
server stopped

postgres@vbox:/home/postgres/data$ cd /home/postgres/
postgres@vbox:/home/postgres$ ls
archivedir backup.tar.gz data data1 data2 logfile posgrelog
postgres@vbox:/home/postgres$ mkdir temp
postgres@vbox:/home/postgres$ mv $PGDATA ./temp/
postgres@vbox:/home/postgres$ tar xavf ./backup.tar.gz

postgres@vbox:/home/postgres$ rm -rf /home/postgres/data/pg_xlog/
postgres@vbox:/home/postgres$ cp -r /home/postgres/

postgres@vbox:/home/postgres$ cp -r ./temp/data/pg_xlog $PGDATA

postgres@vbox:/home/postgres$ pg_ctl start -D /home/postgres/data
pg_ctl: another server might be running; trying to start server anyway
server starting
postgres@vbox:/home/postgres$ LOG: database system was interrupted; last known up at 2017-07-04 22:32:37 JST
LOG: starting archive recovery
cp: cannot stat ‘/home/postgres/archivedir/000000010000000000000002’: No such file or directory
LOG: invalid checkpoint record
FATAL: could not locate required checkpoint record
HINT: If you are not restoring from a backup, try removing the file “/home/postgres/data/backup_label”.
LOG: startup process (PID 2447) exited with exit code 1
LOG: aborting startup due to startup process failure

postgres@vbox:/home/postgres/archivedir/data$ cat /home/postgres/data/recovery.conf
restore_command = ‘cp /home/postgres/archivedir/%f %p’
postgres@vbox:/home/postgres/archivedir/data$ vim /home/postgres/data/recovery.conf
restore_command = ‘cp /home/postgres/archivedir/data/%f %p’
postgres@vbox:/home/postgres/archivedir/data$ pg_ctl start -D /home/postgres/data -w
waiting for server to start….LOG: database system was interrupted; last known up at 2017-07-04 22:32:37 JST
LOG: starting archive recovery
LOG: restored log file “000000010000000000000002” from archive
LOG: redo starts at 0/2000028
LOG: consistent recovery state reached at 0/2000130
LOG: restored log file “000000010000000000000003” from archive
cp: cannot stat ‘/home/postgres/archivedir/data/000000010000000000000004’: No such file or directory
LOG: invalid record length at 0/4000098
LOG: redo done at 0/4000028
cp: cannot stat ‘/home/postgres/archivedir/data/000000010000000000000004’: No such file or directory
cp: cannot stat ‘/home/postgres/archivedir/data/00000002.history’: No such file or directory
LOG: selected new timeline ID: 2
cp: cannot stat ‘/home/postgres/archivedir/data/00000001.history’: No such file or directory
LOG: archive recovery complete
LOG: MultiXact member wraparound protections are now enabled
LOG: database system is ready to accept connections
LOG: autovacuum launcher started
done
server started
postgres@vbox:/home/postgres/archivedir/data$ ls /home/postgres/data/recovery*
/home/postgres/data/recovery.done

6-4 COPY文と\copyコマンド
Postgresqlでは、テーブルのデータをファイルへコピー、あるいはファイルのデータをテーブルへローディングする方法としてSQLのCOPY文を使う方法とpsqlコマンドのメタコマンド\copyコマンドを使う方法が用意されている。SQLのCOPY文と\copyコマンドの違いは動作環境にある。COPY文はサーバ側で動作し、\copyコマンドはクライアント側で動作する。
 COPY文を実行するとサーバ側のファイルにアクセスしてデータベースのデータのやりとりをする。\copyコマンドはクライアント側のファイルにアクセスして、ネットワークを介してデータベースのデータのやりとりをする。psqlコマンドでデータベースへ接続し、\
copyコマンドを使って容易にデータをやりとりすることができるが、\copyコマンドはクライアントとサーバとの間でネットワークを介した通信が発生するため大量のデータを扱うのであれば、サーバ側で動作するCOPY文を使った方が効率的である。
COPY文はサーバ側で動作し\copyコマンドはクライアント側で動作する。

-DB作ってテーブル作るところまで
postgres@vbox:/$ createdb examdb1
postgres@vbox:/$ psql exam1
examdb1=# CREATE TABLE temp1 (
examdb1(# c1 text);
CREATE TABLE
examdb1=# \d
List of relations
Schema | Name | Type | Owner
——–+——-+——-+———-
public | temp1 | table | postgres
(1 row)
=================================

examdb1=# COPY temp1 TO ‘/home/postgres/temp1tablecopy.txt’;
COPY 0
postgres@vbox:/$ cat /home/postgres/temp1tablecopy.txt
postgres@vbox:/$

6-4-2 \copy コマンド
psql コマンドのメタコマンド\copyコマンドはSQLのCOPYコマンドとは異なり、クライアント側で動作する。データベースのデータをやりとりするファイルもクライアント側のファイルとなる。\copyコマンドはネットワークを介してクライアントとサーバ側でデータを送受信するためネットワークの負荷が高くなる。\copyコマンドはCOPY文とは異なりスーパーユーザーである必要はない。
examdb1=# \copy temp1 to /home/postgres/tbl1.txt csv header
COPY 0
examdb1=# \q
postgres@vbox:/$ cat /home/postgres/tbl1.txt
c1

Q6.1
❌A.pg_dumpコマンドはPostgresqlを停止させてバックアップを取得する
D.pg_dumpコマンドにより平文形式で取得したバックアップはpsqlコマンドでリストアする
E.平文以外の形式で取得したバックアップはpg_restoreコマンドでリストアする
pg_dumpコマンドは、pg_dumpallコマンドは、Postgresqlを稼働させたままバックアップを取得する。データベースを指定してバックアップする場合はpg_dumpコマンドをすべてのデータベースを、データベースクラスタ全体をバックアップする場合はpg_dumpallコマンドを使用する。pg_dumpコマンドでは-Fオプションをつけてバックアップの出力形式を指定できる。すべてのデータベースを対象とするpg_dumpallコマンドは複数のデータベースへ接続するため通常スーパーユーザで実行する。
pg_dump、pg_dumpallコマンドにより平文形式で取得したバックアップはpsqlコマンドでリストアする。
平文以外で取得したバックアップはpg_restoreコマンドでリストアする。

Q6.2
❌C.pg_dumpall -Fp -f dbbackup.dump
⭕️E.pg_dump -Fc sampledb -f dbbackup.dump
⭕B.pg_dump -Fc sampledb > dbbackup.dump
⭕D.pg_dumpall -f dbbackup.dump

Q6.3
⭕️B.
⭕️C.
⭕️D.
❌E.
pg_dumpコマンドに夜バックアップはPostgresqlの稼働中に取得できかつ一貫性のあるバックアップを得ることができる。pg_dumpコマンドの出力はデフォルトで平文形式となる。平文形式は汎用的であるため異なるバージョンのPostgresqlにも利用できる。
pg_dumpコマンドのデフォルトの出力形式は平文形式。tar形式で出力する場合は-Ftオプションを指定する。
平文形式で出力されたバックアップは汎用的な形式で出力されるため、Postgresqlのバージョンアップ時のデータ移行に利用できる。pg_dumpコマンドによって平文形式で出力されたバックアップはpsqlコマンドで回復する。
pg_dumpコマンドの出力中は他のユーザのデータベースアクセスを防ぐことはできない。pg_dumpコマンドはオンラインバックアップが可能になっている。

Q6.4
❌A,
⭕️C.pg_dump -Fc sampledb -f sampledb.dump
⭕️E.pg_dump -Fc sampledb > sampledb.dump

Q6.5
❌B
⭕️A.pg_restore -d sampledb sampledb.dump

Q6.6
⭕️B.psql -f alldb.dump

Q6.7
⭕️B
⭕️C
Postgresqlのバックアップには稼働中にバックアップするオンラインバックアップとDBを停止させてバックアップするオフラインバックアップがある。オンラインバックアップにはpg_dumpとpg_dumpallコマンドがある。

Q6.7
⭕️B,⭕️C,⭕️D

Q6.9
❌C,❌E
⭕️A COPY tab1 TO ‘/tmp/tab1.txt’
⭕️D \copy tab1 to tab1.txt

Q6.10
⭕️C,⭕️E
PostgresqlではWALを用いてデータベーすのリカバリを行う。WALの格納領域には限りがあるが、WALファイルを定期的にアーカイブする機能によってWALファイルを別の場所に保存できる。WALファイルのアーカイブを使用することによってPITRに必要なWALを得ることができる。
PITR時に必要な情報がWALに書き込まれるようにするためにはpostgresql.confのwal_levelパラメータにarchiveまたはhot_stanbyを指定しなければならない
WALをアーカイブするためにpostgresql.confのarchive_commandにシェルコマンドを登録しておく。
PITRで使用するバックアップを取得する際にPotgresqlを停止させておく必要はない。
PUTRによってりかばりを行う場合、Postgresqlを停止させる。障害が生じたデータベースクラスタは別ディレクトリに移して$PGDATAディレクトリがない状態からリカバリを開始する。

Read committedではファジーリードとファントムリードが起こる。
postgres=# SHOW default_transaction_isolation;
default_transaction_isolation
——————————-
read committed
(1 row)
postgres=# SET default_transaction_isolation TO ‘serializable’;
SET
postgres=# SHOW default_transaction_isolation; default_transaction_isolation
——————————-
serializable
(1 row)
postgres=# BEGIN ISOLATION LEVEL read committed;
BEGIN
postgres=# SHOW transaction_isolation;
transaction_isolation
———————–
read committed
(1 row)
postgres=# SET default_transaction_isolation TO ‘read committed’;
SET

問10
トランザクションは業務上の処理単位でデータベース処理を含んでいる。トランザクションは業務上の処理単位なので分離できない。トランザクションの特性。
原始性Atomicity
 トランザクションは実行を完了するかあるいは全く実行されないかのどちらか。
整合性Consistency
 トランザクションの実行結果に関係なく、データベースは整合性を保つ。
分離性Isolation
 ここのトランザクションは独立しており別のトランザクションによる処理の影響を受けない。
持続性Durability
 完了したトランザクションの結果は保持されデータベースh窓外などから守られる。

データベースの位置透過性
 データベースを利用するユーザーにとっては必要なデータが得られることが重要。データベースが物理的にどこにあるかについては必要なことではない。データベースの位置透過性は利用者にデータベースのbぬ釣り的な位置を意識させずにデータを提供できるようにするデータベースの性質である。

「ひとまとまりの処理」であるトランザクションの処理開始と処理終了を制御するのがBEGIN、COMMIT、ROLLBACK(ABORT)コマンド。トランザクションの開始時にはBEGINを発行しトランザクションの開始を宣言する。処理が正常に終了すればCOMMITを発行する。処理の途中でエラーや不備が生じトランザクションを取り消したい場合には、ROLLBACK(ABORT)を発行すると、それまで実行されていた内容が全て取り消され、トランザクションの開始前の状態に戻る。BEGINとCOMMITに挟まれた処理が1つのトランザクションとなる。
A トランザクションの開始時にBEGINを発行しトランザクションに終了時に COMMITを発行する。
B トランザクションの実行途上においてエラーや不備が生じた場合は、ROLLBACKを発行しトランザクションを取り消す。
C PostgreSQLでは、autocommitを有効にしておくとSQLコマンドの実行前にBEGINが暗黙的に発行され、SQLコマンドの実行前にBEGINが暗黙的に発行され、SQLコマンドの実行が成功した時にCOMMITが自動的に発行される。
D ROLLBACKコマンドを発行した場合、処理は取り消しされる。自動的に再実行されないため必要に応じて手作業で再実行することになる。
E データベースマネジメントシステムに障害が発生した場合、システムそのものがダウンする。自動的にROLLBACKが発行されることはない。ただし、トランザクションは自動的に取り消しされる。

トランザクションにエラーが発生した場合はROLLBACK(ABORT)を発行しトランザクションを取り消しする。SAVEPOINTはトランザクションの中で部分的にROLLBACKを行いたい場合に使用する。任意の名称のSAVEPOINTをトランザクション中に作成し、必要に応じてSAVEPOINTまでロールバックできる。
A ABORTとROLLBACKは同じ意味。使い分けはない。
B SAVEPOINTには任意の名称をつけることができる。
C SAVEPOINTは複数設定でき、任意のセーブポイントまでロールバックできる。
D トランザクションの中で設定したSAVEPOINTは任意のタイミングで削除できる。
E 1つのトランザクション中に同じ名称のSAVEPOINTを再設定できる。この場合、後から設定したSAVEPOINTが有効になる。

トランザクションのACID特性のうち分離性は同時実行性などアプリケーションに大きな影響を与える。多くのデータベースマネジメントシステムでは分離レベルを設定できるようになっている。一般的には分離性において次の4つのレベルが規定されている。
分離性 弱
Read uncommitted : 実行中のトランザクションの未コミットの挿入/更新/削除の結果が、別のトランザクションから見える(ダーティリードが生じる)
Read committed : 実行中のトランザクションのコミットされた挿入/更新/削除結果が別のトランザクションから見える。(ファジーリードが生じる)
Repeatable read : 実行中のトランザクションのコミットされた挿入が、別のトランザクションから見える(ファントムリード)

問1
PostgresqlはSQL:2008の主な機能のほとんどをサポートしている。しかし、SQL:2008には完全準拠していない。
PostgreSQLは個人目的、学術目的、商用目的など、目的に関わらず無償で使用関わらず無償で使用できる。
PostgreSQLはWindows上でも動作する
PostgreSQLを使用していて発見したバグ情報は、メーリングリストやWebサイトから報告することができる。報告は必須ではない
PostgreSQLは多国語に対応していて日本語にも対応している。日本にはユーザ会があり、活発に情報交換が行われている
問2
PostgreSQLのライセンスはBSDライセンスを基本としている。オリジナルを派生させた二次著作物におけるライセンスはオリジナルのライセンスと同じでなくて構わない
PostgreSQLのソースコードを組み込んで作成した他のプログラムについてソースコードは公開する必要はない。そのためPostgreSQLは商用として使い安いライセンス体型であると言える。
PostgreSQLのライセンスはBSDライセンスのみで商用ライセンスというものは存在しない。
BSDライセンスではソースコードは自由に使用でき改変も可能。かつては開発者への謝辞が義務付けられていたが不要になった。しかし、著作権の表示は必要。
問3
PostgreSQLではm商用目的か商用目的でないかに関わらずソースコードを自由に改変できる。費用についても商用・非商用を問わず無償となる。PostgreSQLは最初POSTGRESという名称で主に研究目的で開発された1996年位PostgreSQLに改称され、バージョンは6.0となった。PostgreSQLのバージョンは「x.y.z」という形式で表されます。「x.y」はメジャーバージョン、「z」マイナーバージョン。マイナーバージョンは主にバグ修正によってカウントアップされる。
問4
PostgreSQLの開発について
開発者はワールドワイドに分散しているため、基本的にメーリングリストで情報交換が行われている。開発メンバが実際に顔を合わせて検討を進める場合もある。重要な審議事項について会合によって決定することが決まっている訳ではない。
開発コミュニティサイトからはソースコード、バイナリコードとも自由にダウンロードできる。コミュニティのメーリングリストから配信されるようなことはない。
開発途上のものであっても自由にダウンロードすることができる。個人の責任で利用するものであって、損害を賠償するような制度はなく、入手に際して事前に「権利の放棄」を宣言するような必要もない。
PostgreSQLのメーリングリストは誰でも自由に参加できる。コミュニティにおいて時期バージョンの検討がなされている。積極的にコミュ二ティ活動をすれば審議事項にも参画が可能となる。
PostgreSQLは「PostgreSQL Global Development Group」というコミュニティで開発が進められている。
問5
PostgreSQLには「日本PostgreSQLユーザ会」(JPUG)という国内のユーザ会がある。ユーザ会ではPostgreSQLのポータルサイト”Let’s Postgres”を開設しており、有用な情報が豊富に掲載されている。
PostgreSQLのマニュアルはソフトウェアに同梱されている。インターネットからのダウンローども可能。
日本PostgreSQLユーザ会には個人の資格で参加できる。
日本PostgreSQLユーザ会ではPostgreSQLに関する各種セミナー/イベントの 運営企画を行なっている。他にPostgreSQLの英文マニュアルの日本語への翻訳を行なっている。
日本PostgreSQLユーザ会のURLは「http://www.postgresql.jp」
PostgreSQLは英語環境で開発されているため原文は英語となっている。コミュニティによって英文のマニュアルが日本語やフランス語に翻訳されている。
問6
データベースを構築する範囲を対称世界という。概念データモデルは対象世界を抽象化することによって作成する。データベースとして実装するために、概念データモデルにおけるデータとデータの間の関係を論理的に実現した論理データモデルを作成する。論理データモデルは、データベースマネジメントシステムが使用している論理構造に対応させて作成する。
経営層から一般従業員へと木構造をしている組織では階層モデルが適している。しかし、複数の部門を兼任するような社員が存在する場合は、上位階層が複数になるためネットワークモデルが適している。
論理データモデルに関係モデルを適用する場合、利用者のデータは表形式で表現される。表間の関連づけはデータを検索(使用)するときに属性(列)の値を比較することによって行われる。
概念データモデルをERモデルによって表現する場合、Entity間のカーディナリティに多対多が存在しても多対多の解消が必須というものではない。
データベースを構築する範囲を対象世界と呼び、対象世界に抽象化して概念データモデルを得る。概念データモデルは企業や組織が保持すべきデータを明確にしてモデル化したものなので特定のデータベースマネジメントシステムには依存しない。
部品と製品の関係について、1つの部品は複数の製品で利用され、1つの製品は複数の部品から構成される場合、部品と製品の関係は多対多となる。このように、部品と製品の間に複雑な関係が存在する場合であっても、適切に正規化を行えば論理データモデルに関係モデルを適用できる。
問7
データベースマネジメントシステムは企業や組織が保有するデータを一元管理するためのソフトウェアである。ユーザやアプリケーションプログラムが直接データの参照・」更新などを行うとデータの不整合が生じたり、適切な権限がない場合に参照を許したりして安全性や一貫性が損なわれる。データベースマネジメントシステムはデータ一元的に管理することによってデータを保護する。データベースマネジメントシステムには同時実効性制御機能、排他制御機構、トランザクション管理機能、障害復旧機能などがそなわっている。
同時実行制御機能によって競合する処理が同時に行われないように制御される。処理の順序によっては結果が異なってしまうことがない。
2人のユーザが同じデータにアクセスしているとき、一方が参照しているだけであっても、他方が更新するような場合は排他制御が行われる。
分割できないような複数の処理がある場合、一部の処理だけが実行されることのないようにトランザクション管理機能が有効に動作する。一部の処理が行われた後に障害が発生したような場合には、障害復旧機能によって実行すみの処理が取り消される。
データベースの維持管理には障害復旧機能がかかせない。障害復旧機能はデータベースマネジメントシステムが持っている機能であり、ハードウェア障害が生じた場合にもデータを復旧させるための障害復旧機能が有効に働く。
データベースマネジメントシステムには自動データ作成機能はない。しかし、データ登録時にはデータの形式のチェックやデータの相互関係のチェックなどを行い、整合性が維持されるような機能を持っている。
問9
正規化は、非正規形→第一正規形→第二正規形→第三正規形→ボイスコッド正規形というように進めていく。
第一正規形は繰り返し項目がなく、各属性の定義域が単純であるような関係。第二正規形は第一正規形であって部分関数従属がない関係、第三正規形は、第二正規形であって推移関数従属が関係。ボイスコッドの正規形は、全ての候補キーについて、候補キー以外の属性が候補キーに(完全)関数従属する関係。
繰り返し項目がなく、各属性の定義域が単純になっているような関係は第一正規形。
関係を構成する全属性によって主キーが構成されているような関係は非キー属性がなくボイスコッドの正規形となる。
部分関数従属と推移関数従属は異なるものであって、別々に排除しなければならない。
関数Rのすべての候補キーについて候補キー以外の属性(の組)が完全関数従属するということなので。。。
更新時以上を防止するためには冗長なデータを排除しなければならず、正規化が必要になる。関係Rが更新されていないのであれば正規化は必ずしも必要ではない。

問13
initdbコマンドによってデータベースクラスタを作成する。initdbを実行するとユーザデータベースの雛形となるテンプレートデータベース、デフォルトの接続先データベースとなるpostgresというデータベースが作成される。initdbのオプションとしてデータベースクラスタを作成する場所、デフォルトのデータベースのエンコーディング、デフォルトのデータベースのロケールなどを指定する。
initdbを実行するとき、データベースのデフォルトのロケール指定は省略しても構わない。日本語を使用する場合であってもロケールの指定は不要。
initdbを実行することによって2つのテンプレートデータベース(template0とtemplate1)が作成される。
データベースクラスタを作成する場所を省略した場合には環境変数PGDATAで指定される場所にデータベースクラスタが作成される。
initdbの対象となったディレクトリに、すでにデータベースクラスタが作成されている場合はinitdbは実行されない。
データベースを日本語で使用する場合にはエンコーディングとしてEUC_JP、UTF-8などを指定できる。
テンプレートデータベースはユーザが新しくデータベースを作成するときにテンプレートとして使用される。テンプレートに定義されている情報を全て引き継いでユーザのデータベースが作成されるため、書くデータベースに共通に必要となるテーブルを事前にテンプレートデータベースに登録しておくと新しく作成したデータベースには共通のテーブルが含まれていることになる。
テンプレートデータベースにはtemplate0とtemplate1があり、template1はカスタマイズしてユーザデータベースの雛形として使用される。template0のデータベースはテンプレートデータベースの初期状態を保持するデータベースであって更新できない。
データベースの作成時にはデフォルトでtemplate1データベースが参照される。
template1データベーすはデフォルトでユーザデータベース作成の雛形となり、共通のテーブルの登録などカスタイズして使用する。
テンプレートデータベースをカスタマイズする場合にはtemplate1データベースを使う。
2つのテンプレートデータベースの 初期状態は全ておなじ。
問15
データベースクラスタは、Postgresqlにおけるデータベースの格納領域。Postgresqlが管理するデータはすべてデータベースクラスタ内で管理される。データベースクラスタの実態はファイルシステム上のディレクトリとなっていて、」当該ディレクトリの配下には、Postgresqlが動作するために必要なデータが保存される。データベースクラスタのディレクトリの配置場所はファイルシステム上の任意の場所をしていできる。
1つのPostgresqlサーバは1つのデータベースクラスタのみ管理できる。
1つのデータベースクラスタは1つのPostgresqlサーバのみが利用できる
データベースクラスタの実体は、ファイルシステムにおける1つのディレクトリ。
データベースクラスタにはユーザ情報などのグローバルデータが含まれる。データベースの雛形であるテンプレートデータベースもデータベースクラスタに含まれる。
ソースコードからPostgresqlをインストールした場合はデータベースクラスタが存在しない。initdbコマンドによってデータベースクラスタを手作業で作成することになる。一部のLinuxディストリビューション向けインストールぱっけーじやWindowsのインストーラでは、インストールの過程でデータベースクラスタを作成する。
問16
データベースクラスタを作成のためのinitdbコマンドは、管理権限を持つユーザが実行する。データベースクラスタが作成されたディレクトリは、一般ユーザが容易にアクセスできないように適切なアクセス権を設定しなければならない。
データベースロケールとエンコーディングは利用者に大きな影響を与えるので、利用環境を踏まえて適切に設定する必要がある。
initdbコマンドは、データベースクラスタを作成するローカルのホストのみで実行できる。リモートのホストからは実行できない。
ロケールを明示的に指定しないとOSのロケールが使用される。
エンコーディングを明治的に指定しないとOSのロケールからエンコーディングが決定される。
データベースのエンコーディングとしてSJISを指定することはできない
サーバロケールはクライアントから独立しており、クライアントの環境には依存しない。
問17
psqlはPostgresqlに付属する対話型インターフェースを持つツール。Postgresqlとの接続、SQLコマンドの発行
SQLコマンドの結果表示などに使用する。psqlでは、事前にファイルに記述しておいた複数のSQLコマンドをバッチ的に実行することができる。
psqlの使用を終了し、データベースとの接続を切断するためには、\qコマンドもしくは\quitコマンドを使用する
psqlでは長いSQLコマンドの行を分けて入力できる。psqlは、SQLコマンドの終了をセミコロンで識別する。末尾にセミコロンのある行を入力すると、SQLコマンドが実行される
psqlのコマンドプロンプトから入力したメタコマンドは一部のメタコマンドを除き大半がPostgresqlによって実行される
標準のpsqlのコマンドプロンプトはスーパーユーザが接続データベース名=#、一般ユーザが接続データベース名=>である。
psqlのメタコマンドは1行で入力しなければならない。SQLコマンドのように行を分割して入力する方法はない。
問18
dropuserは指定した名前のデータベースユーザを削除する。dropdbは指定した名前のデータベースも削除する。droplangは指定した名前の手続き言語を削除する。どのコマンドもデータベースのオブジェクトを削除するため、適切な権限を持つユーザのみがコマンドを実行できる。Postgresqlは標準でPL/pgSQL、PL/Tcl、PL/Perl、PL/Pythonという4つの手続き言語を配布している。dropuserを実行できるのはスーパーユーザとCREATEROLE権限を持つユーザ。
dropdbを実行できるのはスーパユーザとデータベースの所有者のみ。
Postgresqlが標準で提供している手続き言語には`PL/pgSQL、PL/Tcl、PL/Perl、PL/Pythonがある。PL/pgSQLはデフォルトでデータベースに登録されているため、Postgresqlをインストールすれば使用できる。PL/pgSQL、PL/Tcl、PL/Perl、PL/Pythonはcreatelangによってデータベースに登録しておくことができる。
一旦登録した手続き言語はdroplangによってデータベースから削除することができる。
問19
createdbコマンドは指定した名前のデータベースを作成する。データベース名はデータベースクラスタ内で一意でなければならない。データベースを作成すると、接続に使ったデータベースユーザが作成したデータベースの所有者として設定される。
データベース名を指定しなかった場合、createdbを実行するOSのユーザ名と同じ名前のデータベースが作成される。
スーパユーザはCREATEDB権限とは関係なくデータベースを作成できる。
createdbにおいてテンプレートにtemplate0を指定した場合に限り、ロケールを指定できる。
createdbコマンドによってデータベースを作成する場合、-Oオプションによってデータベースの所有者を指定できる。
createdbにおいてテンプレートにtemplate0を指定した場合に限り、エンコーディングを指定できる。
問20
createuserコマンドによってデータベースユーザを作成する。ユーザを作成する際にはデータベース全体を管理できるSUPERUSER権限、データベースを作成できるCREATEDB権限、ユーザを作成できるCREATEROLE権限などを与えることが可能。
-Lオプションを指定すればユーザの作成時にログイン権限を与えないことが可能
createuserコマンドの入力時にオプション指定を省略した場合、必要なオプションについて対話的に処理が行われる
createuserコマンドによってコマンドで指定した名前のユーザがデータベースのオブジェクトとしてデータベースクラスタに作成される。
スーパーユーザを作成する場合、SUPERUSER権限が必要。CREATEDB権限ではスーパーユーザを作成できない。
データベースクラスタに作成される初期ユーザはSUPERUSER権限を持っている。

問21
PostgresqlにはOSと同様にユーザの概念がある。データベースに接続(ログイン)するには適切な権限を持つデータベースユーザアカウントが必要になる。PostgresqlのユーザはPostgresqlが管理しているユーザでOSのユーザとは別のユーザとなる。PostgresqlとOSでユーザ名を一致させる必要はないが管理を容易にするために同じユーザ名をつけることも可能。
ユーザはデータベースクラスタ内で一意にしなければならない。同じ名前のユーザを1つのデータベースクラスタ内で作成することはできない。ユーザを作成する権限とデータベースを作成する権限は別の権限。ユーザを作成する権限はCREATEROLE権限、データベースを作成する権限はCREATEDB権限という。
データベースユーザのパスワードとOSのパスワードは別のもの
データベースに接続できるユーザの権限はLOGIN権限。
SUPERUSER権限を持つユーザはデータベースに対するあらゆる操作を行える。
問22
Postgresqlのターミナル型フロントエンドpsqlを使用する場合、TCP/IPなどのプロトコルを用いて稼働中のPostgresqlに接続する。psqlはPostgresqlに接続できる任意のホストに置いて実行できる。Postgresqlに接続するための情報は-h、-p、-Uなどの接続オプションで指定できる。
Postgresqlに接続するデータベースユーザはログイン権限を持っている必要がある。
接続先ホストとしてIPアドレスもしくはホスト名を指定できる。
接続先ホストを省略した場合は環境変数PGHOSTが使用される。
接続先のポート番号を省略し環境変数PGHOSTも設定されていない場合はデフォルトのポート番号5432が使用される。
Postgresqlの管理ユーザ、一般のユーザ、共にpsqlを使用してデータベースへ接続できる。
問23
データベーすをシャットダウンするためには「pg_ctl stop」を使用する。「pg_ctl stop」のシャットダウンモードを指定することによってシャットダウンの仕方を制御できる。基本的にはデータベースを使用中のユーザの処理が終了するのを待ってデータベースを終了させる。しかし、緊急に停止させなければならない場合には、ユーザの処理状況に関係なく処理することもできる。緊急停止についても、シャットダウンモードで指定できる。
「pg_ctl stop -m smart」を実行すると直後からクライアントからの新しい接続は禁止される。
pg_ctl stop -m fast」を実行するとクライアントからの接続は強制的に切断される。実行中のトランザクションはロールバックされる。データベースの再起動時にはトランザクションの再実行が必要となる。
「pg_ctl stop -m immediate」を実行するとすべての処理が即時に終了させられる。適切な終了処理がされずPostgresqlがクラッシュした場合と同じ状況になるため、データベースの再起動時には復旧処理が必要となる。
「pg_ctl stop」に-Wオプションを指定するとPostgresqlが実際に停止していないにも関わらずpg_ctl の処理が終了する。
「pg_ctl stop」において停止するデータベースクラスタを指定しない環境変数PGDATAが使用される。
問24
pg_ctlはPostgresqlの起動、停止、状態確認などに使用する管理ツール。pg_ctlではどのような管理操作を行うのかについてサブコマンドを指定する。サブコマンドには initdb,start,stop,reloadなどがある。サブコマンドごとに設定できるオプションが異なる。「pg_ctl initdb」はデータベースクラスタを作成する。「pg_ctl start」は、Postgresqlをpバックグラウンドで起動する。「pg_ctl stop」はPostgresqlを停止する。「pg_ctl restart」はPostgresqlを再起動する。「pg_ctl reload」はPostgresqlに設定ファイルを再読み込みさせる。
pg_ctl はローカルホストからのみ実行できる。リモートにあるホストからは実行できない。
pg_ctl を実行するユーザはinitdbを実行してデータベースクラスタを作成したPostgresql管理ユーザに限定される。rootユーザはpg_ctlを実行できない。
「pg_ctl start」はバックグラウンドで実行されるため、デフォルトの環境では、Postgresqlが実際に起動するまで待たない。
「pg_ctl start」では起動するデータベースクラスタを指定しないと環境変数PGDATAが使用される。
「pg_ctl stop」においてスマートシャットダウンモードを指定すると接続が残っている間はデータベースがシャットダウンしない。全ての接続の終了後にデータベースがシャットダウンする。
問25
SET文はパラメータを設定変更するSQLコマンド。SET文によってPostgresqlの起動中に設定値を変更できる。SET文により変更された設定値は、SET文を実行したセッションのみ有効である。「pg_ctl reload」のようにPostgresql全体に設定値の変更が反映されることはない。設定値としてDEFAULTを指定することでデフォルト値に戻すことでデフォルト値に戻すことができる。スキーマをsearch_pathに登録するSETコマンドの書式は次のとおり。
SET seach_path TO スキーマ;
スキーマ名に空白文字を含む場合は「スキーマ名」をシングルクォートで囲む必要がある。スキーマ名をシングルクォートで囲まなくても問題はない。

問29
listen_addressesには、Postgresqlがクライアントからの接続を監視するIPアドレスもしくはホスト名を設定する。
設定する値はPostgresqlサーバのIPアドレスもしくはホスト名。listen_adressesを設定しないとクライアントからのTCP/IP接続を受け付けない。
クライアントが接続するPostgresqlサーバのIPアドレスもしくはホスト名を設定する。
設定値が空の場合、クライアントからのTCP/IP接続を受け付けないことを表している。
複数のIPアドレスを指定する場合は感まで区切って指定する
IPアドレス以外にホスト名を指定することもできる
変更内容はPostgresqlの再起動によって有効になる
postgresql.confの設定値は設定値によってPostgresqlに反映させるタイミングが異なる。具体的にはPostgresqlの起動時、pg_ctl reloadコマンドによる再読み込み時、SET文によるオンデマンドの反映時などがある。設定値によってはユーザが変更できないものもある。
データベースの起動時以外に有効になるタイミングがある。

postgresql.conf の設定値は設定値によってPostgresqlに反映させるタイミングが異なる。具体的にはPostgresqlの起動時、pg_ctl reloadコマンドによる再読み込み時、SET文によるオンデマンドの反映時などがある。設定値によってはユーザが変更できないものもある。
データベースの起動時以外に有効になるタイミングがある。
postgresql.conf の設定値を変更するだけでは設定値は有効にならない。設定値を有効にするにはPostgresqlを再起動するか、pg_ctl reloadコマンドでpostgresql.confをPostgresqlに再読み込みさせる必要がある。
「pg_ctl reload」コマンドによって設定ファイルを再読み込みした時に有効になる設定値がある。
スーパーユーザがSET文で有効にできる設定値がある。一般ユーザでもSET文で有効にできる設定値がある。
問31
PITR(Point In Time Recovery)は、データベース全体のバックアップに加えて、Postgresql運用中にアーカイブとして記録された更新データを基にデータベースをリカバリする機能。PITRでは、ある時点でデータベース全体のバックアップを取得する。これをベースバックアップという。Postgresqlにはデータベースに行われた全ての変更を記録する機構が備わっており、内容はログに出力される。このログのことを先行書き込みログあるいはWAL(Write Ahead Logging)と呼ぶ。WALは1つ16MBのWALファイルと呼ばれるファイルに記録して行く。データベースに変更があるたびにWALファイルにWALが書き込まれて行くことになるため、WALファイルの数が増加して行く。ハードディスクがオーバーフローしないよう、保持するWALファイルの数を決め、古いWALファイルから順に再利用されるようになっている。PITRではWALファイルが再利用される前にWALファイルを別な場所へ保存するようにしている。これをWALアーカイブという。
PostgresqlではWALを用いてデータベースのリカバリを行う。WALファイルの格納領域には限りがあるためWALファイルは古いものから順に再利用される。WALアーカイブ機能を用いることで再利用される前にWALファイルを別の場所に保存できる。
リカバリ時に必要な情報がWALに書き込まれるようにするためにはpostgresql.conf のwal_levelパラメータにarchive_commandにシェルコマンドを登録しておく。
PITRで使用するバックアップを取得する際に、Postgresqlを停止させておく必要はない。
PITRによってリカバリを行う場合Postgresqlを停止させる。障害が生じたデータベースクラスタは別ディレクトリに移して$PGDATAディレクトリがない状態からリカバリを開始する。
問32
Postgresqlではテーブルのデータをファイルへバックアップ、あるいはファイルのデータをテーブルへロードする方法としてSQLのCOPY文を使う方法とpsqlコマンドのメタコマンド\copyコマンドを使う方法が用意されている。SQLのCOPY文も\copyコマンドもどちらもテーブルとファイルの間でデータをやりとりする。COPY文はサーバ側で動作するのに対し\copyコマンドはクライアント側で動作する。COPY文を実行するとサーバ側のファイルにバックアップしたりサーバ側のファイルからロードしたりする。\copyコマンドはクライアント側のファイルにアクセスしてネットワークを介してデータベースのデータのやりとりをする。\copyコマンドを使用する場合はクライアントからpsqlコマンドでデータベースへ接続する。
COPY文によってサーバのテーブルのデータをサーバのファイルへ書き出すことができる。また、COPY文ではサーバのファイルを基にサーバのテーブルにデータをロードすることが可能。\copyコマンドではサーバ側のファイルからロードできない
COPY文によってサーバのテーブルのデータをサーバのファイルへ書き出す場合、スーパーユーザ権限が必要。
\copyコマンドではサーバ側のファイルにバックアップできない。
COPY文で取り扱うデータとしてCSV形式のデータがサポートされている。他にはバイナリ形式のデータも取り扱うことができる
\copyコマンドにおいてファイルに出力する場合、区切り文字としてカンマや「|」ストロークを使用できる

pg_restoreコマンドはpg_dumoコマンドによって平文形式以外の形式で取得されたバックアップをリストアするために使用される。pg_restoreコマンドの書式は
pg_restore 接続オプション -d データベース名 ファイル名
pg_dumpコマンドによるバックアップはPostgresqlの稼働中に取得でき、かつ一貫性のあるバックアップを取ることができる。pg_dumpコマンドの出力はデフォルトで平文形式となる。平文形式は汎用的な形式であるため異なるバージョンのpostgresqlにも利用できる。
pg_dumpコマンドのデフォルトの出力形式は平文形式。カスタム形式で出力する場合は-Fcオプション、tar形式で出力する場合は-Ftオプションを指定する。
平文形式で出力されたバックアップは汎用的な形式で出力されるため、異なるバージョンのPostgresqlでも利用できる。異なるOSで稼働するPostgresqlや、異なるアーキテクチャのデータベースインポートにも活用できる。
pg_dumpコマンドによって平文形式で出力されたバックアップはpsqlコマンドで回復する。平文形式以外の形式で取得されたバックアッをpg_restoreコマンドで回復する
平文形式で取得したバックアップはデータを回復するために必要となるSQLの羅列。SQLのデータベースに依存する部分について修正を加えれば他のデータベースにも使用できる
pg_dumpコマンドの出力中は他のユーザのデータベースアクセスを拒否しない。pg_dumpコマンドはオンラインバックアップが可能になっている。
pg_dumpコマンドでは-Fオプションをつけてバックアップの出力形式を指定することができる。バックアップ対象のデータベースを指定しないpg_dumpallコマンドは平文形式のみサポートしており-Fオプションは指定できない。バックアップの出力先を指定する場合は-fオプションを指定する。出力先を省略した場合は標準出力で出力される。
データベース名はシングルクォートで囲むことが可能。データベース名に空白が含まれる場合はシングルクォートで囲む必要がある。Postgresql稼働中のバックアップはpg_dumpコマンド、もしくはpg_dumpallコマンドによって取得できる。データベースを指定してバックアップする場合はpg_dumpコマンドを使う。
pg_dumpコマンドは-Fオプションをつけてバックアップの出力形式を指定することができる。全てのデータベースを対象とするpg_dumpallコマンドは複数のデータベースへ接続するため通常mスーパーユーザで実行する。
問37
情報スキーマはinformation_schemaという名前のスキーマ。情報スキーマにはデータベース内のテーブル、列、ユーザなど様々なオブジェクトについての定義情報が格納されている。システムカタログはデータベースマネジメントシステム固有の情報が保持されている。
情報スキーマには、Postgresql固有の情報は含まれていない。
データベースの情報が格納されているpg_databaseカタログは、データベースごとではなくデータベースクラスタに1つ存在する。
pg_languageカタログにはデータベースに登録されている手続き言語が格納されている
pg_tablesビューではデータベース内のテーブルに関する情報を参照できる。
pg_tablesビューからスキーマ名、テーブル名、テーブルの所有者などの情報が得られる。
情報スキーマのtablesビューではデータベースに登録されている全てのテーブルを調べることも可能。tablesビューではユーザが作成したテーブルも含まれる。

アクセス権限はSELECT、UPDATE、INSERTなどデータベースユーザがテーブルに対して行うことができる処理をGRANT文によって付与する。データベースユーザに付与されたテーブルのアクセス権限はREVOKE文によって取り消すことができる。Postgresqlでは必要なアクセス権限をデータベース管理者がデータベースユーザに与える。
テーブルのアクセス権はpsqlコマンドの\dpもしくは\zによって確認できる。
アクセス権限はGRANT文によってテーブル単位で付与する。付与されたアクセス権限はREVOKE文によってテーブル単位で取り消しできる。
特定の列についてもSELECT権限を付与できる
テーブルにUPDATEのアクセス権限が不要されている場合であっても当該テーブルをSELECTできない。SELECTすることが必要であれば明示的にSELECT権限を与える。
Postgresqlにおいて「禁止」を意味するアクセス権限はない。アクセス権限を取り消すことでアクセスできな行くなる。
問39
ANALYZEコマンドは対象テーブルの統計情報を最新の情報に更新する。VACUUM FULLコマンドは不要領域を削除してテーブルのサイズを小さくし、テーブルに作成されたインデックスを再作成する。VACUUM ANALYZEコマンドはVACUUMコマンドとANALYZEコマンドをまとめて実行するコマンド。データベースを適切に運用管理するためにはコマンドの役割とコマンドの動作環境、コマンド動作時の制約条件などを理解する必要がある。
ANALYZEコマンドはテーブルに実際どのようなデータが格納されているかという統計情報を更新するコマンド。SQLを効率よく実行できるように統計情報は必要に応じて更新する必要がある。
ANALYZEコマンドはテーブル名を指定してテーブル単位に実行できるようにテーブル単位に実行することができる。テーブル名を省略すると全てのテーブルがANALYZEコマンドの処理対象となる。
データベース中の全てのテーブルを対象にVACUUMコマンドを実行する場合は、テーブル名を省略してVACUUMコマンドを実行する。
VACUUM FULLコマンドの実行中は処理中のテーブルを排他ロックする。参照系であるSELECTについても平行動作させることはできない。VACUUM ANALYZEコマンドを実行するとVACUUMコマンドとANALYZEコマンドが同時に実行されるのではなくVACUUMコマンドの後にANALYZEコマンドが実行される
自動バキュームは書くテーブルが保持している稼働統計情報を定期的に参照し、不要領域が多くなったテーブルに対して自動的にVACUUMコマンドを実行する。VACUUMの処理以外に稼働統計情報を最新の情報に更新するANALYZEコマンドも合わせて実行する。自動バキュームの実行制御はpostgresql.confのパラメータで行うことができる。
自動バキュームによってVACUUMとANALYZEが自動的に実行される。ただし、常に実行されるものではない。
Postgresql9.0はデフォルトで自動バキュームが実行されるように設定されている。
自動バキュームは一律に実行されるのではなく、不要領域の割合が多くなったテーブルごとに実行される。postgresql.confに設定されたパラメータで実行タイミングが制御される。
自動バキュームが有効であってもVACUUMを手動で実行できる。
自動バキュームは、VACUUMとANALYZEを自動的に実行する。自動バキュームがVACUUM FULLを実行することはない。
VACUUMコマンドは更新や削除で不要になったテーブルやインデックスの領域を管理するコマンド。Postgresqlでは行を更新する場合に既存の行を不要領域とし新たな行をデータファイルに追加する。レコードを削除しても不要領域として残り実際には削除されない。この更新/削除方法は同時実行せいという観点に置いて優れている。しかし、データ領域の使用という観点では無駄が生じる。例えば全ての行を更新するUPDATE文を実行すると理論的にはデータファイルのサイズが2倍になる。VACUUMコマンドはこのような不要領域を再利用できるようにするコマンド。
VACUUM tblを実行すると削除や更新によって発生したtbl1の不要なデータ領域が回収される。回収された領域は再利用できる。Postgresqlでは同時実行性を高めるため行の更新/削除を行う場合に不要領域として対象業を無効にする。更新/削除を繰り返すと不要領域が増加していく。不要領域を回収するためにVACUUMコマンドを使用する。
VACUUM FULL tbl1を実行するとtbl1の内容が新しいファイルに書き換えられる。
VACUUMコマンドはテーブル名を指定してテーブル単位に実行することができる。テーブル名を省略すると全てのテーブルがVACUUMコマンドの処理対象となる。
VACUUMコマンドにはインデックス名を指定できない。VACUUMコマンドはテーブルの不要領域を回収するときにそのテーブルに関連するインデックスの不要領域も回収する。
問42
Postgresqlにはセッションに関する情報を返すシステム情報取得関数がある。セッションの情報としては現在接続しているデータベース、現在のスキーマ、現在のユーザ、現在稼働中のPostgresqlのバージョン、接続のアドレスなどがある。関数には()をつけて実行する。()が不要な関数もある。user関数とcurrent_user関数には()がつかない。
サーバで稼働しているPostgresqlのバージョンを確認する場合はSELECT version();を実行する。
現在のユーザを確認するためには「SELECT user;」
現在のユーザを確認するためには「SELECT current_user;」を実行
現在接続しているデータベースを確認するためには「SELECT current_database();」
version()関数によってサーバで稼働しているPostgresqlのバージョンを調査できる。クライアント(psql)のバージョンは、psqlコマンドを実行
問43
EXISTS、NOT EXISTSは「副問い合わせの結果、行が選択されたか」という条件によって行を選択するために使用する。
SELECT xxx FROM テーブル名 WHERE EXISTS 副問い合わせ;
EXISTS 、NOT EXISTS の動作
①副問い合わせの外側のテーブルから1行選択する
②選択した1行を用いて副問い合わせを実行する
3 EXISTSのとき副問い合わせの結果が1行以上返却されれば真、0行ならば偽となる。真の場合に
4 1に戻り次の行について2、3を繰り返す
5 1の全ての行について処理をすれば終了

問44 シーケンスは重複しない番号を自動で出力するオブジェクト。会員番号、伝票番号、などユニークなIDを必要とするデータにシーケンスで得られる番号を割り当てると便利。
 CREATE SEQUENCE シーケンス名;
シーケンスの開始値は「START n」、増分は「INCREMENT m」というオプションで指定できる。
シーケンスのオプション「CYCLE」を指定すると最大値の次のシーケンス値として初期値が得られる。「CYCLE」を指定しない場合、最大値を超えたシーケンスを得ようとすると、エラーになる。
setval関数でシーケンスを特定の値に設定できる。シーケンスを開始値と同じ値に設定することも可能
払い出したシーケンスを巻き戻すことはできない。setval()関数を用いて必要な値をセットする。
シーケンスの開始値、シーケンスの増加値ともマイナスの値を設定可能

問45
インデックスは辞書における索引のような役割を果たす。インデックスを使用すると、DBMSは検索対象のデータがディスクのどの場所にあるかを効率よく決定できる。インデックスが存在しないと基本的にテーブルの全件検索が必要になる。
小さなサイズのテーブルであれば問題ないが、大きなサイズのテーブルを全件検索すると非効率なので、適切なインデックスを作成することが必要。
インデックスを作成するときにインデックス種別を省略すると、インデックス種別にb-treeが用いられB-treeインデックスが作成される。
Gistインデックスは、主に空間情報の検索に用いられる。2次元以上の空間に対する一致や包括の検索に活用できる。
インデックスを使用した検索は「インデックスの検索→実データの検索」のように動作する。性別など、検索対象の列の値が少ない場合はインデックスを経由して検索するより直接実データにアクセスした方が効率が良いこともある。インデックスを付与すると必ず性能が工場するというものではない。
関数をインデックスに使用するためには関数の実行結果が静的でなければならない。実行するたびに実行結果が変化するnow() random()などは、関数インデックスとして使用できない。
インデックス種別にbtreeもしくはgistを指定するとマルチカラムインデックスが使用できる
問46
テーブルを更新(INSERT UPDATE DELETE)する場合は、テーブルが持っている制約に反しないようにしなければならない。主キー制約(ユニーク制約とNOT NULL制約)、ユニーク制約、NOT NULL制約、外部キー制約、チェック制約など多くの制約がある。
問47
OUTER JOINは、結合対象のテーブルから結合キーの条件に合致する行に加え、結合キーの条件に合致しなかったテーブルの行を出力する場合に使用する。FULL OUTER JOINはJOINの左側に位置するテーブルの結合キーに合致しなかった右側のテーブルの行をNULLとした左側のテーブルの行とJOINの右側に位置するテーブルの結合キーに合致
問48
列名、テーブル名を複数指定する場合は、カンマで区切る。列名に「*」を指定でテーブルの全ての列が対象になる。ORDER BYは検索結果をそーとする。DESCは昇順 ASCは降順。ORDER BY 2のように列数を指定することができる。OFFSET mはm件スキップする。
問49
timestampとnow()のように同じ機能を提供する関数がある。SELECT current_date;は現在の日付を表示する。SELECT current_time;は現在の時刻、SELECT now();は現在の日付と時刻を表示する
SELECT current_timestamp;
SELECT date ‘2017-07-31’ + interger ‘5’;は2017年7月31日から5日後の日付を表示する
問50
SIMILAR TOは、正規表現を用いてパターンマッチングする句。SIMILAR TOの基本的な書式は「文字列 SIMILAR TO ‘条件’」となる。「条件」には任意の文字列を表す「%」任意の1文字を表す「_」がる。直前の項目を0回以上繰り返しを「*」直前の項目を「+」指定した文字列いずれかを表す「[]」を使うことができる。
問51
randam()は、0.0以上、1.0未満の範囲の乱数値を表す関数。(randam()*-5.0) – 0.5は-5.5より大きく、0.5以下の乱数値となる。trunc()は引数の小数点以下を切り捨てて整数を返す関数。
問52
現在時刻はnow()関数で表せる。30分はinterval ’30 minites’で表すことができる。
問53
to_char()はtimestampのようなテータ型を文字列型に変換する。書式はto_char(データ、パターン)でデータに返還前のデータを指定する。パターンは返還後の文字列のフォーマットを指定する。現在時刻を変換するのでデータにはcurrent_timestampを指定している。パターンは12時間制で表示するにはHHもしくはHH12分を表すにはMI、秒はSSを指定する。
問54
「~」は文字列のパターンマチングを行う演算子。
文字列~ 正規表現 正規表現に一致する場合は真、大文字小文字は区別
文字列~* 正規表現 正規表現に一致する場合は真、大文字小文字は区別
文字列 !~ 正規表現 正規表現に一致しない場合は真、大文字小文字は区別
文字列 !~* 正規表現 正規表現に一致しない場合は真、大文字小文字は区別
問56
ロックは主に更新処理時の生合成を保持するために排他制御の一環として利用される。ロックには排他ロックと共有ロックがある。ある処理が行の排他ロックを獲得している場合別の処理はその行に対して排他ロックも共有ロックも獲得できない。ロックを必要とする別の処理は先にロックを獲得した処理がロックを解放するまで待たされる。
共有ロックと共有ロックの場合のみ同時にロックすることが許可される
行ロックとは行に対して取得されるロックをさす。行ロックはUPDATEやDELETEといった行の更新や削除処理に置いて取得される。テーブルロックは一時的に特定のテーブルへの検索や更新処理をしたいときなど、テーブル単位での排他/共有ロックを明示的に獲得する場合に使用する。
ロックには共有ロックと排他ロックがある。
ロックの競合の可能性が小さいためロックの粒度の小さい方が同時実行せいに優れている。ロックモードを省略して「LOCK テーブル名」とするとACCESSEXCLUSIVE MODEでロックされる。ACCESS EXCLUCIVE MODEの場合、ロック獲得者以外は当該テーブルに対してアクセスが許可されない。
デッドロックが派生した場合、Postgresqlではデッドロックを確定させたトランザクションがアボートされる。トランザクションのACID特性のうちの分離性は同時実行制などアプリケーションに大きない影響を与える。多くのデータベースマネジメントシステムでは分離性のレベルを設定できるようになっている。
Read uncommitted ダーティーリードが生じる
Read committed ファジーリードが生じる
Repeatable read ファントムリードが生じる
Serializable 別のトランザクションからは見えない
トランザクションの分離レベルはSET文やBEGINなどのトランザクション開始コマンドで指定できる。設定パラメータであるdefault_transaction_isolationで指定できる
分離性が弱いほど待たされる可能性が小さくなり性能上は有利になる。トランザクションは別のトランザクションの影響を受けやすくなり整合性を維持することが難しくなる
SAVEPOINTはトランザクションの中で部分的にロールバックを行いたい場合に使用する。任意の名称のSAVEPOINTをトランザクション中に作成し、必要に応じてSAVEPOINTまでロールバック出来る。1つのトランザクションに任意の数のSAVEPOINTを作成できる。設定したSAVEPOINTはRELEASE SAVEPOINT セーブポイント名で削除します。設定していないセーブポイント名指定してロールバックするとエラーになる
問59
BEGIN(START TRANSACTION) COMMIT ROLLBACK(ABORT)コマンドによって、トランザクションの処理開始と処理終了を制御する。トランザクションの開始時にはBEGIN(START TRANSACTION)を発行し、トランザクションの開始を宣言する。処理が正常に終了すればCOMMITを発行する。処理の途中でエラーや不都合が生じトランザクションを取り消したい場合にはROLLBACK(ABORT)を発行する。ROLLBACKを発行するとそれまでに実行されていた内容が全て取り消しされトランザクション開始前の状態に戻る。BEGINとCOMMITに挟まれた処理が1つのトランザクションである。
トランザクションの開始時にSTART TRANSACTIONを発行しトランザクションの終了時にCOMMITを発行する。
トランザクションの実行中にエラーや不都合が生じた場合にはABORT(ROLLBACK)を発行しトランザクションを取り消しする。
autocommitが有効であってもCOMMITを明治的に発行したい場合などBEGINを発行できる。
ROLLBACKを発行した場合、処理は取り消しされる。自動的に再実行されない、必要に応じて手作業で再実行することになる。
トランザクションの実行中にSQLがエラーとなった場合エラーとなった時点でトランザクションはABORTとなる。エラー以降のSQLの処理は本来であれば正しいSQL出会ってもABORTまたはROLLBACKが発行されるまで無視される。
トランザクションは業務上の処理単位。
原子性Atomicityトランザクションは実行が完了するかあるいは全く実行されないかのどちらか
整合性Consistencyトランザクションの実行結果に関係なくデータベースは整合性を保つ
分離性isolationここのトランザクションは独立しており別のトランザクションによる処理の影響を受けない
持続性Durability完了したトランザクションの実行結果は保持されデータベース障害などから守られる

問2-1
D,E
概念データモデルは特定のデータベースマネジメントに依存しない。論理データモデルはデータベースマネジメントが使用している論理構造に対応させるためデータベースマネジメントモデルに依存する
概念データモデル(多対多)→1対多→正規化→論理データモデル
第一正規形(主キーを決める)→第二正規形(非キー属性の関数従属に着目する)
関数従属は属性間の性質である属性Xの値を決定すれば別の属性Yの値が直ちに決まる場合は属性Xは属性Yに関数従属するという
主キーは候補キーのんかから選ぶ
問2-2
データベースの論理構造を表現するモデルとして論理モデル。論理モデルはネットワークモデル、階層モデル、関係モデルなどに分類される。関係モデルではデータを表形式で管理し、データは行(タプル)の集合として表される。関係データモデルの列は属性と呼ばれ属性の取り得る値の範囲をドメインという。かん系データモデルのテーブルの間に親子関係は存在しない

問2-3
❎B.C,D
データベースマネジメントシステムには同時実行制御昨日、排他制御、トランザクション、障害復旧が備わっている
問2-4
E
問2-6
✖️CD
BD

問7
D
問8
AD

問9
D

admax_area



関連記事

no image

WiMaxの速度について

ある程度、使って見ての印象は「とても遅い」。 動画も勿論のこと、若干重いWebページも開くのに時間

記事を読む

no image

baffalo LS-VL9D2にSSHで接続する

やり方は検索すると出てくるが すんなりいかなかったので書いておく。 目的 Windows7から

記事を読む

no image

Dockerfile

#利用するUbuntuのイメージ FROM ubuntu:16.04 # インストールpyt

記事を読む

no image

teraterm マクロ

yesnobox 'start ?' 'Logging' if result=0 then en

記事を読む

no image

てmp

mysql_fetch_assoc() は、 mysql_fetch_array() の 2 番

記事を読む

no image

vxlan

bridgeと物理IF設定 set interfaces ethernet eth2 addres

記事を読む

no image

光ネクストのv4アドレスについて

概要 フレッツ接続ツール利用しネクストのv4アドレスについて調べる。 環境とツール LAN

記事を読む

no image

vim

以下を参考に http://mknthk.hatenablog.com/entry/20130

記事を読む

admax_area



Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA


日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

admax_area



PAGE TOP ↑