一、安装环境

CentOS 7.6
PostGreSQL 12.1

二、环境准备

2.1 系统初始化
  • 常用软件包安装
  1. rm -rf /etc/yum.repos.d/epel.repo
  2. mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
  3. curl -o /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
  4. yum install epel-release.noarch -y
  5. yum clean all && yum makecache
  6. yum install centos-release-scl openssl-devel gcc-c++.x86_64 net-tools.x86_64 redhat-lsb.x86_64 iptables-services.x86_64 iptables-devel.x86_64 iptables.x86_64 libevent.x86_64 libevent-devel.x86_64 vim-enhanced.x86_64 vim-common.x86_64 -y
  7. yum install glances.noarch telnet.x86_64 wget.x86_64 curl.x86_64 htop.x86_64 iftop.x86_64 lsof.x86_64 gzip.x86_64 unzip.x86_64 zip.x86_64 tree.x86_64 lrzsz.x86_64 nc traceroute.x86_64 bind-utils.x86_64 -y

备注：2021-02-19 编辑 ，添加了 centos-release-scl和 openssl-devel的软件包安装

  • 文件进程及打开数量优化
  1. rm -rf /etc/security/limits.d/*
  2. cat >>/etc/security/limits.conf<<EOF
  3. * soft nproc 65536
  4. * hard nproc 65536
  5. * soft nofile 65536
  6. * hard nofile 65536
  7. EOF
  • 内核参数配置
  1. cat >> /etc/sysctl.conf <<EOF
  2. net.ipv4.tcp_syncookies = 1
  3. net.ipv4.tcp_tw_reuse = 1
  4. net.ipv4.tcp_tw_recycle = 0
  5. net.ipv4.tcp_fin_timeout = 30
  6. net.ipv4.tcp_keepalive_time = 1200
  7. net.ipv4.tcp_keepalive_intvl = 30
  8. net.ipv4.tcp_keepalive_probes = 3
  9. net.ipv4.ip_local_port_range = 1024 65000
  10. net.ipv4.tcp_max_syn_backlog = 8192
  11. net.nf_conntrack_max = 25000000
  12. net.netfilter.nf_conntrack_max = 25000000
  13. net.netfilter.nf_conntrack_tcp_timeout_established = 180
  14. net.netfilter.nf_conntrack_tcp_timeout_time_wait = 120
  15. net.netfilter.nf_conntrack_tcp_timeout_close_wait = 60
  16. net.netfilter.nf_conntrack_tcp_timeout_fin_wait = 120
  17. EOF
  18. /sbin/sysctl -p 
  19. modprobe ip_conntrack
  20. echo  'modprobe ip_conntrack' >>/etc/rc.d/rc.local
  • 关闭防火墙等
    setenforce 0
    sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
    sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/sysconfig/selinux
    /bin/systemctl stop iptables.service
    /bin/systemctl disable iptables.service
    /bin/systemctl stop firewalld.service
    /bin/systemctl disable firewalld.service
    /bin/systemctl daemon-reload

2.2 软件列表及下载地址
yum install -y pgagent_12*
yum install -y postgresql12-server.x86_64 
yum install -y postgresql12-contrib.x86_64         
yum install -y postgresql12-debuginfo.x86_64       
yum install -y postgresql12-devel.x86_64           
yum install -y postgresql12-docs.x86_64                   
yum install -y postgresql12-plpython3.x86_64

三、安装

3.1 yum源仓库添加

根据系统版本选择不同的仓库，可先从浏览器打开https://download.postgresql.org/pub/repos/yum/reporpms，查找和自己系统对应的版本进行安装）

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

查看有哪些可安装的 postgresql包

yum list |grep postgresql12
postgresql12.x86_64                         12.1-2PGDG.rhel7           pgdg12   
postgresql12-contrib.x86_64                 12.1-2PGDG.rhel7           pgdg12   
postgresql12-debuginfo.x86_64               12.1-1PGDG.rhel7           pgdg12   
postgresql12-devel.x86_64                   12.1-2PGDG.rhel7           pgdg12   
postgresql12-docs.x86_64                    12.1-2PGDG.rhel7           pgdg12   
postgresql12-libs.x86_64                    12.1-2PGDG.rhel7           pgdg12   
postgresql12-llvmjit.x86_64                 12.1-2PGDG.rhel7           pgdg12   
postgresql12-odbc.x86_64                    12.00.0000-1PGDG.rhel7     pgdg12   
postgresql12-plperl.x86_64                  12.1-2PGDG.rhel7           pgdg12   
postgresql12-plpython.x86_64                12.1-2PGDG.rhel7           pgdg12   
postgresql12-plpython3.x86_64               12.1-2PGDG.rhel7           pgdg12   
postgresql12-pltcl.x86_64                   12.1-2PGDG.rhel7           pgdg12   
postgresql12-server.x86_64                  12.1-2PGDG.rhel7           pgdg12   
postgresql12-test.x86_64                    12.1-2PGDG.rhel7           pgdg12

3.2 安装llvmjit 提升性能
yum install postgresql12-llvmjit.x86_64

安装报错及解决

[root@021rjsh00151s ~]# yum install yum install postgresql12-llvmjit.x86_64 
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
Package yum-3.4.3-163.el7.centos.noarch already installed and latest version
No package install available.
Resolving Dependencies
--> Running transaction check
---> Package postgresql12-llvmjit.x86_64 0:12.1-2PGDG.rhel7 will be installed
--> Processing Dependency: llvm5.0 >= 5.0 for package: postgresql12-llvmjit-12.1-2PGDG.rhel7.x86_64
--> Processing Dependency: libLLVM-5.0.so()(64bit) for package: postgresql12-llvmjit-12.1-2PGDG.rhel7.x86_64
--> Finished Dependency Resolution
Error: Package: postgresql12-llvmjit-12.1-2PGDG.rhel7.x86_64 (pgdg12)
           Requires: llvm5.0 >= 5.0
Error: Package: postgresql12-llvmjit-12.1-2PGDG.rhel7.x86_64 (pgdg12)
           Requires: libLLVM-5.0.so()(64bit)
 You could try using --skip-broken to work around the problem

2021-02-19 日批注：
今天在部署pg13的时候，需要安装一个orafce扩展，来兼容oracle的部分语法。遇到一个问题

Makefile:29: /usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk: No such file or directory
make: *** No rule to make target `/usr/pgsql-13/lib/pgxs/src/makefiles/pgxs.mk'.  Stop.

该问题是因为没有安装postgresql13-devel.x86_64 导致的 ，但是在安装postgresql13-devel.x86_64时又遇到一个问题

Error: Package: postgresql13-devel-13.2-1PGDG.rhel7.x86_64 (pgdg13)
           Requires: llvm-toolset-7-clang >= 4.0.1
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

根据提示安装llvm-toolset-7-clang ，提示未找到该软件包。需要安装 centos-relase-scl

yum install centos-release-scl

安装好之后可继续之前的操作，安装dev、orafce，亦可以解决llvm的问题

解决办法，安装依赖包，但是还是报错，最终以—skip-broken 方式跳过报错

yum install centos-release-scl后 可以安装下面llvm
yum install llvm-toolset-7-llvm-libs.x86_64
yum install postgresql12-llvmjit.x86_64   --skip-broken

3.3 安装server、client及其他模块

安装 postgresql12-server.x86_64 会自动安装 postgresql12.x86_64 和postgresql12-libs.x86_64

yum install -y postgresql12-server.x86_64 
yum install -y postgresql12-contrib.x86_64         
yum install -y postgresql12-debuginfo.x86_64       
yum install -y postgresql12-devel.x86_64           
yum install -y postgresql12-docs.x86_64                   
yum install -y postgresql12-plpython3.x86_64

四、安装后配置

4.1 数据库初始化及启动配置

记得加checksum
关于checksum的理解，可参考 https://yq.aliyun.com/articles/675942

/usr/pgsql-12/bin/postgresql-12-setup initdb
systemctl enable postgresql-12
systemctl start postgresql-12

4.2 基本操作

创建用户
create user test with password '123456';
创建数据库
CREATE DATABASE test OWNER test;
连接数据库
psql -Utest -h localhost

4.3 参数配置

附件为参数的配置案例，参数解释请参考另外一篇
[配置文件详解]

  • 日志目录
  • 连接配置
  • 性能监控

pg_hba.txtpostgresql.txt