本文涉及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
