Postgresql 環境構築

sudo apt update
sudo apt install postgresql postgresql-contrib

バージョン確認

psql --version

>>> psql (PostgreSQL) 14.8 (Ubuntu 14.8-0ubuntu0.22.04.1)

Postgresql使い方

起動

sudo service postgresql start

ステータス確認

sudo service postgresql status

>>> * Starting PostgreSQL 14 database server   [ OK ]

postgresでログイン

sudo -u postgres -i

postgres@DESKTOP-29D8DSQ:~$ psql
psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1))
Type "help" for help.

postgres=#

データベース一覧

postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres

Role一覧

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

SuperUserでRole作成

postgres=# CREATE ROLE <new_name> WITH SUPERUSER LOGIN PASSWORD '<pass>';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 new_name  | Superuser                                                  | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

新規RoleがOwnerのDataBase作成

postgres=# CREATE DATABASE <db_name> WITH OWNER <ubuntu_name>;
CREATE DATABASE
postgres-# \l
                                 List of databases
    Name    |     Owner     | Encoding | Collate |  Ctype  |   Access privileges
------------+---------------+----------+---------+---------+-----------------------
 <db_name>  | <ubuntu_name> | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres   | postgres      | UTF8     | C.UTF-8 | C.UTF-8 |
 template0  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
            |               |          |         |         | postgres=CTc/postgres
 template1  | postgres      | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
            |               |          |         |         | postgres=CTc/postgres

Roleを指定してDataBaseに接続

psql <ubuntu_name> -d <db_name>
psql (14.8 (Ubuntu 14.8-0ubuntu0.22.04.1))
Type "help" for help.

analysis=#

停止

sudo service postgresql stop

>>> * Stopping PostgreSQL 14 database server     [ OK ]

参考にしたサイト