How PostgreSQL maintains the Wal Log/archive log

Wal-Log Introduction

The Wal full name is the write ahead log, which is the online redo log in PostgreSQL, to ensure data consistency and transactional integrity in the database. And the technology introduced in PostgreSQL 7. Its central idea is "write the log after writing data", that is, to ensure that the database file changes should be released after these changes have been written to the log, at the same time, after PostgreSQL 8.3 joined the Walwriter log write process, you can ensure that the transaction commit record is not synchronously written to disk at commit , but asynchronous writes, which greatly reduces the pressure on I/O. So it's important to say the Wal logs. To ensure the consistency of data in the database and the integrity of the transaction.

The Wal log file for PostgreSQL is in the Pg_xlog directory, in general, each file is 16M size: 000000010000000000000010 file name is 16 binary 24 characters, each 8 characters in one group, each group meaning is as follows:

• Timeline: English for Timeline, is the increment number starting with 1, such as ...
• Logid:32bit a number that is incremented by 0, such as 0,1,2,3 ...
• Logseg:32bit a number that is incremented by 0, such as 0,1,2,3 ...

The Wal log, like online redo log, is not infinite in number. The archive log appears.

Wal Log Maintenance

1. Parameter max_wal_size/min_wal_size9.5 ago: (2+ checkpoint_completion_target) * checkpoint_segments +1  9.5: PostgreSQL9.5The checkpoint_segments parameters are discarded and the max_wal_size and Min_wal_size parameters are introduced,max_wal_size and Checkpoint_completion_target parameters to control how many Xlog are generated after a checkpoint is triggered,the min_wal_size and max_wal_size parameters are used to control which xlog can be recycled. 2.The parameter wal_keep_segments in the context of the stream replication. Use stream replication to build the repository, if the repository is slow to receive logs for some reason. Cause the repository has not been received yet. is covered. Causes the master and standby to be unable to synchronize.   This requires rebuilding the repository. Avoid this by providing this parameter. Each log file size is 16M. If the parameter is set to. Occupy about 64x -=1GB of space. Depending on the actual environment settings. 3.Pg_resetxlog in the previous parameter setting is reasonable.  The Pg_resetxlog command is not available. Use case reference: HTTPS://my. oschina.net/kenyon/blog/101432[Postgres@postgres128~]$ Pg_resetxlog-?Pg_resetxlog resets the PostgreSQL transactionLog.Usage:Pg_resetxlog [OPTION]...datadiroptions:-C XID,XID set oldest and newest transactions bearing commit timestamp (zero in either value means 
    NoChange ) [-D] DataDir Data Directory-E Xidepoch SetNextTransaction ID Epoch-F Force update to being done-L xlogfile Force minimum WAL starting location forNew transactionLog-M Mxid,mxid SetNextand oldest multitransaction ID-NNoUpdate, just show what would is done ( fortesting)-O OID SetNextOID-O OFFSET SetNextmultitransaction Offset-V,--version output version information, thenExit  - xXID SetNextTransaction ID-?,--Help show this help and thenExitReport Bugs to<pgsql-bugs@postgresql. Org>.

Archive Log Maintenance

1.Pg_archivecleanup Clean up the archive log. [Postgres@postgres128~]$ Pg_archivecleanup-?Pg_archivecleanup removes older WAL files from PostgreSQL archives.Usage:Pg_archivecleanup [OPTION]...archivelocation oldestkeptwalfileoptions:  -d generate debug output (verbose mode)-N Dry Run,Show the names of the files that would be removed-V,--version output version information, thenExit  - xEXT Clean up filesifThey has this extension-?,--Help show this help and thenExit for  UseAs Archive_cleanup_command in recovery.conf if Standby_mode = on:Archive_cleanup_command='pg_archivecleanup [OPTION] ... Archivelocation%r'e. G.Archive_cleanup_command='Pg_archivecleanup/mnt/server/archiverdir%r'Or for  UseAs a standalone archive cleaner:e. G.Pg_archivecleanup/mnt/server/archiverdir000000010000000000000010.00000020.Backup1.1When the main library keeps a copy of the Wal log to the standby repository. This time needs to be cleaned up. In recovery.conf can be configured with E. G. Archive_cleanup_command ='Pg_archivecleanup/mnt/server/archiverdir%r'1.2you can receive the Execute command. E. G. pg_archivecleanup/home/postgres/arch/000000010000000000000009in the archive directory/home/postgres/arch/clean up the logs before 000000010000000000000009. 2.pg_rman Backup Reference Blog http://www.cnblogs.com/lottu/p/7490615.HTML is in the Pg_rman backup retention policy. Back up every day. Archive logs can be cleaned up. In a convective replication environment. Backups are typically in a standby repository.  The archive log can be routed to the standby repository. --keep-arclog-files=num Keep num of archived WAL--keep-arclog-days=Day Keep archived WAL modified on day Dayse.g Keep archive log Number 10. Or keep the archive log within 10 days. Keep_arclog_files=Tenkeep_arclog_days=TenThe following information is generated in the backup information. INFO: Start deleting old archived WAL files from Arclog_path (keep files =Ten, Keep days =Ten)

How PostgreSQL maintains the Wal Log/archive log