postgresql 9.6 yum安装

postgresql安装

修改系统配置

修改sysctl.conf

vi /etc/sysctl.conf
kernel.shmmax = 34359738368   #实际内存大小，单位为字节B
kernel.shmall = 8388608     #机器物理内存的页面数，物理内存除以4K
vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=80
vm.dirty_background_ratio=2
vm.dirty_ratio=2
kernel.sem=20 13000 20 650
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152

建议配置coredump，这样程序发生了coredump时，会生成core文件：

kernel.core_uses_pid=1
kernel.core_pattern=/corefile/core-%e-%p-%t

需要建目录/corefile来放coredump文件

修改limits.conf

vi /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
* soft nproc 131072

修改20-nproc.conf

有的时候，limits.conf中的配置会被/etc/security/limits.d下的文件覆盖掉，不生效，需要修改/etc/security/limits.d下的配置文件，在CentOS7.X下面是20-nproc.conf（在CentOS6.X下名称是90-nproc.conf）

vi /etc/security/limits.d/20-nproc.conf
* soft nproc 131072
* hard nproc 131072

关闭SELINUX

查看selinux状态:

# getenforce  

关闭SELINUX

sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

改变配置文件，需要重启生效；我们可以使用setenforce 命令设置临时关闭，不用重启生效。

setenforce 0

关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

禁用ssh的DNS反解

如果不禁止这个，导致从外面ssh这台主机是有些慢。
方法是修改/etc/ssh/sshd_config：

UseDNS no
GSSAPIAuthentication no

上面是禁止了DNS反解析，禁止了GSSAPI。GSS的功能很少用，这个也可能导致ssh慢。

禁止透明大页

