Linux下PostgreSQL安装部署指南

背景数据库的安装一直以来都挺复杂的，特别是Oracle，现在身边都还有安装Oracle数据库赚外快的事情。PostgreSQL其实安装很简单，但是那仅仅是可用，并不是好用。很多用户使用默认的方法安装好数据库之后，然后测试一通性能，发现性能不行就不用了。原因不用说，多方面没有优化的结果。PostgreSQL数据库为了适应更多的场景能使用，默认的参数都设得非常保守，通常需要优化，比如检查点，SHARED BUFFER等。本文将介绍一下PostgreSQL on Linux的最佳部署方法，其实在我的很多文章中都有相关的内容，但是没有总结成一篇文档。

安装常用包

# 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

配置OS内核参数

# vi /etc/sysctl.conf# add by digoal.zhoufs.aio-max-nr = 1048576fs.file-max = 76724600kernel.core_pattern= /data01/corefiles/core_%e_%u_%t_%s.%p # /data01/corefiles事先建好，权限777kernel.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 = 10000net.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 bytesnet.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 = 4096net.ipv4.tcp_max_syn_backlog = 4096net.ipv4.tcp_keepalive_intvl = 20net.ipv4.tcp_keepalive_probes = 3net.ipv4.tcp_keepalive_time = 60net.ipv4.tcp_mem = 8388608 12582912 16777216net.ipv4.tcp_fin_timeout = 5net.ipv4.tcp_synack_retries = 2net.ipv4.tcp_syncookies = 1# 开启SYN Cookies。当出现SYN等待队列溢出时，启用cookie来处理，可防范少量的SYN攻击net.ipv4.tcp_timestamps = 1# 减少time_waitnet.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 = 262144net.ipv4.tcp_rmem = 8192 87380 16777216net.ipv4.tcp_wmem = 8192 65536 16777216net.nf_conntrack_max = 1200000net.netfilter.nf_conntrack_max = 1200000vm.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.extra_free_kbytes = 4096000vm.min_free_kbytes = 2097152vm.mmap_min_addr = 65536vm.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端口号范围# vm.nr_hugepages = 66536#建议shared buffer设置超过64GB时 使用大页，页大小 /proc/meminfo Hugepagesize

配置OS资源限制

# vi /etc/security/limits.conf* softnofile1024000* hardnofile1024000* softnproc unlimited* hardnproc unlimited* softcoreunlimited* hardcoreunlimited* softmemlock unlimited* hardmemlock unlimited

配置OS防火墙

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

selinux

如果没有这方面的需求，建议禁用# vi /etc/sysconfig/selinux SELINUX=disabledSELINUXTYPE=targeted

部署文件系统

注意SSD对齐，延长寿命，避免写放大。parted -s /dev/sda mklabel gptparted -s /dev/sda mkpart primary 1MiB 100%格式化mkfs.ext4 /dev/sda1 -m 0 -O extent,uninit_bg -E lazy_itable_init=1 -T largefile -L u01建议使用的ext4 mount选项# vi /etc/fstabLABEL=u01 /u01 ext4defaults,noatime,nodiratime,nodelalloc,barrier=0,data=writeback0 0# mount -a

编译器

建议使用较新的编译器，安装 gcc 6.2.0 略

cd ~tar -jxvf gcc6.2.0.tar.bz2tar -jxvf python2.7.12.tar.bz2# vi /etc/ld.so.conf/home/digoal/gcc6.2.0/lib/home/digoal/gcc6.2.0/lib64/home/digoal/python2.7.12/lib# ldconfig

环境变量

# vi ~/env_pg.shexport PS1="$USER@`/bin/hostname -s`-> "export PGPORT=$1export PGDATA=/$2/digoal/pg_root$PGPORTexport LANG=en_US.utf8export PGHOME=/home/digoal/pgsql9.6export LD_LIBRARY_PATH=/home/digoal/gcc6.2.0/lib:/home/digoal/gcc6.2.0/lib64:/home/digoal/python2.7.12/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport PATH=/home/digoal/gcc6.2.0/bin:/home/digoal/python2.7.12/bin:/home/digoal/cmake3.6.3/bin:$PGHOME/bin:$PATH:.export DATE=`date +"%Y%m%d%H%M"`export MANPATH=$PGHOME/share/man:$MANPATHexport PGHOST=$PGDATAexport PGUSER=postgresexport PGDATABASE=postgresalias rm="rm -i"alias ll="ls -lh"unalias vi

