zabbix6.2.3系统数据库从Mysql迁移至PostgreSQL数据库（一）安装PostgreSQL数据库

代先生.重庆

已于 2023-11-24 08:17:31 修改

阅读量2.1k

点赞数

分类专栏： zabbix 文章标签：数据库 postgresql mysql zabbix

于 2022-11-11 11:07:11 首次发布

本文链接：https://blog.csdn.net/forestqq/article/details/127801294

版权

zabbix 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Zabbix平台监控主机达4000+以后，mysql数据库性能感觉不足，zabbix最新的6.2.3版对elasticsearch的支持还是不行，于是考虑切换到PostgreSQL数据库，并启用timescaledb，提升整体性能，本项目为生产系统，需进行离线部署。

1、先在能访问公网的主机上下载PostgreSQL数据库所需的安装包：

[root@localhost ~]# dnf reinstall https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm
PostgreSQL 10 for RHEL / Rocky 8 - x86_64                                                                                                                                                                7.4  B/s | 195  B     00:26    
PostgreSQL 10 for RHEL / Rocky 8 - x86_64                                                                                                                                                                1.6 MB/s | 1.7 kB     00:00    
导入 GPG 公钥 0x442DF0F8:
 Userid: "PostgreSQL RPM Building Project <pgsql-pkg-yum@postgresql.org>"
 指纹: 68C9 E2B9 1A37 D136 FE74 D176 1F16 D2E1 442D F0F8
 来自: /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
确定吗？[y/N]： y
PostgreSQL 10 for RHEL / Rocky 8 - x86_64                                                                                                                                                                 
...
安装大小：14 k
确定吗？[y/N]： y
下载软件包：
...

已重装:
  pgdg-redhat-repo-42.0-28.noarch                                                                                                                                                                                                        

完毕！
[root@localhost ~]# dnf list |grep postgresql
...
postgresql14.x86_64                                    14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-contrib.x86_64                            14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-devel.x86_64                              14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-docs.x86_64                               14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-libs.x86_64                               14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-llvmjit.x86_64                            14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-odbc.x86_64                               13.02.0000-1PGDG.rhel8                                     pgdg14       
postgresql14-plperl.x86_64                             14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-plpython3.x86_64                          14.5-1PGDG.rhel8                                           pgdg14       
postgresql14-pltcl.x86_64                              14.5-1PGDG.rhel8                                           pgdg14       
...
[root@localhost ~]# tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
> [timescale_timescaledb]
> name=timescale_timescaledb
> baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$basearch
> repo_gpgcheck=1
> gpgcheck=0
> enabled=1
> gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
> sslverify=1
> sslcacert=/etc/pki/tls/certs/ca-bundle.crt
> metadata_expire=300
> EOL
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/8/$basearch
repo_gpgcheck=1
gpgcheck=0
enabled=1
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
[root@localhost ~]# mkdir /opt/postgresqlrpm
[root@localhost ~]# dnf install timescaledb-2-postgresql-14 --downloadonly --downloaddir=/opt/postgresqlrpm 
上次元数据过期检查：0:00:17 前，执行于 2022年10月26日 星期三 21时11分22秒。
依赖关系解决。
=========================================================================================================================================================================================================================================
 软件包                                                                 架构                                       版本                                                  仓库                                                       大小
=========================================================================================================================================================================================================================================
安装:
 timescaledb-2-postgresql-14                                            x86_64                                     2.8.1-0.el8                                           timescale_timescaledb                                     608 k
安装依赖关系:
 postgresql14                                                           x86_64                                     14.5-1PGDG.rhel8                                      pgdg14                                                    1.5 M
 postgresql14-libs                                                      x86_64                                     14.5-1PGDG.rhel8                                      pgdg14                                                    278 k
 postgresql14-server                                                    x86_64                                     14.5-1PGDG.rhel8                                      pgdg14                                                    5.7 M
 timescaledb-2-loader-postgresql-14                                     x86_64                                     2.8.1-0.el8                                           timescale_timescaledb                                      27 k
 timescaledb-tools                                                      x86_64                                     0.14.1-0.el8                                          timescale_timescaledb                                     2.9 M

事务概要
=========================================================================================================================================================================================================================================
安装  6 软件包

