跳转到主要内容

6 应用中的 PostgreSQL

使用 PHP、Perl、Python 和 Java 将应用程序连接到 PostgreSQL，了解查询和事务处理示例。

独立的用户

上一章介绍了如何以 postgres 用户的身份连接数据库服务器。PostgreSQL 刚安装好时，数据库中只有这个用户。由于 postgres 是超级用户，应用程序不应使用它来连接数据库。更好的做法是新建一个用户，让它成为一个独立数据库的所有者，这样它的权限就会限制在这个数据库内。

postgres=# CREATE USER app PASSWORD 'p@ssw0rd';
CREATE ROLE
postgres=# CREATE DATABASE appdb OWNER app;
CREATE DATABASE

有关用户和权限的详细信息，请参阅 postgrespro.com/doc/user-manag 和 postgrespro.com/doc/ddl-priv。

要以新用户的身份连接新建的数据库并开始使用，运行：

postgres=# \c appdb app localhost 5432

Password for user app: ***
You are now connected to database "appdb" as user
"app" on host "127.0.0.1" at port "5432".
appdb=>

这个命令依次接受四个参数：数据库名（appdb）、用户名（app）、节点（localhost 或 127.0.0.1）和端口号（5432）。

请注意，提示符中变化的不只是数据库名：井号（#）也变成了大于号（>）。井号表示超级用户权限，类似于 Unix 中的 root 用户。

app 用户在 appdb 数据库中拥有全部权限。例如，这个用户可以创建一张表：

appdb=> CREATE TABLE greeting(s text);
CREATE TABLE
appdb=> INSERT INTO greeting VALUES ('Hello, world!');
INSERT 0 1

远程连接

在我们的示例中，客户端和数据库位于同一台计算机上。也可以将 PostgreSQL 安装到独立的服务器上，再从其他计算机（例如应用服务器）连接。在这种情况下，必须用数据库服务器的地址代替 localhost。不过，这还不够：出于安全考虑，PostgreSQL 默认只允许本地连接。

要从外部连接数据库，必须修改两个文件。

首先修改保存主要配置项的 postgresql.conf 文件。它通常位于数据目录中。

找到定义 PostgreSQL 监听哪些网络接口的这一行：

#listen_addresses = 'localhost'

将它替换为：

listen_addresses = '*'

接着修改保存身份验证设置的 pg_hba.conf 文件。

客户端尝试连接服务器时，PostgreSQL 会在这个文件中查找第一条与连接匹配的记录，匹配依据有四个参数：连接类型、数据库名、用户名和客户端 IP 地址。这条记录还指定用户必须如何证明自己的身份。

例如，在 Debian 和 Ubuntu 上，这个文件包含下面这项设置（最上面以井号开头的一行是注释）：

#   TYPE   DATABASE   USER   ADDRESS   METHOD
   local        all    all               peer

这表示，以任意用户（all）身份连接任意数据库（all）的本地连接（local），都必须通过 peer 方法进行身份验证（显然，本地连接不需要 IP 地址）。

peer 方法会让 PostgreSQL 向操作系统获取当前用户名，并假定操作系统已经完成了必要的身份验证检查（提示用户输入密码）。因此，在类 Linux 操作系统上，用户连接本地服务器时通常不必输入密码。

但 Windows 不支持这种本地连接。（译注：此处照译原版；PostgreSQL 17 文档说明的是 Windows 默认不创建 Unix 域套接字，参见连接设置。）因此，这一行是这样的：

#   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 文件末尾加入这一行：

host      appdb    app            all      md5

修改配置文件后，别忘了让服务器重新读取设置：

postgres=# SELECT pg_reload_conf();

有关身份验证设置的详细信息，请参阅 postgrespro.com/doc/client-authentication.html。（译注：上面的重新加载步骤照录原版。修改 listen_addresses 后需要重启服务器，仅调用 pg_reload_conf() 不会使该参数生效；参见 PostgreSQL 17 连接设置。）

测试服务器连接

