【PostgreSQL】CentOS7.x上 PostgreSQL 9.5的安装配置

一、PostgreSQL简介


PostgreSQL是自由的对象-关系型数据库服务器(数据库管理系统),在灵活的BSD许可证下发行。它在其他开放源代码数据库系统(比如MySQL和Firebird),和专有系统(比如Oracle、Sybase、IBM的DB2和Microsoft SQL Server)之外,为用户提供了又一种选择。

PostgreSQL不寻常的名字导致一些读者停下来尝试拼读它,特别是那些把SQL拼读为"sequel"的人。PostgreSQL开发者把它拼读为"post-gress-Q-L",它也经常被简略念为"postgres"。

PostgreSQL维基百科:https://zh.wikipedia.org/wiki/PostgreSQL


二、实验环境

操作系统: CentOS7.5 Minimal 

IP: 192.168.1.106


三、安装PostgreSQL


添加PostgreSQL9.5官方yum源

# yum -y  install  https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

# ll /etc/yum.repos.d/



安装PostgreSQL9.5服务端和客户端

#  yum  -y install   postgresql95    postgresql95-server  

# yum info  postgresql95    postgresql95-server  

# rpm  -ql   postgresql95  

# rpm  -ql  postgresql95-server  


# cat /etc/passwd


初始化数据库,启动数据库

#   /usr/pgsql-9.5/bin/postgresql95-setup initdb  

#   cat /var/lib/pgsql/9.5/initdb.log



#  systemctl start  postgresql-9.5.service

# systemctl enable postgresql-9.5.service

# systemctl status  postgresql-9.5.service


默认的数据存方目录是/var/lib/pgsql/9.5/data/

# ll   /var/lib/pgsql/9.5/data/  

可以看出数据里目录的属主属组为postgres,默认目录权限为700,文件权限为600 


如果你想自定义数据库目录,在输出化数据前更改数据存放目录然后初始化数据库,启动数据库:

#  cat    /usr/lib/systemd/system/postgresql-9.5.service



设置数据库目录,假设为/opt/data/postgres

# vim   /usr/lib/systemd/system/postgresql-9.5.service

#####################################

Environment=PGDATA=/opt/data/postgres

######################################

# chown -R  postgres:postgres  /opt/data/postgres

# chmod 700  /opt/data/postgres


#  /usr/pgsql-9.5/bin/postgresql95-setup initdb  

#  systemctl start  postgresql-9.5.service


四、设置监听IP,开启远程连接


#  vim  /var/lib/pgsql/9.6/data/postgresql.conf

#######################################

listen_addresses = '192.168.1.106'

########################################

注:如果是设置 listen_addresses = '*'  表示监听 0.0.0.0.0


# vim  /var/lib/pgsql/9.5/data/pg_hba.conf

#####################################

host     all         all       127.0.0.1/32       trust

host    all           all      192.168.1.0/24    trust

######################################

注:设置你允许的客户端网段,你可以设置为某个网段如 192.168.1.0/24,具体某个IP如 192.168.1.106 或者任何客户端 0.0.0.0/0



五、数据库的命令行操作

Postgres运行用户默认是postgres,shell为 /bin/bash 

# su -  postgres

$ psql --help

psql 是 PostgreSQL 中的一个命令行交互式客户端工具,

它允许你交互地键入 SQL 命令,然后把它们发送给 PostgreSQL 服务器,再显示 SQL 或命令的结果。

输入的内容允许来自一个文件,此外它还提供了一些元命令和多种类似 shell 的特性来实现书写脚本,以及对大量任务的自动化工作。

postgresql95为我们提供了连接数据库工具的客户端软件psql,postgresql96-server 是服务端软件。psql命令有两种格式,分别是:

#################################################

psql postgres://username:password@host:port/dbname 

 psql -U username -h hostname -p port -d dbname

##################################################

PostgreSQL安装及初始化完成以后,在它的配置文件里,默认只允许本机连接,而且连接到服务器的认证方式是peer和ident。


#  cat   /var/lib/pgsql/9.5/data/pg_hba.conf

命令格式:

本地连接非常好理解,就是从安装PostgreSQL的主机上连接,那 peer 和 ident 认证又是怎么回事呢?

PostgreSQL的官方文档上是这么说的:

map映射是在data目录下的pg_ident.conf目录里配置的,其基本定义格式如下:

#  cat  /var/lib/pgsql/9.5/data/pg_ident.conf



