§2023-03-31
試作機器： hc4MnGnome.yushei.net
- Current setup,
$ lsblk -f
NAME FSTYPE FSVER LABEL UUID FSAVAIL FSUSE% MOUNTPOINTS
mtdblock0
mmcblk0
├─mmcblk0p1 vfat FAT16 BOOT_MNJRO 4C5E-6080 396.7M 13% /boot
├─mmcblk0p2 ext4 1.0 ROOT_MNJRO 88e5ebe4-184e-4a7a-b2cf-5a6c271c05d0 52.5G 10% /
├─mmcblk0p3 swap 1 53ac343d-9c77-4f01-a827-a0e31cf8b25e [SWAP]
└─mmcblk0p4 xfs 2f8598bf-b478-440a-93f6-e7b2c0994adb 159.8G 1% /opt
$ grep postgres /etc/passwd
postgres:x:965:965:PostgreSQL user:/var/lib/postgres:/bin/bash
- modify /usr/lib/systemd/system/postgresql.service
- original /usr/lib/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=notify
TimeoutSec=120
User=postgres
Group=postgres
Environment=PGROOT=/var/lib/postgres
SyslogIdentifier=postgres
PIDFile=/var/lib/postgres/data/postmaster.pid
RuntimeDirectory=postgresql
RuntimeDirectoryMode=755
ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data
ExecStart=/usr/bin/postgres -D ${PGROOT}/data
ExecReload=/bin/kill -HUP ${MAINPID}
KillMode=mixed
KillSignal=SIGINT
# Due to PostgreSQL's use of shared memory, OOM killer is often overzealous in
# killing Postgres, so adjust it downward
OOMScoreAdjust=-200
# Additional security-related features
PrivateTmp=true
ProtectHome=true
ProtectSystem=full
NoNewPrivileges=true
ProtectControlGroups=true
ProtectKernelModules=true
ProtectKernelTunables=true
PrivateDevices=true
RestrictAddressFamilies=AF_UNIX AF_INET AF_INET6
RestrictNamespaces=true
RestrictRealtime=true
SystemCallArchitectures=native
[Install]
WantedBy=multi-user.target
- modify
$ sudo cp -v /usr/lib/systemd/system/postgresql.service /usr/lib/systemd/system/postgresql.service.ori
$ sudo diff /usr/lib/systemd/system/postgresql.service.ori /usr/lib/systemd/system/postgresql.service
11c11
< Environment=PGROOT=/var/lib/postgres
---
> Environment=PGROOT=/opt/postgres
14c14
< PIDFile=/var/lib/postgres/data/postmaster.pid
---
> PIDFile=/opt/postgres/data/postmaster.pid
- Chage postgres home directory to /opt/postgres
$ sudo mkdir /opt/postgres
$ sudo mkdir /opt/postgres/data
$ sudo chown -R postgres:postgres /opt/postgres/
$ ls -l /opt/postgres/
total 0
drwxr-xr-x 2 postgres postgres 6 Mar 31 09:47 data
$ ls -l /opt/
total 0
drwxr-xr-x 3 alexlai alexlai 19 Mar 30 14:42 alexlai
drwxr-xr-x 3 postgres postgres 18 Mar 31 09:47 postgres
$ sudo usermod -d /opt/postgres postgres
$ grep postgres /etc/passwd
postgres:x:965:965:PostgreSQL user:/opt/postgres:/bin/bash
- initdb
$ sudo -iu postgres
[postgres@hc4MnGnome ~]$ cd
[postgres@hc4MnGnome ~]$ pwd
/opt/postgres
[postgres@hc4MnGnome ~]$ ls -l
total 0
drwxr-xr-x 2 postgres postgres 6 Mar 31 09:47 data
[postgres@hc4MnGnome ~]$ initdb --locale=en_US.UTF-8 --encoding=UTF8 -D /opt/postgres/data --data-checksums
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /opt/postgres/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Taipei
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option -A, or --auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
pg_ctl -D /opt/postgres/data -l logfile start
[postgres@hc4MnGnome ~]$ exit
logout
[alexlai@hc4MnGnome ~]$ whoami
alexlai
- systemctl start postgresql
[alexlai@hc4MnGnome ~]$ sudo systemctl daemon-reload
[alexlai@hc4MnGnome ~]$ sudo systemctl start postgresql.service
[alexlai@hc4MnGnome ~]$ sudo systemctl status postgresql.service
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; preset: disabled)
Active: active (running) since Fri 2023-03-31 09:55:02 CST; 7s ago
Process: 8448 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
Main PID: 8450 (postgres)
Tasks: 6 (limit: 4077)
Memory: 17.0M
CPU: 292ms
CGroup: /system.slice/postgresql.service
├─8450 /usr/bin/postgres -D /opt/postgres/data
├─8451 "postgres: checkpointer "
├─8452 "postgres: background writer "
├─8454 "postgres: walwriter "
├─8455 "postgres: autovacuum launcher "
└─8456 "postgres: logical replication launcher "
Mar 31 09:55:01 hc4MnGnome systemd[1]: Starting PostgreSQL database server...
Mar 31 09:55:02 hc4MnGnome postgres[8450]: 2023-03-31 09:55:02.232 CST [8450] LOG: starting PostgreSQL 15.2 on aarch64-unknown-linux-gnu, compiled by gcc (GCC) 12.1.0, 64-bit
Mar 31 09:55:02 hc4MnGnome postgres[8450]: 2023-03-31 09:55:02.235 CST [8450] LOG: listening on IPv4 address "127.0.0.1", port 5432
Mar 31 09:55:02 hc4MnGnome postgres[8450]: 2023-03-31 09:55:02.237 CST [8450] LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
Mar 31 09:55:02 hc4MnGnome postgres[8453]: 2023-03-31 09:55:02.245 CST [8453] LOG: database system was shut down at 2023-03-31 09:52:53 CST
Mar 31 09:55:02 hc4MnGnome postgres[8450]: 2023-03-31 09:55:02.258 CST [8450] LOG: database system is ready to accept connections
Mar 31 09:55:02 hc4MnGnome systemd[1]: Started PostgreSQL database server.
- Create
alexlaias first database/user
[alexlai@hc4MnGnome ~]$ sudo -iu postgres
[postgres@hc4MnGnome ~]$ cd
[postgres@hc4MnGnome ~]$ pwd
/opt/postgres
[postgres@hc4MnGnome ~]$ createuser --interactive
Enter name of role to add: alexlai
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y
[postgres@hc4MnGnome ~]$ psql
psql (15.2)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
alexlai | Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(3 rows)
o
postgres=# ALTER USER alexlai WITH PASSWORD 'alex1nnn';
ALTER ROLE
postgres=# SET ROLE alexlai;
SET
postgres=> CREATE DATABASE alexlai;
CREATE DATABASE
postgres=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
alexlai | alexlai | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
postgres=# \q
- Try to use
alexlai
postgres=# \q
[postgres@hc4MnGnome ~]$ exit
logout
[alexlai@hc4MnGnome ~]$ psql -U alexlai
psql (15.2)
Type "help" for help.
alexlai=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
alexlai | alexlai | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
(4 rows)
alexlai=> \q