如何在CentOS 7上安装PostgreSQL关系数据库

PostgreSQL关系数据库系统是一个功能强大的，可扩展的，并符合标准的开源数据库平台。本指南将帮助您在CentOS 7 Linode上安装和配置PostgreSQL。

开始之前

1. 熟悉我们的入门指南并完成设置Linode主机名和时区的步骤。
2. 完成“ 保护您的服务器”指南的各个部分，以创建标准用户帐户，加强SSH访问并删除不必要的网络服务。
3. 更新您的系统：

sudo yum update

注意本指南是为非root用户编写的。需要提升权限的命令带有前缀sudo。如果您不熟悉该sudo命令，请访问“ 用户和组”指南以获取更多信息。

安装PostgreSQL

在本节中，我们将介绍两种不同的安装PostgreSQL的方法。如果您不需要最新版本，我们建议使用第一种方法从CentOS存储库安装PostgreSQL。

除非另有说明，否则本指南后续部分中的说明将与任一方法安装的版本兼容。

从CentOS存储库安装

1.在撰写本文时，CentOS 7存储库附带PostgreSQL版本9.2.15。要从CentOS存储库安装，只需运行：

sudo yum install postgresql-server postgresql-contrib

2.初始化Postgres数据库并启动PostgreSQL：

sudo postgresql-setup initdb sudo systemctl start postgresql

3.可选：配置PostgreSQL以在启动时启动：

sudo systemctl enable postgresql

从Postgres存储库安装

或者，您可以从Postgres存储库安装最新版本。截至本文发布，PostgreSQL 9.6.3是CentOS 7的最新版本，但这些步骤可应用于任何基于RPM的安装。

注意使用此方法安装Postgres时，版本号包含在其配置目录中。例如，/var/lib/pgsql成为/var/lib/pgsql/9.6。systemd单元也是如此; systemctl status postgresql成为systemctl status postgresql-9.6。

1. 从Postgres Yum存储库中选择要安装的版本。找到所选版本的CentOS 7链接并将其下载到您的Linode：

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

2. 安装RPM以及EPEL存储库，它们将用于满足依赖关系：

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

3. 更新Yum以应用您的更改并安装PostgreSQL。手动安装Postgres时，您必须指定版本：

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

4. 初始化您的数据库并启动PostgreSQL：

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

5. 可选：配置PostgreSQL以在启动时启动：

sudo systemctl enable postgresql-9.6

配置PostgreSQL

保护Postgres用户

默认情况下，PostgreSQL将创建一个名为postgres访问数据库软件的Linux用户。

警告在postgres用户不应该被用于其他目的（例如，连接到其他网络）。这样做会对数据库的安全性造成严重威胁。

1. 更改postgres用户的Linux密码：

sudo passwd postgres

2. 发出以下命令为postgres数据库用户设置密码。请务必newpassword使用强密码替换并将其保存在安全的地方。

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

请注意，此用户与postgresLinux用户不同。Linux用户用于访问数据库，PostgreSQL用户用于在数据库上执行管理任务。

此步骤中设置的密码将用于通过网络连接到数据库。默认情况下，对等身份验证将用于本地连接。有关更改此设置的信息，请参阅Secure Local PostgreSQL访问部分。

访问PostgreSQL Shell

PostgreSQL客户端shell允许您发出SQL命令来管理数据库。作为postgresLinux用户，通过运行登录：

psql postgres

这将以postgres数据库用户身份登录。你会看到类似这样的提示：

psql (9.2.15)
Type "help" for help.

postgres=#

在最后一行中，postgres=#表示当前数据库的名称。要查看可用命令列表，请键入\h。您可以通过添加特定命令来找到有关特定命令的更多信息\h。完成shell的使用后，可以退出\q。

使用数据库

本节将介绍如何创建，删除和访问数据库。

创建数据库

您可以使用该createdb命令创建数据库。mytestdb通过以postgresLinux用户身份运行此命令来创建一个调用的示例数据库：

createdb mytestdb

也可以将数据库的所有权分配给特定的Postgres用户/角色。例如，您可以examplerole通过运行以下内容为角色分配所有权：

createdb mytestdb -O examplerole

该createdb命令有几个附加选项，可以在PostgreSQL文档中找到。

连接到数据库

您可以使用该psql命令连接到特定数据库。

1. 连接到测试数据库：

psql mytestdb

