PostgreSQl 安装-二进制-centos7.X

系统参数优化

防火墙与selinux

systemctl disable --now firewalld
sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

内核参数

cat >> /etc/sysctl.conf <<EOF
# 第一列，表示每个信号集中的最大信号量数目。
# 第二列，表示系统范围内的最大信号量总数目。
# 第三列，表示每个信号发生时的最大系统操作数目。
# 第四列，表示系统范围内的最大信号集总数目。
kernel.sem = 50100 64128000 50100 1280

# 表示系统级别的能够打开的文件句柄①的数量。是对整个系统的限制，并不是针对用户的。
fs.file-max = 7672460
# 对外连接的端口范围
net.ipv4.ip_local_port_range = 10000 65000
# 接收缓冲区（Receive Buffer）的默认大小（字节）
net.core.rmem_default = 1048576
# 接收缓冲区（Receive Buffer）的最大大小（字节）
net.core.rmem_max = 4194304
# 发送缓冲区（Send Buffer）的默认大小（字节）
net.core.wmem_default = 262144
# 发送缓冲区（Send Buffer）的最大（字节）
net.core.wmem_max = 1048576
EOF

cat >> /etc/security/limits.conf <<EOF
* - nofile 100000
* - nproc 100000
* - memlock 60000
EOF

reboot

编译安装

依赖包安装：

yum install -y gcc make readline-devel zlib-devel libicu-devel

源码下载：

cd /usr/local/src/ && wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz --no-check-certificate
tar xf postgresql-16.2.tar.gz && cd postgresql-16.2
# 查看官方安装说明,用于参考
grep -A 20 'Short Version' INSTALL
Short Version

    ./configure
    make
    su
    make install
    adduser postgres
    mkdir -p /usr/local/pgsql/data
    chown postgres /usr/local/pgsql/data
    su - postgres
    /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
    /usr/local/pgsql/bin/createdb test
    /usr/local/pgsql/bin/psql test

The long version is the rest of this document.

------------------------------------------------------------------------

Installation Procedure

编译安装：

# 默认端口就是5432
./configure --prefix=/usr/local/pgsql --with-pgport=5432

# 可以查看cpu核心数，使用多核进行过编译加快速度，默认是单核编译，同时完整编译，包含man帮助等。
make -j 4 world
# 安装时，world方式安装
make install-world

初始化

创建用户和组

PostgreSQL默认不支持以root 身份启动服务,虽然也可修改源码实现root启动,但基于安全考虑不建议，因此必须创建一个用于启动PostgrepSQL的普通用户

useradd -s /bin/bash -m -d /home/postgres postgres
# 设置密码
echo postgres:123456 | chpasswd

目录相关配置

mkdir -pv /data/pgsql
# 此方式会同时配置所属组和所属用户
chown postgres.postgres /data/pgsql

环境变量配置

方便执行命令等情况

cat > /etc/profile.d/postgres.sh <<EOF
export PGHOME=/usr/local/pgsql
export PATH=\$PGHOME/bin/:\$PATH
export PGDATA=/data/pgsql
export PGUSER=postgres
export MANPATH=/usr/local/pgsql/share/man:\$MANPATH
EOF

# 验证
su - postgres

pg_ctl --version
pg_ctl (PostgreSQL) 16.2

初始化数据库

su - postgres

# 通过 -D 指定数据目录，不指定情况下会使用环境变量$PGDATA进行初始化，前期已配置环境变量，所以可不指定
# 生产环境建议初始化方式：
# initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W
# -A #指定local connections默认的身份验证方法
# -D #指定数据目录
# -E #指定字符集
# --locale=C #指定语言环境
# -U  #指定数据库superuser用户名
# -W  #指定数据库superuser的用户密码

[postgres@zx ~]$ initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W
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 "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: 
Enter it again: 

fixing permissions on existing directory /data/pgsql ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    pg_ctl -D /data/pgsql -l logfile start

服务管理

启动和关闭服务

# 启动 start方式常用参数：
# -w #等待启动完成
# -t #等待启动完成的等待秒数，默认为60秒
# -s #只打印错误和警告信息,不打印提示性信息
# -D datadir#指定数据库实例的数据目录
# -l #服务器日志输出附加在“filename”文件上，如果该文件不存在则创建它
# -o options #声明要直接传递给postgres 的选项，具体可见postgres命令的帮助
# -p path #指定postgres可执行文件的位置。默认情况下postgres可执行文件来自和pg_ctl相同的目录，不必使用该选项。除非要进行一些不同寻常的操作，或者产生了postgres执行文件找不到的错误
# -c #提高服务器的软限制(ulimit -c)，尝试允许数据库实例在有异常时产生一个coredump文件,以便于问题定位和故障分析

