Postgresql11.5单机部署标准化文档

2022-05-31 05:24:28

环境设置

创建用户

编译安装成功后，接下来要做的就是创建一个普通用户，因为默认超级用户（root）不能启动postgresql，所以需要创建一个普通用户来启动数据库，执行以下命令创建用户：

[root@localhost build_dir]# groupadd postgres
[root@localhost build_dir]# useradd -g postgres postgres
[root@localhost build_dir]# passwd postgres

接下来设置权限，将pg的数据目录全部赋给postgres用户，执行以下命令：

[root@localhost build_dir]# chown -R postgres:postgres /usr/local/pgsql

创建目录

[root@localhost build_dir]# mkdir -p /mnt/db1/pgdata/pgsql /mnt/db1/pgdata/pgtbs /mnt/db1/archivelog /backups
[root@localhost build_dir]# chmod -R 775 /mnt/db1
[root@localhost build_dir]# chown -R postgres:postgres /mnt/db1

设置环境变量

[root@localhost build_dir]# vi /home/postgres/.bash_profile

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export PGPORT=5432
export PGHOME=/usr/local/pgsql
export PGDATA=/mnt/db1/pgdata/pgsql
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.UTF-8
export DATE=‘date +"%Y%m%d%H%M"‘
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PGHOST=127.0.0.1
export PGUSER=postgres
export PGDATABASE=postgres

PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin
export PATH

执行如下命令使其生效：

[root@localhost build_dir]# source /home/postgres/.bash_profile

安装

依赖包安装

由于centos自带的版本不够高，直接安装postgresql会报错，所以先给llvm和clang升级
#wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
#yum localinstall epel-release-latest-7.noarch.rpm # yum install llvm5.0 llvm5.0-devel c

yum install -y  gcc gcc-c++  openssl openssl-devel readline readline-devel \ 
zlib zlib-devel llvm5.0 llvm5.0-devel \ 
libxml2-devel  libxslt-devel  libicu-devel  python-devel tcl-devel  systemd-devel  openldap-devel  pam-devel clang  perl-ExtUtils-Embed epel-release 

实际执行：
yum install -y gcc gcc-c++ epel-release llvm5.0 llvm5.0-devel clang libicu-devel perl-ExtUtils-Embed readline readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel openldap-devel systemd-devel tcl-devel python-devel
但是centos6上：无法安装systemd-devel，llvm5.0 ，llvm5.0-devel

--德哥标准化
https://github.com/digoal/blog/blob/master/201611/20161121_01.md

# yum -y install coreutils glib2 lrzsz mpstat dstat sysstat e4fsprogs xfsprogs ntp readline-devel zlib-devel openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make smartmontools flex bison perl-devel perl-ExtUtils* openldap-devel jadetex  openjade bzip2

源码编译安装

#cd build_dir/
vim ../src/Makefile.global.in
修改以下行：
COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -flto=thin -emit-llvm -c
修改为：
COMPILE.c.bc = $(CLANG) -Wno-ignored-attributes $(BITCODE_CFLAGS) $(CPPFLAGS) -emit-llvm -c

-- --prefix 指定默认安装路径
[root@localhost build_dir]# ../configure 
--prefix=/usr/local/pgsql 
--enable-nls 
--with-perl 
--with-python 
--with-tcl 
--with-gssapi 
--with-llvm LLVM_CONFIG=‘/usr/lib64/llvm5.0/bin/llvm-config‘ 
--with-icu 
--with-openssl 
--with-pam 
--with-ldap 
--with-systemd 
--with-libxml 
--with-libxslt#configure 命令完成后，会发现创建了 config.status 配置文件# 实际执行：./configure --prefix=/home/cdrom/work/postgresql11_5432 --enable-nls --with-perl --with-python --with-tcl --with-gssapi --with-llvm LLVM_CONFIG=‘/usr/lib64/llvm5.0/bin/llvm-config‘ --with-icu --with-openssl --with-pam --with-ldap --with-systemd --with-libxml --with-libxslt

make 和make install

gmake world -j24
gmake install-world -j24

gmake world 这会编译所有的源文件，包括contrib文件夹下的工具包。
gmake install-world 安装所有工具包括文档，加上world省去了很多麻烦。

初始化数据库

切换用户
[root@localhost build_dir]# su - postgres
初始化数据库
[postgres@localhost ~]$ initdb -D $PGDATA -U postgres --locale=en_US.UTF8 -E UTF8

修改配置参数

修改监听地址 将listen_addresses的值设置成*，使其监听整个网络，端口号默认是5432，也可以自己设置。

[postgres@localhost ~]$ vim /mnt/db1/pgdata/pgsql/postgresql.conf
修改内容:
listen_addresses = ‘*‘
unix_socket_directories = ‘.‘
port = 5432

修改客户端认证方式

[postgres@localhost ~]$ vim /mnt/db1/pgdata/pgsql/pg_hba.conf
添加内容：
host all all 0.0.0.0/0 md5 # 其他用户登陆

设置防火墙规则