(1)PostgreSQL的连接命令psql有两种连接方式。不带-h参数或host参数时,是local连接,用的是peer认证方式,通过unix或者linux系统的socket进行连接。如果使用-h localhost、-h 127.0.0.1、postgres@localhost 或 postgres@127.0.0.1 这样的格式,则会使用host类型,使用TCP/IP的方式连接,使用的是ident的认证方式。

(2)需要修改配置文件设置好连接方式和认证方式,保证能够正常连接。


前面我们讲到,刚安装好并初始化完毕后,PostgreSQL服务器默认监听的地址是本地地址,即localhost。

我们一般都是在自己的电脑上安装客户端,然后远程连接到机房的物理服务器上。那怎么允许我们的机器可以连接到PostgreSQL服务器端呢?这里涉及到data目录下两个配置文件:

pg_hba.conf

postgresql.conf




默认监听localhost,这就是为什么刚安装好的PostgreSQL只能在本机上连接的原因。

我们将这个地址修改为0.0.0.0或服务器的ip地址,如下所示:

#  vim  /var/lib/pgsql/9.6/data/postgresql.conf

######################################

listen_addresses = '192.168.1.106'

######################################

# systemctl  restart  postgres-9.5.service


这个时候还有一个问题,那就是认证的问题。

刚才我们讲过peer和ident两种认证方式,前者只适用于本地连接,而后者则需要从ident服务器上获取用户名,都不适合我们当前的连接方式,那么还需要再修改认证方式,这里要介绍的认证方式是md5和trusted。


我们没讲过PostgreSQL的密码设置问题,也没有为数据库设置一个密码,那么我们只能先使用trusted的认证方式先连接到数据库上,再考虑修改数据库密码的问题。

# vim  /var/lib/pgsql/9.5/data/pg_hba.conf

#####################################

host     all         all       127.0.0.1/32         trust

host    all           all      192.168.1.0/24     trust

######################################

注:设置你允许的客户端网段

六、PostgreSQL密码设置

我们在上一节中讲了两种连接PostgreSQL的方式,分别是命令行连接和图形界面连接。这里不知道大家有没有发现一个问题,就是不管是在命令行连接或者是图形界面连接的时候,我们都没有要求大家设置密码或者输入密码?

这是因为在PostgreSQL安装完成后,我们使用/usr/pgsql-9.5/bin/postgresql96-setup initdb命令进行初始化的时候,并没有通过这个命令指定初始用户postgres的密码。当我们在服务器命令行界面使用peer认证直接登陆时,没要求我们输入密码,但是要记住,这样使用数据库是非常危险的,特别是你的数据库布置在云服务器的时候,很可能分分钟就被黑掉。在图形界面连接的时候,我们还设置了使用trust认证方式,这种方式默认允许不使用密码即可进行连接,也是非常危险的。

为了避免这些意外情况,我们介绍一下如何设置以及修改用户的密码。


1. 初始化时设置密码

当使用rpm包或PostgreSQL的官方yum源安装好PostgreSQL以后,不要使用/usr/pgsql-9.5/bin/postgresql95-setup initdb命令进行初始化,而是使用initdb命令。,

命令语法:

initdb [命令选项] [--pgdata | -D] 目录名


介绍完这个命令的用法以后,我们尝试使用这个命令来初始化数据库并给postgres用户设置一个密码。

这个命令是不能用root用户来允许的,因此先切换到postgres用户:

# su  - postgres 

$  /usr/pgsql-9.5/bin/initdb  -A md5  -U postgres -W -D  9.5/data


在这个命令中,我们使用的是md5加密密码,超级用户是postgres,目录是postgres用户的家目录下的9.6/data/目录,完整路径是/var/lib/psql/9.5/data/。

因为我们没有设置编码模式,从图中我们可以看到默认使用的是UTF8编码,然后执行过程中有让我们输入密码,输入密码确认以后,开始创建目录,设置各种参数。

配置完成以后还给出了启动PostgreSQL服务的命令:

$  /usr/pgsql-9.5/bin/pg_ctl   -D 9.5/data   -l  logfile   start

pg_ctl是postgresql自带的管理工具,也是只能在普通用户权限下执行的命令。

这里我们先暂时不使用这个工具。初始完成以后,我们使用systemctl启动PostgreSQL服务器:

#  su -  root 

# systemctl start postgresql-9.5.service

此时登录就可以看到已经要求你输入账号密码了,输入刚才我们初始化时设置的密码,成功登陆。

我们再看看配置文件pg_hba.conf,如下所示:

# cat    /var/lib/pgsql/9.5/data/pg_hba.conf

