EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Wal

By Sohel SayyadSohel Sayyad

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Wal

PostgreSQL Wal

Definition of PostgreSQL Wal

PostgreSQL provides facility to the user to take backup, continue backup and timely recovery from backup. This is the main task of PostgreSQL tool but how we can perform this operation. The answer to this question is that we use PostgreSQL WAL and it minimizes disk I/O while we save the data. The WAL means Write Ahead Log today’s all database system uses WAL to provide durable and atomic transactions. The WAL stores the transaction in a sequential manner into the log file when we perform a write operation on the database. The WAL is useful to maintain modification of the database on the server for a particular time period after that it is automatically discarded. The main advantages of WAL are, it works as a replication of databases.

Syntax:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

wal_keep_segment

Explanation:

In the above syntax, we use wal_keep_segment to specify the minimum number of previous log file segment stores into the pg_wal directory.

max_wal_size

Explanation:

In the above syntax, we used max size with wal to specify maximum size of log file to grow wal limit.

How Wal works in PostgreSQL?

Let’s see how WAL works in PostgreSQL as follows.

When we make some changes in our database, the WAL file maintains changes of your database into the pg_wal directory. The pg_wal directory is used in PostgreSQL version 10 and in the previous version this directory we called pg_xlog. We can control all WAL file with the help of wal_keep_segments and we also control the size of the WAL file by using max_wal_size. When we write data they are added at the end of the WAL log file and its position is determined by log sequence number. The Log Sequence Number is uniquely identified into the transaction. Log Sequence Number is used to represent a position in WAL that means when data is inserted or when written this kind of information we can see with the help of Log Sequence Number. pg_lsn is a data type used to return Log Sequence Number.

How we can Configure Wal?

WAL configuration in PostgreSQL decided how databases work, for configuration WAL we required two important systems such as checkpoint and background writer. Let’s see different memory areas used in WAL as follows.

 1. Shared Buffers

In the PostgreSQL.conf file, there is one parameter we called shared buffers. They are called shared buffers because all background servers access the shared buffers. The shared buffer is used to determine the amount memory is allocated for caching data that means the main advantages of a shared buffer are increased speed of the system and fast access.

2. Dirty Pages

When we update or modify data into memory that data is stored into a dirty page. The dirty page is different from a shared buffer and the disk is called a dirty page.

3. Checkpointer

The checkpoint is used to create a checkpoint into the Write Ahead Log file. These checkpoints are used in recovery systems when system crash or transaction failure. The main task of the checkpoint is to show all data has been updated at every checkpoint. The checkpoint_segment is used to show maximum log between two checkpoints and checkpoint_timout is used to show maximum time between two checkpoints, the default time between two checkpoints is 5 min or 300 sec.

4. Background Writer

Background writer is a subsystem and it is held at the checkpoint, it divides some load of checkpoint when modified data to the disk offloading the checkpoint.

Examples

Let us discuss examples of PostgreSQL Wal.

Example #1

Suppose the user needs to know the path of the pg_wal log directory at that time you can use the following statement.

show data_directory;

Explanation:

With the help of the above statement, we can see the pg_wal directory path. Illustrate the remaining end result of the above announcement by way of the usage of the following snapshot.

PostgreSQL Wal 1

Let’s see the different examples to understand working of WAL log files into PostgreSQL as follows.

Example #2

Suppose we need to find out the Wal keep segment of a past log file so at that time we use the following statement as follows.

cat postgresql.conf|grep -i wal_keep_segment;

Explanation:

In above statement, we use cat command to find PostgreSQL.conf file, after that we use grep command to find out a specified segment from PostgreSQL.conf file that means we use grep command with wal_keep segment to know past log file segment. . Illustrate the remaining end result of the above announcement by way of the usage of the following snapshot.

PostgreSQL Wal 2

Example #3

Suppose users need to find out the maximum size of the wal file so at that time we can use the following syntax as follows.

cat postgresql.conf|grep -i max_wal_size;

Explanation:

In above statement we use cat command to find out postgresql.conf file from your system location after that we use grep command to find specific value from postgresql.conf file that means in above statement we use grep command with max_wal_size to know the maximum size of log file to grow the performance of WAL log file. . Illustrate the remaining end result of the above announcement by way of the usage of the following snapshot.

PostgreSQL Wal 3

WAL Archiving

The main purpose of WAL in PostgreSQL to secure data integrity. Mainly there are three approaches to backup data in PostgreSQL as follows.

1. Dump

pg_dump utility is used to export the database in PostgreSQL and dump the content into the file. Mainly this file is used to backup the data and dump files are consistent that means they represent the state of the database.

2. File Level Backup

This is the second approach to backup the data and it is faster as compared with the first approach. In this approach, we directly copy files from the system and store on another location, and use them as per requirement.

3. Archiving

This point we already discussed in WAL, the main purpose of archiving in PostgreSQL is that we recover data when system crash or any failure may occur at that time we use checkpoint to recover data. The WAL archiving it must be set as a replica for backup and we activate archiving mode by using specified command.

Conclusion

We hope from this article you have understood about the PostgreSQL WAL. From the above article, we have learned the basic syntax PostgreSQL WAL. We have additionally discovered how we can enforce them in PostgreSQL with different examples of every technique. From this article, we have learned how we can handle WAL in PostgreSQL.

Recommended Articles

This is a guide to PostgreSQL Wal. Here we discuss the definition, syntax, How Wal works in PostgreSQL? along with the examples respectively. You may also have a look at the following articles to learn more –

  1. PostgreSQL Text Search
  2. PostgreSQL Partition
  3. PostgreSQL caching
  4. PostgreSQL enum

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • Postgres Command-Line
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL BIGINT
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
    • Postgres like query
    • PostgreSQL encode
    • PostgreSQL Cheat Sheet
    • PostgreSQL List Databases
    • PostgreSQL Rename Database
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL INSERT INTO
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL ORDER BY DESC
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL Drop Schema
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL User Password
    • PostgreSQL log_statement
    • PostgreSQL repository
    • PostgreSQL shared_buffer
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL where in array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL CHECK Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • PostgreSQL List Tables
    • PostgreSQL TRUNCATE TABLE
    • PostgreSQL Table Partitioning
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL datediff
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL LENGTH()
    • PostgreSQL blob
    • PostgreSQL Median
    • PostgreSQL kill query
    • PostgreSQL Formatter
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL UUID
    • PostgreSQL Merge
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Show Databases
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL Show Tables
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL Encryption
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL Incremental Backup
    • PostgreSQL JSON vs JSONNB
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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

EDUCBA

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

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

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More