总下载：11 M
安装大小：49 M
DNF将仅会从事务下载软件包。
确定吗？[y/N]： y
下载软件包：
(1/6): postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm                                                                                                                                                     7.8 kB/s | 278 kB     00:35    
(2/6): timescaledb-2-loader-postgresql-14-2.8.1-0.el8.x86_64.rpm                                                                                                                                         8.9 kB/s |  27 kB     00:03    
(3/6): timescaledb-2-postgresql-14-2.8.1-0.el8.x86_64.rpm                                                                                                                                                381 kB/s | 608 kB     00:01    
(4/6): timescaledb-tools-0.14.1-0.el8.x86_64.rpm                                                                                                                                                         844 kB/s | 2.9 MB     00:03    
(5/6): postgresql14-14.5-1PGDG.rhel8.x86_64.rpm                                                                                                                                                           29 kB/s | 1.5 MB     00:54    
(6/6): postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm                                                                                                                                                    40 kB/s | 5.7 MB     02:26    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
总计                                                                                                                                                                                                      77 kB/s |  11 MB     02:26     
完毕！
下载的软件包保存在缓存中，直到下次成功执行事务。
您可以通过执行 'dnf clean packages' 删除软件包缓存。
[root@localhost ~]# cd /opt
[root@localhost postgresqlrpm]# ll postgresqlrpm
总用量 19168
-rw-r--r--. 1 root root 1612240 10月 26 21:12 postgresql14-14.5-1PGDG.rhel8.x86_64.rpm
-rw-r--r--. 1 root root  285068 10月 26 21:12 postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm
-rw-r--r--. 1 root root 5968968 10月 26 21:14 postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm
-rw-r--r--. 1 root root   27484 10月 26 21:12 timescaledb-2-loader-postgresql-14-2.8.1-0.el8.x86_64.rpm
-rw-r--r--. 1 root root  622664 10月 26 21:12 timescaledb-2-postgresql-14-2.8.1-0.el8.x86_64.rpm
-rw-r--r--. 1 root root 3057804 10月 26 21:12 timescaledb-tools-0.14.1-0.el8.x86_64.rpm

下载的有以下几个包：

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-loader-postgresql-14-2.8.1-0.el8.x86_64.rpm
timescaledb-2-postgresql-14-2.8.1-0.el8.x86_64.rpm
timescaledb-tools-0.14.1-0.el8.x86_64.rpm

但实际后续直实部署中发现zabbix对timescaledb 2.8.1兼容有坑，在此就不再啰嗦，需要手工到https://packagecloud.io/timescale/timescaledb/el/8/

下载所需timescaledb两个2.7.0低版本的包，最终打包的离线文件版本如下：

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-loader-postgresql-14-2.7.0-0.el8.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
将文件打包上传至目标数据库服务器。

2、初始化安装postgresql+timescaledb

首先安装postgresql14

[root@localhost pgsql14rpm]# tar -xvf postgresqlrpm14.tgz 
postgresqlrpm/
postgresqlrpm/postgresql14-14.5-1PGDG.rhel8.x86_64.rpm
postgresqlrpm/postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm
postgresqlrpm/postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm
postgresqlrpm/timescaledb-2-loader-postgresql-14-2.7.0-0.el8.x86_64.rpm
postgresqlrpm/timescaledb-2-postgresql-14-2.7.0-0.el8.x86_64.rpm
postgresqlrpm/timescaledb-tools-0.14.1-0.el8.x86_64.rpm
[root@localhost pgsql14rpm]# cd postgresqlrpm
[root@localhost postgresqlrpm]# dnf localinstall -y postgresql14*
上次元数据过期检查：3:40:34 前，执行于 2022年10月27日 星期四 06时22分17秒。
依赖关系解决。
=======================================================================================================================================
 软件包                               架构                    版本                                 仓库                           大小
=======================================================================================================================================
安装:
 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
安装依赖关系:
 libicu                               x86_64                  60.3-2.el8_1                         BaseOS                        8.8 M
 lz4                                  x86_64                  1.8.3-3.el8                          BaseOS                        104 k

事务概要
=======================================================================================================================================
安装  5 软件包

总计：16 M
总下载：8.9 M
安装大小：64 M
下载软件包：
(1/2): lz4-1.8.3-3.el8.x86_64.rpm                                                                      2.9 MB/s | 104 kB     00:00    
(2/2): libicu-60.3-2.el8_1.x86_64.rpm                                                                   24 MB/s | 8.8 MB     00:00    
---------------------------------------------------------------------------------------------------------------------------------------
总计                                                                                                    25 MB/s | 8.9 MB     00:00     
...

已安装:
  libicu-60.3-2.el8_1.x86_64                   lz4-1.8.3-3.el8.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   