2. 您将看到以下输出：

psql (9.2.15) Type "help" for help. mytestdb=#

默认情况下，您将作为经过身份验证的用户连接到数据库。但是，如果您已启用本地密码访问，则还可以指定要连接的用户：

psql mytestdb -U examplerole

examplerole在访问shell之前，系统会提示您输入数据库用户的密码。

列出数据库

在Postgres shell中，您可以使用\lor \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从Postgres shell 输入来显示当前数据库和用户。如果您使用\conninfo以下内容，将包括其他信息，如套接字和端口：

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

删除数据库

您可以使用该命令删除或删除数据库dropdb。例如，要删除mytestdb先前创建的数据库，请以postgresLinux用户身份发出此命令：

dropdb mytestdb

警告删除的数据库无法恢复。

使用表

PostgreSQL数据库使用表来存储和组织数据库中的信息。在本节中，您将找到添加，删除和操作表的实际示例。除非另有说明，否则在连接到数据库后，应从Postgres shell发出本节中的命令。

创建表

本节包含使用员工的名字和姓氏创建测试数据库的示例，为每个名称分配一个唯一键。创建自己的表时，可以根据需要指定任意数量的参数（列）并对其进行适当命名。

1. 在测试数据库中创建一个名为“employees”的表：

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

2. 在表中插入记录：

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

查看表的内容

要查看“employees”表的内容：

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表格：

DROP TABLE employees;

DROP TABLE接受多个以逗号分隔的表名作为参数。例如，如果您有两个单独的表名为employees1和employees2，你可以通过运行删除他们两个：

DROP TABLE employees1, employees2;

添加列

可以更改表以添加定义，数据类型和列。在此示例中，您将添加一个start_date使用日期数据类型的新列。

1. 将start_date列添加到employees表中：

ALTER TABLE employees ADD start_date date;

2. 验证您的更改：

SELECT * FROM employees;

您将看到已创建新列，但它不包含任何数据：

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

在此示例中，您使用了date数据类型，但PostgreSQL表支持几种不同类型的数据。有关支持的数据类型的完整说明，请参阅PostgreSQL文档。

添加和更新行

在本节中，您将使用UPDATE在您创建的现有行中输入值。然后，你将创建一个全新的行INSERT。

1. start_date使用列中的值更新用户1的employee_id字段：

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

2. 在employees表中创建一个新行：

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

3. 验证您的更改：

SELECT * FROM employees;

您将看到已将开始日期2016-09-28添加到第一行，并且已为“Jane Smith”创建了一个新行：

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

删除列和行

在本节中，您将从表中删除一列，然后删除第二行。

1. 使用ALTER TABLE删除start_date你以前所做列：

ALTER TABLE employees DROP start_date;

2. 现在DELETE用来删除employees表格的第二行。以下命令将删除列中值为2的employee_id行：

DELETE FROM employees WHERE employee_id = '2';

3. 确认您的更改：

SELECT * FROM employees;

您的表现在包含一行，并start_date删除了列：

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

查询表

您可以使用查询从数据库中提取特定信息。此命令将查询您的employees表以仅返回employee_id和last_name列的值：

SELECT last_name,employee_id FROM employees;

您将收到类似于此的输出：

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

PostgreSQL支持许多查询选项。有关更多信息，请参阅PostgreSQL文档。

使用角色

PostgreSQL通过角色授予数据库访问权限，角色用于指定权限。角色可以理解为具有与Linux“用户”类似的功能。此外，角色也可以创建为一组其他角色，类似于Linux“组”.PostgreSQL角色全局适用，因此您无需创建如果您想在同一服务器上授予对多个数据库的访问权限，则两次使用相同的角色。

创建角色

使用该createuser命令添加新用户角色。要创建一个名为的新用户examplerole，请以postgresLinux用户身份发出以下命令：

createuser examplerole --pwprompt

系统将提示您为新用户创建密码。

授予对数据库的角色访问权限

在此示例中，您将为新创建的examplerole用户授予对数据库的访问权限。

1. 连接数据库：

psql mytestdb

postgres默认情况下，您将以数据库用户身份进行连接。

2. 在PostgreSQL shell中，输入以下内容以将表的所有权限授予employees用户examplerole：

GRANT ALL ON employees TO examplerole;

3. 使用退出数据库\q。

列出所有角色

您可以通过运行列出Postgres Shell中的所有角色\du。您将看到类似于此的输出：

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

