系统版本：Centos7.8
数据库版本：postgresql-13.6
pg_pathman版本：1.5.12
所有文件下载地址：链接：https://pan.baidu.com/s/1XvMk_q4WCtb0rImTq21INA
提取码：yyds
1、官网下载
下载地址：https://www.postgresql.org/ftp/source/v13.6/
并安装需要用到的工具
yum install -y gcc-c++ gcc cmake ncurses-devel perl zlib* readline-devel
2、创建用户和组
// 创建数据库文件目录（data目录和log目录存放于/usr/local/pgfil目录下，其他文件存放于/usr/local/pgsql目录下）
[root@node1 ~]# mkdir -p /usr/local/pgsql/source
[root@node1 ~]# mkdir -p /usr/local/pgfile/data /usr/local/pgfile/log
[root@node1 ~]# useradd postgres
[root@node1 ~]# chown -R postgres:postgres /usr/local/pgfile
[root@node1 pgsql]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
3、设置环境变量
[root@node1 ~]# vim /etc/profile
export PATH=/usr/local/pgsql/bin:$PATH
export PGHOME=/usr/local/pgsql:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH
[root@node1 ~]# source /etc/profile
4、指定安装路径
//把下载好的postgresql13.6源码包、uuid-ossp安装包和pg_pathman（1.5.12）放在/usr/local/pgsql/source下
[root@node1 ~]# cd /usr/local/pgsql/source
[root@node1 source]# tar -zxvf postgresql-13.6.tar.gz
[root@node1 source]# cd postgresql-13.6
//安装uuid依赖包,否则./configure会报错
[root@node1 pgsql-13.6]# yum -y install uuid-devel libuuid-devel
[root@node1 pgsql-13.6]# ./configure --prefix=/usr/local/pgsql --with-uuid=ossp
[root@node1 pgsql-13.6]# make -j4 && make install
[root@node1 pgsql-13.6]# echo $?
5、初始化数据库
//切换用户
[root@node1 pgsql-13.6]]# su - postgres
[[postgres@node1 ~]$ initdb -D /usr/local/pgfile/data/
[postgres@node1 ~]$ chown -R postgres:postgres /usr/local/pgfile
[postgres@node1 ~]$ pg_ctl -D /usr/local/pgfile/data -l /usr/local/pgfile/log/logfile start
6、修改数据库postgres用户密码
[postgres@node1 ~]$ cd /usr/local/pgfile
[postgres@node1 pgfile]$ psql
psql (13.6)
Type "help" for help.
postgres=# ALTER USER postgres WITH PASSWORD 'abc123;
#或者
postgres=# \password postgres；
ALTER ROLE
7、修改配置
postgres=# exit
[postgres@node1 pgfile]$ exit
// 修改配置文件前，先做好备份工作
[root@node1 pgfile]]# cp /usr/local/pgfile/data/pg_hba.conf /usr/local/pgfile/data/pg_hba.conf.bak
[root@node1 pgfile]# vim /usr/local/pgfile/data/pg_hba.conf
//添加host all all 0.0.0.0/0 md5
# replication privilege.
host all all 0.0.0.0/0 md5
[root@node1 pgfile]# cp /usr/local/pgfile/data/postgresql.conf /usr/local/pgfile/data/postgresql.conf.bak
[root@node1 pgfile]# vim /usr/local/pgfile/data/postgresql.conf
//去掉listen_addresses和port注释，将localhost改为*
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directories = '/tmp' # comma-separated list of directories
# (change requires restart)
8、重启数据库服务
[root@node1 pgfile]# su - postgres
[postgres@node1 pgfile]$ pg_ctl -D /usr/local/pgfile/data -l /usr/local/pgfile/log/logfile restart
9、安装数据库插件
9.1安装 uuid-ossp 插件
- 进入/usr/local/pgsql/source/postgresql-13.6/contrib/uuid-ossp/目录进行编译安装。
postgresql13源码包中包含了许多插件，存放于源码包中contrib目录中，不用单独下载，postgresql12则需要单独下载。
[postgres@node1 pgfile]$ exit
// 编译安装uuid-ossp：
[root@node1 pgfile]# cd /usr/local/pgsql/source/postgresql-13.6/contrib/uuid-ossp/
[root@node1 pgfile]# make && make install
[root@node1 pgfile]# su - postgres
[postgres@node1 pgfile]$ psql
Password for user postgres:
psql (13.6)
Type "help" for help.
- 安装 uuid-oss 插件即可
postgres=# create extension IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
9.2安装pg_poathman插件
1、pg_pathman下载地址(建议下载1.5.12版本)
https://github.com/postgrespro/pg_pathman
或者链接：https://pan.baidu.com/s/1lCFNwYhzek4lzdUMj8-l8A
提取码：uw7i
2、将下载好的pg_pathman文件放在/usr/local/pgsql/source下
3、解压文件
[postgres@localhost pgsql]$ cd /usr/local/pgsql/source
[postgres@localhost source]$ unzip 1.5.12.zip
3、设置环境变量
因为在上面已经设置过了环境变量，此处不再设置
4、安装pg_pathman
[postgres@localhost pgsql]$ cd pg_pathman-1.5.12/
[postgres@localhost pg_pathman-1.5.12]$ make USE_PGXS=1
[postgres@localhost pg_pathman-1.5.12]$ make USE_PGXS=1 install
5、修改PostgreSQL配置文件
[postgres@localhost data]$ vim /usr/local/pgfile/data/postgresql.conf
#修改shared_preload_libraries的值
shared_preload_libraries = 'pg_stat_statements,pg_pathman'
如果报错，则将上面参数中的pg_stat_statements删除即可
6、重启数据库
[postgres@localhost data]$ pg_ctl -D /usr/local/pgfile/data -l /usr/local/pgfile/log/logfile restart
7、进入数据库启动插件
[postgres@localhost data]$ psql
postgres=# create extension "pg_pathman";
CREATE EXTENSION
8、查看插件是否生效
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
------------+---------+------------+------------------------------
-------------------
pg_pathman | 1.5 | public | Partitioning tool for Postgre SQL
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
uuid-ossp | 1.1 | public | generate universally unique i
dentifiers (UUIDs)
(3 rows)
10、使用systemctl管理数据库服务
systemctl管理脚本：/usr/lib/systemd/system/pgsql13.service
systemctl管理命令：systemctl {status|restart|start|stop|reload} pgsql13
[root@node1 pgsql]# vim /usr/lib/systemd/system/pgsql13.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. It is recommended to use systemd
# "dropin" feature; i.e. create file with suffix .conf under
# /etc/systemd/system/postgresql-13.service.d directory overriding the
# unit's defaults. You can also use "systemctl edit postgresql-13"
# Look at systemd.unit(5) manual page for more info.
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=PostgreSQL 13 database server
Documentation=https://www.postgresql.org/docs/13/static/
After=syslog.target
After=network.target
[Service]
Type=notify
# 数据库启动属主
User=postgres
# 数据库启动属组
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
# 数据库数据目录
Environment=PGDATA=/usr/local/pgfile/data
# 数据库端口
Environment=PGPORT=5432
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
#OOMScoreAdjust=-1000
#Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
#Environment=PG_OOM_ADJUST_VALUE=0
# start 对应命令
ExecStart=/usr/local/pgsql/bin/postmaster -D ${PGDATA}
#ExecStart=/usr/local/pgsql/bin/pg_ctl -D ${PGDATA} -l logfile start
# stop 对应命令
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
ExecReload=/bin/kill -HUP $MAINPID # reload 对应命令
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutStartSec=0
TimeoutStopSec=1h
[Install]
WantedBy=multi-user.target
[root@node1 pgsql]# systemctl daemon-reload
[root@node1 pgsql]# systemctl enable pgsql13.service
[root@node1 pgsql]# systemctl start pgsql13.service
[root@node1 pgsql]# systemctl status -l pgsql13.service
使用systemctl启动pgsql前，先登陆postgres用户使用pg_ctl -D /usr/local/pgfile/data stop关闭服务，否则会报错
11、修改postgresql日志收集
1、修改配置文件
[root@node1 pgsql]# vim /usr/local/pgfile/data/postgresql.conf
# - Where to Log -
log_destination = 'stderr'
#设置日志收集模式，不开启时默认是stderr
#有效值是stderr、csvlog、syslog和eventlog的组合，具体取决于平台。csvlog要求日志收集器处于打开状态。
# This is used when logging to stderr:
logging_collector = on
#日志收集器是否需要开启
#支持将stderr和csvlog捕获到日志文件中。csvlogs需要开启。
#(更改需要重新启动）
# These are only used if logging_collector is on:
log_directory = '../log'
#写入日志文件的目录可以是绝对目录，也可以是相对于PGDATA的目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
#日志文件名模式，可以包括strftime()转义
log_file_mode = 0600
#日志文件的创建模式，从0开始使用八进制记数法
#默认的权限是0600，表示只有服务器拥有者才能读取或写入日志文件。其他常用的设置是0640，它允许拥有者的组成员读取文件。不过要注意你需要修改log_directory为将文件存储在集簇数据目录之外的某个位置，才能利用这个设置。
log_truncate_on_rotation = on
#如果启用，与新日志文件同名的现有日志文件将被截断，而不是追加。
#但这种截断只发生在时间驱动的旋转上，而不会发生在重新启动或大小驱动的旋转上。默认设置为禁用，表示在所有情况下都附加到现有文件。
log_rotation_age = 1d
#日志文件的自动循环将在该时间之后发生。0表示禁用。
log_rotation_size = 10MB
#在输出那么多日志后，将自动轮换日志文件。
#0表示禁用。
postgresql日志参数的详细内容请参考https://www.jianshu.com/p/407c03aaa600
2、重启服务
[root@node1 pgsql]# systemctl restart pgsql13.service
3、查看配置是否生效
[root@node1 pgsql]# ll /usr/local/pgfile/log
-rw------- 1 postgres postgres 3494 6月 15 12:35 logfile
-rw------- 1 postgres postgres 607 6月 15 14:02 postgresql-2022-06-15_140216.log
