PostgreSQL入门实践：PG 15.3版本编译安装

一、软件信息

操作系统rhel-server-7.6-x86_64-dvd.iso

数据库版本包postgresql-15.3.tar.gz

Python version3.0以上

二、操作系统设置篇

1、固定IP地址设置

vi etc/sysconfig/network-scripts/ifcfg-enp0s3
BOOTPROTO=static 
DEVICE= enp0s3
ONBOOT= yes 
IPADDR=192.168.70.15
NETMASK=255.255.255.0 
GATEWAY=192.168.70.254
重新加载网卡
systemctl restart network

2、挂载驱动（YUM源）

mount dev/cdrom mnt 

ll mnt

vi etc/yum.repos.d/cdrom.repo 
[rhel7iso]
name=rhel7iso
baseurl=file:///mnt/
gpgcheck=0
enabled=1

3、关闭SELinux

setenforce 0
sed -i "s/^SELINUX \\=enforcing/SELINUX \\=disabled/g" etc/selinux/config

4、关闭防火墙

systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service

5、关闭 NetworkManager

systemctl status NetworkManager
systemctl stop NetworkManager
systemctl disable NetworkManager

6、hosts文件配置

vi etc/hosts
192.168.70.15 wypg15

7、配置内核参数

vim etc/sysctl.conf 
kernel.sem= 4096 2147483647 2147483646 512000
fs.file-max=7672460
fs.aio-max-nr=1048576
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=4194304
net.ipv4.ip_local_port_range=9000 65500
net.ipv4.tcp_wmem=8192 65536 16777216
net.ipv4.tcp_rmem=8192 87380 16777216
vm.min_free_kbytes=512000
vm.vfs_cache_pressure=200
vm.swappiness=10
net.ipv4.tcp_max_syn_backlog=4096
net.core.somaxconn=4096
sysctl -p

8、配置资源限制

vi etc/security/limits.conf
postgres soft nofile 655360
postgres hard nofile 655360
postgres soft nproc 655360
postgres hard nproc 655360
postgres soft core unlimited
postgres hard core unlimited
postgres soft memlock 50000000
postgres hard memlock 50000000

9、新增磁盘

mkdir pgdata
fdisk -l
mkfs.xfs dev/sdb

vi etc/fstab
/dev/sdb pgdata xfs noatime,defaults 0 0

mount -onoatime,defaults dev/sdb pgdata

10、升级安装Python

cd opt
tar -zxvf Python-3.7.0.tgz
cd Python-3.7.0/
./configure --with-ssl
make 
make install
错误信息：ModuleNotFoundError: No module named '_ctypes'

解决方法：
yum install libffi-devel -y
yum install zlib*
make 
make install

预期结果：
Successfully installed pip-10.0.1 setuptools-39.0.1

更改默认的python版本

1、检查当前默认的python2
[root@pg15 Python-3.7.0]# ll usr/bin/python*
lrwxrwxrwx. 1 root root 7 11月 22 14:51 usr/bin/python -> python2
lrwxrwxrwx. 1 root root 9 11月 22 14:51 usr/bin/python2 -> python2.7
-rwxr-xr-x. 1 root root 7216 9月 12 2018 usr/bin/python2.7
-rwxr-xr-x. 1 root root 1835 9月 12 2018 usr/bin/python2.7-config
lrwxrwxrwx. 1 root root 16 11月 22 15:37 usr/bin/python2-config -> python2.7-conf        ig
lrwxrwxrwx. 1 root root 14 11月 22 15:37 usr/bin/python-config -> python2-config


2、检查新安装的python3
[root@pg15 Python-3.7.0]# ll usr/local/bin/python*
lrwxrwxrwx. 1 root root   9 11月 22 15:58 usr/local/bin/python3 -> python3.7
-rwxr-xr-x. 2 root root 13966008 11月 22 15:58 usr/local/bin/python3.7
lrwxrwxrwx. 1 root root   17 11月 22 15:58 usr/local/bin/python3.7-config -> python3.7m-config
-rwxr-xr-x. 2 root root 13966008 11月 22 15:58 usr/local/bin/python3.7m
-rwxr-xr-x. 1 root root  3097 11月 22 15:58 usr/local/bin/python3.7m-config
lrwxrwxrwx. 1 root root   16 11月 22 15:58 usr/local/bin/python3-config -> python3.7-config