编译PostgreSQL

建议使用NAMED_POSIX_SEMAPHORES

. ~/env_pg.sh 1921 u01cd postgresql-9.6.1export USE_NAMED_POSIX_SEMAPHORES=1LIBS=-lpthread CC="/home/digoal/gcc6.2.0/bin/gcc" CFLAGS="-O3 -flto" ./configure --prefix=/home/digoal/pgsql9.6LIBS=-lpthread CC="/home/digoal/gcc6.2.0/bin/gcc" CFLAGS="-O3 -flto" make world -j 64LIBS=-lpthread CC="/home/digoal/gcc6.2.0/bin/gcc" CFLAGS="-O3 -flto" make install-world

初始化数据库集群

pg_xlog建议放在IOPS最好的分区。. ~/env_pg.sh 1921 u01initdb -D $PGDATA -E UTF8 --locale=C -U postgres -X /u02/digoal/pg_xlog$PGPORT

配置postgresql.conf

以PostgreSQL 9.6, 512G内存主机为例

最佳到文件末尾即可，重复的会以末尾的作为有效值。$ vi postgresql.conflisten_addresses = "0.0.0.0"port = 1921max_connections = 5000unix_socket_directories = "."tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 10shared_buffers = 128GBmaintenance_work_mem = 4GBdynamic_shared_memory_type = posixvacuum_cost_delay = 0bgwriter_delay = 10msbgwriter_lru_maxpages = 1000bgwriter_lru_multiplier = 10.0bgwriter_flush_after = 0max_parallel_workers_per_gather = 0old_snapshot_threshold = -1backend_flush_after = 0wal_level = replicasynchronous_commit = offfull_page_writes = onwal_buffers = 1GBwal_writer_delay = 10mswal_writer_flush_after = 0checkpoint_timeout = 30minmax_wal_size = 256GBmin_wal_size = 64GBcheckpoint_completion_target = 0.05checkpoint_flush_after = 0max_wal_senders = 5random_page_cost = 1.0parallel_tuple_cost = 0parallel_setup_cost = 0min_parallel_relation_size = 0effective_cache_size = 300GBforce_parallel_mode = offlog_destination = "csvlog"logging_collector = onlog_truncate_on_rotation = onlog_checkpoints = onlog_connections = onlog_disconnections = onlog_error_verbosity = verboselog_timezone = "PRC"autovacuum = onlog_autovacuum_min_duration = 0autovacuum_max_workers = 16autovacuum_naptime = 15sautovacuum_vacuum_scale_factor = 0.02autovacuum_analyze_scale_factor = 0.01vacuum_freeze_table_age = 1500000000vacuum_multixact_freeze_table_age = 1500000000datestyle = "iso, mdy"timezone = "PRC"lc_messages = "C"lc_monetary = "C"lc_numeric = "C"lc_time = "C"default_text_search_config = "pg_catalog.english"shared_preload_libraries="pg_stat_statements"

配置pg_hba.conf

避免不必要的访问，开放允许的访问，建议务必使用密码访问。$ vi pg_hba.confhost all all 0.0.0.0/0 md5

启动数据库

pg_ctl start好了，你的PostgreSQL数据库基本上部署好了，可以愉快的玩耍了。------------------------------------华丽丽的分割线------------------------------------在CentOS 6.5上编译安装PostgreSQL 9.3数据库 http://www.linuxidc.com/Linux/2016-06/132272.htmCentOS 6.3环境下yum安装PostgreSQL 9.3 http://www.linuxidc.com/Linux/2014-05/101787.htmPostgreSQL缓存详述 http://www.linuxidc.com/Linux/2013-07/87778.htmWindows平台编译 PostgreSQL http://www.linuxidc.com/Linux/2013-05/85114.htmUbuntu下LAPP（Linux+Apache+PostgreSQL+PHP）环境的配置与安装 http://www.linuxidc.com/Linux/2013-04/83564.htmUbuntu上的phppgAdmin安装及配置 http://www.linuxidc.com/Linux/2011-08/40520.htmCentOS平台下安装PostgreSQL9.3 http://www.linuxidc.com/Linux/2014-05/101723.htmPostgreSQL配置Streaming Replication集群 http://www.linuxidc.com/Linux/2014-05/101724.htm------------------------------------华丽丽的分割线------------------------------------PostgreSQL 的详细介绍：请点这里
PostgreSQL 的下载地址：请点这里本文永久更新链接地址