473,899 Members | 5,285 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

disaster recovery

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.
(715)324-6900 x3050
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05
18 3819
On Thu, 27 Nov 2003, Doug McNaught wrote:
Tom Lane <tg*@sss.pgh.pa .us> writes:
Doug McNaught <do**@mcnaught. org> writes:
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".
...
Even with ext2, WAL files are preallocated and PG calls fsync() after
writing, so in practice it's not likely to cause problems.


Um. I took the reference to "mounted with async write" to mean a
soft-mounted NFS filesystem. It does not matter which OS you think is
the one true OS --- running a database over NFS is the act of someone
with a death wish. But, yeah, soft-mounted NFS is a particularly
malevolent variety ...


I took it as a garbled understanding of the "Linux does async metadata
updates" criticism. Which is true for ext2, but was never the
show-stopper some BSD-ers wanted it to be. :)


And it's not file metadata, it's directory data. Metadata (inode
data) is synced, even in ext2, AFAIK.

Quoting the man page:
fsync copies all in-core parts of a file to disk, and
waits until the device reports that all parts are on sta-
ble storage. It also updates metadata stat information.
It does not necessarily ensure that the entry in the
directory containing the file has also reached disk. For
that an explicit fsync on the file descriptor of the
directory is also needed

For WALs, this is perfectly fine. It can be a problem for those
applications that do a lot of renames and relay on those as
sync/locking mechanisms (think of mail spoolers).

..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Co*****@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #11
On Fri, 28 Nov 2003, Alex Satrapa wrote:
Doug McNaught wrote:
I took it as a garbled understanding of the "Linux does async metadata
updates" criticism. Which is true for ext2, but was never the
show-stopper some BSD-ers wanted it to be. :)
I have on several occasions demonstrated how "bad" asynchronous writes
are to a BSD-bigot by pulling the plug on a mail server (having a
terminal on another machine showing the results of tail -f
/var/log/mail.log), then showing that when the machine comes back up the
most we've ever lost is one message


Sorry, I can't resist. Posting this on a PostgreSQL list it's too funny.
This is the last place they want to hear about a lost transaction. Even
just one.

