Skip to main content

PostgreSQL 连接 URI 简介

介绍​

¥Introduction

在设计和配置数据库支持的应用时，连接到数据库服务器通常是你需要完成的首要任务之一。虽然有很多方法可以向应用提供地址、监听端口、凭据和其他详细信息，但连接 URI（有时称为连接字符串或连接 URL）是以紧凑格式指定复杂配置的最强大、最灵活的方法之一。

¥Connecting to your database server is usually one of the first tasks you need to accomplish when designing and configuring database-backed applications. While there are many methods of providing the address, listening port, credentials, and other details to applications, connection URIs, sometimes called connection strings or connection URLs, are one of the most powerful and flexible ways of specifying complex configuration in a compact format.

在本指南中，我们将讨论如何使用 PostgreSQL 数据库信息和 authentication 详细信息来格式化连接 URI。连接 URI 分为几个部分，我们将逐一介绍。

¥In this guide, we'll talk about how to format a connection URI with your PostgreSQL database information and authentication details. Connection URIs are divided into sections, so we'll cover each part as we go along.

百分比编码值​

¥Percent encoding values

在开始之前，我们应该提到 PostgreSQL 连接 URI 需要 百分比编码值。这意味着 URL 中任何具有特殊含义的字符都必须转换为其百分比编码的对应字符，以确保库和应用能够正确解释它们。

¥Before we begin, we should mention that PostgreSQL connection URIs expect percent-encoded values. This means that any characters that have a special meaning within the URL must be converted to their percent-encoded counterparts to ensure that libraries and applications can interpret them correctly.

你应该进行百分比编码的字符包括：

¥Characters you should percent-encode include:

  • (空格):%20

    ¥(space): %20

  • %：%25

  • &：%26

  • /：%2F

  • :：%3A

  • =：%3D

  • ?：%3F

  • @：%40

  • [：%5B

  • ]：%5D

这些参数在连接 URI 中具有特殊含义。

¥These have special meaning within the connection URI.

因此，如果你的密码是……：

¥So if your password is...:

pe@ce&lo\/3

...你需要在连接 URI 中将其指定为：

¥...you'll want to specify it within the connection URI as:

pe%40ce%26lo\%2F3

如果你不确定某个字符是否应该进行百分号编码，通常最好还是对其进行编码。例如，如果你不确定 \ 字符是否为保留字符，为了安全起见，可以使用其百分比编码的等效字符 %5C：

¥If you are unsure about whether a character should be percent-encoded, it's usually best to encode it anyways. For example, if you are unsure if the \ character is reserved, you can use its percent-encoded equivalent, %5C, to be safe:

pe%40ce%26lo%5C%2F3

在构建连接 URI 时请记住这一点。

¥Keep this in mind as you build your connection URIs.

快速概览​

¥A quick overview

在深入了解细节之前，我们可以先看看 PostgreSQL 连接 URI 的规范：

¥Before we get into the details, we can look at the spec for a PostgreSQL connection URI:

postgres[ql]://[username[:password]@][host[:port],]/database[?parameter_list]

\_____________/\____________________/\____________/\_______/\_______________/
     |                   |                  |          |            |
     |- schema           |- userspec        |          |            |- parameter list
                                            |          |
                                            |          |- database name
                                            |
                                            |- hostspec

方括号中的部分表示可选部分。你可能已经注意到，URI 的大部分内容都是可选的。URI 中可以编码的信息可能非常多。

¥The parts in square brackets indicate optional parts. You may have noticed that most parts of the URI are optional. It might also be apparent that there are many pieces of information you can encode in the URI.

每个组件的简要描述：

¥A quick description of each of the individual components:

  • postgres[ql]：模式标识符。可以是 postgresql 或简称 postgres。

    ¥postgres[ql]: The schema identifier. Can be postgresql or simply postgres.

  • userspec：URI 的可选组件，可用于指定要连接的用户名和密码。

    ¥userspec: An optional component of the URI that can be used to specify the user and password to connect as.

    • username：可选用户名。如果包含，则从第二个斜杠 (/) 开始，一直到冒号 (:)（如果同时提供了密码）或 @ 符号 (@)（如果未提供密码）。

      ¥username: An optional username. If included, it should start after the second slash (/) and continue until a colon (:) (if a password is also provided) or until an at sign (@) (if a password is not provided).

    • password：可选密码。如果包含，则从冒号 (:) 开始，一直到 @ 符号 (@)。

      ¥password: An optional password. If included, it begins after a colon (:) and continues until the at sign (@).

  • hostspec：用于指定要连接的主机名和端口的可选组件。

    ¥hostspec: An optional component used to specify the hostname and port to connect to.

    • host：要连接的服务器的可选 IP 地址、DNS 名称或本地可解析名称。主机名会一直持续到冒号 (:)（如果包含端口）或斜杠（如果不包含端口）

      ¥host: An optional IP address, DNS name, or locally resolvable name of the server to connect to. The host continues until a colon (:) (if a port is included) or until a slash (if a port is not included)

    • port：可选端口规范，用于指示 PostgreSQL 在服务器上监听的端口。端口以冒号 (:) 开头，一直到斜杠 (/)。

      ¥port: An optional port specification to indicate which port PostgreSQL is listening to on the server. The port begins with a colon (:) and continues until the slash (/)

  • database name：要连接的单个数据库的名称。

    ¥database name: The name of the individual database to connect to.

  • parameter list：可能影响连接行为的附加参数的可选列表。参数列表以问号 (?) 开头。

    ¥parameter list: An optional list of additional parameters that can affect the connection behavior. The parameter list begins with a question mark (?).

    • parameter pairs：参数列表由键值对组成。每对中的键和值用等号 (=) 分隔，每对与下一对之间用“与”号 (&) 分隔。

      ¥parameter pairs: The parameter list is composed of key-value pairs. The key and value within each pair are separated by an equal sign (=) and each pair is separated from the next by an ampersand (&).