完毕！
[root@localhost postgresqlrpm]# /usr/pgsql-14/bin/postgresql-14-setup initdb
Initializing database ... OK

[root@localhost postgresqlrpm]# systemctl enable postgresql-14
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-14.service → /usr/lib/systemd/system/postgresql-14.service.
[root@localhost postgresqlrpm]# systemctl start postgresql-14
[root@localhost postgresqlrpm]# 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 Thu 2022-10-27 10:05:00 CST; 4s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 17114 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 17120 (postmaster)
    Tasks: 8 (limit: 205505)
   Memory: 17.0M
   CGroup: /system.slice/postgresql-14.service
           ├─17120 /usr/pgsql-14/bin/postmaster -D /var/lib/pgsql/14/data/
           ├─17122 postgres: logger 
           ├─17124 postgres: checkpointer 
           ├─17125 postgres: background writer 
           ├─17126 postgres: walwriter 
           ├─17127 postgres: autovacuum launcher 
           ├─17128 postgres: stats collector 
           └─17129 postgres: logical replication launcher 

10月 27 10:05:00 localhost.localdomain systemd[1]: Starting PostgreSQL 14 database server...
10月 27 10:05:00 localhost.localdomain postmaster[17120]: 2022-10-27 10:05:00.806 CST [17120] 日志:  日志输出重定向到日志收集进程
10月 27 10:05:00 localhost.localdomain postmaster[17120]: 2022-10-27 10:05:00.806 CST [17120] 提示:  后续的日志输出将出现在目录 "log" >
10月 27 10:05:00 localhost.localdomain systemd[1]: Started PostgreSQL 14 database server.
[root@localhost postgresqlrpm]# echo 'postgres:此处设置为你操作系统侧的postgres用户密码'|chpasswd -e
[root@localhost postgresqlrpm]# cat /etc/passwd |grep postgres
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash
[root@localhost postgresqlrpm]# su - postgres
[postgres@localhost ~]$ psql
psql (14.5)
输入 "help" 来获取帮助信息.

postgres=# \q

测试服务运行正常后，在数据库正式运行目录下重新初始数据库,本例为/app/pgsqldata

[root@localhost postgresqlrpm]# mkdir /app/pgsqldata
[root@localhost postgresqlrpm]# chown -R postgres:postgres /app/pgsqldata
[root@localhost postgresqlrpm]# ll /app
总用量 0
drwxr-x--- 2 postgres postgres 6 10月 27 10:04 pgsqldata
[root@localhost postgresqlrpm]# su - postgres
上一次登录：四 10月 27 10:08:29 CST 2022pts/0 上
[postgres@localhost ~]$ /usr/pgsql-14/bin/initdb -D /app/pgsqldata
属于此数据库系统的文件宿主为用户 "postgres".
此用户也必须为服务器进程的宿主.
数据库簇将使用本地化语言 "zh_CN.UTF-8"进行初始化.
默认的数据库编码已经相应的设置为 "UTF8".
initdb: 无法为本地化语言环境"zh_CN.UTF-8"找到合适的文本搜索配置
缺省的文本搜索配置将会被设置到"simple"

禁止为数据页生成校验和.

修复已存在目录 /app/pgsqldata 的权限 ... 成功
正在创建子目录 ... 成功
选择动态共享内存实现 ......posix
选择默认最大联接数 (max_connections) ... 100
选择默认共享缓冲区大小 (shared_buffers) ... 128MB
选择默认时区 ... Asia/Shanghai
创建配置文件 ... 成功
正在运行自举脚本 ...成功
正在执行自举后初始化 ...成功
同步数据到磁盘...成功

initdb: 警告: 为本地连接启用"trust"身份验证
你可以通过编辑 pg_hba.conf 更改或你下次
执行 initdb 时使用 -A或者--auth-local和--auth-host选项.

成功。您现在可以用下面的命令开启数据库服务器：

    /usr/pgsql-14/bin/pg_ctl -D /app/pgsqldata -l 日志文件 start

