yum安装postgresql 9.6

转载

Jack_jason 2022-11-18 14:57:01 博主文章分类：数据库

postgresql安装

修改系统配置

一、修改sysctl.conf

vim /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

存储在线换算
https://cn.unithelper.com/data-storage/

建议配置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）

vim /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 命令设置临时关闭，不用重启生效。

关闭防火墙

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=2"

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

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

八、文件系统预读设置

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

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

blockdev --getra /dev/sda

8192

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  https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-6-x86_64/pgdg-redhat-repo-42.0-11.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用户环境变量

vim .bash_profile

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=/data/pgdata
export PGDATA=/data/pgdata

source .bash_profile生效

二、初始化数据库

[postgres@vm-node3 bin]$ ./initdb --auth-host=md5 --auth-local=peer -D /data/pgdata --pwprompt
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password: （postgres）
Enter it again: 

fixing permissions on existing directory /data/pgdata ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

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

三、修改postgresql.conf文件

vi /data/pgdata/postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 2000
superuser_reserved_connections = 10
unix_socket_directories = '/data/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 /data0/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

五、创建流复制用户

[root@vm-node3 ~]# su postgres
[postgres@vm-node3 root]$ cd 
[postgres@vm-node3 ~]$ ls
[postgres@vm-node3 ~]$ psql
psql (9.6.24)
Type "help" for help.

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

六、创建pg_stat_statements扩展

template1=# create extension pg_stat_statements;

七、创建备库

pg_basebackup -D /data/pgdata -Fp -R -P -v -h 127.0.0.1 -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....."