介绍 (Introduction)

Relational database management systems are a key component of many web sites and applications. They provide a structured way to store, organize, and access information.

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

PostgreSQL, or Postgres, is a relational database management system that provides an implementation of the SQL querying language. It is a popular choice for many small and large projects and has the advantage of being standards-compliant and having many advanced features like reliable transactions and concurrency without read locks.

PostgreSQL或Postgres是一种关系数据库管理系统，提供SQL查询语言的实现。 它是许多小型和大型项目的流行选择，它的优点是符合标准，并具有许多高级功能，例如可靠的事务和并发且无读锁 。

In this guide, you will install Postgres on a CentOS 7 server and go over some basic ways to use it.

在本指南中，您将在CentOS 7服务器上安装Postgres，并介绍一些使用它的基本方法。

先决条件 (Prerequisites)

To follow along with this tutorial, you will need:

要遵循本教程，您将需要：

• • To set up firewalld, follow the Configuring a Basic Firewall section of the Additional Recommended Steps for New CentOS 7 Servers tutorial.

    要设置firewalld ，请遵循“ 新CentOS 7服务器的其他推荐步骤”教程的“ 配置基本防火墙”部分。

  • Databases can be particularly vulnerable to system time changes if they are very active and have timestamps on internal records. To prevent some inconsistent behavior that can arise from out-of-sync clocks, make sure to set up Network Time Protocol (NTP) synchronization by following the Configure Timezones and Network Time Protocol Synchronization section of the Additional Recommended Steps for New CentOS 7 Servers tutorial.

    如果数据库非常活跃并且内部记录上带有时间戳，则数据库特别容易受到系统时间更改的影响。 为防止时钟不同步引起某些不一致的行为，请确保遵循“ 新CentOS 7服务器新建议步骤 ”的“配置时区和网络时间协议同步”部分来设置网络时间协议(NTP)同步。 。

  A CentOS 7 server that has been configured by following our Initial Server Setup with CentOS 7 guide and our Additional Recommended Steps for New CentOS 7 Servers tutorial, including a non-root user with sudo privileges and a firewall set up with firewalld.

  通过遵循带有CentOS 7的初始服务器设置指南和新CentOS 7服务器的附加建议步骤指南配置的CentOS 7服务器 ，包括具有sudo特权的非root用户和使用firewalld设置的firewalld 。

第1步-安装PostgreSQL (Step 1 — Installing PostgreSQL)

Postgres can be installed using default CentOS repositories. But as of the writing of this tutorial, the version that is available in the CentOS 7 Base repository is obsolete. Therefore, this tutorial will use the official Postgres repository.

可以使用默认的CentOS存储库来安装Postgres。 但是，在撰写本教程时，CentOS 7 Base资源库中可用的版本已过时。 因此，本教程将使用官方的Postgres存储库。

Before you move on to setting up a new repository, exclude the search for postgresql packages from the CentOS-Base repository. Otherwise, dependencies might resolve to the postgresql supplied by the base repository.

在继续建立新的存储库之前，请从CentOS-Base存储库中排除对postgresql软件包的搜索。 否则，依赖项可能会解析为基础存储库提供的postgresql 。

Open the repository configuration file using your preferred text editor. This tutorial will use vim:

使用首选的文本编辑器打开存储库配置文件。 本教程将使用vim ：

Find the [base] and [updates] sections, enter insert mode by pressing i, and insert the exclude=postgresql* line in both sections. As a result, your file will look like the following, with new lines highlighted:

找到[base]和[updates]部分，按i进入插入模式，然后在两个部分中插入exclude=postgresql*行。 因此，您的文件将如下所示，并突出显示新行：

...
[base]
name=CentOS-$releasever - Base
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7

exclude=postgresql*

#released updates
[updates]
name=CentOS-$releasever - Updates
mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra
#baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
exclude=postgresql*
...

When you’re finished, press ESC to leave insert mode, then :wq and ENTER to save and exit the file. To learn more about the text editor vi and its successor vim, check out our Installing and Using the Vim Text Editor on a Cloud Server tutorial.

完成后，按ESC退出插入模式，然后按:wq和ENTER保存并退出文件。 要了解有关文本编辑器vi及其后续vim的更多信息，请查看我们在Cloud Server上安装和使用Vim文本编辑器的教程。

