本文涉及CentOS 7下PostgreSQL9.6的安装，访问配置及简单使用。

1. CentOS环境

查看CentOS版本

[root@localhost ~]# cat /etc/redhat-release
CentOS Linux release 7.5.1804 (Core)

---

2. 安装postgresql

https://www.postgresql.org/download/linux/redhat/ 生成yum安装命令，最新的包是11.11

2.1 安装rpm

[root@localhost ~]# yum install -y https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm](https://download.postgresql.org/pub/repos/yum/11/redhat/rhel-7-x86_64/pgdg-centos11-11-2.noarch.rpm

2.2 安装客户端

[root@localhost ~]# yum install postgresql11   # 11是版本信息
Package postgresql11-11.1-1PGDG.rhel7.x86_64 already installed and latest version
Nothing to do

2.3 安装服务器端

[root@localhost ~]# yum install -y postgresq11-server
Package postgresql11-server-11.1-1PGDG.rhel7.x86_64 already installed and latest version

2.4 查看安装的package信息

root@localhost ~]# yum info postgresql11
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirrors.nju.edu.cn
 * extras: mirrors.163.com
 * updates: mirrors.cqu.edu.cn
Installed Packages
Name        : postgresql11
Arch        : x86_64
Version     : 11.1
Release     : 1PGDG.rhel7
Size        : 8.6 M
Repo        : installed
From repo   : pgdg11
Summary     : PostgreSQL client programs and libraries
URL         : https://www.postgresql.org/

---

3. 配置使用

启动服务并设置开机启动

systemctl 命令

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10.service

---

4. 常用操作

root@localhost ~]# su - postgres   # 切换为postgres 用户登陆
Last login: Tue Nov 20 04:49:59 EST 2018 on pts/0
-bash-4.2$ psql   #进入psql
psql (11.1)
Type "help" for help.

postgres=# \q   # 退出psql

4.1 数据库相关操作

# 连接数据库, 默认的用户和数据库是postgres
psql -U xxx -d dbname   # 用xxx用户登陆dbname这个数据库

# 切换数据库 == use dbname
\c dbname

# 列举数据库
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privi
leges
-----------+----------+----------+-------------+-------------+---------------
--------
 ossdb     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

# 创建数据库： 
create database [数据库名];

# 创建数据库指定用户：
create database [数据库名] owner [用户名];

# 删除数据库： 
drop database [数据库名];  

---

4.2 用户相关操作

权限	说明
superuser
user	create role name; drop role name; createuser name dropuser name
role	用户和角色的区别是角色没有login权限

# 查看用户列表
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 oss-su    | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vcloud    | Create DB                                                  | {}

# 查看当前是什么用户
postgres=# select * from current_user;
 current_user
--------------
 postgres
(1 row)

# 创建用户
postgres=# create user vcloud with password 'xxx';
postgres=# CREATE ROLE david;　　#默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy;　　#默认具有LOGIN属性
CREATE ROLE

# 给 david 添加LOGIN属性

# 修改用户密码
postgres=# alter user vcloud with password 'xxx';    #xxx是要设置的密码'
ALTER ROLE

# 用户登陆
-bash-4.2$ psql -U vcloud -d vcloud
Password for user vcloud:
psql (11.1)
Type "help" for help.

vcloud=>

# 删除用户
-bash-4.2$ dropuser --help
dropuser removes a PostgreSQL role.

Usage:
  dropuser [OPTION]... [ROLENAME]

---

5. 客户端pgadmin 安装

下载地址 (https://www.postgresql.org/ftp/pgadmin/pgadmin4/v3.5/macos/)

---

6. 问题列表

6.1用户登录 Peer authentication failed

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

需要更新你的pg_hba.conf从Peer authentication 到Password authentication
参考：(https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge)

[root@localhost data]# pwd
/var/lib/pgsql/11/data
[root@localhost data]# vi pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# change to 
local   all             all                                     md5

# Need to restart service
[root@localhost data]# sudo service postgresql-11 restart
Redirecting to /bin/systemctl restart postgresql-11.service