以下是一个包​​含所有这些组件的 PostgreSQL 连接 URI 示例：

¥Here is an example of a PostgreSQL connection URI that incorporates all of these components:

postgresql://sally:sallyspassword@dbserver.example:5555/userdata?connect_timeout=10&sslmode=require&target_session_attrs=primary
    ^          ^         ^               ^           ^     ^          ^
    |- schema  |         |- password     |- host     |     |          |- parameter list
               |                                     |     |
               |- username                           |     |- database
                                                     |
                                                     |- port

指定 URI 类型​

¥Specifying the URI type

连接 URI 中的项通常是协议规范或应用类型。由于 URI 将用于连接和验证 PostgreSQL 数据库，因此我们需要使用一个符号来向我们正在使用的应用和库表明这一点。

¥The item in a connection URI is usually the protocol specification or application type. Since the URI will be used to connect and authenticate to a PostgreSQL database, we need to use a signifier that signifies that to the applications and libraries we're using.

PostgreSQL 项目同时接受 postgresql:// 和 postgres:// 作为有效的 URI 模式指示符。因此，你应该使用以下两个字符串之一作为连接 URI 的开头：

¥The PostgreSQL project accepts both postgresql:// and postgres:// as valid URI schema designators. Therefore, you should start your connection URI with either of these two strings:

postgresql://
postgres://

模式指示符将确保后续信息在正确的上下文中得到解释。

¥The schema designator will ensure that the information that follows is interpreted in the correct context.

指定用户名和密码​

¥Specifying a username and password

URI 的下一部分是用户凭据。在规范中，这被称为 userspec。userspec 从技术上讲是可选的，但如果你不想依赖应用或数据库配置的默认值，则通常是必需的。

¥The next part of the URI is the user credentials. This is called the userspec in the specification. The userspec is technically optional, but is typically required if you don't want to rely on defaults configured by either your application or the database.

