Postgra SQL FAQ

9:32 AM

Q1 - What is WAL(Write Ahead Log)?

Ans -WAL is write-ahead logging. Basically, before the database actually performs an operation, it writes in a log what it's about to do. Then, it goes and does it. This ensures data consistency. Let's say that the
computer was powered off suddenly. There are several points that could happen:
1) Before a write - in this case the database would be fine with or without write-ahead logging.
2) During a write - without write-ahead logging, if the machine is powered off during a write, the database has no way of knowing what remained to be written, or what was being written. WIth Postgres, this is furthere broken down into two possibilities:
*The power-off occurred while it was writing to the log - in this case, the log is rolled back. The database is unaffected because the data was never written to the database proper.
* The power-off occurred after writing to the log, while writing to disk - in this case, Postgres can simply read from the log what was supposed to be written, and complete the write.
3) After a write - again, this does not affect Postgres either with or without WAL.
In addition, WAL increases PostgreSQL's efficiency, because it can delay random-access writes to disk, and just do sequential writes to the log for a long time. This reduces the amount of head-seek the dissk are doing. If you store your WAL files on a different disk, you get even more speed advantages.

Q2 - How to deal with pg_xlog out of disk space problem on Postgres ?

Ans - PostgreSQL keeps track of all changes made to the database data files in Write-Ahead Logs (WALs) under the pg_xlog subdirectory of the cluster data directory. You can archive these WALs in a different directory before the database reuses them by copying them to a directory backed up by a network backup tool.
Running out of disk space in the pg_xlog directory is a fairly common Postgres problem. Because of the near write?only nature of this directory, it is often put on a separate disk. Fixing the out of space error is fairly easy.
When the pg_xlog directory fills up and new files cannot be written to it, Postgres will stop running, try to automatically restart, fail to do so, and give up. The pg_xlog directory is so important that Postgres cannot function until there is enough space cleared out to start writing files again. When this problem occurs, the Postgres logs will give you a pretty clear indication of the problem. They will look similar to this:
PANIC:  could not write to file "pg_xlog/xlogtemp.559": No space left on device
Remove old WAL files. Check first determine which files are safest to remove. Run pg_controldata and seek "Latest checkpoint's REDO WAL file". It is safe to remove any files older than that one.
shell>pg_controldata /var/lib/pgsql/9.4/data/
pg_control version number:            942
Catalog version number:               201409291
Database system identifier:           6146480192998540317
Database cluster state:               in production
pg_control last modified:             Thu 14 May 2015 11:45:39 AM EDT
Latest checkpoint location:           0/6005378
Prior checkpoint location:            0/60052A0
Latest checkpoint's REDO location:    0/6005340
Latest checkpoint's REDO WAL file:    000000020000000000000006
Latest checkpoint's TimeLineID:       2
Latest checkpoint's PrevTimeLineID:   2
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0/1812
Latest checkpoint's NextOID:          16386
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        1800
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  1812
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Time of latest checkpoint:            Thu 14 May 2015 11:45:39 AM EDT
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
Current wal_level setting:            hot_standby
Current wal_log_hints setting:        off
Current max_connections setting:      100
Current max_worker_processes setting: 8
Current max_prepared_xacts setting:   0
Current max_locks_per_xact setting:   64
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0

You Might Also Like

0 comments

Contact Form

Name

Email *

Message *

Translate

Wikipedia

Search results