Darren Reed <darrenr@netbsd.org> wrote:

> Having done that, I'm wondering if it makes sense for NetBSD
> to support -nomtime as well.  I'm not sure what impact this will
> have on postgres's operation (does it depend on file modification
> timestamp changes?) ...

I don't think that it does, but the PostgreSQL lists would be the
canonical place to ask.

The typical way to take a backup of PostgreSQL is with pg_dump which
uses a connection to the running database.  So I don't think it will
care about filesystem times.

Full filesystem backups without the databse running should also be
safe.

A hypothetical user who is making incremental backups after shutting
down the database could get bitten.  

I don't know how the several different PostgreSQL repliaction
implementations work.

> Does anyone else have any thoughts/criticisms/experience on this?

There's some precedent in other operating systems for not updating the
mtime on (some) device files e.g. logical volumes and tape drives.

I've seen a customer (not on NetBSD, and not with PostgreSQL) complain
when mtime was not updated on raw disk partitions (or logical volumes; I
forget which) -- that customer had some scheme whereby they only backed
up their database's raw partitions if the mtime changed, and the
filesystem and/or volume management s/w vendor optimised the code not to
update mtime on those device files ... oops.

My 2c is that a 'nomtime' mount flag would be too coarse for many uses:
unlike atime which relatively few applications regularly use, mtime is
used more -- ls(1)'s default sorting by time and dump(8)'s incremental
backups, at least, plus any equivalent applications.

Unfortunately I can't think of any "nice" options short of a mount flag:
a file attribute would do, but that's a potential area of disaster with
backup utilities not understanding or preserving them, and an ioctl() on
an open file would also work but no new use of ioctl() can be called
elegant.

Perhaps you can hack in a 'nomtime' mount flag and see if it makes a
measurable difference? On a PC with IDE disk(s) with write-back caching
enabled or a disk array with battery backed write cache the volume of
mtime writes may be dwarfed by transaction write traffic.  No way to
tell without testing, though.

Cheers,

Giles