如果包含，则 userspec 从冒号和双斜杠 (://) 开始，一直到 @ 符号 (@)。

¥If included, the userspec begins after the colon and double forward slash (://) and ends with an at sign (@).

要指定应用应尝试连接到本地计算机上的默认 PostgreSQL 端口 (5432)，可以使用：

¥To specify only a username, you can place it in between those two symbols:

postgresql://username@

要仅指定用户名，可以将其放在这两个符号之间：

¥To specify a username and a password, provide the username first, followed by a colon (:), and then the password and at sign:

postgresql://username:password@

应用可以通过记录包含终止符号 (@) 将此数据解释为 userspec。如果未指定，则将使用你的操作系统用户名。

¥Applications are able to interpret this data as the userspec by noting the inclusion of the terminating at sign (@). If only one field is provided (if no colon is present between the slashes and the at sign), it is interpreted as a username.

指定服务器监听的位置​

¥Specifying where the server is listening

userspec 之后是 hostspec，它定义了服务器监听的位置。同样，hostspec 是可选的，但如果你不依赖客户端或库中设置的默认值，它几乎总是有用的。

¥After the userspec comes the hostspec which defines where the server is listening. The hostspec is, again, optional, but almost always useful if you aren't relying on defaults set in your client or library.

hostspec 由 host 和可选的 port 组成。host 可以是本地可解析的主机名、由 DNS 等外部名称系统解析的名称、IP 地址或其他直接地址。端口表示 PostgreSQL 正在监听的端口号。

¥The hostspec consists of a host and an optional port. The host can either be a locally resolvable host name, a name resolved by an external name system like DNS, or an IP address or other direct address. The port signifies the port number where PostgreSQL is listening.

要指定应用尝试连接到本地计算机上的默认 PostgreSQL 端口 (5432)，你可以使用：

¥To specify that the application should attempt to connect to the default PostgreSQL port (5432) on the local computer, you can use:

postgresql://localhost

如果你需要包含用户名和密码，则该信息应放在最前面，并用 at 符号分隔：

¥If you needed to include a username and password, that information would come first and be separated by the at sign:

postgresql://username:password@localhost

要指定用户名和密码，请先输入用户名，然后是冒号 ()，再输入密码和 at 符号：例如，要连接到 198.51.100.22 主机上的 3333 端口，你可以使用：

¥To specify a remote server running on a non-standard port, separate those details with a colon. For example, to connect to port 3333 on a host at 198.51.100.22, you could use:

postgresql://username:password@198.51.100.22:3333

实际上，你可以提供多个主机和端口对，并用逗号 (,) 分隔，以告知应用在无法访问第一个服务器时尝试访问后者。例如，要扩展前面的示例，使其包含一个在 198.51.100.33 上监听 5555 端口的后备服务器，你可以使用：

¥You can actually provide more than one host and port pairs, separated by the commas (,) to tell the application to try the latter servers if the first cannot be reached. For example, to extend the previous example to include a fallback server listening on port 5555 on 198.51.100.33, you could use:

postgresql://username:password@198.51.100.22:3333,198.51.100.33:5555

符合规范的客户端和应用将首先尝试连接到在 198.51.100.22:3333 监听的服务器。如果仅提供一个字段（斜杠和 @ 符号之间没有冒号），则该字段将被解释为用户名。

¥Conforming clients and applications will try to first connect to the server listening at 198.51.100.22:3333. If that fails, they will try to reach a PostgreSQL database listening on 198.51.100.33:5555.

提供数据库名称​

¥Providing the database name

hostspec 之后，下一个数据是数据库名称。虽然并非所有数据库管理系统都如此，但使用 PostgreSQL，建立连接时必须连接到特定的数据库。

¥After the hostspec, the next piece of data is the database name. While not true for all database management systems, with PostgreSQL, you must connect to a specific database when establishing a connection.

数据库名称以正斜杠 (/) 开头，直到行尾或问号 (?)。如果你不依赖默认值，则必须包含数据库名称。

¥The database name begins with a forward slash (/) and proceeds until either the end of the line or a question mark (?). You must include the database name if you aren't relying on the default values.

为了处理模糊输入，例如 07/12/2019（根据格式，它可能被解释为 2019 年 7 月 12 日或 2019 年 12 月 7 日），你可以使用 sales 设置预期排序。

¥To connect to a database called sales hosted on a PostgreSQL server listening on 198.51.100.22:3333, you could type:

postgresql://username:password@198.51.100.22:3333/sales

指定其他参数​

¥Specifying additional parameters

连接 URI 的最后一部分用于为连接提供附加参数。参数列表以问号 (?) 开头，一直到行尾。

¥The last part of the connection URI is used to provide additional parameters for the connection. The list of parameters is introduced by a leading question mark (?) and continues until the end of the line.

列出的每个参数都定义为用等号 (=) 连接的键值对。在第一个参数对之后，每个附加的键值对都用“&”符号 (&) 分隔。

¥Each parameter listed is defined as a key and value pair joined with an equals sign (=). After the first parameter pair, each additional key-value pair is separated by an ampersand (&).

例如，要指定客户端应为我们之前定义的连接应用 10 秒超时，你可以使用：

¥For example, to specify that the client should apply a 10 second timeout for the connection we were previously defining, you could use:

postgresql://username:password@198.51.100.22:3333/sales?connect_timeout=10

如果你想提供其他参数，请在之后添加它们，并在每对参数之间使用 & 符号 (&)。例如，我们可以额外指定需要 SSL，并且仅当服务器是副本集中的主服务器时才进行连接，我们还可以添加：

¥If you wanted to provide additional parameters, you'd add them afterwards with an ampersand (&) between each pair. For instance, we could additionally specify that we require SSL and want to connect only if the server is a primary in a replica set, we could additionally add:

postgresql://username:password@198.51.100.22:3333/sales?connect_timeout=10&sslmode=require&target_session_attrs=primary

PostgreSQL 文档中包含一个 完整参数列表，你可以阅读以了解更多信息。

¥The PostgreSQL documentation has a full list of parameters that you can read to learn more.

结论​

¥Conclusion

在本指南中，我们讨论了什么是 PostgreSQL 连接 URI、如何解释其各个组成部分，以及如何根据一组连接信息构建自己的 URI。连接 URI 将连接到给定数据库所需的所有信息编码在一个字符串中。由于这种灵活性及其广泛采用，了解如何解析和构造这些字符串非常有帮助。

¥In this guide, we discussed what a PostgreSQL connection URI is, how to interpret the various components, and how to construct your own URIs given a set of connection information. Connection URIs encode all of the information required to connect to a given database within a single string. Because of this flexibility and due to their wide adoption, understanding how to parse and construct those strings can be pretty helpful.