PGSQL installation v0.1

1.关闭操作系统防火墙及安全沙盒
service iptables stop
chkconfig iptables off
vi /etc/selinux/config
将enable改为disabled

2.确认gun版本
[root@GDVUCLSDB ~]# make --version
GNU Make 3.82
Built for x86_64-redhat-linux-gnu
Copyright © 2010 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later http://gnu.org/licenses/gpl.html
This is free software: you are free to change and re
distribute it.
There is NO WARRANTY, to the extent permitted by law.

3. mount 7.4
   [root@GDVUCLSDB ~]# mkdir -p /mnt/rhel
   [root@GDVUCLSDB ~]# mount -o loop -t iso9660 /tmp/rhel-server-7.4-update-4-x86_64-dvd.iso /mnt/rhel/
   mount: /dev/loop0 is write-protected, mounting read-only
   [root@GDVUCLSDB ~]# vi /etc/yum.repos.d/rhel-source.repo
   [root@GDVUCLSDB ~]#

[rhel-local]
name=rhel-local
baseurl=file:///mnt/rhel/
enabled=1
gpgcheck=0

[root@GDVUCLSDB ~]# yum install -y readline*
[root@GDVUCLSDB ~]# yum install -y zlib*
[root@GDVUCLSDB ~]# yum install -y readline.i686
[root@GDVUCLSDB ~]# yum install -y readline-devel.i686

4 编译器要求：
推荐使用GCC编译器，我们可以通过yum源安装GCC：
[root@GDVUCLSDB ~]# yum install -y gcc*
5 可选Perl/Python/tcl要求：
需要完整的perl安装，包括libperl 库和头文件。
[root@GDVUCLSDB ~]# yum install -y perl*
Python需要最低版本为2.3
[root@GDVUCLSDB ~]# python -v

tcl需要最低版本为8.5
[root@GDVUCLSDB~]# yum install -y tcl*
安装python相关库文件
[root@GDVUCLSDB~]# yum install python*

6.确认空间大小
[root@GDVUCLSDB~]#df -h

7.创建组和postgres
[root@GDVUCLSDB ~]# groupadd pginstall
[root@GDVUCLSDB ~]# groupadd dba
[root@GDVUCLSDB ~]# useradd -g pginstall -G dba postgres
[root@GDVUCLSDB ~]# passwd postgres
welcome01

8. 修改参数
   操作系统参数修改

vi /etc/security/limits.conf

End of file

• soft nofile 1024000
• hard nofile 1024000
• soft nproc 1024000
• hard nproc 1024000

9. 修改系统配置参数

vi /etc/security/limits.d/20-nproc.conf

#加大普通用户限制也可以改为unlimited

• soft nproc 1024000
• hard nproc 1024000
  root soft nproc unlimited

10. 修改系统配置参数

vi /etc/sysctl.conf

#关闭ipv6
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1

避免放大攻击

net.ipv4.icmp_echo_ignore_broadcasts = 1

开启恶意icmp错误消息保护

net.ipv4.icmp_ignore_bogus_error_responses = 1

#关闭路由转发
net.ipv4.ip_forward = 0
net.ipv4.conf.all.send_redirects = 0
net.ipv4.conf.default.send_redirects = 0

#开启反向路径过滤
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.default.rp_filter = 1

#处理无源路由的包
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0

#关闭sysrq功能
kernel.sysrq = 0

#core文件名中添加pid作为扩展名
kernel.core_uses_pid = 1

开启SYN洪水攻击保护

net.ipv4.tcp_syncookies = 1

#修改消息队列长度
kernel.msgmnb = 65536
kernel.msgmax = 65536

#设置最大内存共享段大小bytes
kernel.shmmax = 68719476736 # half system mem
kernel.shmall = 4294967296 # kernel.shmmax/getconf PAGE_SIZE

FS特性

fs.aio-max-nr = 1048576
fs.file-max = 76724600

#timewait的数量，默认180000
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.tcp_sack = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

#每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
net.core.netdev_max_backlog = 262144

#限制仅仅是为了防止简单的DoS 攻击
net.ipv4.tcp_max_orphans = 3276800

#未收到客户端确认信息的连接请求的最大值
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_timestamps = 0

#内核放弃建立连接之前发送SYNACK 包的数量
net.ipv4.tcp_synack_retries = 1

#内核放弃建立连接之前发送SYN 包的数量
net.ipv4.tcp_syn_retries = 1

#启用timewait 快速回收
net.ipv4.tcp_tw_recycle = 1

#开启重用。允许将TIME-WAIT sockets 重新用于新的TCP 连接
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 30

#当keepalive 起用的时候，TCP 发送keepalive 消息的频度。缺省是2 小时
net.ipv4.tcp_keepalive_time = 30

#允许系统打开的端口范围
net.ipv4.ip_local_port_range = 1024 65500

#修改防火墙表大小，默认65536

net.netfilter.nf_conntrack_max=655350

net.netfilter.nf_conntrack_tcp_timeout_established=1200

确保无人能修改路由表

net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.secure_redirects = 0

10. 创建postgres的profile
    chown postgres:pginstall /u01
    chown postgres:pginstall /shared_data
    chown postgres:pginstall /shared_fra

su - postgres
vi .bash_profile

export LD_LIBRARY_PATH=/u01/pgsql/11.7/lib
export MANPATH=/u01/pgsql/11.7/share/man:$MANPATH export PATH=$PATH:$HOME/.local/bin:$HOME/bin:/u01/pgsql/11.7/bin

cd /u01/
cp /tmp/postgresql-11.7.tar.gz ./
tar -xvzf postgresql-11.7.tar.gz

11.完成配置
./postgresql-11.7/configure --prefix=/u01/pgsql --with-python --with-perl

12.进行源码编译：
编译时间根据硬件而异，时长大概5-30分钟
nohup make &
cat nohup.out 看到以
make[1]: Entering directory /u01/config' make[1]: Nothing to be done for all’.
make[1]: Leaving directory /u01/config' All of PostgreSQL successfully made. Ready to install. nohup make world & cat nohup.out 看到以下内容表示编译成功 make[2]: Leaving directory /u01/contrib/ltree_plpython’
make[1]: Leaving directory `/u01/contrib’
PostgreSQL, contrib, and documentation successfully made. Ready to install.

13.安装部署
[postgres@GDVUCLSDB u01]$ make install
[postgres@GDVUCLSDB u01]$ make install-docs
[postgres@GDVUCLSDB u01]$ make install-world

14.初始化数据库：
创建pgsql的数据文件存放目录（可以使用专门的文件系统空间）/shared_data
initdb --pgdata=/shared_data/data --no-locale --encoding UTF-8 --lc-collate C --lc-ctype C

CLS应用给出的文档中
#sudo -u postgres /usr/pgsql-11/bin/initdb --pgdata=/var/lib/pgsql/11/data --no-locale --encoding UTF-8 --lc-collateC --lc-ctype C

./pg_ctl -D /shared_data/data -l logfile start