pg_ctl -D /data/pgsql -l logfile start
postgres -D /data/pgsql &



# 停止数据库
pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
其中-m是指定数据库的停止方法，有以下三种:
smart:等所有的连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。
fast :快速关闭数据库，断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。相当于Oracle数据库关闭时的immediate模式。此为默认值,建议使用
immediate:立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的 abort模式
使用方式：
#smart关闭
pg_ctl stop -D $PGDATA -ms
#fast关闭,推荐使用,也是默认模式
pg_ctl stop -D $PGDATA -mf
#immediate 相当于kill -9
pg_ctl stop -D $PGDATA -mi

#或者发送信号,直接向数据库主进程发送的signal 信号有以下三种。
SIGTERM:发送此信号为Smart Shutdown关机模式。
SIGINT:发送此信号为Fast Shutdown关机模式。
SIGQUIT:发送此信号为Immediate Shutdown关机模式。


# 重启
pg_ctl restart -mf


# 源码目录中内置PostgreSQL的启动脚本，可以参考修改 chkconfig配置
# /usr/local/src/postgresql-16.2/contrib/start-scripts/linux
cp /usr/local/src/postgresql-16.2/contrib/start-scripts/linux /etc/init.d/postgresql
chmod u+x /etc/init.d/postgresql
# 修改数据目录，启动重启命令
chkconfig --add postgresql
chkconfig postgresql on

/etc/init.d/postgresql start

service方式配置

测试未通过

cat > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
User=postgres
Group=postgres
ExecStart=/usr/local/pgsql/bin/pg_ctl -D /data/pgsql
ExecReload=/bin/kill -HUP
[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload && systemctl enable --now postgresql

远程连接配置

访问控制配置文件介绍

在PostgreSQL中，带有一个网络防火墙的功能的文件pg_hba.conf,可以控制允许设置哪些IP的机器访问
数据库服务器。
HBA的意思是host-based authentication，也就是基于主机的认证,即实现PostgreSQL 防火墙功能initdb初始化数据目录时，会生成一个默认的pg_hba.conf文件。
pg_hba.conf 文件的格式由很多记录组成，每条记录占一行。
以#开头的行为注释及空白行会被忽略。
一条记录由若干个空格或由制表符分隔的字段组成，如果字段用引号包围，那么它可以包含空白。
每条记录声明一种连接类型、一个客户端IP地址范围(如果和连接类型相关)、一个数据库名、一个用户名字，以及对匹配这些参数的连接所使用的认证方法。
第一条匹配连接类型、客户端地址、连接请求的数据库名和用户名的记录将用于执行认证。如果选择了一条记录而且认证失败，那么将不再考虑后面的记录;如果没有匹配的记录,访问将被拒绝。即从上向下匹配，一旦匹配则不会再向下检查

每条记录可以是下面七种格式之一：

1) local <dbname> <user> <auth-method>[auth-options]
2) host <dbname> <user> <ip/masklen> <auth-method>auth-options]
3) hostssl <dbname><user> <ip/masklen> <auth-method>[auth-options]
4) hostnossl <dbname> <user> <ip/masklen> <auth-method>[auth-options]
5) host <dbname> <user> <ip><mask> <auth-method> [auth-options]
6) hostssl <dbname> <user> <ip> <mask> <auth-method>[ auth-options]
7) hostnossl <dbname><user><ip><mask> <auth-method>[auth-options]

pg_hba.conf文件为pg实例的防火墙配置文件。配置文件格式分为5部分:

TYPE  DATABASE     USER      ADDRESS         METHOD

详解如下：

TYPE字段取值范围如下：
- local ：这条记录匹配通过UNIX域套接字的连接认证。没有这种类型的记录，就不允许有UNIX域套接字的连接。当 psql后面不指定主机名或IP地址时，即用UNIX域套接字的方式连接数据库。
- host：这条记录匹配通过TCP/IP进行的连接。包括了SSL和非SS的连接。
- hostssl：这条记录匹配使用TCP/IP的SSL 连接。必须是使用SSL加密的连接，且要使用这个选项,编译服务器时必须打开SSL支持，启动服务器时必须打开SSL配置选项。
- hostnossl：这条记录与hostssI相反，它只匹配那些在TCP/IP上不使用SSL 的连接请求。
DATABASE字段用于设置一个数据库名称，如果设置为all，表示可以匹配任何数据库, 注意:如果设置为replication时比较特殊,表示允许流复制连接，而不是允许连接到一个名为“replication”的数据库上。
USER字段用于设置一个用户的名称，如果设置为all，表示可以匹配任何用户。
ADDRESS：<ip/masklen>表示允许哪些IP地址来访问此服务器，如192.168.1.10/32表示只允许192.168.1.10这台主机访问数据库,192.168.1.0/24表示IP地址前缀为192.168.1.X的主机都允许访问数据库服务器。如要设置无访问地址限制，可配置为0.0.0.0/0
METHOD字段表示验证方法，PostgreSQL支持的认证配置方式很多，最常用的认证方法是trust、reject、md5和ident方法。