此时所有的默认认证方式都是我们刚才设置的md5认证方式,说明设置成功。


2. 初始化完成后修改密码

如果使用的是默认的初始化模式,则默认使用的是peer和ident的认证方式,配置好这两种认证方式以后,是不需要密码就可以登陆。

这时候可以先登陆到PostgreSQL服务器,然后修改密码,修改密码有两种方式:

psql命令工具修改密码

PostgreSQL的SQL语句修改密码

psql用来修改的命令工具是\password,命令行界面登录成功以后,输入这个命令,就可以直接修改当前用户的密码。

比如我们是postgres用户登陆,输入这个密码则修改postgres的密码,若是其他用户,则修改其他用户的密码,如下所示:


连续输入两遍密码,即成功修改,退出以后重新登陆时就需要输入新密码。


SQL语句用来修改密码的格式如下:

ALTER USER username PASSWORD  'passwordtext';


修改成功后会提示ALTER ROLE。

密码修改成功以后,将配置文件里的认证方式修改为md5认证

#  cat /var/lib/pgsql/9.5/data/pg_hba.conf

#  systemctl restart postgresql-9.5.service

# psql   -U  postgres -p 5432

重启完成后,重新登陆就需要输入密码登陆了,不管是命令行登录还是图形工具登录。


注意:psql并不支持像mysql命令行那样直接把密码作为参数传递进去进行非交互式登录,必须使用password file或者环境变量

https://www.postgresql.org/docs/current/libpq-envars.html

https://www.postgresql.org/docs/current/libpq-pgpass.html


3.基本信息查看

(1)通过psql命令查看,命令是psql --version

#  s - postgres

$psql --version


(2)登录后通过SQL语句查看,命令是:SELECT version();

这个命令查看的信息更详细,可以看到服务器版本,平台型号,编译信息等。


(3)查看数据库信息


总结:

这一章里我们学习的内容是如何给用户设置密码、如何配置用户的认证方式以及如何查看用户的基本信息。

设置密码分为两种方式,分别是psql命令行形式和SQL语句形式

用户认证可以在初始化时进行配置,也可以在初始化完成后修改pg_hba.conf文件进行配置

查看表的信息也是通过两种方式,一种是psql的命令行工具,例如\l、\d、\c等,另外一种是PostgreSQL提供的函数,例如pg_relation_size()等。这里要注意的一点是,PostgreSQL提供的函数如果带参数时,要用引号将参数引起来否则会报错。



七、设置防火墙

CentOS7.x  防火墙中内置了PostgreSQL服务,配置文件位置:

#  ls  /usr/lib/firewalld/services/

 # cat /usr/lib/firewalld/services/postgresql.xml

放行相关服务端口方式1:

#  firewall-cmd --add-service=postgresql   --zone=pubilic   --permanent 

# firewall-cmd --reload 

放行相关服务端口方式2:

# firewall-cmd --add-port=5432   --zone=pubilic   --permanent

# firewall-cmd --reload 



八、参考


PostgreSQL FAQ

https://wiki.postgresql.org/wiki/FAQ/zh-hant


Documentation /PostgreSQL 9.5/The pg_hba.conf File

https://www.postgresql.org/docs/9.5/auth-pg-hba-conf.html

https://my.oschina.net/liuyuanyuangogo/blog/497239


CentOS 7 安装、配置、使用 PostgreSQL 9.5

https://www.jianshu.com/p/7e95fd0bc91a


CentOS 7 安装PostgreSQL 9.6

https://www.jianshu.com/p/d26ae43446a2


Centos 7 安装 PostgreSQL

https://www.cnblogs.com/stulzq/p/7766409.html


PostgreSQL与MySQL比较

http://bbs.chinaunix.net/thread-1688208-1-1.html


Linux downloads (Red Hat family)

https://www.postgresql.org/download/linux/redhat/


PostgreSQL RPM Building Project - Repository Packages

https://yum.postgresql.org/repopackages.php


PostgreSQL使用教程

https://www.jianshu.com/p/d9fecdeaa759

https://www.jianshu.com/p/f246dc45e6dc

https://www.jianshu.com/p/bd6680e4abf3


psql使用入门

http://valleylord.github.io/post/201408-psql-basic


高效使用 PostgreSQL 命令行

https://www.atjiang.com/postgresql-on-the-command-line


PostgreSQL psql 命令语法参数说明

https://itbilu.com/database/postgre/410auo8xZ.html

推荐阅读更多精彩内容