在openEuler平台部署特定版本的PostgreSql在安装包收集上存在较大难题，本次需求为制作zabbix系统所需的timescaledbV2插件的PostgreSqlV14.5数据库，在评估多种方案后，采用了基于CentOS底包制作容器方式来实现
一、提前准备事宜
在正式部署前，请提前完成以下事宜：
笔者下载的是centos:latest版本镜像底包，实际版本为CentOS8.3.2011，对应保存为名为“centos8.3.2011.tar ”的文件，其他CentOS8系版本也可以的。
经现网生产系统测试，以下版本zabbix运行良好，且具备平台从Mysql移植数据至PostgreSql库的能力：
postgresql14-14.5-1PGDG.rhel8.x86_64.rpm
postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm
postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm
timescaledb-2-postgresql-14-2.7.0-0.el8.x86_64.rpm
timescaledb-tools-0.14.1-0.el8.x86_64.rpm
timescaledb-2-loader-postgresql-14-2.7.0-0.el8.x86_64.rpm
以上rpm包打包为名为“postgresql14.5el8rpm.tgz”的文件。
二、基于容器底包进行指定版本数据库程序的安装
将容器底包载入
# docker load -i centos8.3.2011.tar
Loaded image: quay.io/centos/centos:latest
[root@localhost dockerfile]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
quay.io/centos/centos latest 300e315adb2f 2 years ago 209MBdock
启动容器
#docker run --privileged -itd --name pgtsdb quay.io/centos/centos:latest /sbin/init
1597f853e87b7e37e1472a839f2b7ff0289c4b6a3ab097ac525d3fcc048573aa
将rpm包传入容器（本步骤没有反馈信息）
# docker cp /tmp/postgresql14.5el8rpm.tgz pgtsdb:/tmp
进入容器安装数据库及插件的rpm包
# docker exec -it pgtsdb /bin/bash
[root@1597f853e87b /]# cd /opt
[root@1597f853e87b opt]# mv /tmp/postgresql14.5el8rpm.tgz .
[root@1597f853e87b opt]# mkdir postgresql14ts-rpm
[root@1597f853e87b opt]# tar -xzvf postgresql14.5el8rpm.tgz
postgresql14-14.5-1PGDG.rhel8.x86_64.rpm
postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm
postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm
timescaledb-2-postgresql-14-2.7.0-0.el8.x86_64.rpm
timescaledb-tools-0.14.1-0.el8.x86_64.rpm
timescaledb-2-loader-postgresql-14-2.7.0-0.el8.x86_64.rpm
[root@1597f853e87b opt]# mv *.rpm postgresql14ts-rpm/
[root@1597f853e87b opt]# cd postgresql14ts-rpm/
[root@1597f853e87b postgresql14ts-rpm]# ls
postgresql14-14.5-1PGDG.rhel8.x86_64.rpm timescaledb-2-loader-postgresql-14-2.7.0-0.el8.x86_64.rpm
postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm timescaledb-2-postgresql-14-2.7.0-0.el8.x86_64.rpm
postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm timescaledb-tools-0.14.1-0.el8.x86_64.rpm
[root@1597f853e87b opt]# yum install glibc-common glibc-langpack-en -y
Failed to set locale, defaulting to C.UTF-8
Waiting for process with pid 125 to finish.
os 160 MB/s | 6.8 MB 00:00
baseos 168 MB/s | 2.2 MB 00:00
Package glibc-common-2.28-127.el8.x86_64 is already installed.
Dependencies resolved.
============================================================================================================================================================
Package Architecture Version Repository Size
============================================================================================================================================================
Installing:
glibc-langpack-en x86_64 2.28-189.el8 baseos 834 k
Upgrading:
glibc x86_64 2.28-189.el8 baseos 2.2 M
glibc-common x86_64 2.28-189.el8 baseos 1.3 M
glibc-minimal-langpack x86_64 2.28-189.el8 baseos 61 k
...
Upgraded:
glibc-2.28-189.el8.x86_64 glibc-common-2.28-189.el8.x86_64 glibc-minimal-langpack-2.28-189.el8.x86_64
Installed:
glibc-langpack-en-2.28-189.el8.x86_64
Complete!
[root@1597f853e87b opt]# cd /opt/postgresql14ts-rpm/ && yum localinstall *.rpm
Last metadata expiration check: 0:39:29 ago on Tue 07 Feb 2023 03:46:53 AM UTC.
Dependencies resolved.
============================================================================================================================================================
Package Architecture Version Repository Size
============================================================================================================================================================
Installing:
postgresql14 x86_64 14.5-1PGDG.rhel8 @commandline 1.5 M
postgresql14-libs x86_64 14.5-1PGDG.rhel8 @commandline 278 k
postgresql14-server x86_64 14.5-1PGDG.rhel8 @commandline 5.7 M
timescaledb-2-loader-postgresql-14 x86_64 2.7.0-0.el8 @commandline 26 k
timescaledb-2-postgresql-14 x86_64 2.7.0-0.el8 @commandline 578 k
timescaledb-tools x86_64 0.14.1-0.el8 @commandline 2.9 M
Upgrading:
openssl-libs x86_64 1:1.1.1k-5.el8_5 baseos 1.5 M
Installing dependencies:
libicu x86_64 60.3-2.el8_1 baseos 8.8 M
lz4 x86_64 1.8.3-3.el8_4 baseos 103 k
openssl x86_64 1:1.1.1k-5.el8_5 baseos 709 k
...
Installed:
libicu-60.3-2.el8_1.x86_64 lz4-1.8.3-3.el8_4.x86_64 openssl-1:1.1.1k-5.el8_5.x86_64
postgresql14-14.5-1PGDG.rhel8.x86_64 postgresql14-libs-14.5-1PGDG.rhel8.x86_64 postgresql14-server-14.5-1PGDG.rhel8.x86_64
timescaledb-2-loader-postgresql-14-2.7.0-0.el8.x86_64 timescaledb-2-postgresql-14-2.7.0-0.el8.x86_64 timescaledb-tools-0.14.1-0.el8.x86_64
Complete!
三、数据库初始化
以下为容器内执行：
初始化数据库，因为是容器化部署，后面会对数据存储目录进行持久化挂载，所以初始化到默认目录就可以了：
# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK
启动postgresql数据库，检查数据库运行状态：
# systemctl enable --now postgresql-14
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2023-02-07 04:26:50 UTC; 10s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 386 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 391 (postmaster)
Tasks: 8 (limit: 304871)
Memory: 14.4M
CGroup: /docker/1597f853e87b7e37e1472a839f2b7ff0289c4b6a3ab097ac525d3fcc048573aa/system.slice/postgresql-14.service
├─391 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─392 postgres: logger
├─394 postgres: checkpointer
├─395 postgres: background writer
├─396 postgres: walwriter
├─397 postgres: autovacuum launcher
├─398 postgres: stats collector
└─399 postgres: logical replication launcher
Feb 07 04:26:49 1597f853e87b systemd[1]: Starting PostgreSQL 14 database server...
Feb 07 04:26:49 1597f853e87b postmaster[391]: 2023-02-07 04:26:49.993 UTC [391] LOG: redirecting log output to logging collector process
Feb 07 04:26:49 1597f853e87b postmaster[391]: 2023-02-07 04:26:49.993 UTC [391] HINT: Future log output will appear in directory "log".
Feb 07 04:26:50 1597f853e87b systemd[1]: Started PostgreSQL 14 database server.
显示“Started PostgreSQL 14 database server”，数据库运行正常。
登录数据库并修改管理员帐户密码
# su - postgres -c "psql"
psql (14.5)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD '此处设置为postgres帐户密码';
ALTER ROLE
postgres=# exit
加载timescale时序数据库插件，交互式命令时一路y即可，后续可以再行配置。
[root@1597f853e87b postgresql14ts-rpm]# timescaledb-tune --pg-config=/usr/pgsql-14/bin/pg_config --conf-path /var/lib/pgsql/14/data/postgresql.conf
Using postgresql.conf at this path:
/var/lib/pgsql/14/data/postgresql.conf
Writing backup to:
/tmp/timescaledb_tune.backup202302070428
shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated
Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 46.55 GB of available memory and 16 CPUs for PostgreSQL 14
Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 11917MB
effective_cache_size = 35753MB
maintenance_work_mem = 2047MB
work_mem = 7627kB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: memory settings will be updated
Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 8
max_worker_processes = 27
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated
WAL settings recommendations
Current:
#wal_buffers = -1
min_wal_size = 80MB
Recommended:
wal_buffers = 16MB
min_wal_size = 512MB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated
Background writer settings recommendations
Current:
Recommended:
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: background writer settings will be updated
Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.9
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 512
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 256
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /var/lib/pgsql/14/data/postgresql.conf
键入exit退出容器
在openeuler主机上重启容器
# docker restart pgtsdb
再次输入docker exec -it pgtsdb /bin/bash进入容器
# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2023-02-07 04:31:26 UTC; 5s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 124 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 133 (postmaster)
Tasks: 9 (limit: 304871)
Memory: 263.1M
CGroup: /docker/1597f853e87b7e37e1472a839f2b7ff0289c4b6a3ab097ac525d3fcc048573aa/system.slice/postgresql-14.service
├─133 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─161 postgres: logger
├─163 postgres: checkpointer
├─164 postgres: background writer
├─165 postgres: walwriter
├─166 postgres: autovacuum launcher
├─167 postgres: stats collector
├─168 postgres: TimescaleDB Background Worker Launcher
└─169 postgres: logical replication launcher
Feb 07 04:31:26 1597f853e87b systemd[1]: Starting PostgreSQL 14 database server...
Feb 07 04:31:26 1597f853e87b postmaster[133]: 2023-02-07 04:31:26.526 UTC [133] LOG: redirecting log output to logging collector process
Feb 07 04:31:26 1597f853e87b postmaster[133]: 2023-02-07 04:31:26.526 UTC [133] HINT: Future log output will appear in directory "log".
Feb 07 04:31:26 1597f853e87b systemd[1]: Started PostgreSQL 14 database server.
看到“TimescaleDB Background Worker Launcher”即可确认时序数据库插件已挂载成功。
四、将制作完成的容器二次打包为PostgreSQl14.5withTimeScale2的镜像，供后续分发部署或分享
打包当前镜像
# docker commit -m "centos8 postgresql with timescale" -a "daijianbing" 1597f853e87b pg14withtimescale:v14.5
sha256:10a71bd1190298baa8ba33dcc1d976bc5f62989611bdb0e05d0f99938d8433ff
[root@localhost dockerfile]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
pg14withtimescale v14.5 10a71bd11902 6 seconds ago 413MB
quay.io/centos/centos latest 300e315adb2f 2 years ago 209MB
删除当前容器，通过新镜像再次生成容器，并检查应用包运行情况
[root@localhost dockerfile]# docker rm pgtsdb
Error response from daemon: You cannot remove a running container 1597f853e87b7e37e1472a839f2b7ff0289c4b6a3ab097ac525d3fcc048573aa. Stop the container before attempting removal or force remove
[root@localhost dockerfile]# docker rm -f pgtsdb
pgtsdb
[root@localhost dockerfile]# docker run --privileged -itd --name pgtsdb pg14withtimescale:v14.5 /sbin/init
6174a0afb9d5f5cfae048831e4772e3461e99a78cb5b09eaf868e45187dca9d2
[root@localhost dockerfile]# docker exec -it pgtsdb /bin/bash
[root@6174a0afb9d5 /]# systemctl status postgresql-14
● postgresql-14.service - PostgreSQL 14 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-14.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2023-02-07 04:35:02 UTC; 4s ago
Docs: https://www.postgresql.org/docs/14/static/
Process: 76 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 139 (postmaster)
Tasks: 9 (limit: 304871)
Memory: 264.1M
CGroup: /docker/6174a0afb9d5f5cfae048831e4772e3461e99a78cb5b09eaf868e45187dca9d2/system.slice/postgresql-14.service
├─139 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
├─156 postgres: logger
├─158 postgres: checkpointer
├─159 postgres: background writer
├─160 postgres: walwriter
├─161 postgres: autovacuum launcher
├─162 postgres: stats collector
├─163 postgres: TimescaleDB Background Worker Launcher
└─164 postgres: logical replication launcher
Feb 07 04:34:58 6174a0afb9d5 systemd[1]: Starting PostgreSQL 14 database server...
Feb 07 04:34:58 6174a0afb9d5 postmaster[139]: 2023-02-07 04:34:58.711 UTC [139] LOG: redirecting log output to logging collector process
Feb 07 04:34:58 6174a0afb9d5 postmaster[139]: 2023-02-07 04:34:58.711 UTC [139] HINT: Future log output will appear in directory "log".
Feb 07 04:35:02 6174a0afb9d5 systemd[1]: Started PostgreSQL 14 database server.
[root@6174a0afb9d5 /]# exit
通过新镜像生成的容器运行良好，至此，带timescaledbV2时序数据库插件的PostgreSqlV14.5容器镜像在openEuler平台构建完成。
