Centos7软件安装系列【九】安装postgresql

#安装
cd /home/software
tar xzvf postgresql-9.6.8.tar.gz
cd postgresql-9.6.8
./configure --prefix=/usr/local/postgresql --without-readline
make && make install
cd contrib
make && make install
echo "export PATH=\$PATH:/usr/local/postgresql/bin" >> /etc/profile
source /etc/profile

#添加用户
groupadd postgres
useradd -g postgres postgres
mkdir -p /home/postgresql/data
mkdir -p /home/postgresql/logfile
chown postgres:postgres /home/postgresql/data
chown postgres:postgres /home/postgresql/
chmod -R 775 /usr/local/postgresql/
chmod -R 775 /home/postgresql/

#添加环境变量
echo "export POSTGRESQL_HOME=/usr/local/postgresql" >> /etc/profile
echo "export PATH=\$PATH:\$POSTGRESQL_HOME/bin" >> /etc/profile
source /etc/profile

cp /home/software/postgresql-9.6.8/contrib/start-scripts/linux /etc/init.d/postgresql
chmod u+x /etc/rc.d/init.d/postgresql

#vi /etc/rc.d/init.d/postgresql
sed -i 's/^prefix=.*$/prefix=\/usr\/local\/postgresql/g' /etc/rc.d/init.d/postgresql
sed -i 's/^PGDATA=.*$/PGDATA="\/home\/postgresql\/data"/g' /etc/rc.d/init.d/postgresql

#初始化数据库
su postgres
cd /home/postgresql/data
initdb -D /home/postgresql/data

#创建用户
su root
service postgresql start
#设置密码
psql -U postgres
alter user postgres with password 'postgres';

su postgres
psql

CREATE USER db_post WITH PASSWORD 'db_post';

CREATE DATABASE db_postOWNER db_post;

GRANT ALL PRIVILEGES ON DATABASE db_post to db_post;

#修改权限文件
cd /home/postgresql/data
vi postgresql.conf
listen_addresses = '*'

vi pg_hba.conf
host all all 192.168.0.1/16 md5


#安装postgresql插件，监控sql执行时间
vi postgresql.conf
#------------------------------------------------------------------------------
# PG_STAT_STATEMENTS OPTIONS
#------------------------------------------------------------------------------
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000
pg_stat_statements.track = all

#开放端口
firewall-cmd --permanent --zone=public --add-port=5432/tcp
firewall-cmd --reload

#安装插件
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_time, (total_time/calls) as average ,rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent 
FROM pg_stat_statements
ORDER BY average DESC LIMIT 10;

#重置统计信息
select pg_stat_statements_reset();