🍊

Ubuntu20.04にPostgreSQLをインストールし、DBとテーブルを作成する方法

2023/05/25に公開

Linux

PostgreSQL

Ubuntu

tech

PostgreSQLをUbuntu 20.04にインストールするには以下の手順を実行します。以下の手順では、Pythonのpsycopg2モジュールもインストールします。これは、PythonからPostgreSQLを操作するためのモジュールです。

1. PostgreSQLとPythonのpsycopg2モジュールをインストールする

sudo apt update
sudo apt install postgresql postgresql-contrib python3-psycopg2

2. PostgreSQLサービスを起動

sudo systemctl start postgresql

また、システム起動時にPostgreSQLを自動的に起動するには以下のコマンドを実行します：

sudo systemctl enable postgresql

3. PostgreSQLのユーザーとデータベースを作成

ここではユーザー名を myuser、パスワードを mypassword、データベース名を mydatabase とします：

sudo -u postgres createuser --interactive

ちなみに、ユーザー名は手元の端末のユーザー名と同じである必要があります。今回はmyuserとします。
出力されるプロンプトに従ってユーザー名 (ここでは myuser) を入力します。スーパーユーザーにするか聞かれたら n を入力します。

次に、ユーザーのパスワードを設定します：

sudo -u postgres psql

これでPostgreSQLのコンソールに入ります。以下のコマンドでパスワードを設定します：

\password myuser

mypassword を入力して確認します。その後、\q でコンソールを終了します。

最後に、データベースを作成します：

sudo -u postgres createdb mydatabase

ユーザーのsuperuser権限を削除する方法

ユーザーを作成する時に間違えてsuperuser権限を与えてしまうこともあると思います。ユーザーのsuperuser権限を削除するには、以下の手順を実行します：

PostgreSQLのコンソールに入ります：
```
sudo -u postgres psql
```
以下のSQLコマンドを実行します：
```
ALTER USER myuser NOSUPERUSER;
```
ここで、myuser は権限を削除したいユーザー名に置き換えてください。
コマンドが成功したら、\q を入力してコンソールを終了します。

以上でスーパーユーザー権限の削除が完了します。

4. データベースを作成

ここではデータベース名を mydatabase としますが、好きな名前に変更できます。

PostgreSQLのコンソールに入ります：

sudo -u postgres psql

以下のSQLコマンドを実行します：

CREATE DATABASE mydatabase;

コマンドが成功したら、\q を入力してコンソールを終了します。

データベース作成後、FastAPIでのデータベース接続文字列を以下のように更新します。

DATABASE_URL = "postgresql://myuser:mypassword@localhost/mydatabase"  # 適宜修正してください

ここで、myuser, mypassword, mydatabase はそれぞれ自身の環境に合わせて置き換えてください。これでFastAPIからデータベースにアクセスできるようになります。

5. 作成したデータベース内にテーブルを作る

次にテーブルを作成します。以下では、idに数値型を設定する方法とuuid型を設定する方法の2パターンを書いています。

5.1 テーブルのidをint型の数値で作成する方法

mydatabaseというデータベースにmytableという名前のテーブルを作成するためには、まずPostgreSQLに接続します。

sudo -u postgres psql -d mydatabase

そして次に以下のSQLコマンドを実行します。これは簡単な例で、テーブルにはid, question, answerの3つの列があります。

CREATE TABLE mytable (
    id SERIAL PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL
);

このコマンドでは次のようなことを行っています：

CREATE TABLE：新しいテーブルを作成します。このコマンドの後にテーブル名（この場合はmytable）を指定します。
()内の部分：テーブルの列を定義します。各列はカンマで区切られ、列名、データ型、および任意の追加属性（この場合はNOT NULL、つまりこの列はNULLを許容しない）を指定します。
id SERIAL PRIMARY KEY：id列を定義します。SERIALは自動インクリメントの整数型で、PRIMARY KEYはこの列がテーブルの主キー（各行を一意に識別するキー）であることを示します。
question TEXT NOT NULLとanswer TEXT NOT NULL：questionとanswer列を定義します。これらの列はテキスト型で、NULLを許容しないことを指定しています。

このコマンドを実行した後、Ctrl + Dでpsqlを終了します。

そして再度PostgreSQLに接続して、テーブルが正しく作成されたことを確認します：

sudo -u postgres psql -d mydatabase -c "\dt"

このコマンドを実行すると、mydatabaseデータベース内のすべてのテーブルが表示され、新しく作成したmytableテーブルもその中にあるはずです。

5.2 テーブルのidを、uuidを使用する想定でテーブル作成する方法

UUIDを格納するカラムを作成する場合、PostgreSQLではUUIDデータ型を使用できます。UUIDを生成するためには、uuid-ossp拡張を使用します。uuid-ossp拡張モジュールは、一連のUUID（Universally Unique Identifier）生成関数を提供します。