要在应用程序中访问 PostgreSQL，需要找到合适的库，并安装相应的数据库驱动程序。驱动程序通常是对 libpq 的封装；这个标准库实现了 PostgreSQL 的客户端—服务器协议，不过也可以采用其他实现方式。库为应用开发者提供了便捷的接口，以使用协议的底层功能。

下面几个小节给出了一些常用语言的简单代码片段，可以帮助你快速检查与已安装、配置好的数据库系统之间的连接。

这些代码片段只是最小示例，不应在生产环境中使用；它们没有任何其他功能，甚至连错误处理都没有。

如果使用 Windows，为了正确显示扩展字符集，可能需要在命令提示符窗口中切换到 TrueType 字体（例如“Lucida Console”或“Consolas”），并更改代码页。例如，要使用俄语，可以运行以下命令：

C:\> chcp 1251
Active code page: 1251
C:\> set PGCLIENTENCODING=WIN1251

PHP

PHP 通过一个专门的扩展与 PostgreSQL 交互。在 Linux 上，除了 PHP 本身，还必须安装包含这个扩展的软件包：

$ sudo apt-get install php-cli php-pgsql

Windows 版 PHP 可以从 PHP 网站下载安装：windows.php.net/download。二进制发行包已经包含 PostgreSQL 扩展，但必须在 php.ini 文件中找到下面这一行，去掉分号以取消注释：

;extension=php_pgsql.dll

示例程序（test.php）：

<?php
  $conn = pg_connect('host=localhost port=5432 ' .
                     'dbname=appdb user=app ' .
                     'password=p@ssw0rd') or die;
  $query = pg_query('SELECT * FROM greeting') or die;
  while ($row = pg_fetch_array($query)) {
    echo $row[0].PHP_EOL;
  }
  pg_free_result($query);
  pg_close($conn);
?>

运行以下命令：

$ php test.php
Hello, world!

PHP 文档介绍了这个 PostgreSQL 扩展：php.net/manual/en/book.pgsql.php。

Perl

在 Perl 中，数据库操作通过 DBI 接口实现。Debian 和 Ubuntu 已经预装 Perl，因此只需安装驱动程序：

$ sudo apt-get install libdbd-pg-perl

Windows 版 Perl 有多种发行版本，列于 perl.org/get.html。ActiveState Perl 和 Strawberry Perl 等常见发行版本已经预装了 PostgreSQL 驱动程序。

示例程序（test.pl）：

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

运行以下命令：

$ perl test.pl
Hello, world!

有关这个接口的说明，请参阅文档：metacpan.org/pod/DBD::Pg。

Python

Python 通常使用 psycopg 库（读作“psycho-pee-gee”）与 PostgreSQL 交互。

较新的 Debian 和 Ubuntu 版本已经预装 Python 3，因此只需添加相应的驱动程序：

$ sudo apt-get install python3-psycopg2

Windows 版 Python 可以从 python.org 网站下载。psycopg 库可从 initd.org/psycopg 获取（请选择与已安装的 Python 版本相对应的版本）。所需的文档也都可以在那里找到。

示例程序（test.py）：

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

运行以下命令：

$ python3 test.py
Hello, world!

Java

Java 通过 JDBC 接口访问数据库。需要安装 Java SE 11 和 JDBC 驱动程序包。

$ 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）：

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 上，路径之间用分号分隔，而不是冒号）：

$ javac Test.java
$ java -cp .:/usr/share/java/postgresql-jdbc4.jar \
Test
Hello, world!

备份

虽然我们的数据库只有一张表，保证数据的持久性仍然很重要。应用程序的数据量较小时，最简单的备份方式是使用 pg_dump 工具：

$ pg_dump appdb > appdb.dump

在文本编辑器中打开生成的 appdb.dump 文件，就会看到一些标准 SQL 命令，用于创建 appdb 中的所有对象，并填入数据。将这个文件传给 psql，就可以恢复数据库的内容。例如，可以创建一个新数据库，再将所有数据导入其中：

$ createdb appdb2
$ psql -d appdb2 -f appdb.dump

上一章介绍的演示数据库就是以这种格式分发的。

pg_dump 工具还有许多功能值得了解：postgrespro.com/doc/app-pgdump。其中有些功能只有以自定义格式导出数据时才能使用。在这种情况下，恢复数据必须使用 pg_restore 工具，而不是 psql。

