在CentOS 7.5上安装PostgreSQL 13
环境
由于 PostgreSQL 13.0 在 2020 年 9 月 24 日发布，所以我将尝试安装。
- PostgreSQL 13 Press Kit
环境如下所示。
-
- CentOS 7.5(firewalldは無効化しています)
- Postgres 13
在以下的PostgreSQL12情况下，安装步骤基本相同。
- CentOS 7.5にPostgreSQL12をインストールする
安装与LLVM相关的库。
从12.3开始安装postgresXX-devel需要LLVM相关库。postgresXX-devel用于编译扩展功能。只要运行PostgreSQL，就不一定需要安装它。
# yum -y install epel-release centos-release-scl
安装PostgreSQL的yum仓库。
将CentOS的PostgreSQL存储库软件包安装起来。
各个操作系统的存储库软件包URL列表可在以下网站上找到。
# yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
～省略～
Running transaction
Installing : pgdg-redhat-repo-42.0-13.noarch 1/1
Verifying : pgdg-redhat-repo-42.0-13.noarch 1/1
Installed:
pgdg-redhat-repo.noarch 0:42.0-13
Complete!
安装PostgreSQL
安装PostgreSQL。
※一并安装”postgresql13-devel”是为了在编译扩展功能时使用，但不是必需的。
# yum -y install postgresql13-contrib postgresql13-devel
～省略～
Installed:
postgresql13-contrib.x86_64 0:13.0-1PGDG.rhel7 postgresql13-devel.x86_64 0:13.0-1PGDG.rhel7
Dependency Installed:
audit-libs-python.x86_64 0:2.8.5-4.el7 checkpolicy.x86_64 0:2.5-8.el7 devtoolset-7-binutils.x86_64 0:2.28-11.el7
devtoolset-7-gcc.x86_64 0:7.3.1-5.16.el7 devtoolset-7-gcc-c++.x86_64 0:7.3.1-5.16.el7 devtoolset-7-libstdc++-devel.x86_64 0:7.3.1-5.16.el7
devtoolset-7-runtime.x86_64 0:7.1-4.el7 libcgroup.x86_64 0:0.41-21.el7 libedit-devel.x86_64 0:3.0-12.20121213cvs.el7
libicu.x86_64 0:50.2-4.el7_7 libicu-devel.x86_64 0:50.2-4.el7_7 libsemanage-python.x86_64 0:2.5-14.el7
libxslt.x86_64 0:1.1.28-5.el7 llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7 llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7
llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7 llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7 llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7
llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7 llvm5.0.x86_64 0:5.0.1-7.el7 llvm5.0-devel.x86_64 0:5.0.1-7.el7
llvm5.0-libs.x86_64 0:5.0.1-7.el7 ncurses-devel.x86_64 0:5.9-14.20130511.el7_4 policycoreutils-python.x86_64 0:2.5-34.el7
postgresql13.x86_64 0:13.0-1PGDG.rhel7 postgresql13-libs.x86_64 0:13.0-1PGDG.rhel7 postgresql13-server.x86_64 0:13.0-1PGDG.rhel7
python-IPy.noarch 0:0.75-6.el7 scl-utils.x86_64 0:20130529-19.el7 setools-libs.x86_64 0:3.3.8-4.el7
Dependency Updated:
audit.x86_64 0:2.8.5-4.el7 audit-libs.x86_64 0:2.8.5-4.el7 libselinux.x86_64 0:2.5-15.el7 libselinux-python.x86_64 0:2.5-15.el7
libselinux-utils.x86_64 0:2.5-15.el7 libsemanage.x86_64 0:2.5-14.el7 libsepol.x86_64 0:2.5-10.el7 policycoreutils.x86_64 0:2.5-34.el7
Complete!
PostgreSQL将安装在”/usr/pgsql-13/”目录下。
# ls -l /usr/pgsql-13/
total 20
drwxr-xr-x. 2 root root 4096 Sep 25 23:11 bin
drwxr-xr-x. 3 root root 23 Sep 25 23:11 doc
drwxr-xr-x. 6 root root 4096 Sep 25 23:11 include
drwxr-xr-x. 5 root root 4096 Sep 25 23:11 lib
drwxr-xr-x. 8 root root 4096 Sep 25 23:11 share
PostgreSQL的自动启动
为了自动启动PostgreSQL，执行以下命令。
# systemctl enable postgresql-13.service
创建数据库集群
创建数据库集群。
正在以root用户身份执行。
数据库文件默认会创建在”/var/lib/pgsql/13/data/”，但在此次中我们将其更改为”/data/”目录下。
可以通过postgresql-13.service文件中的”PGDATA”环境变量进行指定。
# vi /usr/lib/systemd/system/postgresql-13.service
変更前）
Environment=PGDATA=/var/lib/pgsql/13/data/
変更後）
Environment=PGDATA=/data/
# systemctl daemon-reload
为了创建数据库集群，请执行以下命令。
# PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK
确认下面是否已在「/data/」下生成。
# cat /data/PG_VERSION
13
启动PostgreSQL
在启动之前，将PostgreSQL的bin目录添加到路径中。
# su - postgres
/var/lib/pgsql/.pgsql_profileにパスを追加します。
# vi /var/lib/pgsql/.pgsql_profile
PATH=/usr/pgsql-13/bin:$PATH
export PATH
PGDATAを修正します。
# vi /var/lib/pgsql/.bash_profile
#PGDATA=/var/lib/pgsql/12/data
PGDATA=/data
# source ~/.bash_profile
可以通过运行命令「pg_ctl start」来启动PostgreSQL。
$ pg_ctl start
2020-09-25 23:16:08.611 CEST [12910] HINT: Future log output will appear in directory "log".
done
server started
※ 唯一版本:
只要运行命令“systemctl start postgresql-13″，就可以了。
查询数据库
我想确认一下已经创建的数据库集群。
我想显示版本和数据库列表。
$ psql -V
psql (PostgreSQL) 13.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/13/data/postgresql.conf」
変更前）
#listen_addresses = 'localhost'
変更後）
listen_addresses = '*'
下一步是修改pg_hba.conf文件。
# vi /data/pg_hba.conf
※デフォルトでは「/var/lib/pgsql/13/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-13でもいいはず。
更改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/13/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 9.02 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done in 12.99 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 9.12 s, vacuum 0.87 s, primary keys 3.00 s).
一旦准备好数据，接下来就是执行基准测试。
$ pgbench -c 10 -j 10 -t 2000 -N testdb
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 = 4.910 ms
tps = 2036.732154 (including connections establishing)
tps = 2039.124174 (excluding connections establishing)
“-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 | 63.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 | 33.00
pgbench_branches | pgbench_branches_pkey | 50.00
pgbench_accounts | pgbench_accounts_pkey | 98.00
(3 rows)
请参照以下内容。
-
- PostgreSQL 13 Press Kit
-
- PostgreSQL 13.0 Documentation
- pgbenchの使いこなし by Let’s Postgres