Now, install a repository configuration package using the official PostgreSQL repository for CentOS:

现在，使用用于CentOS的官方PostgreSQL存储库安装存储库配置软件包：

When given the prompt, confirm the installation with y.

出现提示时，请使用y确认安装。

The PostgreSQL repository includes information for all available PostgreSQL releases. You can see all available packages and versions using the following command:

PostgreSQL存储库包含所有可用PostgreSQL版本的信息。 您可以使用以下命令查看所有可用的软件包和版本：

Choose and install the desired version of PostgreSQL. In this guide, you will use the PostgreSQL 11 release.

选择并安装所需的PostgreSQL版本。 在本指南中，您将使用PostgreSQL 11版本。

To install the PostgreSQL server use the following command:

要安装PostgreSQL服务器，请使用以下命令：

During the installation process you will be asked about importing a GPG key with a prompt like the following:

在安装过程中，系统将询问您有关导入GPG密钥的提示，如下所示：

...
Importing GPG key 0x442DF0F8:
 Userid     : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>"
 Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8
 Package    : pgdg-redhat-repo-42.0-5.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG
Is this ok [y/N]:

Confirm it with y so the installation can complete.

用y确认，以完成安装。

Now that the software is installed, you will perform some initialization steps to prepare a new database cluster for PostgreSQL.

现在已经安装了该软件，您将执行一些初始化步骤来为PostgreSQL准备一个新的数据库集群。

第2步-创建新的PostgreSQL数据库集群 (Step 2 — Creating a New PostgreSQL Database Cluster)

You have to create a new PostgreSQL database cluster before you can use your Postgres database. A database cluster is a collection of databases that are managed by a single server instance. Creating a database cluster consists of creating the directories in which the database data will be placed, generating the shared catalog tables, and creating the template1 and postgres databases.

您必须先创建一个新的PostgreSQL数据库集群，然后才能使用Postgres数据库。 数据库集群是由单个服务器实例管理的数据库的集合。 创建数据库集群包括：创建将在其中放置数据库数据的目录，生成共享目录表以及创建template1和postgres数据库。

The template1 database is needed to create a new database. Everything that is stored in it will be placed in a new database when it is created. A postgres database is a default database designed for use by users, utilities, and third-party applications.

需要template1数据库来创建新数据库。 创建时，其中存储的所有内容都将放置在新数据库中。 postgres数据库是一个默认数据库，旨在供用户，实用程序和第三方应用程序使用。

Create a new PostgreSQL database cluster with initdb:

使用initdb创建一个新的PostgreSQL数据库集群：

You will see the following output:

您将看到以下输出：

   

    Output
   
Initializing database ... OK

Now start and enable PostgreSQL using systemctl:

现在使用systemctl启动并启用PostgreSQL：

This will give the following output

这将给出以下输出

   

    Output
   
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.

Now that PostgreSQL is up and running, you will go over using roles to learn how Postgres works and how it is different from similar database management systems you may have used in the past.

现在PostgreSQL已启动并运行，您将继续使用角色来学习Postgres的工作原理以及它与您过去使用过的类似数据库管理系统的不同之处。

第3步-使用PostgreSQL角色和数据库 (Step 3 — Using PostgreSQL Roles and Databases)

By default, Postgres uses a concept called roles to handle in authentication and authorization. These are, in some ways, similar to regular Unix-style accounts, but Postgres does not distinguish between users and groups and instead prefers the more flexible term role.

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

Upon installation, Postgres is set up to use ident authentication, meaning that it associates Postgres roles with a matching Unix/Linux system account. If a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role.

安装后，Postgres设置为使用身份认证，这意味着它将Postgres角色与匹配的Unix / Linux系统帐户相关联。 如果Postgres中存在角色，则具有相同名称的Unix / Linux用户名可以作为该角色登录。

The installation procedure created a user account called postgres that is associated with the default Postgres role. In order to use Postgres, you can log in to that account.

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

There are a few ways to use this account to access Postgres.

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

切换到postgres帐户 (Switching Over to the postgres Account)

Switch over to the postgres account on your server by typing:

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

You can now access a Postgres prompt immediately by typing:

现在，您可以通过键入以下内容立即访问Postgres提示符：

