PostgreSQL - サービスデザイン実験室

１．PostgreSQLとは
PostgreSQLは、カリフォルニア大学バークレイ校のコンピュータサイエンス学科で開発されたPOSTGRES, Version 4.2をベースにしたオブジェクトリレーショナルデータベース管理システム（ORDBMS）。

２．PostgreSQLのインストールと基本

２．１　動作環境
　VirtualBox仮想マシン
　ホストOS　Windows10　
　ゲストOS　Ubuntu 16.04

２．２　PostgresSQLのインストール
https://www.postgresql.org/download/
（１）ファイルリポジトリ構成を作成
　$ sudo sh -c ‘echo “deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main” > /etc/apt/sources.list.d/pgdg.list’
（２）リポジトリ署名キーをインポート
　$ wget –quiet -O – https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add –
（３）パッケージリストを更新
　$ sudo apt-get update
（４）最新バージョンのPostgreSQLをインストール
　$ sudo apt-get -y install postgresql
（５）バージョン確認
　$ psql –version
　psql (PostgreSQL) 13.3 (Ubuntu 13.3-1.pgdg16.04+1)

２．３　PostgreSQLのロールとデータベースの使用
（１）postgresユーザにログインする
　※postgresインストール時にOSのユーザpostgresが自動的に作成される
　$ sudo -i -u postgres
　postgres@****-VirtualBox:~$

pqsl --help
psql は PostgreSQL の対話型ターミナルです。
使い方:
  psql [オプション]... [データベース名 [ユーザ名]]

一般的なオプション:
  -c, --command=コマンド   単一の(SQLまたは内部)コマンドを一つだけ実行して終了
  -d, --dbname=DB名        接続するデータベース名(デフォルト: "koba")
  -f, --file=FILENAME      ファイルからコマンドを読み込んで実行後、終了
  -l(エル), --list         使用可能なデータベース一覧を表示して終了
  -v, --set=, --variable=名前=値
                           psql 変数 '名前' に '値' をセット
                           (例: -v ON_ERROR_STOP=1)
  -V, --version            バージョン情報を表示して終了
  -X, --no-psqlrc          初期化ファイル (~/.psqlrc) を読み込まない
  -1 (数字の1), --single-transaction
                           (対話形式でない場合)単一のトランザクションとして実行
  -?, --help[=options]     このヘルプを表示して終了
      --help=commands      バックスラッシュコマンドの一覧を表示して終了
      --help=variables     特殊変数の一覧を表示して終了

入出力オプション:
  -a, --echo-all           スクリプトから読み込んだ入力をすべて表示
  -b, --echo-errors        失敗したコマンドを表示
  -e, --echo-queries       サーバへ送信したコマンドを表示
  -E, --echo-hidden        内部コマンドが生成した問い合わせを表示
  -L, --log-file=FILENAME  セッションログをファイルに書き込む
  -n, --no-readline        拡張コマンドライン編集機能(readline)を無効にする
  -o, --output=FILENAME    問い合わせの結果をファイル (または |パイプ)に送る
  -q, --quiet              静かに実行 (メッセージなしで、問い合わせの出力のみ)
  -s, --single-step        シングルステップモード (各問い合わせごとに確認)
  -S, --single-line        単一行モード (行末でSQLコマンドを終端)

出力フォーマットのオプション
  -A, --no-align           桁揃えなしのテーブル出力モード
      --csv                CSV(カンマ区切り)テーブル出力モード
  -F, --field-separator=文字列
                           桁揃えなし出力時のフィールド区切り文字
                           (デフォルト: "|")
  -H, --html               HTML テーブル出力モード
  -P, --pset=変数[=値]     表示オプション '変数' を '値' にセット
                           (\pset コマンドを参照)
  -R, --record-separator=文字列
                           桁揃えなし出力におけるレコード区切り文字
                           (デフォルト: 改行)
  -t, --tuples-only        行のみを表示
  -T, --table-attr=TEXT    HTMLテーブルのタグ属性をセット (width, border等)
  -x, --expanded           拡張テーブル出力に切り替える
  -z, --field-separator-zero
                           桁揃えなし出力のフィールド区切りをバイト値の0に設定
  -0, --record-separator-zero
                           桁揃えなし出力のレコード区切りをバイト値の0に設定

接続オプション:
  -h, --host=HOSTNAME      データベースサーバのホストまたはソケットの
                           ディレクトリ(デフォルト: "/var/run/postgresql")
  -p, --port=PORT          データベースサーバのポート番号(デフォルト： "5432")
  -U, --username=USERNAME  データベースのユーザ名 (デフォルト: "***")
  -w, --no-password        パスワード入力を要求しない
  -W, --password           パスワードプロンプトの強制表示(本来は自動的に表示)

詳細はpsqlの中で"\?"(内部コマンドの場合)または"\help"(SQLコマンドの場合)
をタイプするか、またはPostgreSQLドキュメント中のpsqlのセクションを参照のこと。

