PG-源码编译安装

1. 下载

-- 下载源码包
https://www.postgresql.org/download/product-categories/

2. 环境配置

2.1 关闭防火墙

systemctl disable firewalld
systemctl stop  firewalld

2.2 关闭selinux

# 禁用selinux，配置/etc/sysconfig/selinux，修改SELINUX项为disabled
if [[ "$(getenforce)" = "Enforcing" ]]; then
    cp /etc/selinux/config{,_$(date +%Y%m%d)} && setenforce 0 && sed -i  "/^(SELINUX=.*)/c\#/1\nSELINUX=disable" /etc/selinux/config
fi

2.3 配置deadline IO调度

cat > /etc/udev/rules.d/60-postgres-schedulers.rules<<EOF
# postgres安装目录及数据目录所在磁盘
ACTION=="add|change", KERNEL=="sd[b-z]", ATTR{queue/rotational}=="0", ATTR{queue/scheduler}="deadline"
EOF

/usr/sbin/udevadm control --reload-rules

-- 检查确认
-- cat /sys/block/[sd*]/queue/scheduler 
cat /sys/block/sdb/queue/scheduler

2.4 添加用户

useradd -u 26 -r -c "PostgreSQL Server" -m postgres
passwd postgres
mkdir -p /ups/data/pgdata/11/pg_root
mkdir -p /ups/data/pgdata/11/pg_usr
mkdir -p /ups/data/pgdata/11/{pg_root,pg_usr,backups,scripts,archive_wals}
chown -R postgres:postgres /ups/data/pgdata
chmod 700 /ups/data/pgdata/11/{pg_root,pg_usr,backups,scripts,archive_wals}

-- /ups/data/pgdata/11/pg_root 目录存储数据库系统数据文件，
-- /ups/data/pgdata/11/pg_usr存储用户自定义表空间文件

2.5 配置用户环境变量

cat >> /home/postgres/.bash_profile<<-EOF
export PGPORT=1921
export PGUSER=postgres
export PGGROUP=postgres
export PGDATA=/ups/data/pgdata/11/pg_root
export LANG=en_US.UTF-8
export PGHOME=/ups/app/pgsql-11
export LD_LIBRARY_PATH=\$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export PATH=\$PGHOME/bin:\$PATH:.
export MANPATH=\$PGHOME/share/man:\$MANPATH
EOF

2.6 安装系统依赖包

# 检查
rpm -q --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" systemd-devel gcc bison gcc-c++ flex readline  readline-devel zlib  zlib-devel \
perl  perl-devel make openssl-devel perl-ExtUtils-Embed perl-ExtUtils-MakeMaker libxml2-devel
# 安装
yum -y install systemd-devel gcc bison flex gcc-c++ readline  readline-devel  zlib  zlib-devel  perl \
perl-devel make openssl-devel perl-ExtUtils-Embed perl-ExtUtils-MakeMaker libxml2-devel

2.7 配置IPC

if [[ -f "/etc/systemd/logind.conf" ]]; then
    cp /etc/systemd/logind.conf /etc/systemd/logind.conf_$(date +%Y%m%d)
    sed -i "/#RemoveIPC=no/c\#RemoveIPC=no\nRemoveIPC=no" /etc/systemd/logind.conf
else
    cat > /etc/systemd/logind.conf << EOF
RemoveIPC=no
EOF
fi

2.8 huge page配置

# 样例
$ head -1 $PGDATA/postmaster.pid
4170
$ pmap 4170 | awk '/rw-s/ && /zero/ {print $2}'
6490428K
$ grep ^Hugepagesize /proc/meminfo
Hugepagesize:       2048 kB

6490428/2048=3170
$ sysctl -w vm.nr_hugepages=3170

3. 部署

3.1 解压

tar -xf postgresql-11.5.tar.gz -C /ups/app/
cd /ups/app/
mv postgresql-11.5 pgsql-11

3.2 编译安装

./configure --prefix=/ups/app/pgsql-11 --with-perl --with-libxml --with-openssl --with-systemd 
make world
make check
make install-world

3.3 初始化

su - postgres
/ups/app/pgsql-11/bin/initdb -D /ups/data/pgdata/11/pg_root
# 启动服务
pg_ctl -D /ups/data/pgdata/11/pg_root -l logfile start

3.4 配置参数

1）配置连接

export MDATE=$(date +"%Y%m%d%H")
-- 设置监听整个网络，查找“listen_addresses ”字符串
cp ${PGDATA}/postgresql.conf ${PGDATA}/postgresql.conf_${MDATE}
vi ${PGDATA}/postgresql.conf
-- 修改listen_addresses如下
listen_addresses = '*'

-- 配置连接方式
cp ${PGDATA}/pg_hba.conf ${PGDATA}/pg_hba.conf_${MDATE}
vi ${PGDATA}/pg_hba.conf
host    all             all             192.168.10.0/24           md5

2）配置共享内存

vi ${PGDATA}/postgresql.conf
shared_buffers = 128MB            # min 128kB      <<<<<<<<默认值

3）配置自启动服务

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

cat> /usr/lib/systemd/system/postgresql-11.service <<-EOF # It's not recommended to modify this file in-place, because it will be # overwritten during package upgrades.  If you want to customize, the # best way is to create a file "/etc/systemd/system/postgresql-11.service", # containing #   .include /usr/lib/systemd/system/postgresql-11.service #   ...make your changes here... # For more info about custom unit files, see # http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F   # Note: changing PGDATA will typically require adjusting SELinux # configuration as well.   # Note: do not use a PGDATA pathname containing spaces, or you will # break postgresql-setup. [Unit] Description=PostgreSQL 11 database server Documentation=https://www.postgresql.org/docs/11/static/ After=syslog.target After=network.target   [Service] Type=notify   User=postgres Group=postgres   # Note: avoid inserting whitespace in these Environment= lines, or you may # break postgresql-setup.   # Location of database directory Environment=PGDATA=/ups/data/pgdata/11/pg_root   # Where to send early-startup messages from the server (before the logging # options of postgresql.conf take effect) # This is normally controlled by the global default set by systemd # StandardOutput=syslog   # Disable OOM kill on the postmaster OOMScoreAdjust=-1000 Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj Environment=PG_OOM_ADJUST_VALUE=0   # ExecStartPre=/ups/app/pgsql-11/bin/postgresql-11-check-db-dir \${PGDATA} ExecStart=/ups/app/pgsql-11/bin/postmaster -D \${PGDATA} ExecReload=/bin/kill -HUP \$MAINPID KillMode=mixed KillSignal=SIGINT      # Do not set any timeout value, so that systemd will not kill postmaster # during crash recovery. TimeoutSec=0   [Install] WantedBy=multi-user.target EOF

# 启动服务
systemctl start postgresql-11.service
systemctl status postgresql-11.service
systemctl enable postgresql-11.service

4）开启日志

# 开启日志
${PGDATA}/postgresql.conf