This will log you into the PostgreSQL prompt, and from here you are free to interact with the database management system right away.

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

Exit out of the PostgreSQL prompt by typing:

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

This will bring you back to the postgres Linux command prompt. Now return to your original sudo account with the following:

这将带您回到postgres Linux命令提示符。 现在，使用以下命令返回您的原始sudo帐户：

在不切换帐户的情况下访问Postgres提示 (Accessing a Postgres Prompt Without Switching Accounts)

You can also run the command you’d like with the postgres account directly with sudo.

您也可以直接使用sudo使用postgres帐户运行所需的命令。

For instance, in the last example, you were instructed to get to the Postgres prompt by first switching to the postgres user and then running psql to open the Postgres prompt. You could do this in one step by running the single command psql as the postgres user with sudo, like this:

例如，在最后一个示例中，通过先切换到postgres用户，然后运行psql打开Postgres提示符，指示您进入Postgres提示符。 您可以通过使用sudo以postgres用户身份运行单个命令psql一步来完成此操作，如下所示：

This will log you directly into Postgres without the intermediary bash shell.

这将无需中间bash外壳即可直接将您登录到Postgres。

Again, you can exit the interactive Postgres session by typing:

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

In this step, you used the postgres account to reach the psql prompt. But many use cases require more than one Postgres role. Read on to learn how to configure new roles.

在此步骤中，您使用了postgres帐户进入psql提示符。 但是许多用例需要多个Postgres角色。 继续阅读以了解如何配置新角色。

第4步-创建新角色 (Step 4 — Creating a New Role)

Currently, you just have the postgres role configured within the database. You can create new roles from the command line with the createrole command. The --interactive flag will prompt you for the name of the new role and also ask whether it should have superuser permissions.

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

If you are logged in as the postgres account, you can create a new user by typing:

如果您以postgres帐户登录，则可以通过键入以下内容来创建新用户：

If, instead, you prefer to use sudo for each command without switching from your normal account, type:

相反，如果您希望对每个命令使用sudo而不用从普通帐户切换，请键入：

The script will prompt you with some choices and, based on your responses, execute the correct Postgres commands to create a user to your specifications. For this tutorial, create a sammy user and give it superuser privileges:

该脚本将提示您一些选择，并根据您的响应，执行正确的Postgres命令以创建符合您要求的用户。 在本教程中，创建一个狡猾的用户并为其赋予超级用户特权：

   

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

You can get more control by passing some additional flags. Check out the options by looking at the man page:

您可以通过传递一些其他标志来获得更多控制。 通过查看man页来检查选项：

Your installation of Postgres now has a new user, but you have not yet added any databases. The next section describes this process.

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

步骤5 —创建一个新数据库 (Step 5 — Creating a New Database)

Another assumption that the Postgres authentication system makes by default is that for any role used to log in, that role will have a database with the same name which it can access.

默认情况下，Postgres身份验证系统做出的另一个假设是，对于用于登录的任何角色，该角色将具有一个其名称可以访问的数据库。

This means that, if the user you created in the last section is called sammy, that role will attempt to connect to a database which is also called sammy by default. You can create the appropriate database with the createdb command.

这意味着，如果您在上一节中创建的用户称为sammy ，则该角色将尝试连接到默认情况下也称为sammy的数据库。 您可以使用createdb命令创建适当的数据库。

If you are logged in as the postgres account, you would type something like:

如果您以postgres帐户登录，则应输入以下内容：

If, instead, you prefer to use sudo for each command without switching from your normal account, you would type:

相反，如果您希望对每个命令使用sudo而不从普通帐户切换，则应输入：

This flexibility provides multiple paths for creating databases as needed.

这种灵活性提供了用于根据需要创建数据库的多种路径。

Now that you’ve created a new database, you will log in to it with your new role.

现在，您已经创建了新数据库，您将以新角色登录到该数据库。

第6步-使用新角色打开Postgres提示 (Step 6 — Opening a Postgres Prompt with the New Role)

To log in with ident based authentication, you’ll need a Linux user with the same name as your Postgres role and database.

要使用基于身份的ident验证登录，您需要一个Linux用户，该用户名称与Postgres角色和数据库相同。