バグは<pgsql-bugs@lists.postgresql.org>に報告してください。
PostgreSQL ホームページ: <https://www.postgresql.org/>

pqsl --help

psql は PostgreSQL の対話型ターミナルです。

使い方:

psql [オプション]... [データベース名 [ユーザ名]]

一般的なオプション:

-c, --command=コマンド単一の(SQLまたは内部)コマンドを一つだけ実行して終了

-d, --dbname=DB名接続するデータベース名(デフォルト: "koba")

-f, --file=FILENAME ファイルからコマンドを読み込んで実行後、終了

-l(エル), --list 使用可能なデータベース一覧を表示して終了

-v, --set=, --variable=名前=値

psql 変数 '名前' に '値' をセット

(例: -v ON_ERROR_STOP=1)

-V, --version バージョン情報を表示して終了

-X, --no-psqlrc 初期化ファイル (~/.psqlrc) を読み込まない

-1 (数字の1), --single-transaction

(対話形式でない場合)単一のトランザクションとして実行

-?, --help[=options] このヘルプを表示して終了

--help=commands バックスラッシュコマンドの一覧を表示して終了

--help=variables 特殊変数の一覧を表示して終了

入出力オプション:

-a, --echo-all スクリプトから読み込んだ入力をすべて表示

-b, --echo-errors 失敗したコマンドを表示

-e, --echo-queries サーバへ送信したコマンドを表示

-E, --echo-hidden 内部コマンドが生成した問い合わせを表示

-L, --log-file=FILENAME セッションログをファイルに書き込む

-n, --no-readline 拡張コマンドライン編集機能(readline)を無効にする

-o, --output=FILENAME 問い合わせの結果をファイル (または |パイプ)に送る

-q, --quiet 静かに実行 (メッセージなしで、問い合わせの出力のみ)

-s, --single-step シングルステップモード (各問い合わせごとに確認)

-S, --single-line 単一行モード (行末でSQLコマンドを終端)

出力フォーマットのオプション

-A, --no-align 桁揃えなしのテーブル出力モード

--csv CSV(カンマ区切り)テーブル出力モード

-F, --field-separator=文字列

桁揃えなし出力時のフィールド区切り文字

(デフォルト: "|")

-H, --html HTML テーブル出力モード

-P, --pset=変数[=値] 表示オプション '変数' を '値' にセット

(\pset コマンドを参照)

-R, --record-separator=文字列

桁揃えなし出力におけるレコード区切り文字

(デフォルト: 改行)

-t, --tuples-only 行のみを表示

-T, --table-attr=TEXT HTMLテーブルのタグ属性をセット (width, border等)

-x, --expanded 拡張テーブル出力に切り替える

-z, --field-separator-zero

桁揃えなし出力のフィールド区切りをバイト値の0に設定

-0, --record-separator-zero

桁揃えなし出力のレコード区切りをバイト値の0に設定

接続オプション:

-h, --host=HOSTNAME データベースサーバのホストまたはソケットの

ディレクトリ(デフォルト: "/var/run/postgresql")

-p, --port=PORT データベースサーバのポート番号(デフォルト： "5432")

-U, --username=USERNAME データベースのユーザ名 (デフォルト: "***")

-w, --no-password パスワード入力を要求しない

-W, --password パスワードプロンプトの強制表示(本来は自動的に表示)

詳細はpsqlの中で"\?"(内部コマンドの場合)または"\help"(SQLコマンドの場合)

をタイプするか、またはPostgreSQLドキュメント中のpsqlのセクションを参照のこと。

バグは<pgsql-bugs@lists.postgresql.org>に報告してください。

PostgreSQL ホームページ: <https://www.postgresql.org/>

（２）データベース一覧の表示
　$ psql -l

（３）データベースの削除
　dropdb <データベース名>
（４）新しいデータベースを作成
　createdb <データベース名>
　$ createdb mydb

（５）データベースに接続
　psql -d <データベース名>
（例）
　$ psql -d mydb
　mydb=#
（６）テーブルの作成

mydb=# CREATE TABLE weather (
    city            varchar(80),

    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date
);

mydb=# CREATE TABLE weather (

city varchar(80),

temp_lo int, -- 最低気温

temp_hi int, -- 最高気温

prcp real, -- 降水量

date date

);

（７）テーブルリスト表示
　postgres=# SELECT * FROM weather;

（８）テーブル一覧の表示
　mydb=# \dt

（９）テーブルのスキーマの詳細表示
　mydb=# \d <テーブル名>

（１０）テーブルのレコード追加
　mydb=# INSERT INTO weather VALUES (‘japan tokyo’, 32, 38, 0.50, ‘1998-12-10’);

（１１）テーブルのレコード検索
　mydb=# SELECT * FROM weather;
　mydb=# SELECT * FROM weather WHERE city=’japan oosaka’;

（１２）テーブルのアクセス権限の表示
　mydb=# \z <テーブル名>

