如何记录 PostgreSQL 查询？

How to log PostgreSQL queries?

提问by Paul

How to enable logging of all SQL executed by PostgreSQL 8.3?

如何启用 PostgreSQL 8.3 执行的所有 SQL 的日志记录？

Edited (more info)I changed these lines :

编辑（更多信息）我改变了这些行：

log_directory = 'pg_log'                    
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'

And restart PostgreSQL service... but no log was created... I'm using Windows Server 2003.

并重新启动 PostgreSQL 服务...但没有创建日志...我使用的是 Windows Server 2003。

Any ideas?

有任何想法吗？

回答by Jarret Hardie

In your data/postgresql.conffile, change the log_statementsetting to 'all'.

在您的data/postgresql.conf文件中，将log_statement设置更改为'all'.

---

---

Edit

编辑

Looking at your new information, I'd say there may be a few other settings to verify:

查看您的新信息，我想说可能还有其他一些设置需要验证：

• make sure you have turned on the log_destinationvariable
• make sure you turn on the logging_collector
• also make sure that the log_directorydirectory already exists inside of the datadirectory, and that the postgres user can write to it.

• 确保您已打开log_destination变量
• 确保你打开 logging_collector
• 还要确保该log_directory目录已存在于data目录中，并且 postgres 用户可以写入该目录。

回答by vijay

Edit your /etc/postgresql/9.3/main/postgresql.conf, and change the lines as follows.

编辑您的/etc/postgresql/9.3/main/postgresql.conf, 并按如下方式更改行。

Note: If you didn't find the postgresql.conffile, then just type $locate postgresql.confin a terminal

注意：如果您没有找到该postgresql.conf文件，则只需$locate postgresql.conf在终端中输入

1. #log_directory = 'pg_log'tolog_directory = 'pg_log'

2. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'tolog_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

3. #log_statement = 'none'tolog_statement = 'all'

4. #logging_collector = offtologging_collector = on

5. Optional: SELECT set_config('log_statement', 'all', true);

6. sudo /etc/init.d/postgresql restartorsudo service postgresql restart

7. Fire query in postgresql select 2+2

8. Find current log in /var/lib/pgsql/9.2/data/pg_log/

1. #log_directory = 'pg_log'到log_directory = 'pg_log'

2. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'到log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

3. #log_statement = 'none'到log_statement = 'all'

4. #logging_collector = off到logging_collector = on

5. 可选：SELECT set_config('log_statement', 'all', true);

6. sudo /etc/init.d/postgresql restart或者sudo service postgresql restart

7. 在 postgresql 中触发查询 select 2+2

8. 查找当前登录 /var/lib/pgsql/9.2/data/pg_log/

The log files tend to grow a lot over a time, and might kill your machine. For your safety, write a bash script that'll delete logs and restart postgresql server.

随着时间的推移，日志文件往往会增长很多，并且可能会杀死您的机器。为了您的安全，请编写一个 bash 脚本来删除日志并重新启动 postgresql 服务器。

Thanks @paul , @Jarret Hardie , @Zoltán , @Rix Beck , @Latif Premani

谢谢@paul、@Jarret Hardie、@Zoltán、@Rix Beck、@Latif Premani

回答by Rix Beck

SELECT set_config('log_statement', 'all', true);

With a corresponding user right may use the query above after connect. This will affect logging until session ends.

有相应的用户权限，可以在连接后使用上面的查询。这将影响日志记录，直到会话结束。

回答by Latif Premani

You also need add these lines in PostgreSQL and restart the server:

您还需要在 PostgreSQL 中添加这些行并重新启动服务器：

log_directory = 'pg_log'                    
log_filename = 'postgresql-dateformat.log'
log_statement = 'all'
logging_collector = on

回答by A T

FYI: The other solutions will only log statements from the default database—usually postgres—to log others; start with their solution; then:

仅供参考：其他解决方案只会记录来自默认数据库的语句——通常是postgres——记录其他人；从他们的解决方案开始；然后：

ALTER DATABASE your_database_name
SET log_statement = 'all';

Ref: https://serverfault.com/a/376888/ log_statement

编号：https://serverfault.com/a/376888/log_statement

回答by Chad Birch

Set log_statementto all:

设置log_statement为all：

Error Reporting and Logging - log_statement

错误报告和日志记录 - log_statement

回答by Shekhar

+1 to above answers. I use following config

+1 以上答案。我使用以下配置

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

回答by Zoltán

Just to have more details for CentOS 6.4 (Red Hat 4.4.7-3) running PostgreSQL 9.2, based on the instructions found on this web page:

根据此网页上的说明，了解有关运行 PostgreSQL 9.2 的 CentOS 6.4（Red Hat 4.4.7-3）的更多详细信息：

1. Set (uncomment) log_statement = 'all'and log_min_error_statement = errorin /var/lib/pgsql/9.2/data/postgresql.conf.
2. Reload the PostgreSQL configuration. For me, this was done by running /usr/pgsql-9.2/bin/pg_ctl reload -D /var/lib/pgsql/9.2/data/.
3. Find today's log in /var/lib/pgsql/9.2/data/pg_log/

1. 设置（取消注释）log_statement = 'all'并log_min_error_statement = error在/var/lib/pgsql/9.2/data/postgresql.conf.
2. 重新加载 PostgreSQL 配置。对我来说，这是通过运行/usr/pgsql-9.2/bin/pg_ctl reload -D /var/lib/pgsql/9.2/data/.
3. 查找今天的登录 /var/lib/pgsql/9.2/data/pg_log/

回答by H.?.T

You should also set this parameter to log every statement:

您还应该设置此参数以记录每个语句：

log_min_duration_statement = 0

回答by Ser

I was trying to set the log_statementin some postgres config file but in fact the file was not read by our postgres.

我试图log_statement在一些 postgres 配置文件中设置 ，但实际上我们的 postgres 没有读取该文件。

I confirmed that using the request :

我确认使用请求：

select *
from pg_settings

[...]
log_statement   none # That was not the value i was expected for !!!

I use this way https://stackoverflow.com/a/41912295/2294168

我用这种方式https://stackoverflow.com/a/41912295/2294168

command: postgres -c config_file=/etc/postgresql.conf