The problem is (was) with programs using _directory_ operations as
syncronization primitives, and mail spoolers (say, MTAs) are typical
in that. Beware that in the MTA world loosing a single message is
just as bad as loosing a committed transaction for DB people. MTAs
are expected to return "OK, I received and stored the message."
only _after_ they committed it to disk in a safe manner (that's because
the other side is allowed to delete its copy after seeing the "OK").
The only acceptable behaviour in case of failure (which is of course
unacceptable in the DB world) for MTAs is to deliver _two_ copies
of a message, but _never_ zero (message lost). That's what might happen
if something crashes (or connection is lost) _after_ the MTA committed
the message to disk and _before_ the peer received notification of
that. Later the peer will try and send the message again (the receiving
MTA has enough knowledge to detect the duplication, but usually real
world MTAs don't do that AFAIK).

My understanding of the problem is: UNIX fsync(), historically,
used to sync also directory data (filename entries) before returning.
MTAs used to call rename()/fsync() or link()/unlink()/fsync()
sequences to "commit" a message to disk. In Linux, fsync() is
documented _not_ to sync directory data, "just" file data and metadata
(inode). While the UNIX behaviour turned out to be very useful,
personally I don't think Linux fsync() is broken/buggy. A file in
UNIX is just that, data blocks and inode. Syncing directory data
was just a (useful) side-effect of one implementation. In Linux,
an explicit fsync() on the directory itself is needed (and in each
path component if you changed one of them too), if you want to
commit changes to disk. Doing that is just as safe as on any filesystem,
even on ext2 with async writes enabled (it doesn't mean "ignore fsync()"
after all!).

AFAIK, but I might be wrong as I know little of this, PostgreSQL
does not relay on directory operations for commits or WAL writes.
It operates on file _data_ and uses fsync(). That works fine with
ext2 in async writes mode, too, no wonder. No need to mount noasync
or to use chattr -S.

BTW, there's no change in the fsync() itself, AFAIK. Some journalled FS
(maybe _all_ of them) will update directory data with fsync() too,
but that's an implementation detail. In my very personal opinion,
any application relaying on that is buggy. A directory and a file
are different "objects" in UNIX, and if you need both synced to disk,
you need to call fsync() two times. Note that syncing a file on
most journalled FS means syncing the journal, _all_ pending writes on
that FS, even those not related to your file. How could the FS
"partially" sync the journal, to sync just _your_ file data and metadata?
That's why directory data gets synced, too. There's no magic in fsync().
From the BSD-bigot's point of view, this is equivalent to the end of
the world as we know it.
From anyone's point of view, loosing track of a committed transaction
(and an accepted message is just that) is the end of the world.
From my point of view, it's just support for my demands to have each
mission-critical server supported by a UPS, if not redundant power
supplies and two UPSes.


Of course. The OS can only be sure it delivered the data to the disk.
If the disk lies on having actually stored it on the plates (as IDE
disks do), there's still a window of vulnerability. What I don't
really get is how SCSI disks can not lie about writes and at the same
time not show performance degradation on writes compared to their
IDE cousins. How any disk mechanics can perform at the same speed of
DRAM is beyond my understanding (even if that mechanics is 3 time
as expensive as IDE one).

..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Co*****@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #12
On Fri, 28 Nov 2003, Craig O'Shannessy wrote:

From my point of view, it's just support for my demands to have each
mission-critical server supported by a UPS, if not redundant power
supplies and two UPSes.


Never had a kernel panic? I've had a few. Probably flakey hardware. I
feel safer since journalling file systems hit linux.


On any hardware flakey enough to cause panics, no FS code will save
you. The FS may "reliably" write total rubbish to disk. It may have been
doing that for hours, thrashing the whole FS structure, before something
triggered the panic.
You are no safer with journal than you are with a plain FAT (or any
other FS technology). Journal files get corrupted themselves.

..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Co*****@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #13
On Fri, 28 Nov 2003, Alex Satrapa wrote:

[...]
I have to admit that in none of those cases would synchronous vs
asynchronous, journalling vs non-journalling or *any* file system
decision have made the slightest jot of a difference to the integrity of
my data.

I've yet to experience a CPU failure (touch wood!).


I have. I have seen memory failures, too. Bits getting flipped at random.
CPUs going mad. Video cards whose text buffer gets overwritten by
"something" ... all were HW failures. There's little the SW can do when
the HW fails, just report that, if it gets any chance.
Your data is already (potentially) lost when that happens. Reliably
saving the content of a memory-corrupted buffer to disk will just cause
_more_ damage to your data. That's expecially true when the "data" is
filesystem metadata. Horror stories. I still remember the day when
/bin/chmod became of type ? and size +4GB on my home PC (that was
Linux 0.98 on a 100MB HD - with a buggy IDE chipset).

..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Co*****@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #14
On Fri, Nov 28, 2003 at 12:28:25 +0100,
Marco Colombo <ma***@esi.it > wrote:

My understanding of the problem is: UNIX fsync(), historically,
used to sync also directory data (filename entries) before returning.
MTAs used to call rename()/fsync() or link()/unlink()/fsync()
sequences to "commit" a message to disk. In Linux, fsync() is
documented _not_ to sync directory data, "just" file data and metadata
(inode). While the UNIX behaviour turned out to be very useful,
personally I don't think Linux fsync() is broken/buggy. A file in
UNIX is just that, data blocks and inode. Syncing directory data
was just a (useful) side-effect of one implementation. In Linux,
an explicit fsync() on the directory itself is needed (and in each
path component if you changed one of them too), if you want to
commit changes to disk. Doing that is just as safe as on any filesystem,
even on ext2 with async writes enabled (it doesn't mean "ignore fsync()"
after all!).


A new function name should have been used to go along with the new semantics.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #15
> 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'.


What does 'writeback' do exactly?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #16
"Rick Gigger" <ri**@alpinenet working.com> writes:
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'.


What does 'writeback' do exactly?


AFAIK 'writeback' only applies to ext3. The 'data=writeback ' setting
journals metadata but not data, so it's faster but may lose file
contents in case of a crash. For Postgres, which calls fsync() on the
WAL, this is not an issue since when fsync() returns the file contents
are commited to disk.

AFAIK XFS and JFS are always in 'writeback' mode; I'm not sure about
Reiser.

-Doug

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #17
Marco Colombo wrote:
On Fri, 28 Nov 2003, Alex Satrapa wrote:
From the BSD-bigot's point of view, this is equivalent to the end of
the world as we know it.
From anyone's point of view, loosing track of a committed transaction
(and an accepted message is just that) is the end of the world.


When hardware fails, you'd be mad to trust the data stored on the
hardware. You can't be sure that the data that's actually on disk is
what was supposed to be there, the whole of what's supposed to be there,
and nothing but what's supposed to be there. You just can't. This
emphasis that some people have on "committing writes to disk" is misplaced.

If the data is really that important, you'd be sending it to three
places at once (one or three, not two - ask any sailor about clocks) -
async or not.
What I don't
really get is how SCSI disks can not lie about writes and at the same
time not show performance degradation on writes compared to their
IDE cousins.
SCSI disks have the advantage of "tagged command queues". A simplified
version of the difference between IDE's single-transaction model and
SCSI's tagged command queue is as follows (this is based on my vague
understanding of SCSI magic):

