PostgreSQL日常维护

PostgreSQL（简称 pgsql）是一款功能强大的开源关系型数据库，具备稳定性、扩展性以及对 SQL 标准的严格遵循等显著特点。这些优势使其在企业级开发与数据分析场景中得到广泛应用。本笔记将围绕 pgsql 的日常使用展开，涵盖从基础登录到核心操作的完整流程，助力读者掌握数据库连接管理、库表创建与操作、模式（Schema）设计、远程访问权限配置以及账户安全维护等关键技能。

二、基本使用

（一）登录数据库

在登录 PostgreSQL 时，必须使用 postgres 用户。具体操作步骤如下：

[root@bogon ~]# su -- postgres  # 切换到 postgres 用户
[postgres@bogon ~]$ /usr/local/pgsql/bin/psql  # 启动 psql 客户端

登录成功后，命令提示符将变为 “postgres=#”，其中 “postgres” 表示当前所在的数据库。

（二）数据库操作

1. 列出数据库

在 PostgreSQL 中，有三种常用的列出数据库的方法：

方法一：使用元命令 \l
在 psql 交互式终端中，以 “\” 开头的命令称为元命令，类似 MySQL 的 SHOW 语句，可用于快速管理数据库。输入 “\l” 即可列出所有数据库。
方法二：使用扩展元命令 \l+
“\l+” 的输出比 “\l” 多了 Size、Tablespace 和 Description 列，能提供更详细的数据库信息。输入 “\l+” 即可查看扩展后的数据库列表。
方法三：使用 SQL 命令
通过查询系统表 pg_database 来获取所有数据库的信息，SQL 语句如下：
```
postgres=# SELECT datname FROM pg_database;
```
pg_database 是系统表，存储了 PostgreSQL 实例中所有数据库的元信息，如数据库名称、所有者、编码等。它属于系统目录（System Catalog），类似 MySQL 的 information_schema，但 PostgreSQL 的系统目录更底层且直接存储在 pg_catalog 模式中。由于系统表默认属于 pg_catalog 模式，且 pg_catalog 始终位于搜索路径（search_path）的首位，因此查询时无需显式指定模式。

2. 创建数据库

使用 CREATE DATABASE 语句创建新的数据库，示例如下：

postgres=# create database mydb;

3. 删除数据库

若要删除不再需要的数据库，可使用 DROP DATABASE 语句，示例如下：

postgres=# drop database mydb;

4. 切换数据库

在 psql 中，使用 \c 元命令切换当前连接的数据库，示例如下：

postgres=# \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=#

5. 查看数据库大小

使用 pg_database_size 函数以字节为单位返回数据库的大小，示例如下：
```
postgres=# SELECT pg_database_size('mydb');
```
使用 pg_size_pretty () 函数将字节转换为更易于阅读的值，示例如下：
```
postgres=# SELECT pg_size_pretty(pg_database_size('mydb'));
```

（三）数据表操作

1. 列出表

在 PostgreSQL 中，有多种列出表的方法：

使用元命令 \dt：列出当前数据库中 search_path 中模式里的表（默认 public 模式），示例如下：
```
mydb=# \dt;
```
使用元命令 \d：列出表、视图和序列，示例如下：
```
mydb=# \d
```
使用元命令 \d+：列出更详细的表信息，示例如下：
```
mydb=# \d+
```
使用元命令 \dt my_schema.*：列出指定模式下的表（例如 my_schema），示例如下：
```
mydb=# \dt my_schema.*
```
使用元命令 \dt .：查看当前数据库的所有表（包括系统表），示例如下：
```
mydb=# \dt *.*
```
使用 SQL 命令：通过查询 pg_tables 视图列出当前数据库中指定模式下的所有表及其详细信息，pg_tables 属于 pg_catalog 模式，是基于 pg_class 和 pg_namespace 的逻辑视图，并非物理表。无需切换数据库，直接查询 pg_catalog.pg_tables 即可获取当前数据库的表信息，示例如下：
```
mydb=# SELECT * FROM pg_tables WHERE schemaname = 'public';
```

2. 创建表

PostgreSQL 支持标准的 SQL 类型，如 int、smallint、real、double precision、char (N)、varchar (N)、date、time、timestamp 和 interval 等，还支持其他通用功能的类型和丰富的几何类型，并且可以定制任意数量的用户定义数据类型。创建表的示例如下：

postgres=# create table test(id int, name char(10), age int);

3. 复制表

要将已有的 table_name 表复制为新表 new_table，包括表结构和数据，可使用以下语句：

CREATE TABLE new_table AS TABLE table_name;

例如，将 test 表复制为 test2 表：

postgres=# CREATE TABLE test2 AS TABLE test;

4. 删除表

使用 DROP TABLE 语句删除表，示例如下：

postgres=# drop table test2;

5. 查看表结构

使用 \d 元命令查看表结构，示例如下：