[postgres@localhost ~]$ ll /app/pgsqldata/
总用量 56
drwx------ 5 postgres postgres    41 10月 27 10:18 base
drwx------ 2 postgres postgres  4096 10月 27 10:18 global
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_commit_ts
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_dynshmem
-rw------- 1 postgres postgres  4789 10月 27 10:18 pg_hba.conf
-rw------- 1 postgres postgres  1636 10月 27 10:18 pg_ident.conf
drwx------ 4 postgres postgres    68 10月 27 10:18 pg_logical
drwx------ 4 postgres postgres    36 10月 27 10:18 pg_multixact
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_notify
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_replslot
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_serial
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_snapshots
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_stat
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_stat_tmp
drwx------ 2 postgres postgres    18 10月 27 10:18 pg_subtrans
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_tblspc
drwx------ 2 postgres postgres     6 10月 27 10:18 pg_twophase
-rw------- 1 postgres postgres     3 10月 27 10:18 PG_VERSION
drwx------ 3 postgres postgres    60 10月 27 10:18 pg_wal
drwx------ 2 postgres postgres    18 10月 27 10:18 pg_xact
-rw------- 1 postgres postgres    88 10月 27 10:18 postgresql.auto.conf
-rw------- 1 postgres postgres 28769 10月 27 10:18 postgresql.conf
[postgres@localhost ~]$ exit
注销

修改服务配置文件中的数据存储路径参数为新的数据库目录，启动数据库：

[root@localhost postgresqlrpm]# vi /usr/lib/systemd/system/postgresql-14.service 
[root@localhost postgresqlrpm]# cat /usr/lib/systemd/system/postgresql-14.service |grep Environment=PGDATA=
#Environment=PGDATA=/var/lib/pgsql/14/data/
Environment=PGDATA=/app/pgsqldata/
[root@localhost postgresqlrpm]# systemctl daemon-reload
[root@localhost postgresqlrpm]# systemctl restart postgresql-14
[root@localhost postgresqlrpm]# ps -ef |grep pgsql
postgres   17363       1  0 10:22 ?        00:00:00 /usr/pgsql-14/bin/postmaster -D /app/pgsqldata/
root       17379    7932  0 10:23 pts/0    00:00:00 grep --color=auto pgsql
[root@localhost postgresqlrpm]# 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 Thu 2022-10-27 10:22:28 CST; 1min 37s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 17356 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 17363 (postmaster)
    Tasks: 8 (limit: 205505)
   Memory: 16.8M
   CGroup: /system.slice/postgresql-14.service
           ├─17363 /usr/pgsql-14/bin/postmaster -D /app/pgsqldata/
           ├─17365 postgres: logger 
           ├─17367 postgres: checkpointer 
           ├─17368 postgres: background writer 
           ├─17369 postgres: walwriter 
           ├─17370 postgres: autovacuum launcher 
           ├─17371 postgres: stats collector 
           └─17372 postgres: logical replication launcher 

10月 27 10:22:28 localhost.localdomain systemd[1]: Starting PostgreSQL 14 database server...
10月 27 10:22:28 localhost.localdomain postmaster[17363]: 2022-10-27 10:22:28.579 CST [17363] 日志:  日志输出重定向到日志收集进程
10月 27 10:22:28 localhost.localdomain postmaster[17363]: 2022-10-27 10:22:28.579 CST [17363] 提示:  后续的日志输出将出现在目录 "log" >
10月 27 10:22:28 localhost.localdomain systemd[1]: Started PostgreSQL 14 database server.
[root@localhost postgresqlrpm]# su - postgres -c "psql"
psql (14.5)
输入 "help" 来获取帮助信息.

postgres=# ALTER USER postgres WITH PASSWORD '此处设置你的postgres用户密码';
ALTER ROLE
postgres=# exit

安装timescaledb

[root@localhost postgresqlrpm]# ll
总用量 11316
-rw-r--r-- 1 root root 1612240 10月 27 09:18 postgresql14-14.5-1PGDG.rhel8.x86_64.rpm
-rw-r--r-- 1 root root  285068 10月 27 09:18 postgresql14-libs-14.5-1PGDG.rhel8.x86_64.rpm
-rw-r--r-- 1 root root 5968968 10月 27 09:20 postgresql14-server-14.5-1PGDG.rhel8.x86_64.rpm
-rw-r--r-- 1 root root   27484 10月 27 09:18 timescaledb-2-loader-postgresql-14-2.7.0-0.el8.x86_64.rpm
-rw-r--r-- 1 root root  622664 10月 27 09:19 timescaledb-2-postgresql-14-2.7.0-0.el8.x86_64.rpm
-rw-r--r-- 1 root root 3057804 10月 27 09:19 timescaledb-tools-0.14.1-0.el8.x86_64.rpm
[root@localhost postgresqlrpm]# dnf localinstall timescaledb-*
上次元数据过期检查：0:27:45 前，执行于 2022年10月27日 星期四 10时02分56秒。
依赖关系解决。
=======================================================================================================================================
 软件包                                            架构                  版本                        仓库                         大小