#METHOD有如下值可选
md5：执行SCRAM-SHA-256或MD5身份验证加密密码来验证,是推荐使用的安全验证的方法
peer：从操作系统获取客户端的操作系统用户名，并检查它是否与请求的数据库用户名匹配。这仅适用于本地
socket连接。
trust：允许无条件连接，允许任何PostgreSQL用户身份登录，而无需密码或任何其他身份验证。
reject：拒绝任何条件连接，这对于从组中“过滤掉”某些主机非常有用。
scram-sha-256：执行SCRAM-SHA-256身份验证以验证用户的密码。
password：要提供未加密的密码以进行身份验证。由于密码是通过网络以明文形式发送的，因此不建议使用
gss：使用GSSAPI对用户进行身份验证，这仅适用于TCP / IP连接。
sspi：使用SSPI对用户进行身份验证，这仅适用于Windows。
ident：允许客户端上的特定操作系统用户连接到数据库。这种认证方式的使用场景是，客户端是主机上的某
个操作系统用户，已经通过了操作系统的身份认证，是数据库服务器可以信任的用户，不需要在数据库层面再
次检测身份。比如，如果配置了这种认证方式(配置中允许的用户名为dba)、这时在操作系统用户dba下，就
能以数据库用户dba 的身份连接到数据库。服务器为了确定接收到的连接请求确实是客户端机器上的dba用户
发起的，而不是这台机器上其他用户发起的假冒请求，会向客户端机器上的ident服务发起请求，让 ident服
务查看此TCP连接是否是dba 用户发起的，如果不是，说明是假冒，则认证失败。如果客户端通过本地连接到
服务器，因为客户端与服务器在一台机器上，数据库服务器可以直接检查客户端用户的操作系统用户身份，就
不需要向ident服务发送请求进行判断了。
ldap：使用LDAP服务器进行身份验证。
radius：使用RADIUS服务器进行身份验证。
cert：使用SSL客户端证书进行身份验证。
pam：使用操作系统提供的可插入身份验证模块（PAM）服务进行身份验证。
bsd：使用操作系统提供的BSD身份验证服务进行身份验证。

配置示例

#如果一台机器只给数据库使用，而没有其他用途，则可以在pg_hba.conf 中加上下面一行配置:
local all all trust
#该配置表示在这台机器上，任何操作系统的用户都可以使用任何数据库用户（包括数据库超级用户）连接到数据库而不需要任何密码。因为这台主机只供数据库使用，可以把不用的操作系统用户都禁止掉,以保证安全性。
#如果数据库中有一个用户“dba”，操作系统中也有一个用户“dba”
#在操作系统"dba”用户下连接数据库不需要密码验证的设置方法:
local all dba ident
#如果想在数据库主机上使用密码验证，可以使用下面的配置项:
local all all md5
#如果想让其他主机的连接都使用md5密码验证，则使用如下配置:
host all all 0.0.0.0/0 md5
#允许用户通过10.0.0.0/24的远程主机进行md5验证登录
#TYPE DATABASE USER ADDRESS METHOD
host all    all 10.0.0.0/24 md5
#允许用户wang通过任意远程主机进行md5验证登录test数据库
#TYPE DATABASE USER ADDRESS METHOD
host test wang   0.0.0.0/0 md5

开启远程访问

修改数据库账号密码，本文中初始化时已配置，此步骤可省略，如初始化时未配置，可通过如下命令进行设置。

ALTER USER postgres with password '123456'
修改监听地址和端口

sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '0.0.0.0'/" /data/pgsql/postgresql.conf
修改用户防火墙配置

sed -i -r '/^host[ ]+all/ s@127.0.0.1/32@0.0.0.0/0@g' /data/pgsql/pg_hba.conf
重启服务

/etc/init.d/postgresql restart