postgres=# \d test;

输出结果将显示表的栏位、类型、校对规则、可空性和预设值等信息。

三、模式操作命令

在 PostgreSQL 中，模式（Schema）是一个逻辑容器，用于组织和管理数据库对象（如表、视图、函数、索引等），类似于文件系统中的文件夹。它可以帮助在同一个数据库中分类存储不同的对象，避免命名冲突，并实现权限隔离。

（一）创建模式

在当前库 postgres 中创建名为 hr 的模式，示例如下：

postgres=# CREATE SCHEMA hr;

（二）在指定模式中创建表

未指定模式时，创建的对象（表、视图等）会按 search_path 顺序创建到第一个可用的模式中。在 postgres 库中的 hr 模式下创建一个名为 employees 的表，示例如下：

postgres=# CREATE TABLE hr.employees (id SERIAL PRIMARY KEY, name TEXT);

（三）切换当前模式

切换模式也就是调整 search_path 的搜索范围。

切换到单个 schema，示例如下：
```
SET search_path TO new_schema;
```
切换到多个 schema（按优先级顺序），示例如下：
```
SET search_path TO hr, public;
```
表示优先搜索 hr 模式，其次是 public 模式。

（四）查看当前所在 schema

使用 current_schema () 函数查看当前所在的 schema，示例如下：

postgres=# SELECT current_schema();

（五）查看搜索路径（Search Path）

使用 SHOW search_path 命令查看当前的搜索路径，示例如下：

postgres=# SHOW search_path;

（六）PostgreSQL 的模式隔离性

PostgreSQL 的模式是数据库内的逻辑分组，不同模式可以存在同名表，这是与 MySQL 的不同之处。跨模式查询需显式指定模式名（如 schemal.users），或通过 search_path 设置默认模式，无需切换数据库连接，所有操作在同一数据库内完成。以下是一个跨模式查询的示例：

创建一个数据库 mydb，并切换到该数据库：
```
postgres=# CREATE DATABASE mydb;
postgres=# \c mydb
```

在数据库中创建两个模式 schemal 和 schema2：

mydb=# CREATE SCHEMA schemal;
mydb=# CREATE SCHEMA schema2;

在每个模式中创建同名表，并插入数据：

mydb=# CREATE TABLE schemal.users (id int);
mydb=# INSERT INTO schemal.users VALUES(1);
mydb=# CREATE TABLE schema2.users (id int);
mydb=# INSERT INTO schema2.users VALUES(2);

跨模式查询：

显式指定模式名查询：

mydb=# SELECT * FROM schemal.users;
mydb=# SELECT * FROM schema2.users;

设置 search_path 切换默认模式查询：

mydb=# SET search_path TO schemal;
mydb=# SELECT * FROM users; -- 默认访问 schemal.users
mydb=# SET search_path TO schema2;
mydb=# SELECT * FROM users; -- 默认访问 schema2.users

四、数据操作

（一）添加数据

在 postgres 库中新建表 test，并插入数据，示例如下：

postgres=# create table test(id int, name char(10), age int);
postgres=# insert into test values(1,'zhangsan', 18);

（二）查询数据

使用 SELECT 语句查询表中的数据，示例如下：

postgres=# select * from test;

（三）修改数据

使用 UPDATE 语句修改表中的数据，示例如下：

postgres=# update test set age=20 where id=1;

五、备份与恢复

（一）备份

pg_dump 是 PostgreSQL 中用于备份数据库的工具。它可以备份整个数据库、单个模式或单个表。以下是使用 pg_dump 备份数据库的基本命令：

pg_dump -U username -h host -p port dbname > dumpfile

其中：

-U username：指定连接数据库的用户名。
-h host：指定数据库服务器的主机名或 IP 地址。
-p port：指定数据库服务器的端口号。
dbname：指定要备份的数据库名称。
dumpfile：指定备份文件的名称和路径。

如果没有足够的特权来备份整个数据库，仍然可以使用诸如 -n schema 或 -t table 选项来备份该数据库中能够访问的部分。例如，备份指定模式 schema1 中的数据：

pg_dump -U postgres -h localhost -p 5432 mydb -n schema1 > schema1_backup.sql

要声明 pg_dump 连接哪个数据库服务器，使用命令行选项 -h host 和 -p port。默认主机是本地主机或 PGHOST 环境变量指定的主机，默认端口是环境变量 PGPORT 或（如果 PGPORT 不存在）内建的默认值。pg_dump 默认使用与当前操作系统用户名同名的数据库用户名进行连接，要使用其他名字，要么声明 -U 选项，要么设置环境变量 PGUSER。请注意，pg_dump 的连接也要通过客户认证机制。

