Linux下PG环境搭建

安装Linux

本实验以CentOS6以及PostgreSQL 13为例

[root@mysql postgres]# cat /etc/redhat-release 
CentOS release 6.10 (Final)
root@mysql postgres]# arch
x86_64
[root@mysql postgres]#

配置Linux

• 网络配置（静态ip配置）

vi /etc/sysconfig/network-scripts/ifcfg-eth0

BOOTPROTO=static  #启用静态ip地址
ONBOOT=yes             #开启自动启用网络连接
IPADDR=192.168.177.100  #设置ip地址
NETMASK=255.255.255.0 #设置子网掩码
GATEWAY=192.168.177.2   #设置网关（注意如果是虚拟机安装，此处的网关应该去vmware 中查看；
如果是物理机安装，则此处网关为实际物理机网关）

DNS1=114.114.114.114        #设置主DNS
DNS2=8.8.8.8     #设置备DNS
IPV6INIT=no            #禁用ipv6

:x   #保存退出

• yum源配置

# 添加华为云 yum 源
cp -a /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.bak
sed -i "s/#baseurl/baseurl/g" /etc/yum.repos.d/CentOS-Base.repo
sed -i "s/mirrorlist=http/#mirrorlist=http/g" /etc/yum.repos.d/CentOS-Base.repo
sed -i "s@http://mirror.centos.org@https://mirrors.huaweicloud.com@g" /etc/yum.repos.d/CentOS-Base.repo
yum clean all  # 清除原有yum缓存。
yum makecache  # 刷新缓存

• vim /etc/sysctl.conf

追加到文件末尾  
  
kernel.shmall = 4294967296  
kernel.shmmax=135497418752  
kernel.shmmni = 4096  
kernel.sem = 50100 64128000 50100 1280  
fs.file-max = 7672460  
fs.aio-max-nr = 1048576  
net.ipv4.ip_local_port_range = 9000 65000  
net.core.rmem_default = 262144  
net.core.rmem_max = 4194304  
net.core.wmem_default = 262144  
net.core.wmem_max = 4194304  
net.ipv4.tcp_max_syn_backlog = 4096  
net.core.netdev_max_backlog = 10000  
net.ipv4.netfilter.ip_conntrack_max = 655360  
net.ipv4.tcp_timestamps = 0  
net.ipv4.tcp_tw_recycle=1  
net.ipv4.tcp_timestamps=1  
net.ipv4.tcp_keepalive_time = 72   
net.ipv4.tcp_keepalive_probes = 9   
net.ipv4.tcp_keepalive_intvl = 7  
vm.zone_reclaim_mode=0  
vm.dirty_background_bytes = 40960000  
vm.dirty_ratio = 80  
vm.dirty_expire_centisecs = 6000  
vm.dirty_writeback_centisecs = 50  
vm.swappiness=0  
vm.overcommit_memory = 0  
vm.overcommit_ratio =

立即生效

sysctl -p

• vim /etc/security/limits.conf

* soft    nofile  131072  
* hard    nofile  131072  
* soft    nproc   131072  
* hard    nproc   131072  
* soft    core    unlimited  
* hard    core    unlimited  
* soft    memlock 500000000  
* hard    memlock 500000000

