服务器环境为 CentOS 7.3 和 PostgreSQL 9.6.2。通常我们建议使用最新的 PostgreSQL 版本。首先在 PostgreSQL 官方网站下载最新版本的 PostgreSQL 源代码包。
$ yum install -y gcc gcc-c++ make readline-devel zlib-devel openssl-devel perl-devel libxml2-devel systemd-devel
$ tar -xzvf postgresql-9.6.2.tar.gz
$ cd postgresql-9.6.2
$ ./configure --prefix=/usr/local/postgresql-9.6 --with-systemd
$ make
$ make install
$ useradd -m -d /var/pgsql -c "postgresql databases user" postgres
$ mkdir -p /var/pgsql/data
$ chown -R postgres:postgres /var/pgsql/data
$ su - postgres
$ /usr/local/postgresql-9.6/bin/initdb --pgdata=/var/pgsql/data --encoding=UTF8
pg_hba.conf
host all all 0.0.0.0/0 md5
postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 128
shared_buffers = 256MB
work_mem = 16MB
maintenance_work_mem = 64MB
effective_cache_size = 256MB
每个配置选项的值需要根据服务器的硬件配置而定,比如 CPU、内存、磁盘等等
$ /usr/local/postgresql-9.6/bin/pg_ctl -D /var/pgsql/pgdata -l /tmp/postgresql.log start
[Unit]
Description=PostgreSQL database server
After=network.target
[Service]
Type=forking
User=postgres
Group=postgres
LimitNPROC=65535
LimitNOFILE=102400
OOMScoreAdjust=-1000
TimeoutSec=300
ExecStart=/usr/local/postgresql-9.6/bin/pg_ctl -D /var/pgsql/data -s -w -t 300 start
ExecStop=/usr/local/postgresql-9.6/bin/pg_ctl -D /var/pgsql/data -s -m fast stop
ExecReload=/usr/local/postgresql-9.6/bin/pg_ctl -D /var/pgsql/data -s reload
[Install]
WantedBy=multi-user.target
将以上内容保存为 postgresql.service 文件,然后将其复制到 /etc/systemd/system 目录下,并开启 postgresql.service 服务
$ systemctl enable postgresql.service
/etc/sysctl.conf
fs.nr_open = 2048000
fs.file-max = 1024000
fs.aio-max-nr = 1048576
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
/etc/security/limits.conf
* soft nofile 1024000
* hard nofile 1024000
* soft nproc unlimited
* hard nproc unlimited
* soft core unlimited
* hard core unlimited
* soft memlock unlimited
* hard memlock unlimited
By typefo typefo@qq.com Update: 2017-04-23 本文档使用 CC-BY 4.0 协议