安装15.3版本单实例PostgreSQL

这里打算安装一个15.3版本的pg单实例,将其安装到/data/postgres下

一 安装步骤

1.1 安装依赖包

yum install -y bison flex readline-devel zlib-devel

yum install -y docbook-dtds docbook-style-xsl fop libxslt

yum install -y gcc

/*

检查是否安装成功：

rpm -qa | grep -E 'bison|flex|readline-devel|zlib-devel'

rpm -qa | grep -E 'docbook-dtds|docbook-style-xsl|fop|libxslt'

rpm -qa | grep gcc

*/

1.2 下载安装包

下载地址：PostgreSQL: File Browser

1.3 创建用户

#创建postgres 用户

useradd postgres

#设置用户密码

passwd postgres

1.4 创建目录

mkdir -p /data/postgres/data

chown -R postgres:postgres /data/postgres/

1.5 编译安装

su - postgres

cd /data/postgres

将安装包上传至该路径下。

tar -zxvf postgresql-15.3.tar.gz

cd postgresql-15.3/

./configure --prefix=/data/postgres

make

make install

这里会将 PostereSQL安装到“/data/postgres” 目录下。

#删除安装包：

rm -rf /data/postgres/postgresql-15.3.tar.gz

1.6 修改bash_profile文件，设置 PostgreSQL 的环境变量。

vi /home/postgres/.bash_profile

新增：

export PGHOME=/data/postgres export PGDATA=/data/postgres/data export PGLIB=/data/postgres/lib export PATH=$PGHOME/bin:$PATH

#使环境变量生效。

source /home/postgres/.bash_profile

1.7 初始化数据库。

cd /data/postgres/

bin/initdb -D data/

在数据库初始化成功之后，输出结果如下。

Success. You can now start the database server using:

bin/pg_ctl -D data/ -l logfile start

1.8 启动 PostgreSQL

bin/pg_ctl -D data/ -l logfile start

启动成功后输出结果如下。

waiting for server to start....done

server started

1.9 为数据库用户postgres用户设置密码

[postgres@mypgtest postgres]$ psql
psql (15.3)
Type "help" for help.

postgres=#alter user postgres with password '自定义密码';

1.10 配置pg

1.10.1 修改Postgresql.conf文件

修改listen_addresses，允许所有客户端访问。

cd /data/postgres/data

vi postgresql.conf

这里将listen_addresses改为*:

listen_addresses = '*'

1.10.2 修改pg_hba.conf

① 增加以下配置：

host all all 0.0.0.0/0 md5

② 将local的METHOD由trust改为password

默认从服务器上登陆数据库，不用输入密码，不安全，需要进行如下修改：

#local all all trust
local all all password

1.11 重启pg数据库

重启数据库，使上述配置生效

cd /data/postgres

pg_ctl -D data/ -l logfile restart

1.12 配置pg开机自启动

用root用户操作。

1.12.1 新建service文件

cd /etc/systemd/system
vi postgresql.service,新增如下内容：

[Unit] Description=PostgreSQL database server Documentation=man:postgres(1) Documentation=http://www.postgresql.org/docs/12/static/ After=network.target [Service] Type=forking User=postgres ExecStart=/data/postgres/bin/pg_ctl -D /data/postgres/data -l /data/postgres/logfile start ExecStop=/data/postgres/bin/pg_ctl -D /data/postgres/data -l /data/postgres/logfile stop ExecReload=/data/postgres/bin/pg_ctl -D /data/postgres/data -l /data/postgres/logfile reload TimeoutSec=300 Restart=on-failure [Install] WantedBy=multi-user.target

1.12.2 加载服务

systemctl daemon-reload

1.12.3 验证用systemctl方式启动

#先关闭之前启动的数据库
su - postgres
/data/postgres/bin/pg_ctl -D /data/postgres/data -l /data/postgres/logfile stop
#验证用systemctl方式启停数据库
#用root用户启动
systemctl start postgresql
#查看是否启动成功
systemctl status postgresql
ps -ef | grep postgres

1.12.4 设置开机自启动

systemctl enable postgresql

至此，数据库已安装成功，可以使用了。如若想使用更多功能，可继续按下面的步骤安装相关插件：

二 安装相关插件

2.1 安装passwordcheck插件

用postgres用户安装。

pg提供的passwordcheck插件可以进行简单的密码复杂度检查。

① 安装该插件：

cd /data/postgres/postgresql-15.3/

./configure --prefix=/data/postgres

cd contrib/passwordcheck/

make

make install

② 修改配置文件 /data/postgres/data/postgresql.conf 中的参数 shared_preload_libraries。

shared_preload_libraries = 'passwordcheck'

③ 重启 PostgreSQL