无论采用哪种格式，pg_dump 都只能备份单个数据库的内容。要备份整个数据库集簇，包括全部数据库、用户和表空间，需要使用另一个命令：pg_dumpall。

大型项目需要细致、全面的备份策略。这时，更好的选择是为数据库集簇制作物理二进制副本，可以使用 pg_basebackup 工具完成：

$ pg_basebackup -D backup

这个命令会在 backup 目录中备份整个数据库集簇。要从备份恢复集簇，将其移至数据目录，然后重启服务器。

有关备份和恢复工具的详细信息，请参阅文档：postgrespro.com/doc/backup。

PostgreSQL 的内置功能几乎可以实现所有备份需求，但需要完成多个步骤，而且缺乏自动化。为此，许多公司都开发了自己的备份工具。Postgres Professional 也有这样的工具，名为 pg_probackup。使用它的免费版本，可以执行页级增量备份、保证数据完整性、在处理大量数据时使用并行执行和压缩，并实施多种备份策略。

完整文档见 postgrespro.com/doc/app-pgprobackup。

下一步做什么？

现在可以开始开发应用程序了。从数据库的角度看，应用程序总是由服务端和客户端两部分组成。服务端包括与数据库系统相关的一切：表、索引、视图、触发器、存储函数和存储过程。客户端则包含在数据库之外运行、并连接数据库的部分；从数据库的角度看，这部分是厚客户端还是应用服务器，并没有区别。

有一个重要问题，没有明确的答案：业务逻辑应该放在哪里？

一种常见的做法是把逻辑移出数据库，全部在客户端实现。开发者不熟悉关系型数据库系统的全部能力时，往往会这样做，因为他们更愿意依赖熟悉的东西，也就是应用代码。

在这种情况下，数据库在应用程序中就退居次要位置，只负责数据持久化，即可靠地保存数据。人们还常常用额外的抽象层隔离数据库系统，例如 ORM 工具，它能根据开发者熟悉的编程语言结构自动生成数据库查询。有时，选择这类方案是为了让应用程序可以移植到任意数据库系统。

这种做法有其存在的理由：如果系统能够运行，并满足所有业务目标，何乐而不为？

不过，这种方案也有一些明显的缺点：

  • 数据一致性由应用程序保证。 所有必要的检查都由应用程序完成，而不依靠数据库系统保证数据一致性（这恰恰是关系型数据库系统特别擅长的事）。可以肯定，数据库中迟早会出现不一致的数据。届时，要么修正这些错误，要么让应用程序学会处理它们。如果多个应用程序共用同一个数据库，不使用数据库层面的约束来保证数据一致性，就不切实际了。

  • 性能不尽如人意。 ORM 系统可以在数据库之上构建一个抽象层，但其生成的 SQL 查询质量颇值得怀疑。通常，它会执行多条小查询，每条单独来看都很快。这种模式只能应付数据量小、负载低的场景，在数据库一侧几乎无法优化。

  • 应用代码变得更复杂。 使用面向应用的编程语言，无法编写出能够自动、正确转换为 SQL 的真正复杂的查询。因此，复杂的数据处理（当然，前提是确有需要）必须在应用层实现，并事先从数据库中取出全部所需数据，这又会增加网络上的数据传输。此外，数据库系统提供的扫描、连接、排序和聚合等算法经过多年改进和优化，性能必定优于应用代码。

要充分使用数据库的功能，包括完整性约束和存储过程，就必须深入理解它的能力。需要掌握 SQL 才能编写查询，还要学习一种服务端编程语言（通常是 PL/pgSQL），才能创建函数和触发器。作为回报，你将获得一个可靠的工具，它是任何信息系统架构中最重要的组成部分之一。

无论如何，业务逻辑究竟放在服务端还是客户端，需要你自己决定。我们只想提醒一句：不必走极端，答案往往在两者之间。

原作：Postgres: The First Experience，© Postgres Professional, 2016–2025。原版出版声明见 Original publication notice，中文说明见出版信息与致谢。