Re: pg_dump, pg_dumpall and data durability

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> First question: Do we even want this? Generally, when a program
> writes to a file, we rely on the operating system to decide when that
> data should be written back to disk. We have to override that
> distinction for things internal to PostgreSQL because we need certain
> bits of data to reach the disk in a certain order, but it's unclear to
> me how far outside the core database system we want to extend that.
> Are we going to have psql fsync() data it writes to a file when \o is
> used, for example? That would seem to me to be beyond insane, because
> we have no idea whether the user actually needs that file to be
> durable. It is a better bet that a pg_dump command's output needs
> durability, of course, but I feel that we shouldn't just go disabling
> the filesystem cache one program at a time without some guiding
> principle.

FWIW, I find the premise pretty dubious. People don't normally expect
programs to fsync their standard output, and the argument that pg_dump's
output is always critical data doesn't withstand inspection. Also,
I don't understand what pg_dump should do if it fails to fsync. There
are too many cases where that would fail (eg, output piped to a program)
for it to be treated as an error condition. But if it isn't reported as
an error, then how much durability guarantee are we really adding?

I think this might be better addressed by adding something to backup.sgml
pointing out that you'd better fsync or sync your backups before assuming
that they can't be lost.

regards, tom lane