本文涉及CentOS 7下PostgreSQL9.6的安裝，訪問配置及簡單使用。
1. CentOS環(huán)境
查看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 安裝服務(wù)器端
[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. 配置使用
啟動服務(wù)并設(shè)置開機啟動
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 數(shù)據(jù)庫相關(guān)操作
# 連接數(shù)據(jù)庫, 默認的用戶和數(shù)據(jù)庫是postgres
psql -U xxx -d dbname # 用xxx用戶登陸dbname這個數(shù)據(jù)庫
# 切換數(shù)據(jù)庫 == use dbname
\c dbname
# 列舉數(shù)據(jù)庫
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 |
# 創(chuàng)建數(shù)據(jù)庫：
create database [數(shù)據(jù)庫名];
# 創(chuàng)建數(shù)據(jù)庫指定用戶：
create database [數(shù)據(jù)庫名] owner [用戶名];
# 刪除數(shù)據(jù)庫：
drop database [數(shù)據(jù)庫名];
4.2 用戶相關(guān)操作
| 權(quán)限 | 說明 |
|---|---|
| superuser | |
| user | create role name; drop role name; createuser name dropuser name |
| role | 用戶和角色的區(qū)別是角色沒有l(wèi)ogin權(quán)限 |
# 查看用戶列表
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)
# 創(chuàng)建用戶
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是要設(shè)置的密碼'
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
