Introduction to PostgreSQL log_statement
PostgreSQL log_statement parameter is basically related to error and reporting to log error, warning and database queries, we can log_statement in PostgreSQL as per options we configured in configuration file. Default log_statement option in PostgreSQL configuration file is none means nothing log anything into the error file. There are four options of log_statement of error and SQL query reporting i.e. none, ddl, mod and all, DDL specifies that log all ddl query into error log. All options of log_statement is defined as log all queries of database server. Log_statement is important parameter in PostgreSQL.
Syntax of PostgreSQL log_statement
Given below is the syntax:
Set log_statement = options;
Or
Log_statement (options or enum);
Or
Alter system set log_statement = options;
Parameters:
- Set: This keyword is used to set the log_statement parameter at session level in PostgreSQL.
- log_statement: It is configuration parameter in PostgreSQL used to log specific statement in error log file. Log statement have four options which was we have configuring in configuration file.
- options: This parameter is defined as set options to log_statement. We can set four options with log_statement in PostgreSQL.
- Alter system: We have set log_statement configuration parameter using alter system command in PostgreSQL. Using alter system command we have set this configuration parameter at global level.
- enum: This is defined specified options which was we have configuring with log_statement configuration parameter.
How log_statement work in PostgreSQL?
Log_statement is basic and very important and useful parameter in PostgreSQL used to log error log, database warning message and SQL queries. To work the parameter of log_statement we need to set log_min_duration_statement configuration parameter to log queries.
In below example we have set log_min_duration_statement parameter as 1ms to work log_statement parameter.
Code:
alter system set log_min_duration_statement = 1;
show log_min_duration_statement;
Output:
If we want to set log_statement configuration parameter in PostgreSQL we need to have super user privileges to set these options.
Below example shows that we need super user privileges to set the log_statement parameter in PostgreSQL.
Code:
psql -U db_test
SET log_statement = 'DDL';
alter system set log_statement = 'DDL';
psql -U postgres
SET log_statement = 'DDL';
alter system set log_statement = 'DDL';
Output:
In above first example we are trying to set log_statement configuration parameter using db_test user, but db_test user have not privileges to set the options so it will issues the error. In second example we are trying to set log_statement configuration parameter using postgres user, this user have super user privileges to set the parameter in PostgreSQL.
Log_statement have four options available in PostgreSQL:
- None: This option of log_statement is defined as doesn’t log any query in error log file. This is default configurations option of log_statement is available. This is also defined as off the log_statement parameter.
- DDL: This option of log_statement is defined as log all query which contains the DDL statement. DDL query is defined as create, alter and drop statement.
- All: This option is defined as log all the statement which was executed from user. It is logging DDL, DML and all the query language in PostgreSQL. We cannot enable this options in production environment because it will take high overhead on database to log all statement. It will log all queries which was fired from the database user.
- Mod: This option in log_statement is defined as log DDL query as well as log the modifying statement like delete, update, truncate, insert, copy, execute and prepare. It will also log the analyze statement into the error log.
Logging in PostgreSQL into error log file will occur when we have received message from database client or user. It will also include the bind parameter at the time of error reporting in PostgreSQL.
Examples
Given below are the examples mentioned:
Example #1
Log_statement using none options.
In below example we have used none options with log_statement parameter. It will not log anything into error log.
Code:
show log_statement;
select * from student;
insert into student values (12, 'PQR', 1234567890);
Output:
tail -10f postgresql-Mon.log
Example #2
Log_statement using DDL options.
In below example we have used DDL options with log_statement parameter. It will log only DDL statement into error log.
Code:
alter system set log_statement = 'DDL';
show log_statement;
insert into student values (14, 'PQR', 1234567890);id = 13;
delete from student where stud_id = 13;
select * from student;
Output:
tail -10f postgresql-Mon.log
Example #3
Log_statement using ALL options.
In below example we have used ALL options with log_statement parameter. It will log all statement into error log.
Code:
alter system set log_statement = 'ALL';
show log_statement;
insert into student values (14, 'PQR', 1234567890);
tail -10f postgresql-Mon.log
Output:
Example #4
Log_statement using mod options.
In below example we have used mod options with log_statement parameter. It will log DDL and data modifying statement into error log.
Code:
alter system set log_statement = 'mod';
show log_statement;
explain select * from student;
insert into student values (14, 'PQR', 1234567890);
tail -10f postgresql-Mon.log
Output:
Recommended Articles
This is a guide to PostgreSQL log_statement. Here we discuss the introduction to PostgreSQL log_statement, how log_statement work along with query examples. You may also have a look at the following articles to learn more –
