PostgreSQL 的审计日志_pgsql开启审计日志

开启审计日志功能
要开启审计日志功能，可以在 postgresql.conf 文件中添加或修改以下配置：

logging_collector = on
1

这将启用日志收集器，使 PostgreSQL 开始记录审计日志。同时，您也需要指定日志文件的路径：

log_directory = 'pg_log'
1

这将指定日志文件存放在 pg_log 目录下。您还可以指定日志文件的名称：

log_filename = 'postgresql-%Y-%m-%d.log'
1

这将将日志文件命名为 postgresql-年-月-日.log，例如 postgresql-2023-04-13.log。
配置日志格式
在 PostgreSQL 中，有多种日志格式可供选择。以下是 csvlog 格式的示例：

log_destination = 'csvlog'
1

log_line_prefix = '%t,%u,%d,%p,'
1

这将使用 CSV 格式记录审计日志，并在每行日志信息前添加时间戳、用户 ID、数据库名称和进程 ID。例如，以下是一个示例日志行：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,“statement: SELECT * FROM users;”
其中，逗号分隔的字段依次为时间戳、用户名、数据库名、进程 ID、主机名、会话 ID、命令标识符、日志级别和日志消息。
配置日志级别
在 PostgreSQL 中，有多个日志级别可供选择。以下是一个示例：

log_min_messages = 'INFO'
1

这将记录所有 INFO 级别及以上的信息。如果您希望记录所有信息，可以将日志级别设置为 DEBUG5：

log_min_messages = 'DEBUG5'
1

这将记录所有调试信息。
DEBUG5：最详细的日志级别，记录所有调试信息。
DEBUG4：记录详细的调试信息。
DEBUG3：记录更加详细的调试信息。
DEBUG2：记录非常详细的调试信息。
DEBUG1：记录较为详细的调试信息。
INFO：记录普通信息。
NOTICE：记录警告信息。
WARNING：记录警告信息。
ERROR：记录错误信息。
LOG：记录所有日志信息。
配置审计日志选项
在 PostgreSQL 中，还有许多其他的选项可供配置，以记录更详细的审计日志信息。以下是一些示例：

log_connections = on
1

这将记录每个连接到数据库的客户端的信息，例如：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,“connection received: host=localhost port=5432”

log_statement = 'all'
1

这将记录每个执行的 SQL 语句，例如：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,“statement: SELECT * FROM users;”

log_duration = on
1

这将记录每个 SQL 语句的执行时间，例如：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,"duration: 0.005 ms statement: SELECT * FROM

其他配置:

log_connections：记录每个连接到数据库的客户端信息。
log_disconnections：记录每个断开与数据库的客户端的信息。 log_duration：记录每个 SQL 语句的执行时间。
log_error_verbosity：设置错误信息的详细程度。 log_hostname：记录每个连接到数据库的客户端的主机名。
log_line_prefix：设置每行日志信息的前缀，可以包括时间戳、用户名、数据库名等信息。
log_lock_waits：记录等待锁的信息。 log_statement：记录每个执行的 SQL 语句，包括
SELECT、INSERT、UPDATE 和 DELETE 等操作。 log_temp_files：记录使用临时文件的信息。
log_timezone：设置日志记录时

创建日志表

