EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL log_statement

PostgreSQL log_statement

By Priya PedamkarPriya Pedamkar

PostgreSQL log_statement

Introduction to PostgreSQL log_statement

The PostgreSQL log_statement parameter is related to error and reporting to log errors, warnings, and database queries, we can log_statement in PostgreSQL as per the options we configured in the configuration file. Default log_statement option in the 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 the error log. All options of log_statement are defined as log all database server queries. Log_statement is an important parameter in PostgreSQL.

Syntax

Given below is the syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Set log_statement = options;

Or

Log_statement (options or enum);

Or

Alter system set log_statement = options;

Parameters:

  • Set: The keyword “SET” sets the log_statement parameter at the 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 configured in the 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 the log_statement configuration parameter using alter system command in PostgreSQL. We have set this configuration parameter using alter system command at the global level.
  • enum: The enum specifies the options we configure with the log_statement configuration parameter.

How log_statement work in PostgreSQL?

The log_statement parameter in PostgreSQL is a basic, important, and useful parameter that logs error logs, database warning messages, and SQL queries. To work the parameter of log_statement, we need to set the log_min_duration_statement configuration parameter to log queries.

In the below example, we have set the log_min_duration_statement parameter as 1ms to work the log_statement parameter.

Code:

alter system set log_min_duration_statement = 1;
show log_min_duration_statement;

Output:

PostgreSQL log_statement 1

If we want to set the 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:

PostgreSQL log_statement 2

In the above first example, we are trying to set the log_statement configuration parameter using the db_test user, but the db_test user does not have privileges to set the options, so it will issues the error. In the second example, we are trying to set the log_statement configuration parameter using a postgres user, this user has super user privileges to set the parameter in PostgreSQL.

Log_statement has four options available in PostgreSQL:

  • None: This option of log_statement is defined as doesn’t log any query in an error log file. This is the default configurations option of log_statement available. This is also defined as off the log_statement parameter.
  • DDL: The log_statement option defines logging all queries that contain the DDL statement. The DDL query includes the create, alter, and drop statements.
  • All: This option is defined as log all the statements executed by the user. It logs DDL, DML, and all the query languages in PostgreSQL. We cannot enable these options in the production environment because it will take a high overhead on the database to log all statements. It will log all queries which were fired from the database user.
  • Mod: The log_statement parameter within the “mod” option logs not only DDL queries but also modifying statements, including delete, update, truncate, insert, copy, execute, and prepare. It will also log the analyze statement into the error log.

Logging in PostgreSQL into an error log file will occur when we have received a message from a 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 the below example, we have used no options with the log_statement parameter. It will not log anything into the error log.

Code:

show log_statement;
select * from student;
insert into student values (12, 'PQR', 1234567890);

Output:

none options

tail -10f postgresql-Mon.log

PostgreSQL log_statement 4

Example #2

Log_statement using DDL options.

In the below example, we have used DDL options with the log_statement parameter. It will log only the DDL statement into the 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:

DDL options

tail -10f postgresql-Mon.log

PostgreSQL log_statement 6

Example #3

Log_statement using ALL options.

In the below example, we have used ALL options with the log_statement parameter. It will log all statements into the 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:

using ALL options

Example #4

Log_statement using mod options.

In the below example, we have used mod options with the log_statement parameter. It will log DDL and data modifying statement into an 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:

using mod options

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL log_statement” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL VARCHAR
  2. PostgreSQL DATE_PART()
  3. Transaction in PostgreSQL
  4. PostgreSQL Materialized Views
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Popular Course in this category
POSTGRESQL Certification Course
 17+ Hour of HD Videos
4 Courses
1 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more