§2023-03-24, 試作機器： hc４Gnome.yushei.net, after makepkg -s
makepkg -i
makepkg -i postgresql-15.2-1-aarch64.pkg.tar.zst postgresql-docs-15.2-1-aarch64.pkg.tar.zst postgresql-libs-15.2-1-aarch64.pkg.tar.zst
==> WARNING: The package group has already been built, installing existing packages...
==> Installing postgresql package group with pacman -U...
[sudo] password for alexlai:
loading packages...
resolving dependencies...
looking for conflicting packages...
Packages (3) postgresql-15.2-1 postgresql-docs-15.2-1 postgresql-libs-15.2-1
Total Installed Size: 80.80 MiB
:: Proceed with installation? [Y/n] y
(3/3) checking keys in keyring [##################################################] 100%
(3/3) checking package integrity [##################################################] 100%
(3/3) loading package files [##################################################] 100%
(3/3) checking for file conflicts [##################################################] 100%
(3/3) checking available disk space [##################################################] 100%
:: Processing package changes...
(1/3) installing postgresql-libs [##################################################] 100%
(2/3) installing postgresql-docs [##################################################] 100%
(3/3) installing postgresql [##################################################] 100%
Optional dependencies for postgresql
python: for PL/Python 3 support [installed]
perl: for PL/Perl support [installed]
tcl: for PL/Tcl support [installed]
postgresql-old-upgrade: upgrade from previous major version using pg_upgrade
logrotate: rotates system logs automatically
:: Running post-transaction hooks...
(1/4) Creating system user accounts...
Creating group 'postgres' with GID 965.
Creating user 'postgres' (PostgreSQL user) with UID 965 and GID 965.
(2/4) Reloading system manager configuration...
(3/4) Creating temporary files...
Cannot set file attributes for '/var/log/journal', maybe due to incompatibility in specified attributes, previous=0x10001000, current=0x10001000, expected=0x10801000, ignoring.
Cannot set file attributes for '/var/log/journal/402afd25c85d4ce4af9c408beb67a5bc', maybe due to incompatibility in specified attributes, previous=0x10001000, current=0x10001000, expected=0x10801000, ignoring.
Cannot set file attributes for '/var/log/journal/remote', maybe due to incompatibility in specified attributes, previous=0x10001000, current=0x10001000, expected=0x10801000, ignoring.
Cannot set file attributes for '/var/lib/postgres/data', maybe due to incompatibility in specified attributes, previous=0x10001000, current=0x10001000, expected=0x10801000, ignoring.
(4/4) Arming ConditionNeedsUpdate...
$ systemctl status postgresql.service
○ postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; preset: disabled)
Active: inactive (dead)
- Locale-gen
$ sudo nano /etc/locale.gen
$ sudo locale-gen
$ sudo localectl set-locale LANG=en_US.UTF-8
- Setup , using
postgresuser account
sudo -iu postgres
[sudo] password for alexlai:
[postgres@hc4Gnome ~]$ whoami
postgres
[postgres@hc4Gnome ~]$ cd
[postgres@hc4Gnome ~]$ pwd
/var/lib/postgres
[postgres@hc4Gnome ~]$ ls -l
total 4
drwx------ 2 postgres postgres 3452 Mar 24 13:21 data
$ initdb --locale=en_US.UTF-8 --encoding=UTF8 -D /var/lib/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 /var/lib/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 /var/lib/postgres/data -l logfile start
$ exit <-- exit as user postgres
- start postgresql.service
$ whoami
alexlai
$ sudo systemctl start postgresql.service
$ 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-24 15:11:15 CST; 12s ago
Process: 213945 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGROOT}/data (code=exited, status=0/SUCCESS)
Main PID: 213947 (postgres)
Tasks: 6 (limit: 4077)
Memory: 16.9M
CPU: 302ms
CGroup: /system.slice/postgresql.service
├─213947 /usr/bin/postgres -D /var/lib/postgres/data
├─213948 "postgres: checkpointer "
├─213949 "postgres: background writer "
├─213951 "postgres: walwriter "
├─213952 "postgres: autovacuum launcher "
└─213953 "postgres: logical replication launcher "
Mar 24 15:11:15 hc4Gnome systemd[1]: Starting PostgreSQL database server...
Mar 24 15:11:15 hc4Gnome postgres[213947]: 2023-03-24 15:11:15.582 CST [213947] LOG: starting PostgreSQL 15.2 on aarch64-unknown-linux-gnu, c>
Mar 24 15:11:15 hc4Gnome postgres[213947]: 2023-03-24 15:11:15.585 CST [213947] LOG: listening on IPv4 address "127.0.0.1", port 5432
Mar 24 15:11:15 hc4Gnome postgres[213947]: 2023-03-24 15:11:15.586 CST [213947] LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
Mar 24 15:11:15 hc4Gnome postgres[213950]: 2023-03-24 15:11:15.593 CST [213950] LOG: database system was shut down at 2023-03-24 15:05:00 CST
Mar 24 15:11:15 hc4Gnome postgres[213947]: 2023-03-24 15:11:15.605 CST [213947] LO
- Create
alexlaias first database/user
$ sudo -iu postgres
[postgres@hc4Gnome ~]$ 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@hc4Gnome ~]$ 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)
postgres=# ALTER USER alexlai WITH PASSWORD 'alex1nnn';
ALTER ROLE
postgres=# CREATE DATABASE alexlai;
CREATE DATABASE
postgres=# ALTER DATABASE alexlai OWNER TO alexlai;
ALTER 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 very
$ psql -U alexlai -W -d alexlai
Password:
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
-
Restricts access rights to the database superuser by default
-
Require password for login
Edit /var/lib/postgres/data/pg_hba.conf and set the authentication method for each user (or "all" to affect all users) to scram-sha-256 (preferred), or md5 (less secure; should be avoided if possible):
[postgres@hc4Gnome data]$ pwd
/var/lib/postgres/data
$ cp -v pg_hba.conf pg_hba.conf.ori