PostgreSQL系统维护命令详解

一、数据库状态查看命令

1.1 查看数据库列表

在 PostgreSQL 中，可以使用 \l 命令来查看当前数据库实例中的所有数据库列表。该命令在 psql 交互式终端下使用。例如，当你登录到 PostgreSQL 服务器后，在 psql 提示符下输入：

\l

执行该命令后，会列出数据库名称、所有者、编码、存取权限等信息。如下是一个示例输出：

                             List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 mydb      | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
(4 rows)

从这个输出中，我们可以看到系统默认的数据库如 postgres、template0、template1，以及用户创建的 mydb 数据库。Access privileges 字段显示了数据库的访问权限设置。

1.2 查看当前数据库

要查看当前正在使用的数据库，可以使用 \c 命令不带参数的形式，或者使用 SELECT current_database(); SQL 查询语句。在 psql 中使用 \c：

\c

输出结果类似：

You are now connected to database "mydb" as user "myuser".

使用 SQL 查询语句：

SELECT current_database();

执行结果：

 current_database 
------------------
 mydb
(1 row)

这两种方法都能明确当前所处的数据库环境，在进行维护操作时，清楚当前数据库非常重要，避免误操作其他数据库。

1.3 查看表空间列表

表空间是 PostgreSQL 用于将数据库文件存储在特定位置的一种机制。可以使用 \db 命令在 psql 中查看表空间列表。

\db

输出示例：

         List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
 my_tablespace | myuser | /var/lib/postgresql/data/mytablespace
(3 rows)

这里列出了系统默认的 pg_default 和 pg_global 表空间，以及用户创建的 my_tablespace 表空间及其对应的所有者和存储位置。表空间的合理规划对于数据库性能和存储管理至关重要，比如可以将大表存储在专门的表空间，方便进行备份、恢复和性能调优。

二、数据库创建与删除命令

2.1 创建数据库

创建数据库使用 CREATE DATABASE 语句。其基本语法如下：

CREATE DATABASE database_name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ] ];

例如，创建一个名为 newdb，所有者为 newuser，使用 UTF8 编码，表空间为 my_tablespace 的数据库：

CREATE DATABASE newdb
    WITH OWNER = newuser
         ENCODING = 'UTF8'
         TABLESPACE = my_tablespace;

在创建数据库时，TEMPLATE 参数指定从哪个模板数据库复制，如果不指定，默认从 template1 复制。LC_COLLATE 和 LC_CTYPE 用于设置数据库的排序规则和字符分类，ALLOW_CONNECTIONS 决定是否允许连接到该数据库，CONNECTION LIMIT 限制可以同时连接到该数据库的最大连接数。

2.2 删除数据库

删除数据库使用 DROP DATABASE 语句。语法如下：

DROP DATABASE [ IF EXISTS ] database_name;

例如，删除名为 olddb 的数据库：

DROP DATABASE olddb;

如果数据库存在正在连接的会话，直接删除会报错。为了避免这种情况，可以使用 IF EXISTS 选项，这样如果数据库不存在，不会报错。

DROP DATABASE IF EXISTS olddb;

在生产环境中，删除数据库是一个非常危险的操作，务必确保数据库不再使用且数据已妥善备份。

三、用户管理命令

3.1 创建用户

在 PostgreSQL 中创建用户使用 CREATE USER 语句。语法如下：

