PostgreSQL准备和部署

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=> 

数据库可正常访问。