database►Postgresql►postgresql standard

postgresql standard

1. 源码安装

1
2
3
4
5
[root@localhost app]# tar xzf postgresql-9.6.1.tar.gz
[root@localhost app]# cd postgresql-9.6.1
[root@localhost postgresql-9.6.1]# ./configure --prefix=/mnt/app/pgsql
[root@localhost postgresql-9.6.1]# make
[root@localhost postgresql-9.6.1]# make install

2. 插件安装

1
2
[root@localhost postgresql-9.6.1]# cd contrib/
[root@localhost contrib]# make install

补充: 安装一个插件

1
2
[root@localhost postgresql-9.6.1]# cd contrib/pgcrypto/
[root@localhost pgcrypto]# mkdir install

3. 环境变量设置

1
2
3
4
[root@localhost postgresql-9.6.1]# echo 'export PGSQL_HOME=/mnt/app/pgsql' | tee /etc/profile.d/postgresql.sh
[root@localhost postgresql-9.6.1]# echo 'export PGSQL_BIN=${PGSQL_HOME}/bin' | tee -a /etc/profile.d/postgresql.sh                   
[root@localhost postgresql-9.6.1]# echo 'export PATH=${PGSQL_BIN}:$PATH' | tee -a /etc/profile.d/postgresql.sh                        
[root@localhost postgresql-9.6.1]# source /etc/profile

4. 创建数据和日志存放位置

1
2
[root@localhost postgresql-9.6.1]# mkdir -p /mnt/{data,log}/pgsql
[root@localhost postgresql-9.6.1]# chown -R smallasa.smallasa /mnt/{data,log}/pgsql

5. 初始化数据库

1
2
[root@localhost postgresql-9.6.1]# su - smallasa
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/initdb -D /mnt/data/pgsql/

6. 设置访问权限

1
2
3
4
5
[smallasa@localhost ~]$ vim /mnt/data/pgsql/pg_hba.conf
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               md5

7. 修改配置文件

1
2
3
4
[smallasa@localhost ~]$ vim /mnt/data/pgsql/postgresql.conf
listen_addresses = '\*'
port = 5432
max_connections = 2000

补充:设置PG连接数,需要修改内核参数kernel.sem

1
2
3
4
[root@localhost postgresql-9.6.1]# cat >> /etc/sysctl.conf <<EOF
> kernel.sem = 50100 128256000 50100 2560
> EOF
[root@localhost postgresql-9.6.1]# sysctl -p

8. 启动服务

1
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/pg_ctl -D /mnt/data/pgsql/ -l /mnt/log/pgsql/pgsql.log start

9. 登录数据库进行验证

1
[smallasa@localhost ~]$ /mnt/app/pgsql/bin/psql -d postgres