在Ubuntu 18.04上安装和使用PostgreSQL的方法

简介

关系数据库管理系统是许多网站和应用程序的关键组件。它们提供了一种结构化的方式来存储、组织和访问信息。

PostgreSQL，或者简称为 Postgres，是一个关系数据库管理系统，提供了 SQL 查询语言的实现。它是许多小型和大型项目的热门选择，并且具有符合标准的优势，以及可靠的事务和并发性而无需读取锁定等许多高级功能。

本指南演示了如何在 Ubuntu 18.04 VPS 实例上安装 Postgres，并提供了基本数据库管理的说明。

先决条件

要按照本教程进行操作，您需要一个已经按照我们的《Ubuntu 18.04 初始服务器设置指南》进行配置的 Ubuntu 18.04 服务器。完成先决条件教程后，您的服务器应该具有一个具有 sudo 权限的非 root 用户和一个基本防火墙。

步骤 1 — 安装 PostgreSQL

Ubuntu 的默认软件仓库中包含了 Postgres 软件包，因此您可以使用 apt 软件包管理系统来安装这些软件包。

由于这是您在本次会话中第一次使用 apt，请先刷新本地软件包索引。然后，安装 Postgres 软件包以及一个 -contrib 软件包，该软件包添加了一些额外的实用工具和功能：

sudo apt update
sudo apt install postgresql postgresql-contrib

使用 systemctl start 命令确保服务器正在运行：

sudo systemctl start postgresql.service

现在软件已安装并运行，我们可以了解它的工作原理以及它与您可能使用过的类似数据库管理系统有何不同。

步骤 2 — 使用 PostgreSQL 角色和数据库

默认情况下，Postgres 使用一种称为 角色 的概念来处理身份验证和授权。在某些方面，这与常规的 Unix 风格帐户类似，但 Postgres 不区分用户和组，而是更倾向于更灵活的术语 “角色”。

在安装时，Postgres 被设置为使用 ident 身份验证，这意味着它将 Postgres 角色与匹配的 Unix/Linux 系统帐户关联起来。如果一个角色存在于 Postgres 中，具有相同名称的 Unix/Linux 用户名就能够以该角色登录。

安装过程创建了一个名为 postgres 的用户帐户，该帐户与默认的 Postgres 角色关联。要使用 Postgres，您可以登录到该帐户。

有几种方法可以使用该帐户访问 Postgres。

切换到 postgres 帐户

通过输入以下命令，在服务器上切换到 postgres 帐户：

sudo -i -u postgres

现在，您可以立即通过输入以下命令访问 Postgres 提示符：

psql

这将使您登录到 PostgreSQL 提示符，从这里您可以立即与数据库管理系统交互。

通过输入以下命令退出 PostgreSQL 提示符：

\q

这将带您回到 postgres Linux 命令提示符。

在不切换帐户的情况下访问 Postgres 提示符

在上一个示例中，您被指示首先切换到 postgres 用户，然后运行 psql 打开 Postgres 提示符。您也可以通过以下单个命令在 postgres 用户下使用 sudo 来完成这一步：

sudo -u postgres psql

这将直接将您登录到 Postgres，而不需要中间的 bash shell。

同样，您可以通过输入以下命令退出交互式 Postgres 会话：

\q

许多用例需要多个 Postgres 角色。接下来了解如何配置这些角色。

步骤 3 — 创建新角色

目前，您只配置了数据库中的 postgres 角色。您可以使用 createrole 命令从命令行创建新角色。--interactive 标志将提示您输入新角色的名称，并询问是否应该具有超级用户权限。

如果您已登录为 postgres 帐户，可以通过输入以下命令创建新用户：

createuser --interactive

如果您更喜欢在不切换到正常帐户的情况下为每个命令使用 sudo，则输入以下命令：

sudo -u postgres createuser --interactive

该脚本将提示您进行一些选择，并根据您的响应执行正确的 Postgres 命令以创建符合您规格的用户。

