PostgreSQL准备和部署

  1. 云栖社区>
  2. 博客>
  3. 正文

PostgreSQL准备和部署

libydwei2019 2019-07-15 12:04:07 浏览710
展开阅读全文

MySQL和PostgreSQL——两个开放源码的关系数据库管理系统,每个系统都有自己的粉丝。多年来,MySQL一直是PostgreSQL社区的乐趣之源——默认的、非常宽松的SQL模式、缺乏对更复杂连接的支持、没有位图索引等等。另一方面,MySQL爱好者指出了MySQL的健壮性,以及通过复制向外扩展是多么容易。

您可能会问——为什么要在一个环境中同时使用这两个数据库?在Galera集群旁边运行一个复制的PostgreSQL设置有什么价值吗?在这篇博客文章中,我们将从MySQL DBA的角度来回答这个问题。我们还将讨论部署PostgreSQL的不同方法。

为什么是PostgreSQL?

复杂查询的性能

PostgreSQL的一个优点是它能够很好地处理复杂的查询,而且是以一种有效的方式。PostgreSQL能够使用不同的连接算法(比如散列连接),并且在构建最佳执行计划方面非常灵活。另一方面,MySQL只支持嵌套循环连接,这可能不是每个查询的最佳选择。

PostgreSQL可能比MySQL更快的另一个原因是子查询处理。在执行子查询方面,MySQL优化器远非完美。在过去,它将大部分查询作为依赖子查询执行,并且需要手动重写查询来加快这类查询的速度。在MySQL 5.6中,已经做了一些改进,现在可以实现子查询的物化。MySQL 5.7进一步改进了对这类查询的处理,但是它仍然可以产生一个不如PostgreSQL那么理想的查询执行计划。

PL/pgSQL

PL/pgSQL

在MySQL中创建程序非常困难。它中可用的编程语言是有限的,而且远不如其他RDBMS系统(包括PostgreSQL)中可用的语言灵活。在PostgreSQL中构建存储过程要比在MySQL中容易得多——它可以构建一组存储过程,甚至可以将复杂的操作转移到数据库中。使用MySQL,由于MySQL存储过程语言的限制,您很可能不得不将一些逻辑转移到应用程序中。使用PostgreSQL更好的是,PL/pgSQL不是惟一可以编写过程的语言—它可以是C,也可以是PL/Tcl、PL/Perl和PL/Python,这些都包含在核心发行版中。还可以添加许多其他选项。

JSON和GIS支持

PostgreSQL支持JSON和GIS数据——如果您使用其中一种类型的数据,您可能希望在您的环境中包含PostgreSQL。说到MySQL, GIS数据是通过MyISAM表来支持的,而且直到最近才被引入到MySQL 5.7的InnoDB中。JSON数据类型在MySQL 5.7中也可用。在编写本文时,Galera不支持5.7,因此Galera用户可以选择使用单独的5.7实例或PostgreSQL来处理这类数据。使用PostgreSQL的另一个理由是,InnoDB中对GIS和JSON的支持是相当新的。这些特性还不成熟,可能需要一些时间来解决早期的问题。PostgreSQL为您提供了一个经过良好测试的替代方案

PostgreSQL的部署

PostgreSQL在大多数Linux的发行版本中都可用,你也可以简单的通过yum或者apt-get进行安装。

当安装完成后,PostgreSQL提供了安全的访问的方式-你只能在本地进行访问,直到你切换到postgres用户下。此时,你可以通过如下方式访问。(译者注:当到PG10的时候,默认的访问方式已经改变,任何用户都可以在本地访问)

[root@localhost ~]# su - postgres
上一次登录:五 7月 12 16:11:26 CST 2019pts/0 上
[postgres@localhost ~]$ psql
psql (10.1)
Type "help" for help.

postgres=# 

你可以使用这种方式访问,但是更好的方式通过配置文件控制客户端的访问。
配置文件为pg_hba.conf(HBA stands for host-based authentication.) ,文件一般在$PGDATA目录下。
PostgreSQL 10 安装完成后,默认的pg_hba.conf文件如下,有六条定义好的规则条目。

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

第一列为主机的类型:

  • local(unix-domain的socket连接访问,即通过运行在同一台主机的进程间进行通信)
  • host(TCP/IP socket连接)
  • hostssl(SSL加密的TCP/IP socket连接)

第二列为DATABASE表示数据库名称

  • all匹配所有数据库
  • sameuse匹配请求的用户和数据库一致的情况
  • samerole匹配请求的用户所在角色与数据库一致的情况
  • replication匹配物理复制的连接请求
  • 数据库名称 ,或者多个数据库名称用 逗号

注意ALL不匹配 replication

第三列表示用户名称

  • all
  • 一个用户名
  • 一组用户名 ,多个用户时,可以用 ,逗号隔开

第四列表示客户端的IP

  • 可以是一个地址,10.110.9.155/32
  • 地址范围,10.110.9.155/28
  • 通配的地址,0.0.0.0/0

第五列(最后一列)表示认证方式

经常使用的有三种

  • peer 操作系统用户名称和数据库的用户名称一致
  • md5 使用md5的方式验证密码登录
  • trust 无需验证,允许所有客户端连接

创建postgres以外的用户,如下所示

[postgres@localhost data]$ createuser -P --interactive
Enter name of role to add: s9suser
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
[postgres@localhost data]$ createuser -P --interactive
Enter name of role to add: s9sadmin
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) y

在上面的示例中,我们创建了两个用户

  • s9suser,作为普通用户;
  • s9sadmin,作为超级用户

接下来,我们可以用新创建的用户连接数据库

[postgres@localhost data]$ psql -h 127.0.0.1 postgres s9suser
psql (10.1)
Type "help" for help.

postgres=> 

虽然此时可以连接。但不幸的是,我们无法从本地主机外部连接到PostgreSQL实例。要改变这一点,需要几个步骤。

首先,我们需要让PostgreSQL监听环回以外的接口。我们需要找到并编辑postgresql.conf文件。它位于与pg_hba相同的目录中。
当你使用选择的文本编辑器打开它时,您将看到以下条目:

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

从这里可以看到,默认情况下,PostgreSQL只监听本地主机,为了接受外部连接,我们需要将这个值更改为其他值。这种更改需要重新启动服务。我们将其更改为本地的IP地址:10.110.9.155。并重新启动PostgreSQL。当它重新出现时,我们可以确认它确实在听:

[root@localhost ~]# netstat -lnp | grep 5432
tcp        0      0 10.110.9.155:5432       0.0.0.0:*               LISTEN      4646/postmaster     
unix  2      [ ACC ]     STREAM     LISTENING     39440    4646/postmaster      /tmp/.s.PGSQL.5432

当我们通过其他主机访问时,仍然会遇到如下的错误

[postgres@localhost data]$ psql -h 10.110.9.155 postgres s9suser
psql: FATAL:  no pg_hba.conf entry for host "10.110.9.154", user "s9suser", database "postgres"
[postgres@localhost data]$ 

我们需要在pg_hba.conf中新增条目,允许这个IP可以连接

host    all             all             10.110.9.154/32         md5
[postgres@localhost data]$ psql -h 10.110.9.155 postgres s9suser
Password for user s9suser: 
psql (10.1)
Type "help" for help.

postgres=> 

数据库可正常访问。

网友评论

登录后评论
0/500
评论
libydwei2019
+ 关注