PostgreSQL 9.6在Ubuntu 16.04和CentOS 7.5下的安装配置

        我本地自己的window10机器上是利用Vagrant配合VirtualBox所运行的Ubuntu 16.04虚拟机环境，但项目的测试环境是阿里云的CentOS 7.5云主机（ECS），为了方便本地测试，便在两个环境下都分别搭建了PostgreSQL，接下来介绍各环境下的安装步骤。

 

Ubuntu 16.04环境

1、添加Postgresql apt存储库

 

# 创建pgdg.list
sudo vi /etc/apt/sources.list.d/pgdg.list 

# 添加一行如下apt地址
deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main

# 导入存储库签名密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# 更新软件包列表
sudo apt-get update

  

2、安装PostgreSQL

 

sudo apt-get install postgresql-9.6

  

说明：ubuntu在安装Postgresql核心数据库的时候会顺便安装postgresql-client-9.6（客户端库和客户端二进制文件）和postgresql-contrib-9.6（附加提供的模块）

 

用这种方式安装成功后，系统会自动将Postgresql注册为一个服务，随着ubuntu操作系统自动启动并自动添加一个名为postgres密码随机的操作系统用户，与此同时还会自动生成一个名为postgres的数据库，用户名也是postgres，密码也是随机。

 

3、打开客户端工具（psql）并登录

 

sudo -u postgres psql

  

4、修改postgres数据库的用户名密码

 

注意：postgres=#为PostgreSQL下的命令提示符，每个SQL语句的结尾都要注意最后的英文分号';'
postgres=# ALTER USER postgres WITH PASSWORD '123456';

  

5、退出PostgreSQL psql客户端

 

postgres=# \q

 

6、修改ubuntu操作系统的postgres用户密码（密码要与数据库用户postgres的密码相同）

 

1) 切换到root用户
su - root
2) 删除postgres用户密码（passwd -d 是清空指定用户密码的意思）
sudo passwd -d postgres
3) 设置postgres用户的密码
sudo -u postgres passwd
4) 按照提示输入两次新密码（123456）

  

7、配置远程登录

 

1) 编辑postgresql配置文件
sudo vi /etc/postgresql/9.6/main/postgresql.conf
更改1：修改监听地址，查找listen_addresses，去掉前面的#号，并将'localhost'改为'*'
listen_addresses = '*'
更改2：启用密码验证，将 #password_encryption = on 前面的 # 号去掉
password_encryption = on
2) 编辑pg_hba.conf配置文件
sudo vi /etc/postgresql/9.6/main/pg_hba.conf
更改1：在文档末尾加上以下内容
host 	all 	all 	0.0.0.0/0 	md5
3) 保存并重启postgresql服务
sudo /etc/init.d/postgresql restart
4) 向防火墙中添加一条开放5432端口的规则
sudo iptables -A INPUT -p tcp -m state --state NEW -m tcp --dport 5432 -j ACCEPT

  

CentOS 7.5环境

1、查看linux系统版本

 

$cat /proc/version
Linux version 3.10.0-693.2.2.el7.x86_64 ([e-mail]) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-16) (GCC) ) #1 SMP Tue Sep 12 22:26:13 UTC 2017

$cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)

 

2、安装PostgreSQL

 

1) 访问如下地址，选择要安装的postgresql版本，linux系统版等相关信息，接着根据官方提供的yum安装指令安装即可
https://www.postgresql.org/download/linux/redhat/#yum

2) Install the repository RPM:
yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

3) Install the client packages:
yum install postgresql96

4) Optionally install the server packages:
yum install postgresql96-server

5) Optionally initialize the database and enable automatic start:
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6

 

3、打开客户端工具（psql）并登录

sudo -u postgres psql

以上命令等价于
sudo su - postgres # 切换账号，进入bash
psql # 进入数据库管理

 

4、修改postgres数据库的用户名密码

注意：postgres=#为PostgreSQL下的命令提示符，每个SQL语句的结尾都要注意最后的英文分号';'
postgres=# ALTER USER postgres WITH PASSWORD '123456';

 

5、新创建一个用户pguser和数据库test

CREATE USER pguser WITH PASSWORD '123456';		# 创建数据库用户
CREATE DATABASE test OWNER pguser;			# 创建数据库
GRANT ALL PRIVILEGES ON DATABASE test to pguser;  	# 授权给pguser

这个时候如果用刚刚新建的用户pguser登录会报错

$ psql -U pguser -d test 

psql: FATAL:  Peer authentication failed for user "pguser"

 

6、配置本地用户登录

编辑pg_hba.conf配置文件
vi /var/lib/pgsql/9.6/data/pg_hba.conf

将
# "local" is for Unix domain socket connections only
local   all      all           peer

改为
# "local" is for Unix domain socket connections only
local   all      all           md5
类似的其它改为：
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
重启postgresql服务
sudo service postgresql-9.6 restart

重新尝试登录即可
psql -U pguser -d test -h 127.0.0.1

 

 

7、配置远程登录

1) 编辑postgresql配置文件
sudo vi /var/lib/pgsql/9.6/data/postgresql.conf
更改1：修改监听地址，查找listen_addresses，去掉前面的#号，并将'localhost'改为'*'
listen_addresses = '*'
更改2：启用密码验证，将 #password_encryption = on 前面的 # 号去掉
password_encryption = on

2) 编辑pg_hba.conf配置文件
sudo vi /var/lib/pgsql/9.6/data/pg_hba.conf
更改1：在文档末尾加上以下内容
host 	all 	all 	0.0.0.0/0 	md5

3) 保存并重启postgresql服务
sudo service postgresql-9.6 restart

 

以上就是PostgreSQL 9.6在Ubuntu 16.04和CentOS 7.5下的安装配置步骤，下面顺便给出一些命令行客户端操作postgresql数据库常用操作命令

1、连接数据库
$psql -U user_name -d database_name -h serverhost

2、命令常用操作
\h      #查看所有的sql关键字
\?      #命令行操作的帮助
\d      #查看当前schema中所有的表
\q      #退出pg命令行
\d      #schema.table查看表的结构
\x      #横纵显示切换 
\dT+    #显示扩展类型相关属性及描述
\dx     #显示已安装的扩展插件
\l      #列出所有的数据库
\timing #显示执行时间
\c database_name        #切换数据库
set search to schema    #切换schema
explain sql             #解释或分析sql执行过程