Is it possible to run Postgres on a write-protected file system? Or a shared file system?

Question

I'm trying to set up a distributed processing environment, with all of the data sitting in a single shared network drive. I'm not going to write anything to it, and just be reading from it, so we're considering write-protecting the network drive as well.

I remember when I was working with MSSQL, I could back up databases to a DVD and load it directly as a read-only database.

If I can do something like that in Postgres, I should be able to give it an abstraction like a read-only DVD, and all will be good.

Is something like this possible in Postgres, if not, any alternatives? (MySQL? sqlite even?)

Or if that's not possible is there some way to specify a shared file system? (Make it know that other processes are reading from it as well?)

For various reasons, using a parallel dbms is not possible, and I need two DB processes running parallel...

Any help is greatly appreciated. Thanks!!

Answer 1

Write-protecting the data directory will cause PostgreSQL to fail to start, as it needs to be able to write postmaster.pid. PostgreSQL also needs to be able to write temporary files and tablespaces, set hint bits, manage the visibility map, and more.

In theory it might be possible to modify the PostgreSQL server to support running on a read-only database, but right now AFAIK this is not supported. Don't expect it to work. You'll need to clone the data directory for each instance.

If you want to run multiple PostgreSQL instances for performance reasons, having them fighting over shared storage would be counter-productive anyway. If the DB is small enough to fit in RAM it'd be OK ... but in that case it's also easy to just clone it to each machine. If the DB isn't big enough to be cached in RAM then both DB instances would be I/O bottlenecked and unlikely to perform any better than (probably slightly worse than) a single DB not subject to storage contention.

There's some chance that you could get it to work by:

• Moving the constant data into a new tablespace onto read-only shared storage
• Taking a basebackup of the database, minus the newly separated tablespace for shared data
• Copying the basebackup of the DB to read/write private storage on each host that'll run a DB
• Mounting the shared storage and linking the tablespace in place where Pg expects it
• Starting pg

... at least if you force hint-bit setting and VACUUM FREEZE everything in the shared tablespace first. It isn't supported, it isn't tested, it probably won't work, there's no benefit over running private instances, and I sure as hell wouldn't do it, but if you really insist you could try it. Crashes, wrong query results, and other bizarre behaviour are not unlikely.