#切换回root用户
[postgres@localhost ~]$ exit
[root@localhost build_dir]# firewall-cmd --zone=public --add-port=5432/tcp --permanent
[root@localhost build_dir]# firewall-cmd --reload

启动数据库

[root@localhost build_dir]# su - postgres
启动
[postgres@localhost ~]$ pg_ctl -D /mnt/db1/pgdata/pgsql -l /mnt/db1/archivelog/pgsql.log start
停止
[root@localhost postgres]# pg_ctl -D /mnt/db1/pgdata/pgsql/ -s -m fast stop

连接测试

[postgres@localhost ~]$ psql
查询所有用户
postgres=# select * from pg_user;
postgres=# select * from pg_roles;
查询权限
postgres=# select * from information_schema.table_privileges where grantee=‘cc‘;
查看有哪些数据库
postgres=# l
相当与mysql的show databases;
postgres=# select datname from pg_database;
相当于mysql的show tables, public 是默认的schema的名字
postgres=# SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public‘;
相当与mysql的describe table_name, ‘table_name‘是要查询的表的名字
postgres=# SELECT column_name FROM information_schema.columns WHERE table_name =‘table_name‘;
退出
postgres=# q

psql 是 PostgreSQL 的客户端程序，要连接 PostgreSQL 数据库，我们需要指定以下内容：

    -d or --dbname 数据库名
    -h or --host 主机名
    -p or --port 端口号，默认5432 端口
    -U or --username 用户名
    [postgres@localhost ~]$ psql -h localhost -p 5432 -U postgres

设置postgres用户密码

[postgres@localhost ~]$ psql
postgres=# ALTER USER postgres WITH encrypted PASSWORD ‘new password‘;
postgres=# q
[postgres@localhost ~]$ psql -h localhost -p 5432 -U postgres

设置开机自启动

设置启动配置

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

# Location of database directory
Environment=PGDATA=/mnt/db1/pgdata/pgsql/

# 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=/usr/pgsql-11/bin/postgresql-11-check-db-dir ${PGDATA}
ExecStart=/usr/local/pgsql/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
# ExecStart=/usr/local/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
# ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
# ExecReload=/usr/local/pgsql/bin/pg_ctl reload -D ${PGDATA} -s
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

添加可执行权限

[root@localhost postgres]# chmod 754 /usr/lib/systemd/system/postgresql-11.service

设置开机自启动

自动启动
[root@localhost postgres]# systemctl enable postgresql-11.service

启动
[root@localhost postgres]# systemctl start postgresql-11.service
停止某服务
[root@localhost postgres]# systemctl stop postgresql-11.service
不自动启动
[root@localhost postgres]# systemctl disable postgresql-11.service
检查服务状态（服务详细信息）
systemctl status postgresql-11.service
检查服务状态（仅显示是否Active）
systemctl is-active postgresql-11.service
显示所有已启动的服务
systemctl list-units --type=service

主机参数配置

配置OS内核参数

1. sysctl

注意某些参数，根据内存大小配置（已说明）

# vi /etc/sysctl.conf

# add by digoal.zhou
fs.aio-max-nr = 1048576
fs.file-max = 76724600
kernel.core_pattern= /data01/corefiles/core_%e_%u_%t_%s.%p         
# /data01/corefiles事先建好，权限777，如果是软链接，对应的目录修改为777
kernel.sem = 4096 2147483647 2147483646 512000    
# 信号量, ipcs -l 或 -u 查看，每16个进程一组，每组信号量需要17个信号量。
kernel.shmall = 107374182      
# 所有共享内存段相加大小限制(建议内存的80%)
kernel.shmmax = 274877906944   
# 最大单个共享内存段大小(建议为内存一半), >9.2的版本已大幅降低共享内存的使用
kernel.shmmni = 819200         
# 一共能生成多少共享内存段，每个PG数据库集群至少2个共享内存段
net.core.netdev_max_backlog = 10000
net.core.rmem_default = 262144       
# The default setting of the socket receive buffer in bytes.
net.core.rmem_max = 4194304          
# The maximum receive socket buffer size in bytes
net.core.wmem_default = 262144       
# The default setting (in bytes) of the socket send buffer.
net.core.wmem_max = 4194304          
# The maximum send socket buffer size in bytes.
net.core.somaxconn = 4096
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1    
# 开启SYN Cookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN***
net.ipv4.tcp_timestamps = 1    
# 减少time_wait
net.ipv4.tcp_tw_recycle = 0    
# 如果=1则开启TCP连接中TIME-WAIT套接字的快速回收，但是NAT环境可能导致连接失败，建议服务端关闭它
net.ipv4.tcp_tw_reuse = 1      
# 开启重用。允许将TIME-WAIT套接字重新用于新的TCP连接
net.ipv4.tcp_max_tw_buckets = 262144
net.ipv4.tcp_rmem = 8192 87380 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
net.nf_conntrack_max = 1200000
net.netfilter.nf_conntrack_max = 1200000
vm.dirty_background_bytes = 409600000       
#  系统脏页到达这个值，系统后台刷脏页调度进程 pdflush（或其他） 自动将(dirty_expire_centisecs/100）秒前的脏页刷到磁盘
vm.dirty_expire_centisecs = 3000             
#  比这个值老的脏页，将被刷到磁盘。3000表示30秒。
vm.dirty_ratio = 95                          
#  如果系统进程刷脏页太慢，使得系统脏页超过内存 95 % 时，则用户进程如果有写磁盘的操作（如fsync, fdatasync等调用），则需要主动把系统脏页刷出。
#  有效防止用户进程刷脏页，在单机多实例，并且使用CGROUP限制单实例IOPS的情况下非常有效。  
vm.dirty_writeback_centisecs = 100            
#  pdflush（或其他）后台刷脏页进程的唤醒间隔， 100表示1秒。
vm.mmap_min_addr = 65536
vm.overcommit_memory = 0     
#  在分配内存时，允许少量over malloc, 如果设置为 1, 则认为总是有足够的内存，内存较少的测试环境可以使用 1 .  
vm.overcommit_ratio = 90     
#  当overcommit_memory = 2 时，用于参与计算允许指派的内存大小。
vm.swappiness = 0            
#  关闭交换分区
vm.zone_reclaim_mode = 0     
# 禁用 numa, 或者在vmlinux中禁止. 
net.ipv4.ip_local_port_range = 40000 65535    
# 本地自动分配的TCP, UDP端口号范围
fs.nr_open=20480000
# 单个进程允许打开的文件句柄上限
net.ipv4.tcp_max_syn_backlog = 16384
net.core.somaxconn = 16384