pg_dump 对于其他备份方法的一个重要优势是，其输出可以很容易地在新版本的 PostgreSQL 中载入，而文件级备份和连续归档都是极度的服务器版本限定的。pg_dump 也是唯一可以将一个数据库传送到一个不同机器架构上的方法，例如从一个 32 位服务器到一个 64 位服务器。由 pg_dump 创建的备份在内部是一致的，也就是说，转储表现了 pg_dump 开始运行时刻的数据库快照，且在 pg_dump 运行过程中发生的更新将不会被转储。pg_dump 工作的时候并不阻塞其他的对数据库的操作（但是会阻塞那些需要排它锁的操作，比如大部分形式的 ALTER TABLE）。

（二）从转储中恢复

pg_dump 生成的文本文件可以由 psql 程序读取。从转储中恢复的常用命令是：

psql dbname < dumpfile

其中 dumpfile 就是 pg_dump 命令的输出文件。这条命令不会创建数据库 dbname，必须在执行 psql 前自己从 template0 创建（例如，用命令 createdb -T template0 dbname）。psql 支持类似 pg_dump 的选项用以指定要连接的数据库服务器和要使用的用户名。非文本文件转储可以使用 pg_restore 工具来恢复。

在开始恢复之前，转储库中对象的拥有者以及在其上被授予了权限的用户必须已经存在。如果它们不存在，那么恢复过程将无法将对象创建成具有原来的所属关系以及权限（有时候这就是所需要的，但通常不是）。

默认情况下，psql 脚本在遇到一个 SQL 错误后会继续执行。如果希望在遇到一个 SQL 错误后让 psql 退出，那么可以设置 ON_ERROR_STOP 变量来运行 psql，这将使 psql 在遇到 SQL 错误后退出并返回状态 3，示例如下：

psql --set ON_ERROR_STOP=on dbname < infile

六、远程连接 PostgreSQL

（一）配置访问权限

默认情况下，PostgreSQL 只能本地访问，要允许远程连接，需要在 pg_hba.conf 配置文件中进行配置。找到 IPv4 local connections 这一行，在这一行下面添加以下内容：

host all all 0.0.0.0/0 trust

其中：

host：指定连接类型，host 表示该规则适用于通过 TCP/IP 进行的远程连接，如果是本地连接，通常会使用 local。
all：定义哪些数据库可以接受这个规则，all 表示适用于所有数据库，也可以指定特定的数据库名。
all：定义哪些用户可以接受这个规则，all 表示适用于所有用户，也可以指定特定的用户名。
0.0.0.0/0：定义哪些客户端 IP 地址或 IP 地址范围可以接受这个规则，0.0.0.0/0 表示任何 IP 地址（即没有 IP 地址限制），也可以指定具体的 IP 地址或 IP 地址范围。
trust：定义认证方法，trust 表示不需要密码或其他任何形式的认证，客户端可以直接连接。这通常只在本地或受信任的网络环境中使用，因为它允许任何人无需认证即可访问数据库，在生产环境中，应该使用更安全的认证方法，如 md5 或 password（对于较新版本的 PostgreSQL，建议使用 scram-sha-256）。

如果选择使用 md5 或 password 等需要密码的认证方法，需要先为用户设置密码，示例如下：

postgres=# ALTER USER postgres WITH PASSWORD '123456';

（二）重启服务

配置完 pg_hba.conf 文件后，需要重启 PostgreSQL 服务，使配置生效，示例如下：

[root@localhost ~]# systemctl start postgresql

（三）验证远程连接

使用其它主机远程连接本机数据库，根据配置的认证方法不同，操作也有所不同：

如果设置的是 trust 认证方法，无需密码可直接登录，示例如下：
```
[postgres@localhost ~]$ psql -h 192.168.10.102
```
如果设置的是 md5 或 password 等需要密码的认证方法，登录时需要输入密码，示例如下：
```
[postgres@localhost ~]$ psql -h 192.168.10.102
Password for user postgres:
```

七、重置密码

如果忘记了 PostgreSQL 的密码，可以通过以下步骤重置密码：

（一）备份配置文件

对 pg_hba.conf 文件进行备份，示例如下：

[root@localhost ^]# cp /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.confbak

（二）修改配置文件

修改配置文件以信任本地连接不需要密码，将配置文件中的认证方法（如 scram-sha-256 或者 md5）修改为 trust，示例如下：

[root@localhost ^]# vim /var/lib/pgsql/data/pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 trust

（三）重启服务

修改完配置文件后，重启 PostgreSQL 服务，示例如下：

[root@localhost ^]# systemctl restart postgresql

（四）修改密码

登录数据库修改密码，密码自定义，示例如下：

postgres=# ALTER USER postgres WITH PASSWORD 'new_password';

（五）恢复 pg_hba.conf 配置文件

将备份的 pg_hba.confbak 文件的内容覆盖 pg_hba.conf，重启 PostgreSQL 数据库服务器，重新登陆时，如果提示输入密码，则输入刚才修改的密码即可。