centos 6.8 + postgresql 9.6 + make
os 调整
# vi /etc/rc.local
#禁用透明大页
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
#修改 io scheduler为deadline
echo deadline > /sys/block/sda/queue/scheduler
修改后添加执行权限 chmod u+x /etc/rc.d/rc.local
或者修改grub
# vi /etc/grub.conf
elevator=deadline numa=off transparent_hugepage=never os 关闭selinux
永久修改
# vi /etc/sysconfig/selinux
SELINUX=DISABLED
或者
# vi /etc/selinux/config
SELINUX=DISABLEDos 资源限制
修改文件句柄打开数和用户最大进程数：
修改limit和max user processes
open files (-n) 40960
max user processes (-u) 7776
# vi /etc/security/limits.conf
root soft nofile 655360 # The maximum number of open file descriptors
root hard nofile 655360
postgres soft nofile 655360
postgres hard nofile 655360
root soft nproc 655360 # The maximum number of processes available to a single user
root hard nproc 655360
postgres soft nproc 655360
postgres hard nproc 655360
* soft memlock unlimited # The maximum size that may be locked into memory
* hard memlock unlimited
* soft core unlimited
* hard core unlimited
* soft stack unlimited
* hard stack unlimited os 修改内核参数
# vi /etc/sysctl.conf
#kernel.shmall = 4294967296
#kernel.shmmax = 4398046511104
kernel.shmmni = 4096
#kernel.sem = 250 32000 100 128
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
vm.swappiness = 1
vm.nr_hugepages = 500存盘退出后运行 sysctl -p 立即生效
os 关闭没用的服务
chkconfig autofs off
chkconfig acpid off
chkconfig sendmail off
chkconfig cups-config-daemon off
chkconfig cpus off
chkconfig lm_sensors off
chkconfig gpm off
chkconfig openibd off
chkconfig iiim off
chkconfig pcmcia off
chkconfig cpuspeed off
chkconfig nfslock off
chkconfig ip6tables off
chkconfig rpcidmapd off
chkconfig apmd off
chkconfig sendmail off
chkconfig arptables_jf off
chkconifg microcode_ctl off
chkconfig rpcgssd off
chkconfig iptables off启动服务检查：
chkconfig --list |grep 3:on确保以下服务正常
crond
sshd
network
sysstat
禁止ipv6：
vi /etc/modprobe.d/dist.confalias net-pf-10 off
alias ipv6 off
vi /etc/sysconfig/network
NETWORKING_IPV6=noos yum安装依赖包
配置好cdrom yum源
# vi /etc/yum.conf
[base]
name=pyb yum
baseurl=file:///mnt/cdrombak/
gpgkey=file:///mnt/cdrombak/RPM-GPG-KEY-CentOS-6安装依赖包
# yum install git tree sysstat wget make cmake gcc gcc-c++ lrzsz flex bison unixODBC
# yum install smartmontools coreutils glib2 perl perl-devel perl-ExtUtils-Embed zlib zlib-devel openldap openldap-devel flex readline readline-devel systemtap systemtap-sdt-devel python python-devel tcl tcl-devel sgml-common docbook* stylesheets openjade sgml-tools libxslt libxslt-devel libxml2 libxml2-devel openssl openssl-devel pam pam-devel libicu libicu-devel uuid uuid-devel
os 创建用户目录
make 时需要事先创建 postgres 用户及相关的目录
# groupadd -g 10000 postgres
# useradd -g postgres -u 10000 postgres
# usermod -g postgres postgres
# mkdir -p /usr/pgsql-9.6
# mkdir -p /var/lib/pgsql/9.6/data
# mkdir -p /tmp/pgsoftware
# chown -R postgres:postgres /usr/pgsql-9.6
# chown -R postgres:postgres /var/lib/pgsql修改 postgres 的环境变量
# vi /home/postgres/.bash_profile
export PGHOME=/usr/pgsql-9.6
export PGDATA=/var/lib/pgsql/9.6/data
export MANPATH=$PGHOME/share/man:$MANPATH
export LD_LIBRARY_PATH=$PGHOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export PATH=$PGHOME/bin:$PATH
export TEMP=/tmp
export TMPDIR=/tmp
export PS1="\[\e[32;1m\][\u@\h \W]$>\[\e[0m\]"
os 重启生效
# reboot下载源码包,并解压,编译,安装
下载地址 https://www.postgresql.org
Files
postgresql-9.6.3.tar.bz2 postgresql-9.6.3.tar.bz2 2017-05-08 21:30:12 18.6 MB
postgresql-9.6.3.tar.bz2.md5 postgresql-9.6.3.tar.bz2.md5 2017-05-08 21:30:12 59 bytes
postgresql-9.6.3.tar.bz2.sha256 postgresql-9.6.3.tar.bz2.sha256 2017-05-08 21:30:12 91 bytes
postgresql-9.6.3.tar.gz postgresql-9.6.3.tar.gz 2017-05-08 21:30:1224.4 MB
postgresql-9.6.3.tar.gz.md5 postgresql-9.6.3.tar.gz.md5 2017-05-08 21:30:12 58 bytes
postgresql-9.6.3.tar.gz.sha256 postgresql-9.6.3.tar.gz.sha256 2017-05-08 21:30:12 90 bytes# cd /tmp/pgsoftware
# ls -l
total 24940
-rw-r--r-- 1 root 25536998 May 22 17:43 postgresql-9.6.3.tar.gz
$ tar -zxvf ./postgresql-9.6.3.tar.gz 开始编译安装三把斧
$ ./configure --help
`configure' configures PostgreSQL 9.6.9 to adapt to many kinds of systems.
Usage: ./configure [OPTION]... [VAR=VALUE]...
To assign environment variables (e.g., CC, CFLAGS...), specify them as
VAR=VALUE. See below for descriptions of some of the useful variables.
Defaults for the options are specified in brackets.
Configuration:
-h, --help display this help and exit
--help=short display options specific to this package
--help=recursive display the short help of all the included packages
-V, --version display version information and exit
-q, --quiet, --silent do not print `checking ...' messages
--cache-file=FILE cache test results in FILE [disabled]
-C, --config-cache alias for `--cache-file=config.cache'
-n, --no-create do not create output files
--srcdir=DIR find the sources in DIR [configure dir or `..']
Installation directories:
--prefix=PREFIX install architecture-independent files in PREFIX
[/usr/local/pgsql]
--exec-prefix=EPREFIX install architecture-dependent files in EPREFIX
[PREFIX]
By default, `make install' will install all the files in
`/usr/local/pgsql/bin', `/usr/local/pgsql/lib' etc. You can specify
an installation prefix other than `/usr/local/pgsql' using `--prefix',
for instance `--prefix=$HOME'.
For better control, use the options below.
Fine tuning of the installation directories:
--bindir=DIR user executables [EPREFIX/bin]
--sbindir=DIR system admin executables [EPREFIX/sbin]
--libexecdir=DIR program executables [EPREFIX/libexec]
--sysconfdir=DIR read-only single-machine data [PREFIX/etc]
--sharedstatedir=DIR modifiable architecture-independent data [PREFIX/com]
--localstatedir=DIR modifiable single-machine data [PREFIX/var]
--libdir=DIR object code libraries [EPREFIX/lib]
--includedir=DIR C header files [PREFIX/include]
--oldincludedir=DIR C header files for non-gcc [/usr/include]
--datarootdir=DIR read-only arch.-independent data root [PREFIX/share]
--datadir=DIR read-only architecture-independent data [DATAROOTDIR]
--infodir=DIR info documentation [DATAROOTDIR/info]
--localedir=DIR locale-dependent data [DATAROOTDIR/locale]
--mandir=DIR man documentation [DATAROOTDIR/man]
--docdir=DIR documentation root [DATAROOTDIR/doc/postgresql]
--htmldir=DIR html documentation [DOCDIR]
--dvidir=DIR dvi documentation [DOCDIR]
--pdfdir=DIR pdf documentation [DOCDIR]
--psdir=DIR ps documentation [DOCDIR]
System types:
--build=BUILD configure for building on BUILD [guessed]
--host=HOST cross-compile to build programs to run on HOST [BUILD]
Optional Features:
--disable-option-checking ignore unrecognized --enable/--with options
--disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no)
--enable-FEATURE[=ARG] include FEATURE [ARG=yes]
--disable-integer-datetimes
disable 64-bit integer date/time support
--enable-nls[=LANGUAGES]
enable Native Language Support
--disable-rpath do not embed shared library search path in
executables
--disable-spinlocks do not use spinlocks
--disable-atomics do not use atomic operations
--enable-debug build with debugging symbols (-g)
--enable-profiling build with profiling enabled
--enable-coverage build with coverage testing instrumentation
--enable-dtrace build with DTrace support
--enable-tap-tests enable TAP tests (requires Perl and IPC::Run)
--enable-depend turn on automatic dependency tracking
--enable-cassert enable assertion checks (for debugging)
--disable-thread-safety disable thread-safety in client libraries
--disable-largefile omit support for large files
--disable-float4-byval disable float4 passed by value
--disable-float8-byval disable float8 passed by value
Optional Packages:
--with-PACKAGE[=ARG] use PACKAGE [ARG=yes]
--without-PACKAGE do not use PACKAGE (same as --with-PACKAGE=no)
--with-extra-version=STRING
append STRING to version
--with-template=NAME override operating system template
--with-includes=DIRS look for additional header files in DIRS
--with-libraries=DIRS look for additional libraries in DIRS
--with-libs=DIRS alternative spelling of --with-libraries
--with-pgport=PORTNUM set default port number [5432]
--with-blocksize=BLOCKSIZE
set table block size in kB [8]
--with-segsize=SEGSIZE set table segment size in GB [1]
--with-wal-blocksize=BLOCKSIZE
set WAL block size in kB [8]
--with-wal-segsize=SEGSIZE
set WAL segment size in MB [16]
--with-CC=CMD set compiler (deprecated)
--with-tcl build Tcl modules (PL/Tcl)
--with-tclconfig=DIR tclConfig.sh is in DIR
--with-perl build Perl modules (PL/Perl)
--with-python build Python modules (PL/Python)
--with-gssapi build with GSSAPI support
--with-krb-srvnam=NAME default service principal name in Kerberos (GSSAPI)
[postgres]
--with-pam build with PAM support
--with-bsd-auth build with BSD Authentication support
--with-ldap build with LDAP support
--with-bonjour build with Bonjour support
--with-openssl build with OpenSSL support
--with-selinux build with SELinux support
--with-systemd build with systemd support
--without-readline do not use GNU Readline nor BSD Libedit for editing
--with-libedit-preferred
prefer BSD Libedit over GNU Readline
--with-uuid=LIB build contrib/uuid-ossp using LIB (bsd,e2fs,ossp)
--with-ossp-uuid obsolete spelling of --with-uuid=ossp
--with-libxml build with XML support
--with-libxslt use XSLT support when building contrib/xml2
--with-system-tzdata=DIR
use system time zone data in DIR
--without-zlib do not use Zlib
--with-gnu-ld assume the C compiler uses GNU ld [default=no]
Some influential environment variables:
CC C compiler command
CFLAGS C compiler flags
LDFLAGS linker flags, e.g. -L<lib dir> if you have libraries in a
nonstandard directory <lib dir>
LIBS libraries to pass to the linker, e.g. -l<library>
CPPFLAGS (Objective) C/C++ preprocessor flags, e.g. -I<include dir> if
you have headers in a nonstandard directory <include dir>
CPP C preprocessor
LDFLAGS_EX extra linker flags for linking executables only
LDFLAGS_SL extra linker flags for linking shared libraries only
DOCBOOKSTYLE
location of DocBook stylesheets
Use these variables to override the choices made by `configure' or to help
it to find libraries and programs with nonstandard names/locations.
Report bugs to <pgsql-bugs@postgresql.org>.在一次postgresql 大会上，平安科技的汪洋大神分享了他们在实际中添加的一个必要的参数，大家参考下。
--with-segsize
单个文件大小，建议10G以上，避免文件数太多
--with-wal-segsize
单个WAL文件大小，默认时 16MB
--with-libedit-preferred
--with-python
--with-uuid就是 configure 时，添加如下参数：
--with-libedit-preferred --with-python --with-uuid=ossp --with-segsize=10 --with-blocksize=16 --with-wal-segsize=64 --with-wal-blocksize=16
下面是本地的操作
# cd /tmp/pgsoftware/postgresql-9.6.3
# ./configure --prefix=/usr/pgsql-9.6 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-dtrace --enable-debug --with-libedit-preferred --with-uuid=ossp --with-segsize=10 --with-blocksize=16 --with-wal-segsize=64 --with-wal-blocksize=16
# make world
# make install-world
# su - postgres
$ cd /usr/pgsql-9.6
$ ls -l
total 16
drwxrwxr-x 2 root root 4096 May 22 17:59 bin
drwxrwxr-x 6 root root 4096 May 22 17:59 include
drwxrwxr-x 4 root root 4096 May 22 17:59 lib
drwxrwxr-x 6 root root 4096 May 22 17:59 share一旦软件构建完成并安装好，你可以通过pg_config命令获得一份PostgreSQL系统的配置：
$ pg_config --bindir
$ pg_config --includedir
$ pg_config --libdir
$ pg_config --configure
$ pg_config --version运行 initdb 工具，创建数据库
$ /usr/pgsql-9.6/bin/initdb -D /var/lib/pgsql/9.6/data
~~~~~~脚本最后输出如下
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:
/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data -l logfile start
启动 postgresql 服务
/usr/pgsql-9.6/bin/pg_ctl -D /var/lib/pgsql/9.6/data start
尝试下是否工作
/usr/pgsql-9.6/bin/createdb test
/usr/pgsql-9.6/bin/psql test参考 /u01/software/postgresql-9.6.3/INSTALL 的Short Version 部分
参考，参考
__________________________________________________________________
Short Version
./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test
The long version is the rest of this document.
配置 pg_hba.conf
$ vi /var/lib/pgsql/9.6/data/pg_hba.conf
#peiyb add
host all all 192.168.56.0/24 password配置 postgresql.auto.conf
alter system set listen_addresses = '*';
alter system set port = '5432';
alter system set shared_buffers = '1024MB';
alter system set huge_pages = 'try';
alter system set work_mem = '10MB' ;
alter system set maintenance_work_mem = '256MB';
alter system set effective_cache_size = '2GB';
alter system set track_activity_query_size = 4096;
alter system set checkpoint_completion_target = 0.5;
alter system set superuser_reserved_connections = '10';
alter system set max_connections=600;
alter system set max_wal_size = '32GB';
alter system set min_wal_size = '2GB';
alter system set log_destination = 'csvlog';
alter system set logging_collector = on;
alter system set log_line_prefix = '%t ';
alter system set log_directory = '/var/log/postgresql';
alter system set log_filename = 'postgresql-%Y-%m-%d.log';
alter system set log_file_mode = '0600';
alter system set log_truncate_on_rotation = 'off';
alter system set log_rotation_age = '1d';
alter system set log_rotation_size = 0;
alter system set log_min_messages = notice;
alter system set log_min_error_statement = notice;
alter system set log_min_duration_statement = 5000;
alter system set log_connections = 'on';
alter system set log_disconnections = 'on';
alter system set log_checkpoints = 'on';
alter system set log_duration = 'on';
alter system set log_hostname = 'on';
alter system set log_lock_waits = 'on';
alter system set log_statement = 'none';
alter system set log_timezone = 'PRC';
alter system set lc_messages = 'C';
会话级别修改部分参数
set work_mem = ‘6MB’;
set work_mem to ‘6MB’;
show work_mem;
reset work_mem;
启动 pgsql
$ pg_ctl start
$ pg_ctl start -D $PGDATA
$ pg_ctl start -D $PGDATA -o "-c config_file=/etc/postgresql/9.6/main/postgresql.conf"
$ pg_ctl stop -m smart -D $PGDATA
$ pg_ctl stop -m fast -D $PGDATA
$ pg_ctl stop -m immediate -D $PGDATA
$ pg_ctl stop
$ pg_ctl restart查看 pgsql 进程
# ps -ef|grep 20174
postgres 20174 1 0 18:11 ? 00:00:00 /usr/pgsql-9.6/bin/postgres -D /var/lib/pgsql/9.6/data
postgres 20176 20174 0 18:11 ? 00:00:00 postgres: checkpointer process
postgres 20177 20174 0 18:11 ? 00:00:00 postgres: writer process
postgres 20178 20174 0 18:11 ? 00:00:00 postgres: wal writer process
postgres 20179 20174 0 18:11 ? 00:00:00 postgres: autovacuum launcher process
postgres 20180 20174 0 18:11 ? 00:00:00 postgres: stats collector process 
