centos7 PostgreSQL9.6 在线安装

环境准备

centos版本：centos 7

oracle版本：PostgreSQL9.6

一、下载安装

1、创建PostgreSQL9.6的yum源文件

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

2、安装PostgreSQL客户端

yum install postgresql96

3、安装PostgreSQL服务端

yum install postgresql96-server

4、安装PostgreSQL拓展包(可选)

yum install postgresql96-devel.x86_64 

5、安装PostgreSQL的附加模块（可选）

yum install postgresql96-contrib.x86_64

二、创建用户与组

groupadd postgres
useradd -r -g postgres postgres
mkdir /home/postgres
chmod 755 /home/postgres
chown -R postgres:postgres  /home/postgres

 

三、配置远程连接

初始化数据库

/usr/pgsql-9.6/bin/postgresql96-setup initdb

在/var/lib/pgsql/9.6/data下

1、修改pg_hba.conf

在最后添加允许访问IP段（全网段可访问）

vi postgresql.conf
host all all 0.0.0.0/0 md5

2、修改postgresql.conf

找到用户参数listen_address(取消掉注释),改成下面样式:

vi postgresql.conf
listen_addresses = '*'   # what IP address(es) to listen on;

启用密码验证，将#password_encryption = on修改为 password_encryption = on

vi postgresql.conf
password_encryption = on

启动postgresql服务，并设置为开机自动启动

systemctl enable postgresql-9.6
systemctl start postgresql-9.6

检查启动状态

[root@test644-1 bin]# service postgresql-9.6 status
Redirecting to /bin/systemctl status postgresql-9.6.service
● postgresql-9.6.service - PostgreSQL 9.6 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2020-03-24 09:32:26 UTC; 14s ago
     Docs: https://www.postgresql.org/docs/9.6/static/
  Process: 3145 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 3153 (postmaster)
   CGroup: /system.slice/postgresql-9.6.service
           ├─3153 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
           ├─3154 postgres: logger process   
           ├─3156 postgres: checkpointer process   
           ├─3157 postgres: writer process   
           ├─3158 postgres: wal writer process   
           ├─3159 postgres: autovacuum launcher process   
           └─3160 postgres: stats collector process   

Mar 24 09:32:26 test644-1.novalocal systemd[1]: Starting PostgreSQL 9.6 database server...
Mar 24 09:32:26 test644-1.novalocal postmaster[3153]: < 2020-03-24 09:32:26.647 UTC > LOG:  redirecting log output to logging collector process
Mar 24 09:32:26 test644-1.novalocal postmaster[3153]: < 2020-03-24 09:32:26.647 UTC > HINT:  Future log output will appear in directory "pg_log".
Mar 24 09:32:26 test644-1.novalocal systemd[1]: Started PostgreSQL 9.6 database server.

postgres用户初始配置

安装完成后，操作系统会自动创建一个postgres用户用来管理数据库，为其初始化密码(输入命令后连输2次密码)：

[root@test644-1 bin]# passwd postgres
Changing password for user postgres.
New password: 123456
BAD PASSWORD: The password contains the user name in some form
Retype new password: 123456
passwd: all authentication tokens updated successfully.

数据库初始配置

使用数据库自带的postgres用户登录数据库,并为其赋予密码

su - postgres
$ psql -U postgres
alter user postgres with password '你的密码';

3、重启数据库

systemctl restart postgresql-9.6  

备注:

1.使用Navicat For PostgreSql来连接

2.数据库初始化失败，需要清理/var/lib/pgsql/9.6/data下面的数据和文件