If you don’t have a matching Linux user available, you can create one with the adduser command. You will have to do this from your non-root account with sudo privileges (meaning, not logged in as the postgres user):

如果没有可用的匹配Linux用户，则可以使用adduser命令创建一个。 您将必须使用sudo特权(意味着，不是以postgres用户身份登录)从非root帐户执行此操作：

Once this new account is available, you can either switch over and connect to the database by typing:

一旦此新帐户可用，您可以通过键入以下内容来切换并连接到数据库：

Or, you can do this inline:

或者，您可以内联执行此操作：

This command will log you in automatically.

此命令将自动登录。

If you want your user to connect to a different database, you can do so by specifying the database like this:

如果希望用户连接到其他数据库，可以通过指定数据库来做到这一点：

Once logged in, you can check your current connection information by typing:

登录后，您可以通过键入以下内容检查当前的连接信息：

This will show the following output:

这将显示以下输出：

   

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

This is useful if you are connecting to non-default databases or with non-default users.

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

Having connected to your database, you can now try out creating and deleting tables.

连接到数据库后，您现在可以尝试创建和删除表。

步骤7 —创建和删除表 (Step 7 — Creating and Deleting Tables)

Now that you know how to connect to the PostgreSQL database system, you can learn some basic Postgres management tasks.

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

First, create a table to store some data. As an example, you will make a table that describes some playground equipment.

首先，创建一个表来存储一些数据。 例如，您将制作一张表，描述一些游乐场设备。

The basic syntax for this command is as follows:

此命令的基本语法如下：

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

These commands give the table a name, and then define the columns as well as the column type and the max length of the field data. You can also optionally add table constraints for each column.

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

You can learn more at our How To Create, Remove & Manage Tables in PostgreSQL on a Cloud Server tutorial.

您可以在Cloud Server教程的PostgreSQL中如何创建，删除和管理表中了解更多信息。

For demonstration purposes, create a simple table like this:

为了演示，创建一个简单的表，如下所示：

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
);

These commands will create a table that inventories playground equipment. This starts with an equipment ID, which is of the serial type. This data type is an auto-incrementing integer. You’ve also given this column the constraint of primary key, which means that the values must be unique and not null.

这些命令将创建一个表，用于库存游乐场设备。 这从设备ID开始，该ID为serial类型。 此数据类型是自动递增的整数。 您还为该列提供了primary key约束，这意味着这些值必须唯一且不能为null。

For two of the columns (equip_id and install_date), the commands do not specify a field length. This is because some column types don’t require a set length because the length is implied by the type.

对于其中两列( equip_id和install_date )，这些命令未指定字段长度。 这是因为某些列类型不需要设置长度，因为该长度由类型隐含。

The next two commands create columns for the equipment type and color respectively, each of which cannot be empty. The command after these creates a location column and a constraint that requires the value to be one of eight possible values. The last command creates a date column that records the date on which you installed the equipment.

接下来的两个命令分别为设备type和color创建列，每个列不能为空。 这些之后的命令创建一个location列和一个约束，该约束要求该值是八个可能值之一。 最后一条命令创建一个date列，该列记录安装设备的日期。

You can see your new table by typing:

您可以通过键入以下内容查看新表格：

This will show the following output:

这将显示以下输出：

   

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

Your playground table is here, but there’s also something called playground_equip_id_seq that is of the type sequence. This is a representation of the serial type that you gave your equip_id column. This keeps track of the next number in the sequence and is created automatically for columns of this type.

您的游乐场表在这里，但是还有一个名为“ playground_equip_id_seq的东西，其类型为sequence 。 这是您赋予equip_id列的serial类型的表示。 这会跟踪序列中的下一个数字，并且会自动为此类型的列创建。

If you want to see just the table without the sequence, you can type:

如果只想查看不带序列的表，则可以键入：

This will yield the following:

这将产生以下结果：

   

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

In this step, you created a sample table. In the next step, you will try out adding, querying, and deleting entries in that table.

在此步骤中，您创建了一个示例表。 在下一步中，您将尝试在该表中添加，查询和删除条目。

步骤8 —在表中添加，查询和删除数据 (Step 8 — Adding, Querying, and Deleting Data in a Table)

Now that you have a table, you can insert some data into it.

现在您有了一个表，您可以在其中插入一些数据了。