CREATE USER user_name
    [ WITH ]
    [ SYSID uid ]
    [ [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' ]
    [ CREATEUSER | NOCREATEUSER ]
    [ CREATEDB | NOCREATEDB ]
    [ CREATEROLE | NOCREATEROLE ]
    [ INHERIT | NOINHERIT ]
    [ LOGIN | NOLOGIN ]
    [ REPLICATION | NOREPLICATION ]
    [ BYPASSRLS | NOBYPASSRLS ]
    [ CONNECTION LIMIT connlimit ]
    [ VALID UNTIL 'timestamp' ]
    [ IN ROLE role_name [, ...] ]
    [ ROLE role_name [, ...] ]
    [ ADMIN role_name [, ...] ]
    [ USER role_name [, ...] ]
    [ SYSID uid ];

例如，创建一个名为 testuser，密码为 testpass，具有创建数据库权限的用户：

CREATE USER testuser WITH PASSWORD 'testpass' CREATEDB;

CREATEUSER 选项赋予用户创建其他用户的权限；CREATEROLE 用于创建角色；INHERIT 决定用户是否继承其所属角色的权限；LOGIN 决定用户是否可以登录到数据库；REPLICATION 用于设置用户是否具有复制权限；BYPASSRLS 允许用户绕过行级安全策略；CONNECTION LIMIT 限制用户的最大连接数；VALID UNTIL 设置用户密码的有效截止日期。

3.2 修改用户

修改用户使用 ALTER USER 语句。例如，修改 testuser 的密码：

ALTER USER testuser WITH PASSWORD 'newtestpass';

可以使用 ALTER USER 来修改用户的各种属性，如权限、连接限制等。例如，撤销 testuser 的创建数据库权限：

ALTER USER testuser NOCREATEDB;

3.3 删除用户

删除用户使用 DROP USER 语句。语法如下：

DROP USER [ IF EXISTS ] user_name;

例如，删除 testuser：

DROP USER testuser;

同样，IF EXISTS 选项可以避免在用户不存在时报错。在删除用户之前，要确保该用户没有正在进行的数据库操作，并且其权限和相关资源已妥善处理。

四、表操作命令

4.1 创建表

创建表使用 CREATE TABLE 语句。基本语法如下：

CREATE TABLE [ IF NOT EXISTS ] table_name (
    column1 data_type [ constraint ],
    column2 data_type [ constraint ],
    ...
    [ table_constraint [, ...] ]
);

例如，创建一个简单的 employees 表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    salary DECIMAL(10, 2)
);

这里 id 列使用 SERIAL 数据类型，它会自动生成唯一的序列值，并且设置为主键。name 列设置为 VARCHAR 类型，长度为 100，且不允许为空。age 是整数类型，salary 是具有 10 位总长度，2 位小数的十进制类型。

表约束可以在列定义时设置，也可以在表级设置。例如，为 employees 表添加一个 UNIQUE 约束：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    salary DECIMAL(10, 2),
    UNIQUE (name)
);

4.2 修改表

修改表使用 ALTER TABLE 语句。可以用于添加列、修改列的数据类型、删除列、添加约束等操作。

添加列：

ALTER TABLE employees ADD COLUMN department VARCHAR(50);

修改列的数据类型：

ALTER TABLE employees ALTER COLUMN age TYPE BIGINT;

注意，在修改数据类型时，如果数据不能直接转换，可能会导致错误。例如，将包含非数字字符的 VARCHAR 列转换为 INT 类型会失败。

删除列：

ALTER TABLE employees DROP COLUMN department;

添加约束：

ALTER TABLE employees ADD CONSTRAINT salary_check CHECK (salary > 0);

4.3 删除表

删除表使用 DROP TABLE 语句。语法如下：

DROP TABLE [ IF EXISTS ] table_name [, ...] [ CASCADE | RESTRICT ];

例如，删除 employees 表：

DROP TABLE employees;

IF EXISTS 选项防止在表不存在时报错。CASCADE 选项会级联删除依赖该表的对象，如视图、触发器等；RESTRICT 选项（默认）在有依赖对象时不允许删除表。

五、索引管理命令

5.1 创建索引

创建索引可以提高查询性能。使用 CREATE INDEX 语句。基本语法如下：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] index_name
    ON table_name ( column_name [, ...] )
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ];

例如，为 employees 表的 name 列创建普通索引：

CREATE INDEX idx_employees_name ON employees (name);

创建唯一索引，确保列值的唯一性：

CREATE UNIQUE INDEX idx_employees_id ON employees (id);

CONCURRENTLY 选项允许在创建索引时不锁定表，这样可以在不影响正常数据库操作的情况下创建索引，但创建过程可能会更慢，并且有一些限制，例如不能在创建索引时修改表结构。

5.2 查看索引

在 psql 中，可以使用 \d 命令查看表的结构和索引信息。例如，查看 employees 表的信息：

\d employees

输出结果中会显示索引相关信息：

              Table "public.employees"
   Column   |       Type        | Collation | Nullable | Default 
------------+-------------------+-----------+----------+---------
 id         | integer           |           | not null | nextval('employees_id_seq'::regclass)
 name       | character varying |           | not null | 
 age        | integer           |           |          | 
 salary     | numeric(10,2)     |           |          | 
Indexes:
    "idx_employees_id" UNIQUE, btree (id)
    "idx_employees_name" btree (name)

也可以使用系统视图 pg_indexes 来查看数据库中的所有索引信息：

SELECT * FROM pg_indexes WHERE tablename = 'employees';