（１３）テーブルの削除
　mydb=# drop table <テーブル名>;

（１４）PostgreSQLプロンプトの終了
　mydb=# \q

３．PostgresSQLのロールについて
　ロールは操作権限などの役割を示すものでPostgresSQLではユーザ管理はロールで行う。OS上のユーザーとは別に扱われ、OS上のユーザーとロールとの対応は、「pg_hba.conf」ファイルで設定されている。

（１）スーパーユーザpostgresでpsqlを起動して、postgresのDBにログインする
　$ psql -U postgres -d postgres
（２）ロール（user01）を作成する
　postgres=# CREATE ROLE user01;
（３）ログイン権限を与える
　postgres=# ALTER ROLE user01 LOGIN;　
（４）パスワードを設定する
　postgres=# ALTER ROLE user01 PASSWORD ‘1212’;
（５）ロールuser01でデータベースにアクセスする
　postgres@*-VirtualBox:~$ psql -U user01 -d postgres
　psql: エラー: FATAL: ユーザ”user01″で対向(peer)認証に失敗しました
※peer認証とは
　PostgreSQL内のユーザーとUNIXユーザで、ユーザー名が一致していれば認証情報なしでログインできる仕組みで、psql側でユーザーを作るときには同名のUNIXユーザーも追加する必要がある。ここでは、テスト用として認証方法を変更して進める。
pg_hba.confファイル
PostgreSQLに接続するクライアントの認証に関する設定を記述するファイル
$ cd /etc/postgresql/13/main
$ ls -al

/etc/postgresql/13/main# vi pg_hba.conf

（変更前）
# Database administrative login by Unix domain socket
local   all             postgres                                peer
# "local" is for Unix domain socket connections only
local   all             all                                     peer

※認証方式
trust：任意のロール名でパスワードなしで接続可能
md5：パスワード認証
peer：Peer認証
peerをtrustに変更

（変更後）
# Database administrative login by Unix domain socket
local   all             postgres                                trust
# "local" is for Unix domain socket connections only
local   all             all                                     trust

/etc/postgresql/13/main# vi pg_hba.conf

（変更前）

# Database administrative login by Unix domain socket

local all postgres peer

# "local" is for Unix domain socket connections only

local all all peer

※認証方式

trust：任意のロール名でパスワードなしで接続可能

md5：パスワード認証

peer：Peer認証

peerをtrustに変更

（変更後）

# Database administrative login by Unix domain socket

local all postgres trust

# "local" is for Unix domain socket connections only

local all all trust

（６）user01でpostgresに接続する
　psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名
　$ psql -h localhost -p 5432 -U user01 -d postgres
　パスワードは前述で設定した’1212′

ホスト名：ローカルホストで動いている PostgreSQL に接続する場合は省略可
ポート番号：デフォルトの値(通常は 5432 )で別の番号を使用するとき以外は省略可
ロール名：省略した場合は OS のユーザー名が使用される

（７）ロール一覧
　=# \du

（８）コマンドプロンプト
　=#　スーパーユーザpostgresからデータベースに接続したとき
　=>　スーパーユーザ以外からデータベースに接続したとき

# psql -U user01 -d mydb
　psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1))
　"help"でヘルプを表示します。
　mydb=>

　# psql -U postgres -d mydb
　psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1))
　"help"でヘルプを表示します。
　mydb=#

# psql -U user01 -d mydb

　psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1))

　"help"でヘルプを表示します。

　mydb=>

　# psql -U postgres -d mydb

　psql (13.3 (Ubuntu 13.3-1.pgdg16.04+1))

　"help"でヘルプを表示します。

　mydb=#

（９）SQLコマンドの分割入力
　SQLコマンドを実行するときは最後にセミコロン”;”を入力してから、[Enter]キーを押します。セミコロンが入力される前に[Enter]キーを押した場合は入力が分割されたものとして扱われ、コマンドプロンプトが”=#”（”=>”）から”-#”（”->”）に変わります。

４．PostgresSQLのスキーマについて
　スキーマとは、1つのデータベースの中に複数設定することができる名前空間で自由に定義できる。
（１）スキーマ作成
mydb=# create schema <スキーマ名>
（２）スキーマの確認
　mydb=# select current_schema;
（３）スキーマの一覧
　mydb=# \dn

（４）スキーマの変更
　mydb=# set search_path to <schema_name>;

５．PostgreSQLアンインストール
（１）パッケージリストの確認
　postgresqlのパッケージリストを確認
　$ dpkg -l | grep postgres

（２）Postgresパッケージの削除
　$ sudo apt remove –purge postgresql
　$ sudo apt remove –purge postgresql-13
　$ sudo apt remove –purge postgresql-client-13
　$ sudo apt remove –purge postgresql-client-common
　$ sudo apt remove –purge apostgresql-common
　すべて、ｙ、または「はい」で続ける
（３）再起動
　$ shutdown -r now
（４）postgresユーザの削除
　$ sudo userdel postgres

　The end