安装PostgreSQL14到AlmaLinux8
环境
由于 PostgreSQL 14.0 在 2021/9/30 发布，因此我打算尝试安装它。
- PostgreSQL 14 Press Kit
环境如下所示。
-
- AlmaLinux 8(firewalldは無効化しています)
- Postgres 14
我以前一直在使用CentOS，但由于最新版本的CentOS Linux 8将于2021年底停止支持，所以我转而使用代替操作系统AlmaLinux。
在以下的 PostgreSQL 13 中，安装步骤有些许变化。
- CentOS 7.5にPostgreSQL13をインストールする
安装PostgreSQL的yum仓库。
我将检查PostgreSQL的软件包。
看起来AlmaLinux只提供了最高到13版本的PostgreSQL。
# dnf module list postgresql
determining the fastest mirror (10 hosts).. done. 100% [==================================================] 1.1 kB/s | 639 B 00:00 ETA
AlmaLinux 8 - BaseOS 3.1 MB/s | 8.6 MB 00:02
AlmaLinux 8 - AppStream 5.4 MB/s | 9.4 MB 00:01
AlmaLinux 8 - Extras 10 kB/s | 10 kB 00:01
AlmaLinux 8 - AppStream
Name Stream Profiles Summary
postgresql 9.6 client, server [d] PostgreSQL server and client module
postgresql 10 [d] client, server [d] PostgreSQL server and client module
postgresql 12 client, server [d] PostgreSQL server and client module
postgresql 13 client, server [d] PostgreSQL server and client module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
请安装PostgreSQL的存储库软件包。
请参考以下网站，获取各个操作系统的存储库软件包的URL列表。
# dnf -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
～省略～
Running transaction
Preparing : 1/1
Installing : pgdg-redhat-repo-42.0-20.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-20.noarch 1/1
Installed:
pgdg-redhat-repo-42.0-20.noarch
Complete!
安装PostgreSQL
安装PostgreSQL。
请注意，”postgresql14-devel”也一并安装，这是为了在编译扩展功能时使用，但不是必需的。
# dnf -qy module disable postgresql
# dnf -y install postgresql14 postgresql14-server postgresql14-contrib postgresql14-devel
～省略～
Installed:
annobin-9.50-1.el8.x86_64 clang-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64
clang-devel-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64 clang-libs-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64
clang-tools-extra-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64 cmake-filesystem-3.18.2-11.el8_4.x86_64
compiler-rt-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64 emacs-filesystem-1:26.1-5.el8.noarch
gcc-8.4.1-1.el8.alma.x86_64 gcc-c++-8.4.1-1.el8.alma.x86_64
glibc-devel-2.28-151.el8.x86_64 glibc-headers-2.28-151.el8.x86_64
kernel-headers-4.18.0-305.19.1.el8_4.x86_64 libicu-60.3-2.el8_1.x86_64
libicu-devel-60.3-2.el8_1.x86_64 libomp-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64
libomp-devel-11.0.0-1.module_el8.4.0+2107+39fed697.x86_64 libstdc++-devel-8.4.1-1.el8.alma.x86_64
libxcrypt-devel-4.1.1-4.el8.x86_64 libxslt-1.1.32-6.el8.x86_64
llvm-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64 llvm-devel-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64
llvm-libs-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64 llvm-static-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64
llvm-test-11.0.0-2.module_el8.4.0+2107+39fed697.x86_64 lz4-1.8.3-3.el8_4.x86_64
postgresql14-14.0-1PGDG.rhel8.x86_64 postgresql14-contrib-14.0-1PGDG.rhel8.x86_64
postgresql14-devel-14.0-1PGDG.rhel8.x86_64 postgresql14-libs-14.0-1PGDG.rhel8.x86_64
postgresql14-server-14.0-1PGDG.rhel8.x86_64 python3-lit-0.11.0-1.module_el8.4.0+2107+39fed697.noarch
python3-setuptools-39.2.0-6.el8.noarch
Complete!
PostgreSQL会被安装在「/usr/pgsql-14/」目录下。
# ls -l /usr/pgsql-14/
total 20
drwxr-xr-x 2 root root 4096 Oct 4 23:00 bin
drwxr-xr-x 3 root root 23 Oct 4 23:00 doc
drwxr-xr-x 6 root root 4096 Oct 4 23:00 include
drwxr-xr-x 5 root root 4096 Oct 4 23:00 lib
drwxr-xr-x 8 root root 4096 Oct 4 23:00 share
PostgreSQL的自动启动
为了自动启动PostgreSQL，请执行以下命令。
# systemctl enable postgresql-14.service
创建数据库集群
创建数据库集群。
正在使用root用户执行。
默认情况下，数据库文件将会被创建在”/var/lib/pgsql/14/data/”目录下，但在此次操作中，我们将其改为在”/data/”目录下。
可以通过postgresql-14.service文件中的”PGDATA”环境变量进行指定。
# vi /usr/lib/systemd/system/postgresql-14.service
変更前）
Environment=PGDATA=/var/lib/pgsql/14/data/
変更後）
Environment=PGDATA=/data/
# systemctl daemon-reload
下面执行以下命令来创建数据库集群。
# PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
确认在「/data/」下生成的内容。
# cat /data/PG_VERSION
14
启动PostgreSQL
在启动之前，将PostgreSQL的bin目录添加到路径中。
# su - postgres
/var/lib/pgsql/.pgsql_profileにパスを追加します。
# vi /var/lib/pgsql/.pgsql_profile
PATH=/usr/pgsql-14/bin:$PATH
export PATH
PGDATAを修正します。
# vi /var/lib/pgsql/.bash_profile
#PGDATA=/var/lib/pgsql/14/data
PGDATA=/data
# source ~/.bash_profile
使用”pg_ctl start”来启动PostgreSQL。
$ pg_ctl start
waiting for server to start....2021-10-04 23:02:57.238 UTC [8178] LOG: redirecting log output to logging collector process
2021-10-04 23:02:57.238 UTC [8178] HINT: Future log output will appear in directory "log".
done
server started
※ 只需要一个选项，在中文中进行重新表达:
※ 即使是 “systemctl start postgresql-14” 也没问题。
查询数据库
让我们来查看创建的数据库集群。
我们将显示版本和数据库列表。
$ psql -V
psql (PostgreSQL) 14.0
$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+---------+-------+-----------------------
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
创建用户和数据库
创建一个用户（”testuser”）和一个数据库（”testdb”）。
$ createuser --login --pwprompt testuser
Enter password for new role:
Enter it again:
$ createdb --owner=testuser testdb
允许来自外部的连接
PostgreSQL默认情况下无法进行远程连接，因此需要更改设置。
# vi /data/postgresql.conf
※デフォルトでは「/var/lib/pgsql/14/data/postgresql.conf」
変更前）
#listen_addresses = 'localhost'
変更後）
listen_addresses = '*'
接下来，我们将修改pg_hba.conf文件。
# vi /data/pg_hba.conf
※デフォルトでは「/var/lib/pgsql/14/data/pg_hba.conf」
# "local" is for Unix domain socket connections only
local testdb testuser md5
→ローカルからtestdbへtestuserでmd5接続できるように1行追加。
local all all peer
# IPv4 local connections:
host all all 192.168.10.0/24 md5
→192.168.10.0/24（今回のサーバの環境）から接続できるように1行追加。
host all all 127.0.0.1/32 ident
重新加载设置。
$ pg_ctl reload
※ もしくはsystemctl reload postgresql-14でもいいはず。
更改postgres用户的密码（不需要更改也可以）。
$ psql
alter role postgres with password 'postgres';
确认与远程连接的连接（A5：已进行了与SQL的连接确认，但省略了说明）。
请执行以下操作以确认本地连接。
$ psql testdb testuser
Password for user testuser:
psql (13.0)
Type "help" for help.
testdb=>
创建桌子
我将创建一个表，并尝试插入数据。
testdb=> create table test (id int, value text);
CREATE TABLE
testdb=> insert into test (id, value) values (1, 'test text');
INSERT 0 1
testdb=> select * from test;
id | value
----+-----------
1 | test text
(1 row)
使用「\d」命令来查看表的列表，使用「\du」命令来查看角色的列表。
testdb=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | testuser
(1 row)
testdb=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
testuser | | {}
其他的设置更改
从整体上来看，PostgreSQL似乎设置了较小的各种参数值。我试着将共享缓冲区从128MB增加到512MB（大约为总内存的20%~40%）。虽然还有很多其他应该改变的参数，包括共享缓冲区，但由于这些参数与系统有关，所以本次不进行更改。
以下是更改共享缓冲区的方法：
# vi /data/postgresql.conf
※デフォルトでは「/var/lib/pgsql/14/data/postgresql.conf」
変更前）
shared_buffers = 128MB
変更後）
shared_buffers = 512MB
接下来只需要重新启动PostgreSQL即可。
日志配置
我已经将日志的设置更改为以下内容。
$ vi /data/postgresql.conf
修改前如下。
log_filename = 'postgresql-%a.log'
log_rotation_size = 0
#log_min_duration_statement = -1
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_lock_waits = off
变更后如下所示。
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_size = 1GB
log_min_duration_statement = 500ms
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
在pgbench上执行基准测试。
在PostgreSQL中，标准配备了用于基准测试的工具pgbench。
首先，使用pgbench创建基准测试所需的表和数据。
$ pgbench -i -s 10 testdb
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data (client-side)...
1000000 of 1000000 tuples (100%) done (elapsed 0.95 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 1.68 s (drop tables 0.00 s, create tables 0.00 s, client-side generate 0.98 s, vacuum 0.22 s, primary keys 0.47 s).
当数据准备好后，接下来要进行基准测试。
$ pgbench -c 10 -j 10 -t 2000 -N testdb
pgbench (14.0)
starting vacuum...end.
transaction type: <builtin: simple update>
scaling factor: 10
query mode: simple
number of clients: 10
number of threads: 10
number of transactions per client: 2000
number of transactions actually processed: 20000/20000
latency average = 1.778 ms
initial connection time = 10.700 ms
tps = 5625.815743 (without initial connection time)
“-c 10” 表示客户端数量为 “10”，”-t 1000″ 表示每个客户端的交易数。
可以通过以下方式来查看基准测试后的缓存命中率。
select relname,
round(heap_blks_hit * 100 / (heap_blks_hit+heap_blks_read), 2)
as cache_hit_ratio from pg_statio_user_tables
where heap_blks_read > 0 order by cache_hit_ratio;
relname | cache_hit_ratio
------------------+-----------------
test | 50.00
pgbench_accounts | 66.00
pgbench_branches | 82.00
pgbench_tellers | 95.00
pgbench_history | 99.00
(5 rows)
索引缓存命中率如下。
select relname, indexrelname,
round(idx_blks_hit * 100 / (idx_blks_hit + idx_blks_read), 2)
as cache_hit_ratio from pg_statio_user_indexes
where idx_blks_read > 0 order by cache_hit_ratio;
relname | indexrelname | cache_hit_ratio
------------------+-----------------------+-----------------
pgbench_tellers | pgbench_tellers_pkey | 0.00
pgbench_branches | pgbench_branches_pkey | 50.00
pgbench_accounts | pgbench_accounts_pkey | 98.00
(3 rows)
请参考
-
- PostgreSQL 14 Press Kit
-
- PostgreSQL 14.0 Documentation
- pgbenchの使いこなし by Let’s Postgres
