2020-01-03 KK日记，第一次进行postgresql 11.5+pgpool 安装

一、安装规划

1. 安装单实例
2. 配置主从
3. 安装pgpool -ii

二、单实例安装

2.1 操作系统

os: centos 7.6

cpu: 4 core

memory:32g

disk: 60g

192.168.0.1

192.168.0.2

2.2 postgresql 软件准备

源码： postgresql 11.5

 

2.3 安装前检查

使用 yum install 安装以下包 

make 2.5+

gcc

readline readline-devel

zlib

zlib-devel

perl 5.8.3

perl-devel

python

tcl 8.4

2.4 修改内核参数、增加postgres用户、修改/dev/shm 大小

2.4.1 修改内核参数

net.ipv4.ip_forward = 0

net.ipv4.conf.default.rp_filter = 1

net.ipv4.conf.default.accept_source_route = 0

kernel.sysrq = 0

kernel.core_uses_pid = 1

net.ipv4.tcp_syncookies = 1

kernel.msgmnb = 65536

kernel.msgmax = 65536

kernel.shmmax = 123695058124

kernel.shmall = 30198988

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 262144

net.core.rmem_max = 41943040

net.core.wmem_default = 262144

net.core.wmem_max = 1048576

2.4.2 增加用户

useradd postgres

passwd postgres

2.4.3 修改/dev/shm

fuser -km /dev/shm

mount -t tmpfs -o size=10240m tmpfs /dev/shm

 

vi /etc/fstab

tmpfs /dev/shm tmpfs defaults,size=10240m 0 0

备注：不修改这个，后面在大并发查询时，可能会出现下面大错误

CAUSE BY:could not resize shared memory segment "/PostgreSQL.644318876" to 196864 bytes: 设备上没有空间

2.6 修改limit配置

/etc/security/limits.conf

postgres   soft    nproc    65536

postgres   hard   nproc   65536

postgres   soft    nofile    2048
postgres   hard   nofile    65536
postgres   soft    stack    10240
postgres   hard   stack    10240

 

2.7 源码安装

scp postgresql-11.5.tar.gz postgres@192.168.0.1:/tmp

postgres login the 192.168.0.1

cd /tmp

tar zxvf postgresql-11.5.tar.gz

./configure 

 make 

 su -

 make install 

 mkdir /work/pgsql/data 

chown postgres /work/pgsql/data 

su - postgres 

#初始化数据库

/usr/local/pgsql/bin/initdb -D /work/pgsql/data 

[postgres@pgdbdsvr001 ~]$ initdb -D /work/pgsql/data   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 "zh_CN.UTF-8".   The default database encoding has accordingly been set to "UTF8".   initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"   The default text search configuration will be set to "simple".       Data page checksums are disabled.       fixing permissions on existing directory /work/pgsql/data ... ok   creating subdirectories ... ok   selecting default max_connections ... 100   selecting default shared_buffers ... 128MB   selecting default timezone ... PRC   selecting dynamic shared memory implementation ... posix   creating configuration files ... ok   running bootstrap script ... ok   performing post-bootstrap initialization ... ok   syncing data to disk ... ok         WARNING: enabling "trust" authentication for local connections   You can change this by editing pg_hba.conf or using the option -A, or   --auth-local and --auth-host, the next time you run initdb.         Success. You can now start the database server using:             pg_ctl -D /work/pgsql/data -l logfile start

 

#启动实例

/usr/local/pgsql/bin/pg_ctl -D /work/pgsql/data -l logfile start 

[postgres@pgdbdsvr001 ~]$ ps -ef | grep postgres   root     25582  6303  0 18:32 ?        00:00:00 sshd: postgres [priv]   postgres 25610 25582  0 18:33 ?        00:00:00 sshd: postgres@pts/0   postgres 25611 25610  0 18:33 pts/0    00:00:00 -bash   postgres 25937     1  0 18:36 pts/0    00:00:00 /usr/local/pgsql/bin/postgres -D /work/pgsql/data   postgres 25939 25937  0 18:36 ?        00:00:00 postgres: checkpointer     postgres 25940 25937  0 18:36 ?        00:00:00 postgres: background writer     postgres 25941 25937  0 18:36 ?  &nb