灾难恢复

- Bruce Momjian | http://candle.pha.pa.us pg *** @ candle.pha.pa.us | [phone] +如果你的生活是硬盘，|罗伯茨路13号 +基督可以成为你的备份。 |宾夕法尼亚州新城广场19073年 ---------------------------（播出结束） - -------------------------- 提示4：不要杀掉-9的邮政局长 We are evaluating Postgres and would like some input about disaster recovery. I know in MsSQL they have a feature called transactionallogs that would enable a database to be put back together based off those logs. Does Postgres do anything like this? I saw in the documentationtransactional logging but I don''t know if it is the same. Where can I find info about disaster recovery in Postgres. Thank you in advancefor any info given.Jason TesserWeb/Multimedia ProgrammerNorthland Ministries Inc.[phone] x3050---------------------------(end of broadcast)---------------------------TIP 2: you can get off all lists at once with the unregister command(send "unregister YourEmailAddressHere" to ma*******@postgresql.org) 解决方案A roughly parallel concept in PostgreSQL (what''s the correctcapitalisation and spelling?) is the "Write Ahead Log" (WAL). There isalso a quite dissimilar concept called the query log - which is good toinspect for common queries to allow database tuning, but is not replay-able.The theory is that given a PostgreSQL database and the respective WAL,you can recreate the database to the time that the last entry of the WALwas written to disk.Some caveats though:1) Under Linux, if you have the file system containing the WAL mountedwith asynchronous writes, "all bets are off". The *BSD crowd (that Iknow of) take great pleasure in constantly reminding me that if thepower fails, my file system will be in an indeterminate state - thingscould be half-written all over the file system.2) If you''re using IDE drives, under any operating system, and havewrite-caching turned on in the IDE drives themselves, again "all betsare off"3) If you''re using IDE drives behind a RAID controller, YMMV.So to play things safe, one recommendation to ensure database robustnessis to:1) Store the WAL on a separate physical drive2) Under Linux, mount that file system with synchronous writes (ie:fsync won''t return until the data is actually, really, written to theinterface)3) If using IDE drives, turn off write caching on the WAL volume so thatyou know data is actually written to disk when the drive claims it is.Note that disabling write caching will impact write performancesignificantly. Most people *want* write caching turned on forthroughput-critical file systems, and turned off for mission-criticalfile systems.Note too that SCSI systems tend to have no "write cache" as such, sincethey use "tagged command queues". The OS can say to the SCSI drivesomething that is effectively, "here are 15 blocks of data to write todisk, get back to me when the last one is actually written to themedia", and continue on its way. On IDE, the OS can only have onecommand outstanding - the purpose of the write cache is to allowmultiple commands to be received and "acknowledged" before any data isactually written to the media.When the host is correctly configured, you can recover a PostgreSQLdatabase from a hardware failure by recovering the database file itselfand "replaying" the WAL to that database.Read more about WAL here: http://www.postgresql.org/docs/current/static/wal.htmlRegardsAlexPS: Please send corrections to the listPPS: Don''t forget to include "fire drills" as part of your disasterrecovery plan - get plenty of practice at recovering a database from acrashed machine so that you don''t make mistakes when the time comes thatyou actually need to do it!PPPS: And follow your own advice ;)---------------------------(end of broadcast)---------------------------TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.htmlThis is pretty out of date. If you use a journaling filesystem(there are four solid ones available and modern distros use them)metadata is consistent and crash recovery is fast.Even with ext2, WAL files are preallocated and PG calls fsync() afterwriting, so in practice it''s not likely to cause problems.-Doug---------------------------(end of broadcast)---------------------------TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.orgThis is only a problem for ext2. Ext3, Reiser, XFS, JFS are all fine,though you get better performance from them by mounting them''writeback''.--Bruce Momjian | http://candle.pha.pa.us pg***@candle.pha.pa.us | [phone]+ If your life is a hard drive, | 13 Roberts Road+ Christ can be your backup. | Newtown Square, Pennsylvania 19073---------------------------(end of broadcast)---------------------------TIP 4: Don''t ''kill -9'' the postmaster 这篇关于灾难恢复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！