CentOS 7 安装、配置、使用PostgreSQL及PostGIS

实际开发过程有两种安装方式，一种是编译安装；一种是包安装方式。编译安装需要cmake，gcc以及编译所需要的各种devel，这里仅介绍包安装方式。

¶从CentOS仓储安装

CentOS 7仓储装载有PostgreSQL版本9.2.15，你可以直接通过仓储安装：

1	sudo yum install postgresql-server postgresql-contrib

初始化数据库，启动PostgreSQL：

1 2	sudo postgresql-setup initdb sudo systemctl start postgresql

将PostgreSQL加入Systemd中：

1	sudo systemctl enable postgresql

¶指定仓库的版本

作为替代，你可以从Postgres仓储安装最新版本，目前CentOS7可用的最高PostgresSQL版本是9.6.3，但需要提供RPM-based的安装方式。

注意：Postgres指定版本后，版本信息会包含在配置目录中。例如/var/lib/pgsql会变成/var/lib/pgsql/9.6。这对于system units也一样，systemctl status postgresql会变成systemctl status postgresql-9.6。

首先需要从Postgres的仓储选择希望安装的版本。

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

安装RPM，以及EPEL仓储，用于依赖构建：

1	sudo yum install pgdg-centos96-9.6-3.noarch.rpm epel-release

安装前需要update一下，不然会有conflict：

1 2	sudo yum update sudo yum install postgresql96-server postgresql96-contrib

初始化数据库，启动PostgreSQL：

1 2	sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb sudo systemctl start postgresql-9.6

加入boot自启：

1	sudo systemctl enable postgresql-9.6

¶配置用户信息、数据库

默认地，PostgreSQL为Linux创建了一个无home目录的用户postgres，用于访问数据库软件目录。

小心：
这个postgres用户不应该用于其它目的(例如，连接其它网络)。这样会给数据库安全带来风险。

更改postgres密码：

1	sudo passwd postgres

下面方式是更改postgres数据库用户密码。确保使用的newpassword是强类型密码：

1 2	su - postgres psql -d template1 -c "ALTER USER postgres WITH PASSWORD 'newpassword';"

注意这里的postgres用户不同于Linux的那个postgres用户。Linux的postgres是用于访问数据库，这个PostgreSQL用户是负责该数据库的管理任务处理。

上面设置的密码被用于通过网络访问数据库。本机方式使用的peer授权。本章最后介绍如何实现远程访问。

¶PostgreSQL Shell

PostgreSQL client shell允许你在数据库处理SQL命令。首先登录postgres用户，进入shell：

1	psql postgres

首先会登录postgres数据库，prompt信息类似于：

psql (9.2.15)
Type "help" for help.

postgres=#

最后一行，postgres=#标示了当前数据库的名称。要查看可用命令，输入\h。退出当前shell，输入\q。

¶数据库工作

首先，你可以使用createdb命令创建数据库。例如以postgres用户身份创建一个mytestdb数据库：

1	createdb mytestdb

也可以为该数据库指派角色关系。例如，指派一个examplerole角色：

1	createdb mytestdb -O examplerole

createdb命令有几个操作选项，你可以在PostgreSQL documentation查看更多细节。

连接数据库，可以使用psql命令指定：

1	psql mytestdb

出现下面prompt说明连接成功：

psql (9.2.15)
Type "help" for help.

mytestdb=#

默认地，数据库的连接是作为peer-authenticated用户的方式。当然，你也可以指定本地用户的方式访问：

1	psql mytestdb -U examplerole

要查看所有的数据库，使用\l或\list命令：

postgres=# \l
                          List of databases
Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
mytestdb  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|         |          |          |             | postgres=CTc/postgres
template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
|         |          |          |             | postgres=CTc/postgres
(4 rows)

通过\c命令可以展示当前数据库和当前用户信息。要展示其它额外信息，可以使用\conninfo：

1	You are connected to database "mytestdb" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

数据数据库用dropdb命令，例如：

1	dropdb mytestdb

注意，删除的数据库不能恢复。

¶表

创建一个employees的表：

1	CREATE TABLE employees (employee_id int, first_name varchar, last_name varchar);

插入一条记录：

1	INSERT INTO employees VALUES (1, 'John', 'Doe');

查看表内容：

1	SELECT * FROM employees;

输出内容为

employee_id | first_name | last_name
-------------+------------+-----------
          1 | John       | Doe
(1 row)

列举当前数据库的所有表信息，可以通过\dt命令：

mytestdb-# \dt
          List of relations
Schema |   Name    | Type  |  Owner
--------+-----------+-------+----------
public | employees | table | postgres

删除表通过DROP TABLE命令，例如删除employees：

1	DROP TABLE employees;

可以通过逗号分隔方式删除多个表，例如：

1	DROP TABLE employees1, employees2;

更改和添加表column，和标准SQL一样：

1	ALTER TABLE employees ADD start_date date;

验证先前的更改：

1	SELECT * FROM employees;

你会看到新增的列不包含任何内容：

employee_id | first_name | last_name | start_date
-------------+------------+-----------+------------
          1 | John       | Doe       |
(1 row)

PostgreSQL支持好几种数据库类型，可以在这里查看相关信息。

你可以使用UPDATE更改记录：

1	UPDATE employees SET start_date = '2016-09-28' WHERE employee_id = '1';

或插入一条新的记录：

1	INSERT INTO employees VALUES (2, 'Jane', 'Smith', '2015-03-09');

验证一下，

SELECT * FROM employees;