# 以下参数请注意
# vm.extra_free_kbytes = 4096000
# vm.min_free_kbytes = 2097152 # vm.min_free_kbytes 建议每32G内存分配1G vm.min_free_kbytes
# 如果是小内存机器，以上两个值不建议设置
# vm.nr_hugepages = 66536    
#  建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize
# vm.lowmem_reserve_ratio = 1 1 1
# 对于内存大于64G时，建议设置，否则建议默认值 256 256 32

生效

sysctl -p

配置OS资源限制

# vi /etc/security/limits.conf

# nofile超过1048576的话，一定要先将sysctl的fs.nr_open设置为更大的值，并生效后才能继续设置nofile.

* soft    nofile  1024000
* hard    nofile  1024000
* soft    nproc   unlimited
* hard    nproc   unlimited
* soft    core    unlimited
* hard    core    unlimited
* soft    memlock unlimited
* hard    memlock unlimited

最好在关注一下/etc/security/limits.d目录中的文件内容，会覆盖limits.conf的配置。

已有进程的ulimit请查看/proc/pid/limits，例如

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            10485760             unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             11286                11286                processes 
Max open files            1024                 4096                 files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       11286                11286                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us
如果你要启动其他进程，建议退出SHELL再进一遍，确认ulimit环境配置已生效，再启动。

配置OS防火墙

（建议按业务场景设置，我这里先清掉）

iptables -F

配置范例

# 私有网段
-A INPUT -s 192.168.0.0/16 -j ACCEPT
-A INPUT -s 10.0.0.0/8 -j ACCEPT
-A INPUT -s 172.16.0.0/16 -j ACCEPT

selinux

如果没有这方面的需求，建议禁用

# vi /etc/sysconfig/selinux 

SELINUX=disabled
SELINUXTYPE=targeted

关闭不必要的OS服务

chkconfig --list|grep on  
关闭不必要的,例如 
chkconfig iscsi off

部署文件系统

注意SSD对齐，延长寿命，避免写放大。

parted -s /dev/sda mklabel gpt
parted -s /dev/sda mkpart primary 1MiB 100%

格式化(如果你选择ext4的话)

mkfs.ext4 /dev/sda1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L u01

建议使用的ext4 mount选项

# vi /etc/fstab

LABEL=u01 /u01     ext4        defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback    0 0

# mkdir /u01
# mount -a

设置SSD盘的调度为deadline

如果不是SSD的话，还是使用CFQ，否则建议使用DEADLINE。

临时设置(比如sda盘)

echo deadline > /sys/block/sda/queue/scheduler
永久设置

编辑grub文件修改块设备调度策略

vi /boot/grub.conf

elevator=deadline
注意，如果既有机械盘，又有SSD，那么可以使用/etc/rc.local，对指定磁盘修改为对应的调度策略。

关闭透明大页、numa

加上前面的默认IO调度，如下

vi /boot/grub.conf

elevator=deadline numa=off transparent_hugepage=never

Postgresql11.5单机部署标准化文档

码农公寓

创建用户

创建目录

设置环境变量

安装

依赖包安装

源码编译安装

make 和make install

初始化数据库

修改配置参数

修改客户端认证方式

设置防火墙规则

启动数据库

连接测试

设置postgres用户密码

设置开机自启动

设置启动配置

添加可执行权限

设置开机自启动

主机参数配置

配置OS内核参数

1. sysctl

生效

配置OS资源限制

配置OS防火墙

selinux

关闭不必要的OS服务

部署文件系统

格式化(如果你选择ext4的话)

建议使用的ext4 mount选项

设置SSD盘的调度为deadline

关闭透明大页、numa

相关文章