组角色

为了便于管理，可以将多个用户角色添加到单个组中，以便可以将其权限作为一个整体进行管理。在本节中，您将创建一个新组并将examplerole用户添加到该组。这些命令应该以postgresLinux用户身份运行。

1. 使用该createuser命令可以创建新的组角色。--no-login指定该选项是因为组不需要登录功能。

createuser examplegroup --no-login

2. 登录Postgres shell并添加examplerole到新组：

psql postgres GRANT examplegroup TO examplerole;

3. 从Postgres shell中，验证您的更改\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命令还有其他几个选项。有关更多详细信息，请参阅PostgreSQL文档。

4. 完成应用更改后，退出Postgres shell \q。

Alter Roles

虽然可以在创建角色时将特定设置和权限应用于角色，但您也可以稍后修改角色的属性。在此示例中，我们将修改examplerole用户，以便它可以创建新数据库。本节中的命令应以postgresLinux用户身份运行。

1. 以postgres数据库用户身份登录：

psql postgres

2. 从Postgres shell中，将CREATEDB参数添加到examplerole用户：

ALTER ROLE examplerole CREATEDB;

创建或更改角色时可以应用许多权限。有关更多详细信息，请参阅PostgreSQL文档。

3. 使用\du以确认更改。您将看到examplerole用户旁边列出了“创建数据库”属性：

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

4. 完成后，退出Postgres shell \q。

删除角色

该dropuser命令用于删除PostgreSQL角色。要删除examplerole用户，请以postgresLinux用户身份发出以下命令：

dropuser examplerole

对等身份验证

PostgreSQL 默认使用对等身份验证。这意味着如果Linux用户名与其PostgreSQL角色的名称匹配，则将向本地系统用户授予数据库连接。要有效地使用对等身份验证，您需要创建Linux用户和相应的PostgreSQL角色。对于examplerole刚刚创建的角色，可以通过创建examplerole本地系统用户来使用对等身份验证。必须以具有sudo访问权限的用户身份运行此命令：

sudo adduser examplerole && passwd examplerole

请注意，系统将提示您为新exampleroleLinux用户创建密码。或者，您可以按照我们的步骤来保护本地访问。

安全的PostgreSQL

安全本地访问

虽然PostgreSQL的默认对等身份验证在特定系统用户运行本地程序（例如，脚本，单独用户拥有的CGI / FastCGI进程等）的情况下非常有用，但您可能希望需要密码以提高安全性。

postgres除非另有说明，否则本节中的命令应作为Linux用户运行。

1. /var/lib/pgsql/data/pg_hba.conf在# "local" is for Unix domain socket connections only标题下编辑文件： /var/lib/pgsql/data/pg_hba.conf1 2"local" is for Unix domain socket connections only local all all peer更换peer与md5此线路上使用MD5哈希激活密码验证。注意如果您从Postgres存储库安装了PostgreSQL ，则需要在此文件路径中指定版本号，例如：/var/lib/pgsql/9.6/data/pg_hba.conf。
2. 要启用这些更改，您需要重新启动PostgreSQL。但是，postgres出于安全原因，您未授予用户sudo权限。返回普通用户shell：

exit

3. 重启PostgreSQL并切换回postgres用户：

sudo systemctl restart postgresql su - postgres

4. 因为postgres，以examplerolePostgreSQL用户身份连接到测试数据库：

psql mytestdb -U examplerole

系统将提示您输入examplerole用户的密码，然后授予psql对数据库的shell访问权限。使用数据库时，可以使用该\z命令检查每个表的访问权限。

安全远程访问

localhost默认情况下，PostgreSQL会侦听连接，并且不建议将其重新配置为侦听公共IP地址。如果您希望从外部访问PostgreSQL，建议您按照Postgres文档使用SSL来保护您的远程连接。或者，您可以通过SSH隧道连接到PostgreSQL 。要使用图形工具远程访问数据库，请遵循以下指南之一：

• 在Windows上使用pgAdmin安全地管理远程PostgreSQL服务器
• 在Mac OS X上使用pgAdmin安全地管理远程PostgreSQL服务器

更多信息

有关此主题的其他信息，您可能需要参考以下资源。虽然提供这些是希望它们有用，但请注意，我们无法保证外部托管材料的准确性或及时性。

• PostgreSQL在线文档
• psql手册页