以下のSQLコマンドは、idカラムをUUID型として定義し、デフォルト値として新しいUUIDを生成するようにmytableテーブルを作成します。

まず、uuid-ossp拡張を有効にします：

sudo -u postgres psql -d mydatabase

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

その後、テーブルを作成します：

CREATE TABLE mytable (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    question TEXT NOT NULL,
    answer TEXT NOT NULL
);

上記のコマンドは、新しいレコードが追加されるたびに、uuid_generate_v4()関数が呼び出され、新しいUUIDが生成されてidカラムのデフォルト値となります。

注意：uuid-ossp拡張は一度だけ有効化すればよく、そのデータベースで今後UUIDを使う場合は既に有効化されているので再度実行する必要はありません。

SQLAlchemyを使用する場合、テーブル定義は以下のようになります：

from sqlalchemy import Table, Column, String, MetaData
from sqlalchemy.dialects.postgresql import UUID
import uuid

metadata = MetaData()

mytable = Table(
    "mytable",
    metadata,
    Column("id", UUID(as_uuid=True), primary_key=True, default=uuid.uuid4),
    Column("question", String, nullable=False),
    Column("answer", String, nullable=False)
)

この場合、uuid.uuid4関数はPythonレベルでUUIDを生成しています。

5.3 作成テーブルへのアクセス権限を付与

テーブルに対するSELECT, INSERT, UPDATE, DELETEの権限を付与するには以下のようにします。

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE questions_answers TO myuser;

上記のコマンドは、myuserユーザーに対してquestions_answersテーブルへのSELECT, INSERT, UPDATE, DELETEの権限を付与します。

また、新しいテーブルが作成されたときに自動的にこれらの権限が付与されるようにするには、デフォルトの権限を設定します。

ALTER DEFAULT PRIVILEGES FOR USER myuser IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO myuser;

上記のコマンドは、myuserユーザーが今後publicスキーマで新しく作成するテーブルに対して自動的にSELECT, INSERT, UPDATE, DELETEの権限を付与します。

これらのコマンドを実行するには、それらの権限を付与できるユーザー（通常はデータベースのオーナー）としてログインする必要があります。

注意: テーブルの所有者や他のユーザーがテーブルに対する権限を適切に管理できるようにするため、これらの権限を適切に設定することが重要です。誤った設定はセキュリティの問題を引き起こす可能性があるため、どのユーザーにどの権限を付与するかは慎重に決定する必要があります。

6. データベースの中身を見る方法

psqlはPostgreSQLのインタラクティブ端末です。以下のコマンドを実行して、PostgreSQLに接続します。

psql -U myuser -d mydatabase

パスワードを求められるので、設定したパスワードを入力します。接続に成功すると、以下のようなプロンプトが表示されます。

mydatabase=>

テーブルの一覧を表示するには、以下のコマンドを実行します：

\d

特定のテーブルの内容を見るには、以下のコマンドを実行します（例えばテーブル名がmytableの場合）：

SELECT * FROM mytable;

終了するには、以下のコマンドを実行します：

\q

7. テーブルの中身を見る方法

新しく作成したmytableテーブルの中身を見るには、以下のSQLのSELECT文を使用します。まず、PostgreSQLに接続します：

sudo -u postgres psql -d dentalchat

次に以下のSQLコマンドを実行します：

SELECT * FROM mytable;

これはmytableテーブルのすべての行と列を選択するコマンドです。*は"全て"という意味で、すべての列を選択します。テーブルに行がない場合は何も表示されません。

このコマンドを実行した後は、Ctrl + Dを押してpsqlを終了します。

具体的なテーブルの列を選択したい場合は、*の代わりに列名をカンマで区切って指定します。例えば、questionとanswerだけを見たい場合は次のようにします：

SELECT question, answer FROM mytable;

これにより、mytableテーブルからquestionとanswer列だけが表示されます。

8. テーブルを削除する方法

テーブルを削除するためには、PostgreSQLのDROP TABLEコマンドを使用します。

以下の手順でmytableという名前のテーブルを削除します。

まず、以下のコマンドを使用してPostgreSQLにログインします：
```
sudo -u postgres psql -d mydatabase
```
ここで、postgresはPostgreSQLのスーパーユーザー名（Ubuntuユーザー名でも可）、mydatabaseはデータベース名です。
次に、以下のSQLコマンドを実行してテーブルを削除します：
```
DROP TABLE mytable;
```
ここで、mytableは削除したいテーブル名です。

このコマンドを実行すると、mytableという名前のテーブルがmydatabaseデータベースから削除されます。ただし、この操作は取り消せないので、実行前にテーブルの内容を適切にバックアップしておくことをお勧めします。