用root用户重启

systemctl restart postgresql

2.2 安装pg_top扩展

下载地址：https://gitlab.com/pg_top/pg_top

将安装包上传至/home/postgres下,改下owner：

chown postgres:postgres pg_top-main.tar.gz

1.安装依赖包

yum install cmake -y

yum install postgresql-devel -y

rpm -ivh libbsd-0.8.3-1.el7.x86_64.rpm

rpm -ivh libbsd-devel-0.8.3-1.el7.x86_64.rpm

/*

我这里是centos 9 stream系统，我没下载rpm包，我是这样装的：

yum install -y epel-release #libbsd在epel仓库里，因此先装上epel

yum install libbsd -y

yum install libbsd-devel

如果还是提示没用libbsd包，尝试下这俩命令：

yum install -y dnf-plugins-core

yum config-manager --set-enabled crb

*/

2.解压缩安装包

su - postgres

tar -zxvf pg_top-main.tar.gz

cd pg_top-main

3.配置安装路径，并检查pg_top扩展的安装条件

cmake -DCMAKE_INSTALL_PREFIX=/data/postgres/ CMakeLists.txt

/*

假如提示：

CMake Warning at CMakeLists.txt:137 (message):

Cannot build man page without rst2man.py or rst2man...

可以忽略这个警告，这个不影响主程序运行。

也可以安装下下面这个包，再执行下上面的cmake命令消除这个警告：

yum install -y python3-docutils

*/

4.安装pg_top扩展

make install

安装成功后，会提示：

Install the project...

……

-- Installing: /data/postgres/bin/pg_top

5.验证是否安装成功——查看帮助信息

pg_top --help

6.使用示例

pg_top -h localhost

这里可以看到该用户的insert操作占用了56%的cpu。

这里只查看到是insert，但看不到具体是什么sql，可以这样看：

SELECT pid, usename, datname, application_name,client_addr,query, state, query_start,now() - query_start AS duration,wait_event

FROM pg_stat_activity

WHERE pid =进程id;

结果示例：

pg_top的常用参数

-X参数

参数-X用于展示每个进程的IO信息，如IOPS,Reads,Writes。使用该参数可以监控到IO高的进程。

-R参数

-R参数用于监控主从复制信息，主从延迟和延迟LSN的位置。得到的监控信息和系统表pg_stat_replication一致，如图：

-z参数

可以使用-z参数值监控指定的用户，示例：

./pg_top -h localhost -z postgre

-o参数

使用-o参数可以对显示出来的数据进行排序，如安装xtime进行排序：

./pg_top -h localhost -o xtime

2.3 安装pg_stat_statements扩展

pg_stat_statements可以分析出现频率较高的sql语句，即Top SQL，可以查看哪些语句会导致性能低下，以及慢sql的执行频率等信息。

这里用postgres用户安装。

1 进入 PostgreSQL 源码目录，编译和安装pg_stat_statements 扩展。

cd /data/postgres/

cd postgresql-15.3/

./configure --prefix=/data/postgres

cd contrib/pg_stat_statements/

make

make install

在执行成功后，会自动将生成的 pg_stat statements.so 文件复制到PostgreSQL安装目录的lib/目录下。

2 修改 postgresql.conf 文件中的shared_preload_libraries 参数

cd /data/postgres/data/

vi postgresql.conf

shared_preload_libraries='pg_stat_statements'

3 重启pg

/data/postgres/bin/pg_ctl -D /data/postgres/data -l /data/postgres/logfile restart

4 查看shared_preload_libraries参数值

show shared_preload_libraries;

5 创建pg_stat_statements扩展

create extension pg_stat_statements;

6 查看该扩展的详细信息

postgres=# \dx+ pg_stat_statements; Objects in extension "pg_stat_statements" Object description

function pg_stat_statements(boolean)

function pg_stat_statements_info()

function pg_stat_statements_reset(oid,oid,bigint)

view pg_stat_statements

view pg_stat_statements_info

(5 rows)

7 查看该扩展的参数设置

postgres=# select name,setting from pg_settings where name like 'pg_stat_statements%';

name | setting

-----------------------------------+---------

pg_stat_statements.max | 5000

pg_stat_statements.save | on

pg_stat_statements.track | top

pg_stat_statements.track_planning | off

pg_stat_statements.track_utility | on

(5 rows)

#使用该扩展查询总执行时间最长的5条SQL语句。

select query "SQL语句",calls "被执行的次数",total_exec_time "总执行时间",rows

from pg_stat_statements

order by total_exec_time desc

limit 5;

这里的时间单位是毫秒。

--本篇文章参考自《PostgreSQL数据库实战派》，并做了些许改动（改动了数据目录路径）