• /etc/security/limits.d/*

rm

• 关闭selinux

• vim /etc/sysconfig/selinux

SELINUX=disabled  
SELINUXTYPE=targeted

• setenforce 0

• 关闭防火墙

iptables -F
service

• 重启

reboot

安装PG前准备工作

• 安装依赖包

root用户下，使用yum 安装依赖包  
  
yum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-Ext  
Utils* openldap-devel jadetex  openjade bzip2

• 创建用户 postgres

useradd

• 下载PG源码包

mkdir -p /tools && cd /tools && wget

• 解压PG源码包

tar zxvf postgres*gz
chown

编译安装PG

• 编译PG源码包

[root@mysql tools]# su - postgres
[postgres@mysql ~]$ cd /tools/
[postgres@mysql tools]$ ls
axel-2.17.8  axel-2.17.8.tar.gz  mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz  postgresql-13beta1  postgresql-13beta1.tar.gz
[postgres@mysql tools]$ cd postgresql-13beta1
[postgres@mysql postgresql-13beta1]$ ./configure --prefix=/home/postgres/pg13 && make world -j 8  && make

• 编译完成

/bin/mkdir -p '/home/postgres/pg13/share/extension'
/usr/bin/install -c -m 755  tsm_system_time.so '/home/postgres/pg13/lib/tsm_system_time.so'
/usr/bin/install -c -m 644 ./tsm_system_time.control '/home/postgres/pg13/share/extension/'
/usr/bin/install -c -m 644 ./tsm_system_time--1.0.sql  '/home/postgres/pg13/share/extension/'
make[2]: Leaving directory `/tools/postgresql-13beta1/contrib/tsm_system_time'
make -C unaccent install
make[2]: Entering directory `/tools/postgresql-13beta1/contrib/unaccent'
/bin/mkdir -p '/home/postgres/pg13/lib'
/bin/mkdir -p '/home/postgres/pg13/share/extension'
/bin/mkdir -p '/home/postgres/pg13/share/extension'
/bin/mkdir -p '/home/postgres/pg13/share/tsearch_data'
/usr/bin/install -c -m 755  unaccent.so '/home/postgres/pg13/lib/unaccent.so'
/usr/bin/install -c -m 644 ./unaccent.control '/home/postgres/pg13/share/extension/'
/usr/bin/install -c -m 644 ./unaccent--1.1.sql ./unaccent--1.0--1.1.sql  '/home/postgres/pg13/share/extension/'
/usr/bin/install -c -m 644 ./unaccent.rules '/home/postgres/pg13/share/tsearch_data/'
make[2]: Leaving directory `/tools/postgresql-13beta1/contrib/unaccent'
make -C vacuumlo install
make[2]: Entering directory `/tools/postgresql-13beta1/contrib/vacuumlo'
/bin/mkdir -p '/home/postgres/pg13/bin'
/usr/bin/install -c  vacuumlo '/home/postgres/pg13/bin'
make[2]: Leaving directory `/tools/postgresql-13beta1/contrib/vacuumlo'
make[1]: Leaving directory `/tools/postgresql-13beta1/contrib'

• 创建PG数据目录与软件安装目录

root用户下
mkdir -p /data/pgsql/data
chown -R postgres.postgres /data/pgsql
mkdir -p /home/postgres/pg13
ln

• 配置环境变量
  postgres 用户下，配置环境变量

su - postgres  
vim ~/.bash_profile  
  
追加  
  
export PS1="$USER@`/bin/hostname -s`-> "  
export PGPORT=1921  
export PGDATA=/data/pgsql/data
export LANG=en_US.utf8  
export PGHOME=/home/postgres/pgsql  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
export PATH=$PGHOME/bin:$PATH:.  
export DATE=`date +"%Y%m%d%H%M"`  
export MANPATH=$PGHOME/share/man:$MANPATH  
export PGHOST=$PGDATA  
export PGUSER=postgres  
export PGDATABASE=postgres  
alias rm='rm -i'  
alias ll='ls -lh'

• ​ 使配置生效

source

• 初始化数据库集群

initdb -D $PGDATA -E UTF8 --locale=C -U postgres

• 修改PG配置文件 postgresql.conf（以4G内存、8核心为例）
  vim $PGDATA/postgresql.conf

追加到文件末尾 
listen_addresses = '0.0.0.0'  
port = 5432
shared_buffers = 512MB  # (内存8分之1)
checkpoint_timeout = 30min   
max_wal_size = 2GB  # (内存0.5倍)
min_wal_size = 128MB  
max_connections = 200  # (根据实际业务量)

其他参考：https://github.com/digoal/blog/blob/master/201704/20170411_01.md

• 修改PG认证文件 pg_hba.conf
  vim $PGDATA/pg_hba.conf

追加  
  
host all all 0.0.0.0/0 md5

• 启动数据库集群

su

• 连接数据库

su

• 编写PG启停脚本
  vim /usr/local/bin/pgsql_ctl

#! /bin/sh

status()
{
    if [[ -n `pidof -s postmaster` ]] || [[ -n `pidof -s postgres` ]];then
        echo "PostgreSQL is running ! "
    else
        echo "PostgreSQL is stopped ! "
    fi
}


start()
{
    su - postgres -c "pg_ctl start "
    if [[ $? -eq 0 ]];then
        echo "PostgreSQL started ! "
    else
        echo "PostgreSQL start failed! "
    fi
}


stop()
{
    su - postgres -c "pg_ctl stop -m fast "
    if [[ $? -eq 0 ]];then
        echo "PostgreSQL stoped ! "
    else
        echo "PostgreSQL stop  failed! "
    fi
}


case $1 in 
    status)
        status
        ;;
    start)
        start
        ;;
    stop)
        stop
        ;;
    restart)
        stop
        start
        ;;
    *)
    echo "your param is wrong,please input like pgsql_ctl <start|stop|status>"
    ;;

• 测试启停脚本

[root@mysql postgres]# pgsql_ctl --help
your param is wrong,please input like pgsql_ctl <start|stop|status>
[root@mysql postgres]# 
[root@mysql postgres]# 
[root@mysql postgres]# pgsql_ctl statsu
your param is wrong,please input like pgsql_ctl <start|stop|status>
[root@mysql postgres]# 
[root@mysql postgres]# pgsql_ctl status
PostgreSQL is running ! 
[root@mysql postgres]# 
[root@mysql postgres]# pgsql_ctl stop
waiting for server to shut down....2020-06-06 16:55:30.740 CST [13726] LOG:  received fast shutdown request
2020-06-06 16:55:30.741 CST [13726] LOG:  aborting any active transactions
2020-06-06 16:55:30.742 CST [13726] LOG:  background worker "logical replication launcher" (PID 13733) exited with exit code 1
2020-06-06 16:55:30.744 CST [13728] LOG:  shutting down
2020-06-06 16:55:30.796 CST [13726] LOG:  database system is shut down
 done
server stopped
PostgreSQL stoped ! 
[root@mysql postgres]# pgsql_ctl status
PostgreSQL is stopped ! 
[root@mysql postgres]# 
[root@mysql postgres]# pgsql_ctl start
waiting for server to start....2020-06-06 16:55:34.941 CST [13803] LOG:  starting PostgreSQL 13beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
2020-06-06 16:55:34.942 CST [13803] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-06-06 16:55:34.944 CST [13803] LOG:  listening on Unix socket "./.s.PGSQL.5432"
2020-06-06 16:55:34.960 CST [13804] LOG:  database system was shut down at 2020-06-06 16:55:30 CST
2020-06-06 16:55:34.983 CST [13803] LOG:  database system is ready to accept connections
 done
server started
PostgreSQL started ! 
[root@mysql postgres]# pgsql_ctl status
PostgreSQL is running ! 
[root@mysql postgres]# 
[root@mysql postgres]# pgsql_ctl restart
waiting for server to shut down....2020-06-06 16:55:40.313 CST [13803] LOG:  received fast shutdown request
2020-06-06 16:55:40.315 CST [13803] LOG:  aborting any active transactions
2020-06-06 16:55:40.316 CST [13803] LOG:  background worker "logical replication launcher" (PID 13810) exited with exit code 1
2020-06-06 16:55:40.317 CST [13805] LOG:  shutting down
2020-06-06 16:55:40.380 CST [13803] LOG:  database system is shut down
 done
server stopped
PostgreSQL stoped ! 
waiting for server to start....2020-06-06 16:55:40.574 CST [13860] LOG:  starting PostgreSQL 13beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
2020-06-06 16:55:40.574 CST [13860] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-06-06 16:55:40.578 CST [13860] LOG:  listening on Unix socket "./.s.PGSQL.5432"
2020-06-06 16:55:40.592 CST [13861] LOG:  database system was shut down at 2020-06-06 16:55:40 CST
2020-06-06 16:55:40.619 CST [13860] LOG:  database system is ready to accept connections
 done
server started
PostgreSQL started ! 
[root@mysql postgres]# pgsql_ctl status
PostgreSQL is running ! 
[root@mysql postgres]#