prime部署pg11并添加从库

一、准备

1、创建系统用户postgres

useradd postgres

2、创建目录

mkdir -p /data/postgres/pgdata

mkdir /data/arch

chown -R postgres:postgres /data/

chmod -R 700 /data/postgres/pgdata

二、步骤

1、安装PG11

# Install the repository RPM:
--目前这种安装的yum源是没有11版本的  sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
需要配置新的yum源：
cat << EOF > /etc/yum.repos.d/pgdg-11.repo
[pgdg11-archive]
name=PostgreSQL 11.22 RPMs for RHEL/CentOS 7
baseurl=https://yum-archive.postgresql.org/11/redhat/rhel-7-x86_64
enabled=1
gpgcheck=0
gpgkey=https://yum.postgresql.org/keys/RPM-GPG-KEY-PGDG
EOF
# Install PostgreSQL:
安装数据库服务
yum install -y postgresql11-server
安装扩展组件（选装）
yum install -y postgresql11-contrib.x86_64

2、修改/etc/sysctl.conf

fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
fs.aio-max-nr = 1048576
net.ipv4.tcp_timestamps = 0
vm.overcommit_memory = 0
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=1
vm.dirty_background_bytes=102400000
vm.dirty_bytes=102400000
vm.dirty_expire_centisecs=10
vm.dirty_writeback_centisecs=10
vm.swappiness=0

3、使参数生效

    sysctl -p

4、修改/etc/security/limits.conf

vim /etc/security/limits.conf
* soft    nofile  1024000 * hard    nofile  1024001 * soft nproc unlimited * hard nproc unlimited * soft    core    unlimited * hard    core    unlimited * soft    memlock unlimited * hard    memlock unlimited

5、修改/etc/security/limits.d/20-nproc.conf

vim /etc/security/limits.d/20-nproc.conf
*          soft    nproc     40960
root       soft    nproc     unlimited

三、必要配置文件

1、postgres用户环境变量

vim /home/postgres/.bash_profile
# 设置环境变量
export PGPORT=1921   # 端口不变
export PGHOME=/usr/pgsql-11
# 定义数据路径
export PGDATA=/data/postgres/pgdata
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres

2、修改PG启动文件

cp /usr/lib/systemd/system/postgresql-11.service /usr/lib/systemd/system/postgresql-11.service.bak
vim /usr/lib/systemd/system/postgresql-11.service
# 添加内容：
[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
# 自己定义的数据路径
Environment=PGDATA=/data/postgres/pgdata
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStart=/usr/pgsql-11/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
 
[Install]
WantedBy=multi-user.target

3、开机自启

systemctl daemon-reload
systemctl enable postgresql-11

四、关闭透明大页

关闭透明页(OEL 6.x/7.x) (开了大内存页要关闭)

1.cat /sys/kernel/mm/transparent_hugepage/defrag

[always] madvise never

2.cat /sys/kernel/mm/transparent_hugepage/enabled

[always] madvise never

3.
vi /etc/rc.d/rc.local

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi

4.chmod +x /etc/rc.d/rc.local

5.重启服务器

reboot

6.cat /sys/kernel/mm/transparent_hugepage/defrag

always madvise [never]

7.cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

五、同步从库

1、同步数据

nohup pg_basebackup -h 172.21.13.102 -p 1921 -U replica -D /data/postgres/pgdata -P -Xs 1>1.log 2>2.log &

screen -S prime_91 #创建prime_91后台
screen -ls #查看screen任务
pg_basebackup -h 172.21.13.102 -p 1921 -U replica -D /data/postgres/pgdata -P -Xs 1>1.log 2>2.log


screen -r prime_91 #进入prime_91后台

2、修改recovery.conf 文件

vi /data/postgres/pgdata/recovery.conf
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'application_name=172.21.13.91 host=172.21.13.101 port=1921 user=replica password=PostGRESQL.replica keepalives_idle=60'

说明：

· application_name 本机IP

· host 上一级库的IP

3、验证主备同步

select * from pg_stat_replication;

六、开启大页(以下方式针对PG11)

1.确保

CONFIG_HUGETLBFS=y

CONFIG_HUGETLB_PAGE=y

[root@xfzconpys0310 boot]# cat config-3.10.0-1160.el7.x86_64 |grep CONFIG_HUGETLB_PAGE

CONFIG_HUGETLB_PAGE=y

[root@xfzconpys0310 boot]# cat config-3.10.0-1160.el7.x86_64 |grep CONFIG_HUGETLBFS

CONFIG_HUGETLBFS=y

2.启动数据库进程（postgres用户下执行）

3.查询pg服务的pid

head -1 $PGDATA/postmaster.pid

128942

4.查询pg占用的内存大小

pmap 128942 | awk '/rw-s/ && /zero/ {print $2}'

207904072K

5.查看默认大页单位大小

grep ^Hugepagesize /proc/meminfo

6.Hugepagesize: 2048 kB

207904072 /2048 大约是101515.66，因此在这个示例中你至少需要 101516个大页面

7.设置/etc/sysctl.conf，增加如下配置

vm.nr_hugepages=101516

8.修改pg confg文件中的参数

添加 huge_pages=on

9.启动数据库

su - postgres

pg_ctl start -D $PGDATA #数据库能正常启动，大页生效

show huge_pages;

10.检查大页是否生效

grep HugePages /proc/meminfo

七、对接监控

1.等清洗到CMDB 完成后，对接监控 略

八、对接clup平台

已完成