PostgreSQL的PITR中，对 unfilled wal log 如何处理为好

磨砺技术珠矶，践行数据之道，追求卓越价值

回到上一级页面： PostgreSQL基础知识与基本操作索引页     回到顶级页面：PostgreSQL索引页

 

通过实验，可以发现，PostgreSQL中使用WAL log来存储到其他地方，来辅助完成PITR。

但是，WAL是16MB一个，

缺省情况下，如果一个WAL文件没有写满，它就不会被archive_command 调用来拷贝走。

如果长时间没有太多作业，可能有点WAL log会一直位于pg_xlog目录下作为online redo log存在。

那么从崩溃中或者出错中恢复时，也需要提供这些pg_xlog目录下的文件。

 

有的人建议每间隔几分钟就拷贝pg_xlog目录下的文件。
我认为是不妥当的，这是因为这对于PostgreSQL而言是举手之劳，但是它没有这样作。

而且，如果自己去拷贝online redo log，也可能产生数据不一致的问题。还是不要这样作的好。

PostgreSQL　建议的方法是：archive_timeout，通过更加频繁的强制log_switch来保证对事务的保存。

http://www.postgresql.org/docs/9.1/static/continuous-archiving.html

The archive command is only invoked on completed WAL segments. Hence, if your server generates only little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To put a limit on how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file at least that often. Note that archived files that are archived early due to a forced switch are still the same length as completely full files. It is therefore unwise to set a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable.

Also, you can force a segment switch manually with pg_switch_xlog if you want to ensure that a just-finished transaction is archived as soon as possible. Other utility functions related to WAL management are listed in Table 9-56.

archive_timeout (integer)
The archive_command is only invoked for completed WAL segments. Hence, if your server generates little WAL traffic (or has slack periods where it does so), there could be a long delay between the completion of a transaction and its safe recording in archive storage. To limit how old unarchived data can be, you can set archive_timeout to force the server to switch to a new WAL segment file periodically. When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint. (Increasing checkpoint_timeout will reduce unnecessary checkpoints on an idle system.) Note that archived files that are closed early due to a forced switch are still the same length as completely full files. Therefore, it is unwise to use a very short archive_timeout — it will bloat your archive storage. archive_timeout settings of a minute or so are usually reasonable. You should consider using streaming replication, instead of archiving, if you want data to be copied off the master server more quickly than that. This parameter can only be set in the postgresql.conf file or on the server command line.

回到上一级页面： PostgreSQL基础知识与基本操作索引页     回到顶级页面：PostgreSQL索引页

磨砺技术珠矶，践行数据之道，追求卓越价值