5.3 删除索引

删除索引使用 DROP INDEX 语句。语法如下：

DROP INDEX [ IF EXISTS ] index_name;

例如，删除 idx_employees_name 索引：

DROP INDEX idx_employees_name;

IF EXISTS 选项防止在索引不存在时报错。删除索引要谨慎，因为它可能会影响相关查询的性能。在删除索引之前，要评估其对应用程序查询的影响。

六、备份与恢复命令

6.1 使用 pg_dump 进行备份

pg_dump 是 PostgreSQL 提供的备份工具，它可以将数据库的结构和数据导出到文件中。基本语法如下：

pg_dump [options] database_name > backup_file.sql

例如，备份 mydb 数据库到 mydb_backup.sql 文件：

pg_dump mydb > mydb_backup.sql

常用选项：

-U：指定用户名，例如 -U myuser。
-F：指定备份格式，有 p（纯 SQL，默认）、c（自定义格式）、t（tar 格式）等。例如，使用自定义格式备份：

pg_dump -F c mydb > mydb_backup.custom

自定义格式备份文件通常更小，并且恢复速度更快，因为它可以并行恢复。

-v：详细模式，显示备份过程的详细信息。
-f：指定输出文件名，例如 -f my_backup.sql，与上述重定向方式作用相同，但更明确。

6.2 使用 pg_restore 进行恢复

pg_restore 用于恢复由 pg_dump 创建的备份文件。对于纯 SQL 格式的备份文件，可以使用 psql 来恢复：

psql -U myuser mydb < mydb_backup.sql

对于自定义格式或 tar 格式的备份文件，使用 pg_restore：

pg_restore -U myuser -d mydb mydb_backup.custom

常用选项：

-U：指定用户名。
-d：指定目标数据库。
-v：详细模式，显示恢复过程的详细信息。
-c：在恢复之前先执行 DROP TABLE 等命令来清理目标数据库中的对象，这在重新初始化数据库时很有用，但要谨慎使用，以免误删除数据。

6.3 基于文件系统的备份（冷备份）

冷备份是在数据库关闭状态下对数据文件进行备份。首先关闭 PostgreSQL 服务：

sudo systemctl stop postgresql

然后复制数据目录，默认位置通常是 /var/lib/postgresql/data：

sudo cp -r /var/lib/postgresql/data /var/lib/postgresql/data_backup

恢复时，停止数据库服务，删除原数据目录，将备份目录复制回原位置，并设置正确的权限：

sudo systemctl stop postgresql
sudo rm -r /var/lib/postgresql/data
sudo cp -r /var/lib/postgresql/data_backup /var/lib/postgresql/data
sudo chown -R postgres:postgres /var/lib/postgresql/data
sudo systemctl start postgresql

冷备份的优点是简单直接，缺点是需要停止数据库服务，影响业务连续性。在生产环境中，通常会结合热备份（如基于 WAL 归档的备份）来实现无间断备份。

七、日志管理命令

7.1 查看日志配置

PostgreSQL 的日志配置文件是 postgresql.conf，通常位于数据目录中。可以通过修改该文件来配置日志相关参数。要查看当前的日志配置，可以查看该文件内容。例如，在 Linux 系统下：

sudo nano /var/lib/postgresql/data/postgresql.conf

关键的日志配置参数有：

logging_collector：是否启用日志收集，设置为 on 表示启用，会将日志输出到文件中。
log_directory：日志文件存储目录，默认是 pg_log。
log_filename：日志文件名格式，例如 postgresql-%Y-%m-%d_%H%M%S.log。
log_statement：设置记录哪些 SQL 语句，取值有 none（不记录）、ddl（只记录数据定义语言语句）、mod（记录数据定义和修改语句）、all（记录所有语句）。

7.2 查看日志文件

如果启用了日志收集，日志文件会存储在指定的 log_directory 中。例如，要查看最新的日志文件：

sudo tail -f /var/lib/postgresql/data/pg_log/postgresql-2023-10-01_120000.log

tail -f 命令可以实时跟踪日志文件的更新，方便查看数据库运行过程中的最新日志信息。日志文件中记录了数据库的启动、关闭、SQL 执行、错误等各种信息，对于排查问题、性能分析非常重要。

7.3 清理日志文件

