postgresql安裝參考

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'
logfilename = '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