employee_id | first_name | last_name | start_date
-------------+------------+-----------+------------
          1 | John       | Doe       | 2016-09-28
          2 | Jane       | Smith     | 2015-03-09
(2 rows)

要删除行和列，例如删除start_date字段，

1	ALTER TABLE employees DROP start_date;

删除记录使用DELETE，例如：

1	DELETE FROM employees WHERE employee_id = '2';

验证一下，

SELECT * FROM employees;

employee_id | first_name | last_name
-------------+------------+-----------
          1 | John       | Doe
(1 row)

查询数据库和标准SQL一样，例如

SELECT last_name,employee_id FROM employees;

last_name | employee_id
-----------+-------------
Doe       |           1
(1 row)

更多查询选项，可以点击这里

¶角色

PostgreSQL通过roles授权数据库访问，表示某种权限。另外，角色可以由其它一系列角色创建，类似于“group”。PostgreSQL的角色是全局的，所以你不需要为同一个角色创建两次以访问不同数据库。

创建角色通过createuser命令，例如创建一个examplerole的角色，

1	createuser examplerole --pwprompt

授权角色访问，默认以postgres数据连接，再进行授权，这里的postgres相当于一个sa

1	GRANT ALL ON employees TO examplerole;

罗列所有用户角色，可以通过\du命令查看，

postgres=# \du
                             List of roles
Role name   |                   Attributes                   | Member of
-------------+------------------------------------------------+-----------
examplerole |                                                | {}
postgres    | Superuser, Create role, Create DB, Replication | {}

为了便于管理，可以将多个角色添加到一个组，这样可以全局管理它们的权限。下面展示如何实现，所有这些命令应该在postgres用户下进行。

使用createuser创建一个组角色。其中--no-login选项指定了该组不需要登录。

1	createuser examplegroup --no-login

登录并添加角色到组中，

1 2	psql postgres GRANT examplegroup TO examplerole;

验证一下，使用\du命令，可以看到examplerole用户现在在examplegroup组中：

postgres=# \du
                                List of roles
Role name    |                   Attributes                   |   Member of
--------------+------------------------------------------------+----------------
examplegroup | Cannot login                                   | {}
examplerole  |                                                | {examplegroup}
group        |                                                | {}
postgres     | Superuser, Create role, Create DB, Replication | {}

createuser命令有几个选项。点击这里查看相关信息。

要更改角色属性，有几个选项，例如，授予examplerole属性CREATEDB，

1	ALTER ROLE examplerole CREATEDB;

使用\du验证一下，

postgres=# \du
                             List of roles
Role name   |                   Attributes                   | Member of
-------------+------------------------------------------------+-----------
examplerole | Create DB                                      | {}
group       |                                                | {}
postgres    | Superuser, Create role, Create DB, Replication | {}

删除角色，使用dropuser命令，

1	dropuser examplerole

¶Peer Authentication

PostgreSQL默认使用对等授权(peer authentication)的方式进行连接。意味着，如何当前授权的本地系统用户和PostgreSQL角色用户名匹配，则授予连接。所以你需要同时创建一个相同的Linux用户和一个对应的PostgreSQL角色。例如，刚刚创建了examplerole角色，Linux中需要创建一个examplerole，

1	sudo adduser examplerole && passwd examplerole

¶Secure PostgreSQL

PostgreSQL默认使用的peer authentication方式，限制了仅能通过本地访问，它的配置信息在/var/lib/pgsql/dta/pg_hba.conf中，

1 2	# "local" is for Unix domain socket connections only local all all peer

将peer替换为md5，以允许通过MD5哈希密码方式访问。

也可以指定信任任何服务器的连接，修改为，

# IPv4 local connections:
host    all            all      127.0.0.1/32      trust
host    all            all      0.0.0.0/0  		  trust
# IPv6 local connections:
host    all            all      ::/0  		       trust

退出，重启服务，

1 2	sudo systemctl restart postgresql su - postgres

再次登录，

1	psql mytestdb -U examplerole

它会提示你输入密码，用于登录examplerole角色以访问数据库。你可以通过\z命令访问表权限。

另外，要实现远程访问，还需要修改/var/lib/pgsql/data/postgresql.conf配置，去掉前面注释，

1	listen_addresses='*'

某些情况下，还需要关闭系统防火墙，或直接添加到iptalbe，

CentOS 防火墙中内置了PostgreSQL服务，配置文件位置在/usr/lib/firewalld/services/postgresql.xml，我们只需以服务方式将PostgreSQL服务开放即可。

1 2	firewall-cmd --add-service=postgresql --permanent 开放postgresql服务 firewall-cmd --reload 重载防火墙

或干脆关闭防火墙，

1 2	sudo systemctl stop firewalld sudo systemctl disable firewalld

重启postgresql即可。

¶安装PostGIS

PostGIS可以由CentOS仓储安装，

1	sudo yum -y install postgis.x86_64 postgis-client.x86_64 postgis-debuginfo.x86_64 postgis-devel.x86_64 postgis-docs.x86_64 postgis-utils.x86_64

查看PostGI相关文件是否安装成功：

1 2	ls /usr/pgsql/share/extension ls /usr/pgsql/share/contrib/postgis

加载PostGIS插件

1 2	su - postgres psql -d postgres -U postgres

在PostgreSQL Shell中，输入

postgres=# create database postgis ;
postgres=# \c postgis
postgis=# select * from pg_extension ;
postgis=# create extension postgis ;
postgis=# \dt

postgis=# select count(*) from spatial_ref_sys ;

postgres=# \dn

postgis=# create extension postgis_topology ;
CREATE EXTENSION
postgis=# \dn