灾难恢复 [英] disaster recovery

问题描述

我们正在评估Postgres，并希望获得有关灾难恢复的一些信息。我知道在MsSQL中他们有一个名为transactional

日志的功能，可以根据这些日志将数据库重新组合在一起。 Postgres做这样的事吗？我在文档中看到了

事务日志，但我不知道它是否相同。我在哪里可以找到有关Postgres灾难恢复的信息。提前感谢

给出的任何信息。


Jason Tesser

网络/多媒体程序员

Northland Ministries Inc.

[phone] x3050

---------------------- -----（播出结束）---------------------------

提示2：你可以使用取消注册命令立即取消所有列表

（将取消注册YourEmailAddressHere发送到 ma ** *****@postgresql.org ）

解决方案

Jason Tesser写道：

我们正在评估Postgres，并希望获得有关灾难恢复的一些信息。


我将尝试传达我理解的内容，以及其他列表

成员可以在他们选择的激烈程度上纠正我：）

请将更正发送到列表 - 我可能需要几天才能发布后续内容。

我知道在MsSQL中他们有一个称为事务性日志的功能，可以启用数据库将根据这些日志重新组合在一起。

PostgreSQL中一个大致平行的概念（正确的

大小写和拼写？）是预先写入日志。 （WAL）。

也是一个非常不同的概念，称为查询日志 - 这对于检查常见查询以允许数据库调优是好的，但是不能重放。 />

理论上给定一个PostgreSQL数据库和相应的WAL，

你可以重新创建数据库到WAL的最后一个条目

已写入磁盘。


但有些警告：

1）在Linux下，如果你有包含WAL的文件系统，所有赌注都关闭。 * BSD的人群（我知道的话）非常高兴不断提醒我，如果

电源出现故障，我的文件系统将处于不确定的状态 - 事物

可以在整个文件系统中写一半。

2）如果您在任何操作系统下使用IDE驱动器，并且有

在IDE驱动器中打开写入缓存，再次所有投注

关闭

3）如果您使用的是IDE后面的驱动器RAID控制器，YMMV。


所以为了安全起见，一个建议确保数据库的健壮性

是：

1）将WAL存储在单独的物理驱动器上

2）在Linux下，使用同步写入挂载该文件系统（即：

fsync直到实际数据才会返回，真的，写到

界面）

3）如果使用IDE驱动器，关闭WAL卷上的写缓存，这样就可以了。

你知道数据实际写入驱动器声称它是磁盘。


请注意，禁用写入缓存会显着影响写入性能

。大多数人*希望*写入缓存打开

吞吐量关键文件系统，并关闭任务关键型

文件系统。


另请注意，SCSI系统往往没有写入缓存。因此，由于

他们使用标记命令队列。操作系统可以对SCSI驱动器说

有效的东西，这里有15个数据块写入

磁盘，最后一个回到我身边实际上是写在

媒体上，并继续前进。在IDE上，操作系统只能有一个

命令未完成 - 写缓存的目的是允许接收和确认多个命令。在任何数据之前实际写入媒体的
。


当主机配置正确时，你可以恢复一个PostgreSQL

数据库来自硬件故障，通过恢复数据库文件本身

和重播 WAL到该数据库。


在这里阅读更多关于WAL的信息：
http://www.postgresql.org/docs/current/static/wal.html


问候

Alex

PS：请将更正发送到列表

PPS：别忘了包括消防演习作为你的灾难的一部分

恢复计划 - 从

崩溃的机器恢复数据库中获得大量练习，以便在时机成熟时不会犯错误

你真的需要这样做！

PPPS：并按照你自己的建议;）

---------- -----------------（广播结束）---------------------------

提示5：您是否检查了我们广泛的常见问题解答？

http://www.postgresql.org/docs/faqs/FAQ.html

Alex Satrapa< al ** @ lintelsys.com.au>写道：

1）在Linux下，如果您的文件系统包含带有异步写入的WAL，则所有赌注都关闭。 * BSD人群（我知道）非常高兴不断提醒我，如果
电源出现故障，我的文件系统将处于不确定的状态 - 事情可能是一半 - 写在整个文件系统。

这已经过时了。如果您使用日记文件系统

（有四个可用的实体和现代发行版使用它们）

元数据是一致的，崩溃恢复很快。