CREATE TABLE pg_audit_log (
    event_time TIMESTAMP WITH TIME ZONE,
    user_name TEXT,
    database_name TEXT,
    process_id INTEGER,
    remote_host TEXT,
    remote_port TEXT,
    session_id INTEGER,
    command_tag TEXT,
    session_start_time TIMESTAMP WITH TIME ZONE,
    virtual_transaction_id TEXT,
    transaction_id BIGINT,
    error_severity TEXT,
    sql_state_code TEXT,
    message TEXT,
    detail TEXT,
    hint TEXT,
    internal_query TEXT,
    internal_query_pos INTEGER,
    context TEXT,
    query TEXT,
    query_pos INTEGER,
    location TEXT,
    application_name TEXT,
    client_hostname TEXT,
    client_port_num INTEGER,
    client_username TEXT
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

COPY pg_audit_log FROM '/var/lib/postgresql/data/postgresql14.1-2023-04-27.csv' DELIMITER ',' CSV HEADER;
1

log_line_prefix 可以用来配置所有的日志信息，你可以根据需要在 log_line_prefix 中包含所有你需要的列和标记。

以下是一个示例 log_line_prefix 配置，它包含了 PostgreSQL 日志中的所有列和标记：

log_line_prefix = '%m [%p] %c %l %x %a %u %d %s:%i %t %r %e %q '
1

解释一下这个配置：

%m: 时间戳（格式为YYYY-MM-DD HH24:MI:SS）
[%p]: 进程 ID
%c: 数据库名称
%l: 日志级别（DEBUG1, DEBUG2, DEBUG3, LOG, INFO, NOTICE, WARNING, ERROR, FATAL, or PANIC）
%x: 事务 ID（如果日志消息是事务的一部分）
%a: 客户端 IP 地址
%u: 用户名
%d: 连接的数据库名称
%s:%i: 客户端的套接字地址和端口号
%t: 日志消息的文本
%r: 关联的相关信息（例如，错误信息、SQL 语句等）
%e: SQLSTATE 错误码
%q: 查询开始时的毫秒数

开启设置汇总

log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/lib/postgresql/data'
log_filename = 'postgresql9.1-%Y-%m-%d.log'
log_rotation_age = 1d
log_rotation_size = 0
client_min_messages = log
log_min_messages = log
log_min_error_statement = log
log_statement = 'all'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
1
2
3
4
5
6
7
8
9
10
11

pg_dump的使用（二进制）

pg_dump 是 PostgreSQL 数据库的一个命令行工具，用于将数据库中的数据和结构导出到一个文件中，以便稍后进行恢复或迁移。以下是一些常见的 pg_dump 使用示例：

导出整个数据库到文件:
pg_dump -U <username> -h <hostname> -d <database_name> -F c -f <output_file.dump>

<username>: 数据库用户
<hostname>: 数据库主机
<database_name>: 数据库名称
<output_file.dump>: 输出文件名，可以是任何您选择的名称，通常使用 .dump 作为文件扩展名。
导出特定表到文件:

pg_dump -U <username> -h <hostname> -d <database_name> -t <table_name> -F c -f <output_file.dump>
<table_name>: 要导出的特定表的名称。
导出数据库结构而不包括数据:

pg_dump -U <username> -h <hostname> -d <database_name> -s -F c -f <output_file.dump>
参数 -s 表示只导出结构，不包括数据。

使用密码进行身份验证:
如果 PostgreSQL 数据库需要密码身份验证，您可以添加 -W 标志，并在运行命令后输入密码。


pg_dump -U <username> -h <hostname> -d <database_name> -F c -f <output_file.dump> -W
命令运行后，系统会提示您输入密码。

从导出文件中还原数据库:

pg_restore -U <username> -h <hostname> -d <database_name> -F c -c <input_file.dump>
<input_file.dump>: 先前使用 pg_dump 导出的文件名。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

pg_dump的使用（sql文件）

如果你想要导出 SQL 语句，而不是二进制格式的备份文件，你可以使用 pg_dump 命令的 -F 选项指定为 plain。这将生成一个纯文本 SQL 脚本文件，其中包含数据库结构和数据的 SQL 语句。以下是一个示例：

pg_dump -U <username> -h <hostname> -d <database_name> -F plain -f <output_file.sql>
<username>: 数据库用户
<hostname>: 数据库主机
<database_name>: 数据库名称
<output_file.sql>: 输出文件名，可以是任何您选择的名称，通常使用 .sql 作为文件扩展名。
这个命令会生成一个包含 CREATE TABLE、INSERT 等 SQL 语句的文件，您可以将这个文件用于数据库结构和数据的迁移。要还原数据库，您可以使用 psql 命令，例如：

psql -U <username> -h <hostname> -d <database_name> -f <output_file.sql>
这将执行导出的 SQL 文件中的所有 SQL 语句，从而还原数据库。请确保在执行这些命令之前进行适当的备份，并确保对目标数据库有适当的权限。
1
2
3
4
5
6
7
8
9
10
11