=======================================================================================================================================
安装:
 timescaledb-2-loader-postgresql-14                x86_64                2.7.0-0.el8                 @commandline                 27 k
 timescaledb-2-postgresql-14                       x86_64                2.7.0-0.el8                 @commandline                608 k
 timescaledb-tools                                 x86_64                0.14.1-0.el8                @commandline                2.9 M

事务概要
=======================================================================================================================================
安装  3 软件包

总计：3.5 M
安装大小：18 M
确定吗？[y/N]： y
...
已安装:
  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                                

完毕！
[root@localhost postgresqlrpm]# timescaledb-tune --pg-config=/usr/pgsql-14/bin/pg_config --conf-path /app/pgsqldata/postgresql.conf
Using postgresql.conf at this path:
/app/pgsqldata/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202210271642

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 31.40 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 = 8037MB
effective_cache_size = 24112MB
maintenance_work_mem = 2047MB
work_mem = 5143kB
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 = 256
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: /app/pgsqldata/postgresql.conf
[root@localhost postgresqlrpm]# 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 Thu 2022-10-27 16:44:10 CST; 20s ago
     Docs: https://www.postgresql.org/docs/14/static/
  Process: 5962 ExecStartPre=/usr/pgsql-14/bin/postgresql-14-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 5969 (postmaster)
    Tasks: 9 (limit: 205505)
   Memory: 184.7M
   CGroup: /system.slice/postgresql-14.service
           ├─5969 /usr/pgsql-14/bin/postmaster -D /app/pgsqldata/
           ├─5971 postgres: logger 
           ├─5973 postgres: checkpointer 
           ├─5974 postgres: background writer 
           ├─5975 postgres: walwriter 
           ├─5976 postgres: autovacuum launcher 
           ├─5977 postgres: stats collector 
           ├─5978 postgres: TimescaleDB Background Worker Launcher 
           └─5979 postgres: logical replication launcher 

10月 27 16:44:10 localhost.localdomain systemd[1]: Starting PostgreSQL 14 database server...
10月 27 16:44:10 localhost.localdomain postmaster[5969]: 2022-10-27 16:44:10.207 CST [5969] 日志:  日志输出重定向到日志收集进程
10月 27 16:44:10 localhost.localdomain postmaster[5969]: 2022-10-27 16:44:10.207 CST [5969] 提示:  后续的日志输出将出现在目录 "log"中.
10月 27 16:44:10 localhost.localdomain systemd[1]: Started PostgreSQL 14 database server.
[root@localhost postgresqlrpm]# psql -U postgres -h localhost
psql (14.5)
输入 "help" 来获取帮助信息.

postgres=# CREATE EXTENSION timescaledb;
警告:  
WELCOME TO
 _____ _                               _     ____________  
|_   _(_)                             | |    |  _  \ ___ \ 
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ / 
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \ 
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 2.7.0
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/timescaledb/latest/getting-started
 2. API reference documentation: https://docs.timescale.com/api/latest
 3. How TimescaleDB is designed: https://docs.timescale.com/timescaledb/latest/overview/core-concepts

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescale.com/timescaledb/latest/how-to-guides/configuration/telemetry.

CREATE EXTENSION
postgres=# \dx
                                            已安装扩展列表
    名称     | 版本  |  架构模式  |                               描述                                
-------------+-------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0   | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.7.0 | public     | Enables scalable inserts and complex queries for time-series data
(2 行记录)

postgres=# \q
[root@localhost postgresqlrpm]# psql -U postgres -h localhost
psql (14.5)
输入 "help" 来获取帮助信息.

postgres=# \dx
                                            已安装扩展列表
    名称     | 版本  |  架构模式  |                               描述                                
-------------+-------+------------+-------------------------------------------------------------------
 plpgsql     | 1.0   | pg_catalog | PL/pgSQL procedural language
 timescaledb | 2.7.0 | public     | Enables scalable inserts and complex queries for time-series data
(2 行记录)

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
(1 行记录)

测试数据库及插件均正常运行。

代先生.重庆

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
0
评论
zabbix6.2.3系统数据库从Mysql迁移至PostgreSQL数据库（一）安装PostgreSQL数据库

zabbix最新的6.2.3版从mysql切换到PostgreSQL数据库，并启用timescaledb，提升整体性能的实践案例
复制链接

扫一扫