On an IDE disk, you do this:

PC: here, disk, store this data
Disk: Okay, done
PC: and here's a second block
Disk: Okay, done
.... ad nauseum ...
PC: and here's a ninety fifth block
Disk: Okay, done.

On a SCSI disk, you do this:
PC: Disk, stor these ninety five blocks, and tell me when you've finished
[time passes]
PC: Oh, can you fetch me some blocks from over there while you're at it?
[time passes]
Disk: Okay, all those writes are done!
[fetching continues]

How any disk mechanics can perform at the same speed of
DRAM is beyond my understanding (even if that mechanics is 3 time
as expensive as IDE one).


It's not the mechanics that are faster, it's just the the transferring
stuff to the disk's buffers can be done "asynchronously " - you're not
waiting for previous writes to complete before queuing new writes (or
reads). At the same time, the SCSI disk isn't "lying" to you about
having committed the data to media, since the two stages of request and
confirmation can be separated in time.

So at any time, the disk can have a number of read and write requests
queued up, and it can decide which order to do them in. The OS can
happily go on its way.

At least, that's my understanding.
Alex
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #18
On Tue, 2 Dec 2003, Alex Satrapa wrote:
Marco Colombo wrote:
On Fri, 28 Nov 2003, Alex Satrapa wrote:
From the BSD-bigot's point of view, this is equivalent to the end of
the world as we know it.


From anyone's point of view, loosing track of a committed transaction
(and an accepted message is just that) is the end of the world.


When hardware fails, you'd be mad to trust the data stored on the
hardware. You can't be sure that the data that's actually on disk is
what was supposed to be there, the whole of what's supposed to be there,
and nothing but what's supposed to be there. You just can't. This
emphasis that some people have on "committing writes to disk" is misplaced.

If the data is really that important, you'd be sending it to three
places at once (one or three, not two - ask any sailor about clocks) -
async or not.


Sure, but we were discussing a 'pull the plug' scenario, not HW failures.
Only RAID (which is a way of sending data to different places)
saves you from a disk failure (if it can be _detected_!), and nothing
from a CPU/RAM failure, on a conventional PC (but a second PC, if you're
lucky). The original problem was ext2 loosing _only_ one message after
reboot when someone pulls the plug. The real problem is not the disk, it's
the application returning "OK, COMMITTED" to the other side (which may
be a SMTP client or a PostgreSQL client). IDE tricks these applications
in returning OK _before_ the data hits safe storage (platters). The FS
may play a role too, expecially for those applications that use fsync()
on a file to sync directory data too. On many journalled FS, fsync()
triggers a (global) journal write (which sometimes can be a performance
killer), so, as a side effect, a sync of directory data too.