首先，提示将要求您指定新角色的名称。以下示例将角色命名为 sammy，但您可以根据需要命名：

Enter name of role to add: sammy

接下来，将询问新角色是否应该是超级用户。在 PostgreSQL 中，超级用户角色拥有极其广泛的特权，并且可以绕过几乎所有权限检查。

以下示例指定 sammy 角色应该是超级用户，但是由于超级用户角色在数据库上具有极大的权限和控制，您不应该轻易授予新角色超级用户状态：

Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

请注意，只有在以已经是超级用户的角色创建它们时，才能创建新的超级用户角色。默认情况下，postgres 角色是超级用户。

您可以通过传递一些额外的标志来获得更多控制。通过查看 man 页面来查看选项：

man createuser

您的 Postgres 安装现在有了一个新角色，但您尚未添加任何数据库。下一节将描述此过程。

步骤 4 —— 创建新数据库

Postgres 认证系统默认假设任何用于登录的角色都将拥有同名的数据库，并且可以访问该数据库。

这意味着，如果你在上一节创建的用户叫做 sammy，那么该角色将默认尝试连接到一个也叫“sammy”的数据库。你可以使用 createdb 命令创建相应的数据库。

如果你以 postgres 账户登录，你可以输入如下命令：

createdb sammy

如果你更喜欢在不切换到 postgres 用户的情况下为每个命令使用 sudo，你可以输入：

sudo -u postgres createdb sammy

这种灵活性提供了多种创建数据库的途径。

步骤 5 —— 使用新角色打开 Postgres 提示符

要使用基于 ident 的认证登录，你需要一个与你的 Postgres 角色和数据库同名的 Linux 用户。

如果你没有匹配的 Linux 用户可用，你可以使用 adduser 命令创建一个。你需要在拥有 sudo 权限的非 root 账户下执行此操作（也就是说，不要以 postgres 用户登录）：

sudo adduser sammy

一旦新账户可用，你可以切换并连接到数据库，输入：

sudo -i -u sammy
psql

或者，你可以内联执行：

sudo -u sammy psql

这个命令将自动登录你，假设所有组件都已正确配置。

如果你想让你的用户连接到不同的数据库，你可以这样指定数据库：

psql -d postgres

登录后，你可以通过输入以下命令检查当前连接信息：

\conninfo

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

如果你连接到非默认数据库或使用非默认用户，这将非常有用。

步骤 6 —— 创建和删除表

现在你知道如何连接到 PostgreSQL 数据库系统，可以学习一些基本的 Postgres 管理任务。

首先，创建一个表来存储一些数据。举个例子，创建一个描述一些游乐场设备的表。

这个命令的基本语法如下：

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

这些命令为表命名，然后定义列以及列类型和字段数据的最大长度。你还可以为每个列可选地添加表约束。

你可以在这里了解更多关于如何在 Postgres 中创建和管理表的信息。

为了演示目的，创建一个样本表如下：

CREATE TABLE playground (
    equip_id serial PRIMARY KEY,
    type varchar (50) NOT NULL,
    color varchar (25) NOT NULL,
    location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')),
    install_date date
);

这些命令将创建一个用于存储游乐场设备清单的表。首先是一个设备 ID，它是 serial 类型。这种数据类型是自增整数。你还给这个列添加了 primary key 约束，这意味着值必须是唯一的且不为空。

对于两个列（equip_id 和 install_date），命令没有指定字段长度。这是因为某些列类型不需要设置长度，因为长度由类型隐含确定。

接下来的两个命令分别为设备 type 和 color 创建了列，每个列都不能为空（由应用到每个列的 NOT NULL 约束指定）。在这些命令之后的行创建了一个 location 列，并添加了一个约束，要求该列的值必须是八个可能值中的一个。括号内的最后一行创建了一个 date 列，记录你安装设备的日期。

请注意，在 SQL 中，每个语句必须以分号（;）结尾。

如果你正确输入了 CREATE TABLE 操作，它将返回以下输出：