3、修改默认的python
cd usr/bin/
rm -f python2
mv python python2.6.ori
ln -s python2.7 python2
ln -s usr/local/bin/python3 usr/bin/python

4、检查修改后的python版本
 python -V
 显示为：
Python 3.7.0

二、数据库设置篇

1、创建postgres用户

groupadd postgres
useradd -g postgres postgres
echo "postgres" |passwd --stdin postgres

2、创建数据库目录

mkdir -p postgresql/pg15 pgdata/data backup arch

chmod -R 775 postgresql
chown -R postgres:postgres postgresql

chmod -R 775 backup
chown -R postgres:postgres backup

chmod -R 775 pgdata
chown -R postgres:postgres pgdata

chmod -R 775 arch
chown -R postgres:postgres arch

3、数据库安装包解压

 rpm -ivh libzstd-1.5.2-1.el7.x86_64.rpm
官网下载地址：https://www.postgresql.org/ftp/source/v15.3/
cd postgresql/pg15/
tar -zxvf postgresql-15.3.tar.gz
cd postgresql-15.3/
chmod +x configure

4、依赖包安装

yum install -y openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel python-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel gettext gettext-devel bison flex uuid-devel gcc gcc-c++ 
安装过程中报错：File "/usr/bin/yum", line 30
 except KeyboardInterrupt, e: ^
SyntaxError: invalid syntax
处理方法(修改为python2)：
vim usr/bin/yum
#!/usr/bin/python2
vim usr/libexec/urlgrabber-ext-down
#! usr/bin/python2

5、编译安装

./configure --prefix=/postgresql/pg15 --with-pgport=6543 --with-openssl --with-perl --with-blocksize=16 
make world 
make install-world

6、验证安装是否成功

cd postgresql/pg15
ll
总用量 27164
drwxr-xr-x. 2 root root  4096 11月 21 18:41 bin
drwxr-xr-x. 6 root root  4096 11月 21 18:41 include
drwxr-xr-x. 4 root root  4096 11月 21 18:41 lib
drwxr-xr-x. 8 root root  4096 11月 21 18:41 share

7、更改软件目录和数据目录所属用户和组

chown -R postgres. postgresql/pg15 pgdata backup /arch

8、环境变量设置

su - postgres

vi .bashrc
if [ -f /etc/bashrc ]; then
 . /etc/bashrc
fi
export PATH=/postgresql/pg15/bin/:$PATH
export PGDATA=/pgdata/data
export PGDATABASE=postgres
export PGUSER=postgres
export PGPORT=6543
export PGLOCALEDIR=/postgresql/pg15/share/locale
alias pg_ctl='LD_LIBRARY_PATH=/postgresql/pg15/lib pg_ctl'
source ~/.bashrc

9、初始化数据库实例

su - postgres
initdb -D /pgdata/data -E UTF8 --locale=zh_CN.utf8

10、配置 pg_hba.conf

su - postgres
vi $PGDATA/pg_hba.conf
host replication  postgres   192.168.70.0/24    trust
host repmgr    postgres   192.168.70.0/24    trust
host all      all      all          md5

11、配置 postgresql.conf

su - postgres
mv $PGDATA/postgresql.conf $PGDATA/postgresql.conf_bf
vi $PGDATA/postgresql.conf
listen_addresses = '*'
port = 6543
max_connections = 4000
shared_preload_libraries = 'pg_stat_statements,passwordcheck'
wal_level = replica
wal_log_hints = on
wal_buffers = 16MB
archive_mode = on
archive_command = 'cp %p /arch/%f'
max_wal_senders = 10
hot_standby = on
log_destination = 'csvlog'
logging_collector = on
log_connections = on
log_disconnections = on
log_error_verbosity = verbose 
log_lock_waits = on
autovacuum = on
shared_buffers = 500MB
work_mem = 50MB
maintenance_work_mem = 100MB
effective_cache_size = 2GB
#wal_keep_segments = 1000
注意15版本里 wal_keep_segments已不存在 新增wal_keep_size和max_slot_wal_keep_size

12、启动数据库

 su - postgres
 pg_ctl start
 psql登录