# 应用中的 PostgreSQL
> 使用 PHP、Perl、Python 和 Java 将应用程序连接到 PostgreSQL，了解查询和事务处理示例。
---
LLMS 索引： [llms.txt](/llms.txt)
---
## 独立的用户 {#intro-section-6-1}
上一章介绍了如何以 `postgres` 用户的身份连接数据库服务器。PostgreSQL 刚安装好时，数据库中只有这个用户。由于 `postgres` 是超级用户，应用程序不应使用它来连接数据库。更好的做法是新建一个用户，让它成为一个独立数据库的所有者，这样它的权限就会限制在这个数据库内。
```psql
postgres=# CREATE USER app PASSWORD 'p@ssw0rd';
```
```sql
CREATE ROLE
```
```psql
postgres=# CREATE DATABASE appdb OWNER app;
```
```sql
CREATE DATABASE
```
有关用户和权限的详细信息，请参阅 [postgrespro.com/doc/user-manag]() 和 [postgrespro.com/doc/ddl-priv]()。
要以新用户的身份连接新建的数据库并开始使用，运行：
```psql
postgres=# \c appdb app localhost 5432
```
```text
Password for user app: ***
You are now connected to database "appdb" as user
"app" on host "127.0.0.1" at port "5432".
```
```psql
appdb=>
```
这个命令依次接受四个参数：数据库名（appdb）、用户名（app）、节点（localhost 或 127.0.0.1）和端口号（5432）。
请注意，提示符中变化的不只是数据库名：井号（`#`）也变成了大于号（`>`）。井号表示超级用户权限，类似于 Unix 中的 `root` 用户。
`app` 用户在 `appdb` 数据库中拥有全部权限。例如，这个用户可以创建一张表：
```psql
appdb=> CREATE TABLE greeting(s text);
```
```sql
CREATE TABLE
```
```psql
appdb=> INSERT INTO greeting VALUES ('Hello, world!');
```
```sql
INSERT 0 1
```
## 远程连接 {#intro-section-6-2}
在我们的示例中，客户端和数据库位于同一台计算机上。也可以将 PostgreSQL 安装到独立的服务器上，再从其他计算机（例如应用服务器）连接。在这种情况下，必须用数据库服务器的地址代替 localhost。不过，这还不够：出于安全考虑，PostgreSQL 默认只允许本地连接。
要从外部连接数据库，必须修改两个文件。
首先修改保存**主要配置项**的 postgresql.conf 文件。它通常位于数据目录中。
找到定义 PostgreSQL 监听哪些网络接口的这一行：
```text
#listen_addresses = 'localhost'
```
将它替换为：
```text
listen_addresses = '*'
```
接着修改保存**身份验证设置**的 pg\_hba.conf 文件。
客户端尝试连接服务器时，PostgreSQL 会在这个文件中查找第一条与连接匹配的记录，匹配依据有四个参数：连接类型、数据库名、用户名和客户端 IP 地址。这条记录还指定用户必须如何证明自己的身份。
例如，在 Debian 和 Ubuntu 上，这个文件包含下面这项设置（最上面以井号开头的一行是注释）：
```bash
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
```
这表示，以任意用户（`all`）身份连接任意数据库（`all`）的本地连接（`local`），都必须通过 `peer` 方法进行身份验证（显然，本地连接不需要 IP 地址）。
`peer` 方法会让 PostgreSQL 向操作系统获取当前用户名，并假定操作系统已经完成了必要的身份验证检查（提示用户输入密码）。因此，在类 Linux 操作系统上，用户连接本地服务器时通常不必输入密码。
但 Windows 不支持这种本地连接。（译注：此处照译原版；PostgreSQL 17 文档说明的是 Windows 默认不创建 Unix 域套接字，参见[连接设置](https://www.postgresql.org/docs/17/runtime-config-connection.html)。）因此，这一行是这样的：
```bash
# TYPE DATABASE USER ADDRESS METHOD
host all all 127.0.0.1/32 md5
```
这表示，从本机地址（`127.0.0.1`）发起、以任意用户（`all`）身份连接任意数据库（`all`）的网络连接（`host`），必须使用 `md5` 方法进行验证。这种方法要求用户输入密码。
要允许 `app` 用户从任意地址访问 `appdb` 数据库，并要求其提供有效密码，可以在 `pg_hba.conf` 文件末尾加入这一行：
```text
host appdb app all md5
```
修改配置文件后，别忘了让服务器重新读取设置：
```psql
postgres=# SELECT pg_reload_conf();
```
有关身份验证设置的详细信息，请参阅 [postgrespro.com/doc/client-authentication.html]()。（译注：上面的重新加载步骤照录原版。修改 listen_addresses 后需要重启服务器，仅调用 pg_reload_conf() 不会使该参数生效；参见 [PostgreSQL 17 连接设置](https://www.postgresql.org/docs/17/runtime-config-connection.html)。）
## 测试服务器连接 {#intro-section-6-3}
要在应用程序中访问 PostgreSQL，需要找到合适的库，并安装相应的数据库驱动程序。驱动程序通常是对 `libpq` 的封装；这个标准库实现了 PostgreSQL 的客户端—服务器协议，不过也可以采用其他实现方式。库为应用开发者提供了便捷的接口，以使用协议的底层功能。
下面几个小节给出了一些常用语言的简单代码片段，可以帮助你快速检查与已安装、配置好的数据库系统之间的连接。
这些代码片段只是最小示例，不应在生产环境中使用；它们没有任何其他功能，甚至连错误处理都没有。
如果使用 Windows，为了正确显示扩展字符集，可能需要在命令提示符窗口中切换到 TrueType 字体（例如“Lucida Console”或“Consolas”），并更改代码页。例如，要使用俄语，可以运行以下命令：
```text
C:\> chcp 1251
```
```text
Active code page: 1251
```
```text
C:\> set PGCLIENTENCODING=WIN1251
```
### PHP {#intro-subsection-6-3-1}
PHP 通过一个专门的扩展与 PostgreSQL 交互。在 Linux 上，除了 PHP 本身，还必须安装包含这个扩展的软件包：
```bash
$ sudo apt-get install php-cli php-pgsql
```
Windows 版 PHP 可以从 PHP 网站下载安装：[windows.php.net/download]()。二进制发行包已经包含 PostgreSQL 扩展，但必须在 php.ini 文件中找到下面这一行，去掉分号以取消注释：
```text
;extension=php_pgsql.dll
```
示例程序（test.php）：
```php
```
运行以下命令：
```bash
$ php test.php
Hello, world!
```
PHP 文档介绍了这个 PostgreSQL 扩展：[php.net/manual/en/book.pgsql.php]()。
### Perl {#intro-subsection-6-3-2}
在 Perl 中，数据库操作通过 DBI 接口实现。Debian 和 Ubuntu 已经预装 Perl，因此只需安装驱动程序：
```bash
$ sudo apt-get install libdbd-pg-perl
```
Windows 版 Perl 有多种发行版本，列于 [perl.org/get.html]()。ActiveState Perl 和 Strawberry Perl 等常见发行版本已经预装了 PostgreSQL 驱动程序。
示例程序（test.pl）：
```perl
use DBI;
use open ':std', ':utf8';
my $conn = DBI->connect(
'dbi:Pg:dbname=appdb;host=localhost;port=5432',
'app','p@ssw0rd') or die;
my $query = $conn->prepare('SELECT * FROM greeting');
$query->execute() or die;
while (my @row = $query->fetchrow_array()) {
print @row[0]."\n";
}
$query->finish();
$conn->disconnect();
```
运行以下命令：
```bash
$ perl test.pl
```
```text
Hello, world!
```
有关这个接口的说明，请参阅文档：[metacpan.org/pod/DBD::Pg]()。
### Python {#intro-subsection-6-3-3}
Python 通常使用 psycopg 库（读作“psycho-pee-gee”）与 PostgreSQL 交互。
较新的 Debian 和 Ubuntu 版本已经预装 Python 3，因此只需添加相应的驱动程序：
```bash
$ sudo apt-get install python3-psycopg2
```
Windows 版 Python 可以从 [python.org]() 网站下载。psycopg 库可从 [initd.org/psycopg]() 获取（请选择与已安装的 Python 版本相对应的版本）。所需的文档也都可以在那里找到。
示例程序（test.py）：
```python
import psycopg2
conn = psycopg2.connect(
host='localhost', port='5432', database='appdb',
user='app', password='p@ssw0rd')
cur = conn.cursor()
cur.execute('SELECT * FROM greeting')
rows = cur.fetchall()
for row in rows:
print(row[0])
conn.close()
```
运行以下命令：
```bash
$ python3 test.py
```
```text
Hello, world!
```
### Java {#intro-subsection-6-3-4}
Java 通过 JDBC 接口访问数据库。需要安装 Java SE 11 和 JDBC 驱动程序包。
```bash
$ sudo apt-get install openjdk-11-jdk
$ sudo apt-get install libpostgresql-jdbc-java
```
Windows 版 JDK 可以从 [oracle.com/technetwork/java/javase/downloads]() 下载。JDBC 驱动程序可从 [jdbc.postgresql.org]() 获取（请选择与系统中已安装的 JDK 相对应的版本）。所需的文档也都可以在那里找到。
来看一个示例程序（Test.java）：
```java
import java.sql.*;
public class Test {
public static void main(String[] args)
throws SQLException {
Connection conn = DriverManager.getConnection(
"jdbc:postgresql://localhost:5432/appdb",
"app", "p@ssw0rd");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(
"SELECT * FROM greeting");
while (rs.next()) {
System.out.println(rs.getString(1));
}
rs.close();
st.close();
conn.close();
}
}
```
编译并执行程序，同时指定 JDBC 驱动类的路径（在 Windows 上，路径之间用分号分隔，而不是冒号）：
```bash
$ javac Test.java
```
```bash
$ java -cp .:/usr/share/java/postgresql-jdbc4.jar \
Test
```
```text
Hello, world!
```
## 备份 {#intro-section-6-4}
虽然我们的数据库只有一张表，保证数据的持久性仍然很重要。应用程序的数据量较小时，最简单的备份方式是使用 `pg_dump` 工具：
```bash
$ pg_dump appdb > appdb.dump
```
在文本编辑器中打开生成的 appdb.dump 文件，就会看到一些标准 SQL 命令，用于创建 `appdb` 中的所有对象，并填入数据。将这个文件传给 `psql`，就可以恢复数据库的内容。例如，可以创建一个新数据库，再将所有数据导入其中：
```bash
$ createdb appdb2
```
```bash
$ psql -d appdb2 -f appdb.dump
```
上一章介绍的演示数据库就是以这种格式分发的。
`pg_dump` 工具还有许多功能值得了解：[postgrespro.com/doc/app-pgdump]()。其中有些功能只有以自定义格式导出数据时才能使用。在这种情况下，恢复数据必须使用 `pg_restore` 工具，而不是 `psql`。
无论采用哪种格式，`pg_dump` 都只能备份单个数据库的内容。要备份整个数据库集簇，包括全部数据库、用户和表空间，需要使用另一个命令：`pg_dumpall`。
大型项目需要细致、全面的备份策略。这时，更好的选择是为数据库集簇制作物理二进制副本，可以使用 `pg_basebackup` 工具完成：
```bash
$ pg_basebackup -D backup
```
这个命令会在 backup 目录中备份整个数据库集簇。要从备份恢复集簇，将其移至数据目录，然后重启服务器。
有关备份和恢复工具的详细信息，请参阅文档：[postgrespro.com/doc/backup]()。
PostgreSQL 的内置功能几乎可以实现所有备份需求，但需要完成多个步骤，而且缺乏自动化。为此，许多公司都开发了自己的备份工具。Postgres Professional 也有这样的工具，名为 **pg\_probackup**。使用它的免费版本，可以执行页级增量备份、保证数据完整性、在处理大量数据时使用并行执行和压缩，并实施多种备份策略。
完整文档见 [postgrespro.com/doc/app-pgprobackup]()。
## 下一步做什么？ {#intro-section-6-5}
现在可以开始开发应用程序了。从数据库的角度看，应用程序总是由服务端和客户端两部分组成。服务端包括与数据库系统相关的一切：表、索引、视图、触发器、存储函数和存储过程。客户端则包含在数据库之外运行、并连接数据库的部分；从数据库的角度看，这部分是厚客户端还是应用服务器，并没有区别。
有一个重要问题，没有明确的答案：业务逻辑应该放在哪里？
一种常见的做法是把逻辑移出数据库，全部在客户端实现。开发者不熟悉关系型数据库系统的全部能力时，往往会这样做，因为他们更愿意依赖熟悉的东西，也就是应用代码。
在这种情况下，数据库在应用程序中就退居次要位置，只负责数据持久化，即可靠地保存数据。人们还常常用额外的抽象层隔离数据库系统，例如 ORM 工具，它能根据开发者熟悉的编程语言结构自动生成数据库查询。有时，选择这类方案是为了让应用程序可以移植到任意数据库系统。
这种做法有其存在的理由：如果系统能够运行，并满足所有业务目标，何乐而不为？
不过，这种方案也有一些明显的缺点：
- **数据一致性由应用程序保证。** 所有必要的检查都由应用程序完成，而不依靠数据库系统保证数据一致性（这恰恰是关系型数据库系统特别擅长的事）。可以肯定，数据库中迟早会出现不一致的数据。届时，要么修正这些错误，要么让应用程序学会处理它们。如果多个应用程序共用同一个数据库，不使用数据库层面的约束来保证数据一致性，就不切实际了。
- **性能不尽如人意。** ORM 系统可以在数据库之上构建一个抽象层，但其生成的 SQL 查询质量颇值得怀疑。通常，它会执行多条小查询，每条单独来看都很快。这种模式只能应付数据量小、负载低的场景，在数据库一侧几乎无法优化。
- **应用代码变得更复杂。** 使用面向应用的编程语言，无法编写出能够自动、正确转换为 SQL 的真正复杂的查询。因此，复杂的数据处理（当然，前提是确有需要）必须在应用层实现，并事先从数据库中取出全部所需数据，这又会增加网络上的数据传输。此外，数据库系统提供的扫描、连接、排序和聚合等算法经过多年改进和优化，性能必定优于应用代码。
要充分使用数据库的功能，包括完整性约束和存储过程，就必须深入理解它的能力。需要掌握 SQL 才能编写查询，还要学习一种服务端编程语言（通常是 PL/pgSQL），才能创建函数和触发器。作为回报，你将获得一个可靠的工具，它是任何信息系统架构中最重要的组成部分之一。
无论如何，业务逻辑究竟放在服务端还是客户端，需要你自己决定。我们只想提醒一句：不必走极端，答案往往在两者之间。