grubby --update-kernel=ALL --args="transparent_hugepage=never"`

#cat /sys/kernel/mm/*transparent_hugepage/enabled
[always] madvise never

1G大页的配置方法

grubby --update-kernel=ALL --args="default_hugepagesz=1G hugepagesz=1G hugepages=16"

执行上面的命令后，需要重启操作系统才能生效。

上面配置中hugepages=16表明配置了16GB的大页。一般配置25%的物理内存。数据库的shared_buffer参数指定的内存值需要比这个值小一点。

文件系统预读设置

在Linux文件系统中，当执行向前的顺序的读操作时，Linux内核里的预读会在应用读取数据请求之前，预先读取磁盘上的数据对于当下较快的驱动，预读操作是获取高性能读取的关键所在。pg数据库安装之前，要根据业务的类型与具体情况设置该值

用户可以通过blockdev 命令查看当前预读取值得大小，如

blockdev  --getra /dev/sda
8192

该值的单位为512字节，对于现在的硬件，通常大小为 4096~16384
可通过如下命令修改

blockdev  --setra 4096  /dev/sda

blockdev  --getra /dev/sda
4096

文件访问时间

用户每次访问Linux 上的文件时，文件都会更新一个叫做=作上次访问时间（atime）的属性。读取数据时，这种开销成了稳定的写入流，在数据库中，这可不是一个受欢迎的开销。在/etc/fstab中，用户可以将 noatime 添加到卷挂载选项中来禁止这种行为。例如

/dev/sda  ext3   noatime,errors=remount-ro 0 1

创建用户

groupadd -g 701 postgres
useradd -u 701 -g postgres -G postgres postgres
echo "postgres" | passwd --stdin postgres

创建数据目录

mkdir -p /data01/pgdata
mkdir -p /data01/arch
mkdir -p /data01/pgbak
chown -R postgres.postgres /data01
chmod -R 700 /data01/pgdata

安装postgresql

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

yum install -y postgresql96-server postgresql96 postgresql96-libs postgresql96-contrib postgresql96-devel

postgresql96 pg的客户端程序，输入psql进入数据库需要这个
postgresql96-libs pg的静态库程序
postgresql96-server pg的服务端程序，用来创建数据库实例
postgresql96-contrib pg的一些工具，用来维护数据库
postgresql96-devel pg的C语言开发库程序

配置postgres用户环境变量

export PGHOME=/usr/pgsql-9.6
export PGPORT=5432
export PATH=$PGHOME/bin:$PATH:$HOME/.local/bin:$HOME/bin
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=/data01/pgdata
export PGDATA=/data01/pgdata

加载生效

source .bash_profile

初始化数据库

initdb --auth-host=md5 --auth-local=peer -D /data01/pgdata --pwprompt

修改postgresql.conf文件

vi /data01/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 2000
superuser_reserved_connections = 10
unix_socket_directories = '/data01/pgdata'
tcp_keepalives_idle = 5
tcp_keepalives_interval = 5
tcp_keepalives_count = 3   
shared_buffers = 8GB
shared_preload_libraries = 'pg_stat_statements'
wal_level = replica
max_wal_size = 10GB
min_wal_size = 8GB
checkpoint_completion_target = 0.9   
archive_mode = on
archive_command = '/bin/bash /home/postgres/bin/pg_archive.sh %p %f'
max_wal_senders = 16
hot_standby = on
log_destination = 'csvlog'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 100MB
log_statement = 'ddl'
track_functions = all
track_activity_query_size = 4096

修改pg_hba.conf文件

vi /data01/pgdata/pg_hba.conf
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local    all            repl                                 trust
local   all             all                                    trust
host    all             all             0/0                     md5

local   replication      postgres                              peer
host    replication     repl            0/0                    trust
host    replication     postgres        0/0                    md5

创建流复制用户

postgres=#  CREATE ROLE repl superuser PASSWORD '@postgres' login;
CREATE ROLE

创建pg_stat_statements扩展

template1=# create extension pg_stat_statements;

创建备库

pg_basebackup -D /data01/pgdata -Fp -R -P -v -h 192.168.2.154 -p 5432 -U repl

创建密码文件

vi .pgpass
192.168.2.154:5432:*:repl:@postgres

chmod 600 .pgpass

配置recovery.conf文件

vi /data01/pgdata/recovery.conf
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'application_name=stb155 user=repl password=@postgres host=192.168.2.154 port=5432 sslmode=prefer sslcompression=1 krbsrvname=postgres'

配置归档脚本

[postgres@ztt_pg ~]$ mkdir -p bin logs
[postgres@ztt_pg ~]$ vi bin/pg_archive.sh
#!/bin/bash
source /home/postgres/.bash_profile

DATE=`date +%F`
DIR="/data01/arch/$DATE"

BACK="/data01/arch/"`date -d '-7 day' +%Y-%m-%d`
if [ -d $BACK ]; then
                rm -rf $BACK
                echo "success rm $BACK" > /home/postgres/logs/pg_archive.log
        else
                echo "the old backup file  $BACK not exists!" > /home/postgres/logs/pg_archive.log
fi

(test -d $DIR || mkdir -p $DIR) && cp $1 $DIR/$2

配置备份脚本

[postgres@ztt_pg ~]$ mkdir /data01/pgbak

[postgres@ztt_pg ~]$ vi bin/pgbackup.sh
#!/bin/bash

source /home/postgres/.bash_profile

prefixBackDir=/data01/pgbak

back=$prefixBackDir"/"`date -d '-7 day' +%Y-%m-%d`

backf=$prefixBackDir"/"`date +%F`"/base"


current_time=$(date "+%Y-%m-%d %H:%M:%S")
echo "$current_time  starting backup....."

pg_basebackup -D $prefixBackDir/`date +%F` -X s -P -F p -h /data01/pgdata  -U repl -p 5432 -c fast

if [ -d $backf ]; then
        echo "successd backup to $backf"

        if [ -d $back ]; then
                rm -rf $back
                echo "success rm $back"
        else
                echo "the old backup file $back not exists!"
        fi
else
        echo "backup failed, because of the file $backf not found"
fi

echo "$current_time end backup....."

配置crontab

[postgres@ztt_pg ~]$ crontab -l
0 0 * * *     /home/postgres/bin/pgbackup.sh >>/home/postgres/logs/pgbackup.log 2>&1 &

安装node_exporter

下载rpm包

wget --content-disposition https://packagecloud.io/prometheus-rpm/release/packages/el/7/node_exporter-1.0.1-1.el7.x86_64.rpm/download.rpm

安装

rpm -ivh  node_exporter-1.0.1-1.el7.x86_64.rpm

设置开机自启

systemctl enable node_exporter

启动

systemctl start node_exporter

查看服务状态

systemctl status node_exporter

查看收集的信息

curl 127.0.0.1:9100/metrics

安装postgres_exporter

下载rpm包

wget --content-disposition https://packagecloud.io/prometheus-rpm/release/packages/el/7/postgres_exporter-0.8.0-4.el7.x86_64.rpm/download.rpm

安装

rpm --ivh postgres_exporter-0.8.0-4.el7.x86_64.rpm/download.rpm

修改环境变量

vi /etc/default/postgres_exporter
DATA_SOURCE_NAME="user=repl host=/data01/pgdata port=5432 dbname=postgres sslmode=disable"
POSTGRES_EXPORTER_OPTS="--extend.query-path=/etc/prometheus/postgres_exporter_queries.yaml"

注意修改ip、用户名、密码

设置开机自启

systemctl enable postgres_exporter

启动

systemctl start postgres_exporter

查看服务状态

systemctl status postgres_exporter

查看收集信息

curl 127.0.0.1:9187/metrics