CREATE TABLE

你可以通过输入以下命令找到此数据库中的表列表：

\d

List of relations
 Schema |          Name           |   Type   | Owner
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

你的 playground 表在这里，但还有一个叫做 playground_equip_id_seq 的类型为 sequence 的东西。这是你给 equip_id 列的 serial 类型的表示。它跟踪序列中的下一个数字，并且会自动为此类型的列创建。

如果你只想返回表而不返回序列，你可以输入：

\dt

List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

第 7 步 —— 向表中添加、查询和删除数据

现在你已经有了一个表，你可以向其中插入一些数据。

举个例子，通过调用你想要添加数据的表，命名列，然后为每列提供数据，来添加一个滑梯和一个秋千，就像这样：

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28');
INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

在输入数据时要小心，避免一些常见的问题。首先，不要用引号括住列名，但是你输入的列值需要加上引号。

另一件需要记住的事情是，不要为 equip_id 列输入值。这是因为每当在表中创建新行时，它会自动生成。

通过输入以下命令来检索你添加的信息：

SELECT * FROM playground;

equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        1 | slide | blue   | south     | 2017-04-28
        2 | swing | yellow | northwest | 2018-08-16
(2 rows)

这个输出表明你的 equip_id 已经成功填充，而且所有其他数据都被正确地组织了起来。

如果游乐场上的滑梯坏了，你需要移除它，你也可以通过输入以下命令从表中移除这一行：

DELETE FROM playground WHERE type = 'slide';

再次查询表：

SELECT * FROM playground;

equip_id | type  | color  | location  | install_date
----------+-------+--------+-----------+--------------
        2 | swing | yellow | northwest | 2018-08-16
(1 row)

注意到你的滑梯不再是表中的一部分了。

第 8 步 —— 向表中添加和删除列

在创建表之后，你可以相对容易地修改它来添加或删除列。通过输入以下命令来添加一个列，用于显示每个设备的最后维护日期：

ALTER TABLE playground ADD last_maint date;

下次再次查看表信息时，新列将已经被添加（但是没有数据被输入）：

SELECT * FROM playground;

equip_id | type  | color  | location  | install_date | last_maint
----------+-------+--------+-----------+--------------+------------
        2 | swing | yellow | northwest | 2018-08-16   |
(1 row)

要删除一个列，你可以输入一个与你用来添加 last_maint 列非常相似的 SQL 语句。如果你发现你的工作人员使用单独的工具来跟踪维护历史，你可以通过输入以下命令来删除该列：

ALTER TABLE playground DROP last_maint;

这将删除 last_maint 列以及其中找到的任何值，但会保留所有其他数据。

第 9 步 —— 更新表中的数据

到目前为止，你已经学会了如何向表中添加记录以及如何删除它们，但是本教程还没有涵盖如何修改现有条目。

你可以通过查询你想要的记录并将列设置为你希望使用的值来更新现有条目的值。你可以查询 “swing” 记录（这将匹配表中的 每个 秋千）并将其颜色更改为 “red”。如果你给秋千涂了一层油漆，这可能会很有用：

UPDATE playground SET color = 'red' WHERE type = 'swing';

你可以通过再次查询数据来验证操作是否成功：

SELECT * FROM playground;

equip_id | type  | color | location  | install_date
----------+-------+-------+-----------+--------------
        2 | swing | red   | northwest | 2010-08-16
(1 row)

正如这个输出所示，你的秋千现在被注册为红色了。

结论

你现在已经在你的 Ubuntu 18.04 服务器上设置了 PostgreSQL。然而，还有 更多 关于 Postgres 的知识等待你去学习。以下是一些覆盖如何使用 Postgres 的更多指南：

• 关系数据库管理系统的比较
• 学习如何创建和管理 Postgres 表
• 更好地管理角色和权限
• 使用 Select 在 Postgres 中编写查询
• 学习如何保护 PostgreSQL
• 学习如何备份 Postgres 数据库