Pythonスキルの習得

【Ubuntu対象】PostgreSQL コマンド まとめ(導入から解説)

【Ubuntu対象】PostgreSQL コマンド まとめ(導入から解説)

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

PostgreSQLの初期化

不要です。

デフォルトでは、「postgres」というユーザーアカウントが作製されます。

PostgreSQLの起動、停止処理

導入直後に、サービスは開始された状態になっています。

# 起動
sudo service postgresql start

# 停止
sudo service postgresql stop

# 状態確認
sudo service postgresql status

DBの操作方法(コマンド)

下記を実行する前に、デフォルトで作成されたユーザーアカウント「postgres」にユーザーを変更します。

sudo -i -u postgres

DB一覧 確認コマンド

作成済みの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…]をクリックします。

image-222

下記のダイアログが、出てきます。
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です(笑)