Ubuntuを使用してソフトウェア開発している方で、
- 急にPostgreSQLを使う事になってしまった。
- 今までMySQLを使っていたけど、乗り換えたい。
- 初めてのDBで、何から学習すればいいか分からない。
と思っている方々を対象にした記事です。
以前、Mac版の記事を書きましたが、Ubuntu版(20.04を対象)に書き換えたものになります。
本記事により、PostgreSQLの導入から視覚的に操作するためのツール「pgAdmin」の使い方まで、一通りの基本知識が習得できます。
導入方法
PostgreSQLのインストール方法
aptを使用するのが、最も簡単です。
端末（ターミナル）を開いて、下記コマンドを入力します。
sudo apt update
sudo apt install postgresql postgresql-contrib上記コードの「postgresql-contrib」は拡張機能が入ったパッケージです。非常に便利なので、併せて導入をお勧めします。
pgAdminのインストール方法
次に、視覚的に操作できる「pgAdmin」をインストールします。（任意）
使用方法はのちほど、詳細を記します。
ここでもaptを使用します。
まずはリポジトリのキー取得と、構成ファイルを作成します。
# リポジトリのキー取得
sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
# 構成ファイルの作成
sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list'
# apt update
apt update次に下記コマンドで導入します。(デスクトップ版、Web版両方が導入されます。）
sudo apt install pgadmin4ソフトウェア一覧の中に、下記アイコンの「pgAdmin4」が出現します。
PostgreSQLの初期化
不要です。
デフォルトでは、「postgres」というユーザーアカウントが作製されます。
PostgreSQLの起動、停止処理
導入直後に、サービスは開始された状態になっています。
# 起動
sudo service postgresql start
# 停止
sudo service postgresql stop
# 状態確認
sudo service postgresql status
DBの操作方法（コマンド）
下記を実行する前に、デフォルトで作成されたユーザーアカウント「postgres」にユーザーを変更します。
sudo -i -u postgresDB一覧 確認コマンド
作成済みのDBを確認するコマンドです。
「PostgreSQL」の初期状態では、以下の表示になります。
psql -l
# 以下は実行結果(例)
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
------------+-----------+----------+---------+-------+-------------------------
example-db | *** | UTF8 | C | C |
postgres | *** | UTF8 | C | C |
template0 | *** | UTF8 | C | C | =c/*** +
| | | | | ***=CTc/***
template1 | *** | UTF8 | C | C | ***=CTc/***+
| | | | | =c/***
test | *** | UTF8 | C | C |
DBの作成、削除コマンド
# データベース新規作成
createdb データベース名
# データベース削除
dropdb データベース名デーブル関係コマンド
SQLの入力受付け状態に入るコマンド
psql -h ホスト名 -p ポート番号 -U ロール名 -d データベース名
# 下記条件の場合、データベースのみの入力でOK
（現在のユーザー名で、ホスト:localhost
ポート：postgreSQLのデフォルトポートの場合）
psql データベース名
# ⬆実行結果 (データベース名に「test」を指定した場合)
psql (13.1)
Type "help" for help.
test=#
# ⬆ SQLが入力できる状態
SQL入力受付け状態から出るコマンド
\q # 文末にセミコロンはつけない
# ショートカット ctrl−d でもOK
テーブル一覧の確認、テーブルのカラム情報を見るコマンド
# テーブル一覧を確認
\dt; # 文末にセミコロン無しでもOK
# ⬆実行結果 （例）
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-----------
public | mytable | table | ********
# テーブルのカラム情報を確認
\d (テーブル名); # 例 \d mytable
# ⬆実行結果 （例）
Table "public.mytable"
Column | Type | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
id | integer | | not null |
name | character varying(10) | | |
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id)
テーブルのCRUD（Create, Read, Update, Delete）
普通のSQL文です。参考までに記します。
# テーブル作成 （テーブル名、カラム名は、一例です。）
CREATE TABLE mytable (
id integer PRIMARY KEY,
name varchar(10)
);
# テーブル削除
DROP table mytable;
# 全データの読込み
SELECT * FROM mytable;
# インサート
INSERT INTO mytable VALUES (2, 'Nakagawa');
# 更新
UPDATE mytable SET name = 'Asai' where id = 2;
# 削除
DELETE FROM mytable; # ←の場合、全データが削除されますロール（誰が何の権限でどのテーブルにアクセスできるか）関係の操作
以下のコマンドは、SQLの入力受付け状態に入ってから打ち込みます。
ロール一覧 確認コマンド
\du;
# ⬆ 実行結果
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
taro | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
jiro | | {}
saburo | Superuser | {}各ロール 権限確認コマンド
下記の通り、各テーブル毎に権限は付与できます。
\dp;
# ⬆実行結果
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------+-------+-----------------------------+-------------------+----------
public | mytable | table | taro=arwdDxt/taro+ | |
| | | jiro=arwdDxt/taro | |
Access privileges の記号
a: INSERT(add)
r: SELECT(read)
w: UPDATE(write)
d: DELETE
D: TRUNCATE
x: REFERENCESロールの新規作成コマンド
# 一般ユーザーの場合
CREATE ROLE username LOGIN PASSWORD 'password';
# ⬆ username:ユーザー名、password:パスワード を入れて下さい。
# ⬆ LOGINを入れないと、ログインできなくなります。
# ログイン権限 無しでもOK（後から設定も可能）
CREATE ROLE testuser PASSWORD 'password';
# パスワード設定 無しでもOK（後から設定も可能）
CREATE ROLE testuser;
# スーパーユーザーの場合
CREATE ROLE username superuser;
# ⬆LOGINを付けていないので、superuserなのに、ログインできません・・・作成したロールに、パスワード、LOGIN権限を付与するコマンド
# パスワードの設定
ALTER ROLE username PASSWORD 'password';
# ログイン権限を付与
ALTER ROLE username LOGIN;ロールにテーブル操作権限を付与するコマンド
# 個別権限を付与する場合
GRANT 権限 ON tablename TO username;
# ⬆ tablename:テーブル名、 username:ユーザー名 を入力
# ⬆ 権限は
SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、
CREATE、CONNECT、TEMPORARY、EXECUTE、USAGE から選択
# 全権限を付与する場合
GRANT all ON tablename TO username;各権限の詳細情報はこちら
ロールからテーブル操作権限を剥奪するコマンド
REVOKE 権限 ON tablename FROM username;ロールを削除するコマンド
DROP ROLE username;視覚的に操作できる「pgAdmin」の操作方法
DB接続設定
起動すると下記画面が出てきます。
[Servers]を右クリックし、[Create]→[Server…]をクリックします。
下記のダイアログが、出てきます。
Nameは、任意です。
次にConnectionのタブを開きます。
- Hostname/address: 127.0.0.1（localの場合）
- Port: 5432 （デフォルト）
- Maintenace database: postgres
- Usernmae: ロール名
- Password: そのロール名のパスワード
これで繫げると、下記のようなツリーになります。(DB一覧が表示されます。)
テーブルデータの閲覧方法、変更方法
DB→Schemas→public→Tablesの直下にテーブル一覧があります。
（下図は一例です。）
テーブルデータを見たい場合、テーブルを右クリック→veiw/Edit Data→All Rowsを選択します。
下記画面が出てきます。
ここから、値を変更したり、挿入したりできます。
最後に
後はきっと、ORM様があればOKです（笑）