As an example, add a slide and a swing by calling the table you want to add to, naming the columns, and then providing data for each column, like this:

例如，通过调用要添加到的表，命名列，然后为每一列提供数据来添加幻灯片和Swing，如下所示：

You should take care when entering the data to avoid a few common hangups. For one, do not wrap the column names in quotation marks, but the column values that you enter do need quotes.

输入数据时应格外小心，以避免一些常见的挂断。 首先，不要将列名用引号引起来，但是输入的列值确实需要用引号引起来。

Another thing to keep in mind is that you do not enter a value for the equip_id column. This is because this is automatically generated whenever a new row in the table is created.

要记住的另一件事是，您没有为equip_id列输入值。 这是因为只要在表中创建新行，它就会自动生成。

Retrieve the information you’ve added by typing:

通过键入以下内容来检索添加的信息：

You will see the following output:

您将看到以下输出：

   

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

Here, you can see that your equip_id has been filled in successfully and that all of your other data has been organized correctly.

在这里，您可以看到您的equip_id已成功填写，并且所有其他数据都已正确组织。

If the slide on the playground breaks and you have to remove it, you can also remove the row from your table by typing:

如果操场上的幻灯片折断了并且您必须将其删除，则还可以通过键入以下内容从表格中删除该行：

Query the table again:

再次查询表：

You will see the following:

您将看到以下内容：

   

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

Notice that your slide is no longer a part of the table.

请注意，您的幻灯片不再是表格的一部分。

Now that you’ve added and deleted entires in your table, you can try adding and deleting columns.

现在，您已经在表中添加和删除了整体，现在可以尝试添加和删除列。

步骤9 —在表中添加和删除列 (Step 9 — Adding and Deleting Columns from a Table)

After creating a table, you can modify it to add or remove columns. Add a column to show the last maintenance visit for each piece of equipment by typing:

创建表后，可以对其进行修改以添加或删除列。 通过输入以下内容以添加列以显示每台设备的最后一次维护访问：

If you view your table information again, you will see the new column has been added (but no data has been entered):

如果再次查看表信息，将看到已添加新列(但未输入任何数据)：

You will see the following:

您将看到以下内容：

   

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

Deleting a column is just as simple. If you find that your work crew uses a separate tool to keep track of maintenance history, you can delete the column by typing:

删除列也很简单。 如果发现工作人员使用单独的工具跟踪维护历史记录，则可以通过键入以下内容来删除该列：

This deletes the last_maint column and any values found within it, but leaves all the other data intact.

这将删除last_maint列及其中找到的所有值，但保留所有其他数据不变。

Having now added and deleted columns, you can try updating existing data in the final step.

现在添加和删除了列，您可以尝试在最后一步中更新现有数据。

步骤10 —更新表中的数据 (Step 10 — Updating Data in a Table)

So far, you’ve learned how to add records to a table and how to delete them, but this tutorial hasn’t yet covered how to modify existing entries.

到目前为止，您已经了解了如何向表中添加记录以及如何删除记录，但是本教程尚未介绍如何修改现有条目。

You can update the values of an existing entry by querying for the record you want and setting the column to the value you wish to use. You can query for the swing record (this will match every swing in your table) and change its color to red:

您可以通过查询所需记录并将列设置为希望使用的值来更新现有条目的值。 您可以查询swing记录(这将匹配表中的每个挥杆)并将其颜色更改为red ：

You can verify that the operation was successful by querying the data again:

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

You will see the following:

您将看到以下内容：

   

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

As you can see, your slide is now registered as being red.

如您所见，您的幻灯片现已注册为红色。

结论 (Conclusion)

You are now set up with PostgreSQL on your CentOS 7 server. However, there is still much more to learn with Postgres. Here are some more guides that cover how to use Postgres:

现在，您已在CentOS 7服务器上设置了PostgreSQL。 但是，Postgres还有很多要学习的东西。 以下是一些其他指南，介绍了如何使用Postgres：

• A comparison of relational database management systems

  关系数据库管理系统的比较

• Learn how to create and manage tables with Postgres

  了解如何使用Postgres创建和管理表格

• Get better at managing roles and permissions

  更好地管理角色和权限

• Craft queries in Postgres with Select

  使用Select在Postgres中制作查询

翻译自: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7