即使使用ext2，WAL文件也会被预分配，PG在写完
之后会调用fsync（），所以在实践中它不太可能导致问题。


-Doug


---------------------------（...结束广播）---------------------------

提示1：订阅和取消订阅命令转到 ma ******* @ postgresql.org

< blockquote> Alex Satrapa写道：

但有些警告：
1）在Linux下，如果你的文件系统包含带有异步写入的WAL，那么所有的赌注都是断" ;. * BSD人群（我知道）非常高兴不断提醒我，如果
电源出现故障，我的文件系统将处于不确定的状态 - 事情可能是一半 - 写在整个文件系统。

这只是ext2的一个问题。 Ext3，Reiser，XFS，JFS都很好，

尽管你通过安装它们可以获得更好的性能

''回写''。

-

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 transactional
logs that would enable a database to be put back together based off those logs. Does Postgres do anything like this? I saw in the documentation
transactional logging but I don''t know if it is the same. Where can I find info about disaster recovery in Postgres. Thank you in advance
for any info given.

Jason Tesser
Web/Multimedia Programmer
Northland 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)

解决方案

Jason Tesser wrote:

We are evaluating Postgres and would like some input about disaster recovery.
I''m going to try to communicate what I understand, and other list
members can correct me at their selected level of vehemence :)
Please send corrections to the list - I may take days to post follow-ups.
I know in MsSQL they have a feature called transactional
logs that would enable a database to be put back together based off those logs.

A roughly parallel concept in PostgreSQL (what''s the correct
capitalisation and spelling?) is the "Write Ahead Log" (WAL). There is
also a quite dissimilar concept called the query log - which is good to
inspect 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 WAL
was written to disk.

Some caveats though:
1) Under Linux, if you have the file system containing the WAL mounted
with asynchronous writes, "all bets are off". The *BSD crowd (that I
know of) take great pleasure in constantly reminding me that if the
power fails, my file system will be in an indeterminate state - things
could be half-written all over the file system.
2) If you''re using IDE drives, under any operating system, and have
write-caching turned on in the IDE drives themselves, again "all bets
are off"
3) If you''re using IDE drives behind a RAID controller, YMMV.

So to play things safe, one recommendation to ensure database robustness
is to:
1) Store the WAL on a separate physical drive
2) Under Linux, mount that file system with synchronous writes (ie:
fsync won''t return until the data is actually, really, written to the
interface)
3) If using IDE drives, turn off write caching on the WAL volume so that
you know data is actually written to disk when the drive claims it is.

Note that disabling write caching will impact write performance
significantly. Most people *want* write caching turned on for
throughput-critical file systems, and turned off for mission-critical
file systems.

Note too that SCSI systems tend to have no "write cache" as such, since
they use "tagged command queues". The OS can say to the SCSI drive
something that is effectively, "here are 15 blocks of data to write to
disk, get back to me when the last one is actually written to the
media", and continue on its way. On IDE, the OS can only have one
command outstanding - the purpose of the write cache is to allow
multiple commands to be received and "acknowledged" before any data is
actually written to the media.

When the host is correctly configured, you can recover a PostgreSQL
database from a hardware failure by recovering the database file itself
and "replaying" the WAL to that database.

Read more about WAL here:
http://www.postgresql.org/docs/current/static/wal.html

Regards
Alex
PS: Please send corrections to the list
PPS: Don''t forget to include "fire drills" as part of your disaster
recovery plan - get plenty of practice at recovering a database from a
crashed machine so that you don''t make mistakes when the time comes that
you 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.html

Alex Satrapa <al**@lintelsys.com.au> writes:

1) Under Linux, if you have the file system containing the WAL mounted
with asynchronous writes, "all bets are off". The *BSD crowd (that I
know of) take great pleasure in constantly reminding me that if the
power fails, my file system will be in an indeterminate state - things
could be half-written all over the file system.

This 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() after
writing, so in practice it''s not likely to cause problems.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Alex Satrapa wrote:

Some caveats though:
1) Under Linux, if you have the file system containing the WAL mounted
with asynchronous writes, "all bets are off". The *BSD crowd (that I
know of) take great pleasure in constantly reminding me that if the
power fails, my file system will be in an indeterminate state - things
could be half-written all over the file system.

This 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

这篇关于灾难恢复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！