AFAIK, ext2 is safe to use with PostgreSQL, since commits do not involve
any directory operation (if so, I hope PostgreSQL does a fsync() on the
involved directory too). With heavy transaction loads, I guess it will
outperform journalled filesystems, w/o _any_ loss in data safety. I have
no data to back up such a statement, though.

[ ok on the SCSI async behavior ]

..TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Co*****@ESI.it
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #19

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
4440
by: Neil Truby | last post by:
There's something clearly missing in my understanding of recovery: I set up a small sample datavase and deleted all the rows from a table. Crucially, I omitted the "commit". I then shut down db2, and copied everything under the db2inst1/db2inst1/NODE0000 directory to another server. When I restarted db2 on the first server the rows were still missing. But on the second server they were still there :-( I repeated the exercise several...
10
9561
by: xixi | last post by:
i have db2 udb v8.1 on windows 64 bit 2003 server, after db2 server start , i found this in the db2diag.log, is this error? 2004-05-05-15.28.30.780000 Instance:DB2 Node:000 PID:1692(db2syscs.exe) TID:2860 Appid:AC10040A.GD5F.00FC56D8BEC5 base sys utilities sqledint Probe:30 Crash Recovery is needed. 2004-05-05-15.28.31.890000 Instance:DB2 Node:000
3
2644
by: jignesh shah | last post by:
Hi all, Is there a way to recover a single container if its been corrupted or mark bad without restoring whole tablespace? environment: db28.1/aix5.1/tsm/rs-6000. Regards Jignesh
3
2521
by: Konstantin Andreev | last post by:
Hello, everybody. I've spent a lot of time reading "DB2 Information Center" and Raul Chong's book "Understanding DB2. Learning Visually with Examples", but still unable to answer this simple question. I need to perform the full database backup for at least these two goals: - to restore data onto developers' database system. - to ensure disaster recovery
2
3656
by: Racerx | last post by:
Hi All : I use db2 8.1 fixpack 3 on AIX. I recieved the following message in the diaglog ====================================================== ADM7513W Database manager has started. 2007-01-13-18.55.08.262174 Instance:db2inst1 Node:000 PID:467078(db2agent (mumar) 0) TID:1 Appid:GA010302.O03F.01101B9A3444 base sys utilities sqledint Probe:30
3
1784
by: sureshabi | last post by:
Hello All, I am on a mission critical MS ACCESS database, which has runout of storage space of 2GB. I need to know the following to do a diaster recovery of the existing situation if situation goes bad to worse. 1. How can i Purge Old Data? 2. How can i do nightly backup to the network 3. How can i stablize the current database? 4. What are the pitfalls of storing Image in MS Access database? Anyhelp to website links, greatly...
2
1876
by: Tin | last post by:
I bought a laptop and burned 4 recovery CDs for recovery purpose. Instead of burning as disc images, I just copied and pasted these 4 CDs to my USB HDD as 4 folders called "RecoveryCD 1", "RecoveryCD 2", "RecoveryCD 3" and "RecoveryCD 4". Now my laptop got problem and I lost my 4 recovery CDs. All I have now is 4 recovery folders in my USB HDD. I burned another 4 CDs as data discs from my USB HDD, but it didn't work out (it didn't boot...
0
1278
by: APP1MVF | last post by:
First let me start by saying I am not a DBA nor do I claim to be one, ok with that out of the way I am looking for some help understanding DB2 UDB Recovery after Total Media Failure or Server Loss due to Extreme Disaster. Currently on my system hot backups are being run on a daily basis with cold backups being executed during a down window on the weekend. The backup data is sent to tape which is then sent off site for safety. Hypothetically,...
2
1803
by: =?Utf-8?B?c3BhcmtsZWJhbg==?= | last post by:
My recovery disk on vista is almost full. I have performed a back up, deleted all but the most recent recovery point, done a disk clean up and also compressed the recovery disk. It is STILL almost full. What do I do? Can I (and how do I) delete the recovery disk? HELP! -- sparkleban
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11272
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10971
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9666
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8039
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6081
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4720
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4300
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3317
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.