随着时间推移，日志文件会占用大量磁盘空间。可以手动删除旧的日志文件，但要注意不能删除正在使用的日志文件。一种更安全的方式是使用工具来管理日志文件，例如 pg_archivecleanup。首先，确保已经配置了 WAL 归档（Write - Ahead Log 归档，这是一种高级的备份和恢复机制）。然后，使用 pg_archivecleanup 清理归档日志：

pg_archivecleanup /var/lib/postgresql/archive 00000001000000010000000A

这里 /var/lib/postgresql/archive 是归档日志目录，00000001000000010000000A 是当前最新的归档日志文件之前的一个日志文件标识，pg_archivecleanup 会删除该标识之前的所有归档日志文件。对于普通日志文件，如果使用日志轮转工具（如 logrotate），可以配置 logrotate 来定期清理和压缩日志文件。例如，在 /etc/logrotate.d/postgresql 文件中添加如下配置：

/var/lib/postgresql/data/pg_log/*.log {
    daily
    missingok
    rotate 7
    compress
    delaycompress
    notifempty
    create 0600 postgres postgres
    sharedscripts
    postrotate
        /usr/lib/postgresql/14/bin/pg_ctl reload -D /var/lib/postgresql/14/main > /dev/null 2>&1 || true
    endscript
}

上述配置表示每天对 pg_log 目录下的日志文件进行轮转，保留 7 天的日志，压缩旧日志文件，并且在轮转后重新加载 PostgreSQL 配置，使新的日志配置生效。

八、性能分析命令

8.1 使用 EXPLAIN 分析查询计划

EXPLAIN 是 PostgreSQL 中用于分析查询执行计划的工具。基本语法如下：

EXPLAIN [ ( option [, ...] ) ] statement;

例如，分析一个简单的查询：

EXPLAIN SELECT * FROM employees WHERE age > 30;

输出结果会显示查询计划，包括扫描方式（如顺序扫描、索引扫描）、连接方式（如果有连接操作）等信息。例如：

                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..15.00 rows=100 width=16)
   Filter: (age > 30)
(2 rows)

这里显示对 employees 表进行顺序扫描，因为没有合适的索引用于过滤 age > 30 的条件。如果查询性能不佳，可以通过分析查询计划来优化查询，例如创建合适的索引。

常用选项：

ANALYZE：不仅显示查询计划，还会实际执行查询并收集统计信息，这样可以得到更准确的成本估计。例如：

EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

VERBOSE：显示更详细的查询计划信息，包括表的别名、列的信息等。

8.2 使用 pg_stat_statements 扩展分析 SQL 性能

pg_stat_statements 是一个 PostgreSQL 扩展，用于统计 SQL 语句的执行情况。首先，需要在 postgresql.conf 文件中启用该扩展：

shared_preload_libraries = 'pg_stat_statements'

然后重启 PostgreSQL 服务。接着，在数据库中创建扩展：

CREATE EXTENSION pg_stat_statements;

之后，可以查询 pg_stat_statements 视图来获取 SQL 语句的统计信息，例如：

SELECT query, calls, total_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC;

该查询会按总执行时间降序显示所有执行过的 SQL 语句及其调用次数、总执行时间和返回的行数。通过分析这些数据，可以找出执行时间长的 SQL 语句，进而进行优化。

8.3 使用 pg_stat_activity 查看当前活动

pg_stat_activity 视图用于查看当前数据库中正在进行的活动，包括客户端连接信息、执行的 SQL 语句等。例如：

SELECT pid, usename, client_addr, query
FROM pg_stat_activity;

输出结果类似：

  pid  |  usename  | client_addr |             query             
-------+-----------+-------------+-------------------------------
 12345 | myuser    | 192.168.1.10 | SELECT * FROM employees WHERE age > 30;
(1 row)

通过查看 pg_stat_activity，可以发现长时间运行的查询、阻塞其他事务的事务等，有助于及时解决性能问题和资源争用问题。如果发现某个查询长时间占用资源，可以考虑终止该查询，使用 SELECT pg_cancel_backend(pid); 语句，其中 pid 是要终止的查询的进程 ID。但要谨慎使用，因为可能会导致数据不一致等问题，尤其是在事务未完成的情况下。

通过对上述 PostgreSQL 系统维护命令的详细了解和使用，可以更好地管理和维护 PostgreSQL 数据库，确保其稳定、高效地运行。无论是数据库的日常管理、性能优化，还是备份恢复等工作，这些命令都提供了强大的功能支持。在实际应用中，需要根据具体的业务需求和场景，灵活运用这些命令。