Zenn
🐷

【Ubuntu/Debian】PostgreSQL 14 導入

2022/12/13に公開
PostgreSQL
Ubuntu
Bash
shell
tech

データーベース

データーベースは有名なところで言えばmySQLや最近人気上昇中のMariaDB、Redisなどがあります。今回はPostgreSQLを導入していきます。ディストリビューションはUbuntu22.04LTSを引き続き使っていきます。

インストール

$ sudo apt-get update
$ sudo apt-get install postgresql-14
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl postgresql-client-14
  postgresql-client-common postgresql-common sysstat
Suggested packages:
  lm-sensors postgresql-doc-14 isag
The following NEW packages will be installed:
  libcommon-sense-perl libjson-perl libjson-xs-perl libllvm14 libpq5
  libsensors-config libsensors5 libtypes-serialiser-perl postgresql-14
  postgresql-client-14 postgresql-client-common postgresql-common sysstat
0 upgraded, 13 newly installed, 0 to remove and 48 not upgraded.
Need to get 42.4 MB of archives.
After this operation, 161 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

# ローカルアドレスをリッスン
$ sudo grep listen_addresses /etc/postgresql/14/main/postgresql.conf
#listen_addresses = 'localhost'         # what IP address(es) to listen on;

# 認証方式を確認
$ sudo grep -v -E "^#|^$" /etc/postgresql/14/main/pg_hba.conf
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

[peer] 認証の場合、任意の PostgreSQL ユーザーを追加して利用するには、同名の OS ユーザーも必要になります。

# OSユーザー追加
$ sudo adduser dbuser
Adding user `dbuser' ...
Adding new group `dbuser' (1001) ...
Adding new user `dbuser' (1001) with group `dbuser' ...
Creating home directory `/home/dbuser' ...
Copying files from `/etc/skel' ...
New password:
Retype new password:
passwd: password updated successfully
Changing the user information for dbuser
Enter the new value, or press ENTER for the default
        Full Name []:
        Room Number []:
        Work Phone []:
        Home Phone []:
        Other []:
Is the information correct? [Y/n] y

# PostgreSQL 管理ユーザーで PostgreSQL ユーザーとデータベース追加
$ sudo su - postgres
$ createuser dbuser
$ createdb testdb -O dbuser

# 追加を確認
$ psql -c "select usename from pg_user;"
 usename
----------
 postgres
 dbuser
(2 rows)

$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | dbuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

基本動作の確認

# テストユーザーへ切り替え
$ sudo su - dbuser

# テストDBに接続
$ psql testdb
psql (14.5 (Ubuntu 14.5-0ubuntu0.22.04.1))
Type "help" for help.

# ユーザーロール一覧を表示
testdb=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 dbuser    |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 
# データベース一覧を表示 
testdb=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 testdb    | dbuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)

# テーブルを作成、一覧表示
testdb=> create table test_table (no int, name text);
CREATE TABLE
testdb=> \dt
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+--------
 public | test_table | table | dbuser
(1 row)

# テーブルにデーターを挿入、表示
testdb=> insert into test_table (no,name) values (01,'Ubuntu');
INSERT 0 1
testdb=> select * from test_table;
 no |  name
----+--------
  1 | Ubuntu
(1 row)

# テーブルを削除、確認
testdb=> drop table test_table;
DROP TABLE
testdb=> \dt
Did not find any relations.

# exit
testdb=> \q

# テストDB削除、確認
$ dropdb testdb
$ psql -l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(3 rows)

以上、PostgreSQLの導入でした。
お疲れ様でした。

Discussion