[PostgreSQL]DBクラスタとDB、DBテーブルの違いをそれっぽく理解し、それっぽく操作してみる[03]
こんにちは。
しょうです。
現在、私はOSS-DB Silverの資格取得の為に勉強中。
若干体調を崩していますけど、ペースを落として人並み程度には
頑張っているのかなと思います。
今回はローカル環境でPostgreSQLのデータベースクラスタ、データベース、
データベーステーブルの違いや、それぞれの作成方法について調べ、
実行してみたので、こちらにアウトプットをしていきたいと思います。
以下過去の2記事になります。
もし興味があればどうぞ<m(__)m>
１．データベースクラスタ・データベース・データベーステーブルの違い
データベースクラスタとデータベース、データベーステーブルの違いは
図にすると以下のようになります。
1つのデータベースクラスタ内に、複数のデータベースがあり、
そのデータベースの中に複数のデータベーステーブルがある。
ということですね。
※正確にはここにスキーマと呼ばれるものがあり、データベースを横断する
データベーステーブルも存在する？ようなのですが、それは後日。
icon used from icon-icons.com
figure created by draw.io
図にもちらっと書いていますが、
psqlというコマンドをLinux上で打つと、LinuxからPostgreSQLの
データベースに移動して、そこでSQL文を打ってデータを
追加したり、取り出しているってことですね。
psqlのコマンドは以下のように使います。
オプションを指定したり、アクセスするデータベースにやユーザ名を
指定して使用します。
参考：https://www.postgresql.jp/document/pg920doc/html/app-psql.html
psql [option...] [dbname [username]]オプション以降を省略して以下の様に使用することも出来ます。
psqlこの時「どこのデータベースにアクセスしているのか？」
ということが疑問になってきますが、
psqlコマンドを実行したOSユーザ名と同じ名前のデータベースに
アクセスする様です。
以下のようなコマンドを実行してデータベースにアクセスする場合
[postgres@ssh_server ~]$ whoami
postgres
[postgres@ssh_server ~]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=#次の画像の様にpostgresデータベースにアクセスしている、
ということになりますね。
２．データベーステーブル一覧取得と作成
データベーステーブルの一覧を表示する為には、
psqlコマンドでデータベースにアクセスして「\dt」という
メタコマンドを実行することで確認することが出来るようです。
デフォルトだと何も存在していないようですね。
postgres=# \dt
リレーションが見つかりませんでした。次に作成するためのコマンドを実行してみます。
テーブルを作成するためにはSQL文で以下の様に入力します。
CREATE TABLE [nDBname] (DB内のデータについて);作成した後に再度メタコマンドを実行してみると
データベーステーブルが作成されていることが分かりますね。
※今回はpsqlコマンドでユーザとデータベースを指定して
アクセスする方法も確認する為に以下のようなコマンドを一番最初に
実行しています。
[postgres@ssh_server ~]$ psql -U postgres -d postgres
psql (13.2)
"help"でヘルプを表示します。
postgres=# CREATE TABLE weather (city varchar(80), temp int);
CREATE TABLE
postgres=# \dt
リレーション一覧
スキーマ | 名前 | タイプ | 所有者
----------+---------+----------+----------
public | weather | テーブル | postgres
(1 行)３．データベース一覧取得と作成
データベースクラスタ内にあるデータベース一覧を取得する為には
psqlで任意のデータベースにアクセスした後に「\l」というメタコマンドを
実行する必要がある。
デフォルトでは前の記事でも記載した通り、Template0、Template1、
initdbコマンドを実行したユーザ名のデータベースの3つが作成されている
事が確認出来ますね。
[postgres@ssh_server ~]$ psql -U postgres -d postgres
psql (13.2)
"help"でヘルプを表示します。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行)これを図にしてみると以下のような状態ということですね。
※テーブルの状態については気にしないでください。
データベースを作成する為には
①psqlを使わないで、createdbコマンドを実行する。
②psqlで適当なデータベースにアクセスした後、
CREATE DATABASE SQL文を実行
の2パターンがあるようです。
createdbコマンドとは、CREATE DATABASEのラッパの様ですね。
ラッパとは、個人的なイメージだと
コマンドの複雑さを別のコマンドを用意することで、複雑さを緩和する
ようなイメージがあります。
ではデータベースを実際に作成してみたいと思います。
①psqlを使わないで、createdbコマンドを実行する。
createdbコマンドはPostgreSQLにはアクセスしません。
今回は、testDBというデータベースを作成してみました。
作成後、psqlコマンドで適当なデータベースにアクセスして、
\lメタコマンドを実行してデータベース一覧を確認し見てると、
作成出来ているのが確認出来ます。
[postgres@ssh_server ~]$ createdb testDB
[postgres@ssh_server ~]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testDB | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
(4 行)②psqlで適当なデータベースにアクセスした後、
CREATE DATABASE SQL文を実行
こちらの場合はpsqlコマンドで適当なデータベースにアクセスして
CREATE DATABASE SQL文を打つことで作成することが出来ます。
今回はmyTestというデータベースを作成してみました。
作成後\lメタコマンドを実行することで作成されていることが
確認出来ますね。
[postgres@ssh_server ~]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# CREATE DATABASE myTest;
CREATE DATABASE
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+-------------+-------------------+-----------------------
mytest | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
postgres | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
template0 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testDB | postgres | UTF8 | ja_JP.UTF-8 | ja_JP.UTF-8 |
(5 行)どちらを使用していけば良いかは・・・
まだ学習途中なので分からないです。
ただ、SQL文の方が細かく設定出来るのかなと思うので、
ラッパに頼りすぎるのも良くないのかなと感じました。
上記の状態を図式化すると以下のようになると思います。
この時、OS側のディレクトリがどうなっているのか気になったので、
確認してみました。base/配下に各データベースが格納されているという
認識があったの、変化があるだろうと推測しました。
データベースクラスタを作成した後、初期状態のbase/は以下になります。
[postgres@ssh_server data]$ cd /home/postgres/data/base/
[postgres@ssh_server base]$ ls
1 14173 14174そしてmytest、testDBデータベースを作成した後に再度確認した結果が
以下になります。
てっきり5つになると思っていたのですが、違うようですね。。。
[postgres@ssh_server ~]$ cd /home/postgres/data/base/
[postgres@ssh_server base]$ ls
1 14173 14174４．データベースクラスタに新規ユーザ（≒ロール）を作成
PostgreSQLで操作を行うためには、データベースクラスタ内に
ユーザ（≒ロール）情報を登録する必要があるようです。
私がPostgreSQLをテストしている環境はCentOS7になるのですが、
OSユーザとPostgreSQLユーザは明確に異なり、OSユーザをそのまま
使用することが出来ないようです。
まだ、誰もPostgreSQLユーザを作成していない状態を確認してみます。
PostgreSQLユーザはデータベースクラスタ内のpg_userテーブルやpg_shadowテーブルで管理しているようです。
このテーブルをSQL文で確認すると良いようです。
※後々学んだのですが、メタコマンド\duでも確認出来るようです。
[postgres@ssh_server data]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
(1 行)
postgres=# select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+--------+----------+-----------
postgres | 10 | t | t | t | t | | |
(1 行)ユーザを作成すためには以下の2つの方法があるようです。
①についてはcreatedbと同様にラッパの様です。
①createuserコマンドを実行する
②CREATE ROLE SQLを実行する
それぞれ試してみます。
①createuserコマンドを実行する
testAをデータベースクラスタに作成してみる。
作成したユーザにパスワードを設定するために-Pオプションを付ける。
パスワード設定は対話的に決めることが出来ます。
ユーザを作成した後、pg_userテーブルを確認してみると
testAの行が追加されていることが分かりますね。
[postgres@ssh_server ~]$ createuser -P testA
新しいロールのためのパスワード:
もう一度入力してください：
[postgres@ssh_server ~]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
testA | 16391 | f | f | f | f | ******** | |
(2 行)pg_userの各列の意味については以下サイトを参考ください。
スーパユーザやデータベース作成権限を付与していないので、
それぞれの列に「f（≒false）」が入っていることが分かります。
createuserコマンドに-P以外のオプションを付けることで
スーパユーザとしてユーザを作成したり、データベース作成権限を
付与することも可能なようです。
-Pでパスワードを設定
-sでスーパユーザ権限付与
-dでDB作成権限付与
-rでユーザ作成権限付与
-lでこのユーザでのログイン可
なお、Pを除くすべてのオプションで大文字を指定することで明示的に
権利を剥奪することが出来ます。
オプションを試してみます。
testSuperというユーザにスーパユーザ権限、データベース作成権限、
ユーザ作成権限、ログイン可を付与して作成してみます。
作成した後pg_userテーブルを確認してみると、usecreatedbとusesuperの
欄が「t」になっていることが分かりますね。
[postgres@ssh_server ~]$ createuser -P -s -d -r -l testSuper
新しいロールのためのパスワード:
もう一度入力してください：
[postgres@ssh_server ~]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
-----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
testA | 16391 | f | f | f | f | ******** | |
testSuper | 16392 | t | t | f | f | ******** | |
(3 行)
postgres=#②CREATE ROLE SQLを実行する
こちらの場合はpsqlコマンドで適当なデータベースにアクセスして
CREATE ROLE SQL文を打つことで作成することが出来ます。
今回はexam01というユーザを作成してみました。
作成後pg_userテーブルを実行することで作成されていることが
確認出来ますね。
[postgres@ssh_server ~]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# CREATE ROLE exam01 LOGIN PASSWORD 'password';
CREATE ROLE
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
-----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
testA | 16391 | f | f | f | f | ******** | |
testSuper | 16392 | t | t | f | f | ******** | |
testb | 16393 | f | f | f | f | ******** | |
exam01 | 16394 | f | f | f | f | ******** | |
(5 行)このユーザにtestSuperユーザと同等の権限を与えてみます。
ALTER SQLで権利を付与したいユーザを指定、WITHの後に付与したい権利の
内容を指定することでいけるみたいです。
※pg_userのユーザ数が少なくなっているのは気にしないでください。
postgres=# ALTER ROLE exam01 WITH CREATEDB CREATEROLE SUPERUSER;
ALTER ROLE
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
exam01 | 16385 | t | t | f | f | ******** | |
(2 行)５．データベース・データベーステーブル・ユーザ（≒ロール）を削除
削除する為には
データベースはdropdbコマンド
ユーザはdropuserコマンド
テーブルはDROP TABLE SQL
を使うようです。
dropdbとdropuserコマンドはそれぞれcreatedbとcreateuserコマンドが
それぞれラッパしているのと同様にラッパコマンドだと思われます。
今回はそれぞれ削除のテストを行うために以下の環境を
準備しました。myTestデータベース、tableTestデータベーステーブル、
userTestユーザを削除していきたいと思います。
postgres=# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
myTest | postgres | UTF8 | C | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(4 行)
postgres=# \dt
リレーション一覧
スキーマ | 名前 | タイプ | 所有者
----------+-----------+----------+----------
public | tabletest | テーブル | postgres
(1 行)
^
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
exam01 | 16385 | t | t | f | f | ******** | |
userTest | 16387 | f | f | f | f | ******** | |それぞれ削除していきます。
[postgres@ssh_server log]$ psql
psql (13.2)
"help"でヘルプを表示します。
postgres=# DROP TABLE tableTest;
DROP TABLE
postgres=# \q
[postgres@ssh_server log]$ dropdb myTest
[postgres@ssh_server log]$ dropuser userTest削除されているか確認します。
postgres-# \l
データベース一覧
名前 | 所有者 | エンコーディング | 照合順序 | Ctype(変換演算子) | アクセス権限
-----------+----------+------------------+----------+-------------------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行)
postgres-# \dt
リレーションが見つかりませんでした。
postgres=# SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
postgres | 10 | t | t | t | t | ******** | |
exam01 | 16385 | t | t | f | f | ******** | |この記事が気に入ったらサポートをしてみませんか？