[转帖]源码安装postgresql13+uuid-ossp+pg_pathman

源码,安装,postgresql13,uuid,ossp,pg,pathman · 浏览次数 : 0

小编点评

**Service Configuration** ``` [Type=notify] Type=notify Location of database directory = /usr/local/pgfile/data Database port = 5432 StandardOutput = syslog Disable OOM kill on the postmaster = True StandardOutput = syslog ExecStart = /usr/local/pgsql/bin/postmaster -D ${PGDATA} ExecStop = /usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fastExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s ExecReload = /bin/kill -HUP $MAINPID ```

正文

https://www.jianshu.com/p/5331ad46861a

系统版本：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