一、安装PgSQL
1.1 下载安装包
1.2 下载依赖包
[root@localhost ~]# yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
1.3 编译安装
[root@localhost ~]# tar -xf postgresql-12.0.tar.gz
[root@localhost ~]# cd postgresql-12.0/
[root@localhost ~]# mkdir -p /opt/software/pgsql/postgresql/data
[root@localhost postgresql-12.0]# ./configure --prefix=/opt/software/pgsql/postgresql
[root@localhost postgresql-12.0]# make && make install
1.4 创建用户组postgres并创建用户postgres
[root@localhost ~]# groupadd postgres
[root@localhost ~]# useradd -g postgres postgres
1.5 给postgresql数据目录授权
[root@localhost ~]# cd /opt/software/pgsql/postgresql
[root@localhost postgresql]# chown postgres:postgres data
1.6 配置环境变量
[root@localhost ~]# cat /etc/profile.d/my_env.sh
export PGHOME=/opt/software/pgsql/postgresql
export PGDATA=/opt/software/pgsql/postgresql/data
PATH=$PATH:$HOME/bin:$PGHOME/bin
[root@localhost ~]# source /etc/profile.d/my_env.sh
1.7 切换postgres用户并使用initdb初使用化数据库
[root@localhost ~]# su - postgres
[postgres@localhost ~]$ initdb
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 "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /pgsql/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
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 /pgsql/postgresql/data -l logfile star
1.8 修改配置文件
- postgresql.conf 配置PostgreSQL数据库服务器的相应的参数。
- pg_hba.conf 配置对数据库的访问权限。
[root@localhost data]# pwd
/opt/software/pgsql/postgresql/data
[root@localhost data]# ls
base pg_dynshmem pg_logical pg_replslot pg_stat pg_tblspc pg_wal postgresql.conf serverlog
global pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_twophase pg_xact postmaster.opts
pg_commit_ts pg_ident.conf pg_notify pg_snapshots pg_subtrans PG_VERSION postgresql.auto.conf postmaster.pid
[root@localhost data]#
image.png
image.png
1.9 配置PostgreSQL开机自启
[root@localhost start-scripts]# pwd
/root/postgresql-12.0/contrib/start-scripts
[root@localhost start-scripts]# ls
freebsd linux macos
[root@localhost start-scripts]# chmod a+x linux
[root@localhost start-scripts]# cp linux /etc/init.d/postgresql
修改/etc/init.d/postgresql文件的两个变量
- prefix 设置为postgresql的安装路径
- PGDATA 设置为postgresql的数据目录路径
[root@localhost start-scripts]# cat /etc/init.d/postgresql | grep -A 5 "Installation prefix"
# Installation prefix
prefix=/opt/software/pgsql/postgresql
# Data directory
PGDATA="/opt/software/pgsql/postgresql/data"
[root@localhost start-scripts]#
设置postgresql服务开机自启
[root@localhost start-scripts]# chkconfig --add postgresql
启动
[root@localhost start-scripts]# service postgresql start
测试
- 执行命令能进去则安装成功
[root@localhost start-scripts]# psql -U postgres -d postgres
psql (12.0)
Type "help" for help.
postgres=#
以上是PostgreSQL的安装
二、监控---PostgreSQL配置
2.1 创建用户
- 需要在 PostgreSQL 数据库建立监控专用的用户，由于 PostgreSQL 版本不同相关命令会有一定差别，创建一个 zbx_monitor 用户密码为 zbx_monitor。
PostgreSQL 10 以上版本
su - postgres
psql
CREATE USER zbx_monitor WITH PASSWORD 'zbx_monitor' INHERIT;
GRANT pg_monitor TO zbx_monitor;
PostgreSQL 9.6 版本及以下
su - postgres
psql
CREATE USER zbx_monitor WITH PASSWORD 'zbx_monitor';
GRANT SELECT ON pg_stat_database TO zbx_monitor;
ALTER USER zbx_monitor WITH SUPERUSER;
1.3 配置访问策略
- 编辑 pg_hba.conf 文件，并添加如下内容
host all zbx_monitor 127.0.0.1/32 trust
host all zbx_monitor 0.0.0.0/0 md5
host all zbx_monitor ::0/0 md5
- 如果 Zabbix agent 和 PostgreSQL 在不同机器，需要配置密码文件，需要创建.pgpass 文件，并存放在 zabbix 用户的家目录下，内容如下：
<REMOTE_HOST1>:5432:postgres:zbx_monitor:<PASSWORD>
- 重启PgSQL
三、监控---Zabbix配置
- zabbix官方模板：https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/templates?at=refs%2Fheads%2Frelease%2F5.0
3.1 添加主机并关联模板
image.png
配置主机宏
image.png
3.2 配置agent
- PostgreSQL 监控需要在 Zabbix Agent 端添加脚本文件
- 下载到 Agent 所在机器，添加 Postgresql 监控 SQL 文件
wget https://dl.cactifans.com/zabbix/postgresql.tar.gz
mkdir -p /var/lib/zabbix/
tar zxvf postgresql.tar.gz
cp -r postgresql/postgresql/ /var/lib/zabbix/
- 添加 UserParameter 文件到 Agent 的 zabbix_agentd.d 目录（根据实际情况修改)
cp -r postgresql/template_db_postgresql.conf /etc/zabbix/zabbix_agentd.d/
image.png
- 重启Agent生效
脚本修复
vi /var/lib/zabbix/postgresql/pgsql.dbstat.sql
#修改为以下内容
SELECT json_object_agg(datname, row_to_json(T)) FROM (
SELECT datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_fetched,
tup_inserted,
tup_updated,
tup_deleted,
conflicts,
temp_files,
temp_bytes,
deadlocks
FROM pg_stat_database
WHERE datname is not NULL
) T
3.3 查看监控数据
image.png
image.png
