ubuntu 24.04 LTSで自宅サーバー構築（PostgreSQL 16の設定）

<a href="https://px.a8.net/svt/ejp?a8mat=3ZDGSF+2SMFEY+CO4+60OXE" rel="nofollow">初期費用無料、月額990円から、高速・多機能・高安定レンタルサーバー『エックスサーバー』</a>
<amp-pixel src="https://www19.a8.net/0.gif?a8mat=3ZDGSF+2SMFEY+CO4+60OXE" layout="nodisplay"></amp-pixel>
taisuke@ubuntuserver:~$ sudo apt -y install postgresql-16
#ローカルホストをリスン
taisuke@ubuntuserver:~$ sudo grep listen_addresses /etc/postgresql/16/main/postgresql.conf
#listen_addresses = 'localhost' # what IP address(es) to listen on;
#認証方式
taisuke@ubuntuserver:~$ sudo grep -v -E "^#|^$" /etc/postgresql/16/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
#OS ユーザー追加
taisuke@ubuntuserver:~$ sudo adduser ubuntu
#PostgreSQL 管理ユーザーで PostgreSQL ユーザーとデータベース追加
taisuke@ubuntuserver:~$ sudo su - postgres
postgres@www:~$ createuser ubuntu
postgres@www:~$ createdb testdb -O ubuntu
#確認
postgres@www:~$ psql -c "select usename from pg_user;"
 usename
----------
 postgres
 ubuntu
(2 rows)
postgres@www:~$ psql -l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 testdb    | ubuntu   | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
(4 rows)
#テスト DB に接続
ubuntu@www:~$ psql testdb
psql (16.2 (Ubuntu 16.2-1ubuntu4))
Type "help" for help.

#ユーザーロール一覧を表示
testdb=> \du
                             List of roles
 Role name |                         Attributes
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 ubuntu    |

#データベース一覧を表示
testdb=> \l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 testdb    | ubuntu   | UTF8     | libc            | C.UTF-8 | C.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 | ubuntu
(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 
#テストデータベースを削除
ubuntu@www:~$ dropdb testdb
ubuntu@www:~$ psql -l
                                                   List of databases
   Name    |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 postgres  | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |         |         |            |           | postgres=
<a href="https://px.a8.net/svt/ejp?a8mat=3ZDGSF+2SMFEY+CO4+60OXE" rel="nofollow">初期費用無料、月額990円から、高速・多機能・高安定レンタルサーバー『エックスサーバー』</a>
<amp-pixel src="https://www19.a8.net/0.gif?a8mat=3ZDGSF+2SMFEY+CO4+60OXE" layout="nodisplay"></amp-pixel>