os：centos 7.5 1.posgres软件目录说明： 软件安装目录:/opt/pg10/ 归档日志文件目录:/pgdata/10/archive_wal 备份目录：/pgdata/10/backup 脚本目录：/pgdata/10/script 数据文件目录:/pgdata/10/data PID文件目录:/pgdata/10/run 后台日志目录:/pgdata/10/log 配置文件目录:/pgdata/10/etc
2.创建目录和权限： mkdir -p /pgdata/10/{archive_wal,backup,script,data,run,log,etc} chown -R postgres:postgres /pgdata/10/
3.安装依赖软件： 安装依赖软件： yum -y install vim make cmake gcc gcc-c++ glibc lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make flex bison perl-YAML* perl-ExtUtils*
4.添加用户： useradd postgres echo postgres | passwd postgres --stdin
5.设置环境变量： su - postgres vi .bash_profile export PGLIB=/opt/pg10/lib export PGDATA=/pgdata/10/data export PATH=$PATH:/opt/pg10/bin export PGHOST=/pgdata/10/run
6.编译安装软件： tar xvzf postgresql-10.0.tar.gz cd postgresql-10.0 ./configure --prefix=/opt/pg10/ --sysconfdir=/pgdata/10/etc --with-pgport=5432 --with-perl --with-tcl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --with-blocksize=8 --enable-thread-safety --with-wal-segsize=128 make make install
7.安装扩展插件： a.pg_stat_statements cd /root/postgresql-10.0/contrib/pg_stat_statements make && sudo make install
psql psql (10.0) Type "help" for help.
postgres=# create extension pg_stat_statements; CREATE EXTENSION
SELECT pg_stat_statements_reset(); 清空pg_stat_statements
b.pg_pathman root用户执行： export PGLIB=/opt/pg10/lib export PGDATA=/pgdata/10/data export PATH=$PATH:/opt/pg10/bin export PGHOST=/pgdata/10/run 编译安装： cd /opt/pg10/share/postgresql/extension git clone https://github.com/postgrespro/pg_pathman.git cd pg_pathman make USE_PGXS=1 make USE_PGXS=1 install
psql psql (10.0) Type "help" for help.
postgres=# create extension pg_pathman ; CREATE EXTENSION
8.设置权限： chown -R postgres:postgres /pgdata/10/
9.初始化数据库 su - postgres
cd /opt/pg10/bin ./initdb -D /pgdata/10/data/ -E UTF-8 --locale=en_US.UTF-8 -k
10.编辑配置文件： cp pg_hba.conf /pgdata/10/etc/ cp pg_ident.conf /pgdata/10/etc/
vi /pgdata/10/data/postgresql.conf
data_directory = '/pgdata/10/data'
hba_file = '/pgdata/10/etc/pg_hba.conf'
ident_file = '/pgdata/10/etc/pg_ident.conf'
external_pid_file = '/pgdata/10/run/'
listen_addresses = '*'
port = 5432
max_connections = 5000
unix_socket_directories = '/pgdata/10/run'
ssl = false
#ssl_cert_file = '/etc/pgsql/server.crt'
#ssl_key_file = '/etc/ssl/pgsql/server.key'
shared_buffers = 1GB
max_prepared_transactions = 3000
work_mem = 4MB
maintenance_work_mem = 512MB
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
wal_level = hot_standby
full_page_writes = on
wal_log_hints = off
archive_mode = on
archive_command = 'cp -i %p /pgdata/10/archive_wal/%f </dev/null'
archive_timeout = 86400
max_wal_senders = 25
wal_keep_segments = 100
hot_standby = on
effective_cache_size = 110GB
logging_collector = on
log_directory = '/pgdata/10/log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 2000
log_line_prefix = '%t [%p-%l] %q%u@%d '
log_timezone = 'PRC'
stats_temp_directory = '/pgdata/10/run'
datestyle = 'iso, mdy'
timezone = 'PRC'
client_encoding = UTF8
lc_messages = 'en_US.UTF-8'
lc_monetary = 'en_US.UTF-8'
lc_numeric = 'en_US.UTF-8'
lc_time = 'en_US.UTF-8'
default_text_search_config = 'pg_catalog.english'
deadlock_timeout = 8s
max_locks_per_transaction = 100
shared_preload_libraries = 'pg_pathman,pg_stat_statements'
11.启动数据库： pg_ctl start 查看数据库状态： pg_ctl -D /pgdata/10/data status
