Thread: Data directory on read-only media
Hi, I've been thinking about running postgres from read-only media. It's handy for creating demo CDs etc. I hacked together a patch that allows you to run Postgres without write permissions to the data directory. Some changes are needed: 1. Force all transactions to be read-only, using the mechanisms alread in place. This catches all the obvious INSERT/UPDATE/CREATE modifications. 2. Disable VACUUM, CLUSTER, and nextval/setval 3. Disable bgwriter and manual checkpoints. 4. Don't increment xid counter, just use a single xid for all transactions. Otherwise we might reach the xid wrap-around point, and we can't vacuum. It seems safe to me, am I missing anything? 5. Don't try to write buffers with commit hint modifications. Just discard them. 6. Don't write the stats file, opts file, or user/group files. 7. Don't write free space map. 8. Disable two-phase commit. 9. Modify startup and shutdown routines so that they don't write control file, checkpoint, or attempt recovery. Tricky parts: 10. Temporary tables. Should they be created in /tmp instead of the datadir, or just disabled? How about temporary views? 11. Temporary files in sorting and hash joins. Use /tmp? 12. Subtransactions. Even read-only transactions write to the subtrans-files, don't they? Also, what happens to the subtrans system if we reuse the same xid over and over again? 13. NOTIFY/LISTEN. I guess most people use NOTIFY/LISTEN for cache coherency etc, so I don't think many people will miss it in read-only mode. And there has been plans to refactor it to an in-memory thing anyway. 14. How to put the system to read-only mode? It needs to be something that makes it impossible to run another postmaster in read/write mode at the same time, without requiring write access to the data dir. I'm thinking of a utility that creates a special postmaster.pid file. When a postmaster starts up and sees that special postmaster.pid file, it starts in read-only mode. The system must be in clean shutdowned state. Does anyone remember anything else that writes to disk? The patch does most of the above. There's no utility yet to create the special postmaster.pid file. Use "echo readonly > data/postmaster.pid" instead. Here's the patch: http://users.tkk.fi/~hlinnaka/pgsql/readonly-20051120.diff - Heikki
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> 5. Don't try to write buffers with commit hint modifications. Just discard
> them.
The performance costs of that alone are astonishing (ie, repeated
verifications of commit status).
I think what you are doing is a completely wrongheaded way to approach
it... it sounds incredibly invasive, messy, and fragile. A database or
tablespace that has been frozen (per VACUUM FREEZE) could sensibly be
put on read-only media, but I can't see doing the rest of this. Have
you thought about putting the more dynamic stuff onto a RAM disk?
regards, tom lane
Heikki, > 4. Don't increment xid counter, just use a single xid for all > transactions. Otherwise we might reach the xid wrap-around point, and we > can't vacuum. It seems safe to me, am I missing anything? Well, it eliminates transaction isolation. Completely. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > Well, it eliminates transaction isolation. Completely. If the data is read-only, you presumably don't need that. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Heikki Linnakangas wrote: > I've been thinking about running postgres from read-only media. It's > handy for creating demo CDs etc. I think that a read-only installation of PostgreSQL would be a very poor demonstration of its capabilities. Better put the data in a RAM disk. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Hi, does anyone have experiences about putting a tablespace on ramdisk? Does it work (and keep working after a restart of the server)? Thanks in advance for any insight. Paolo
pmagnoli@systemevolution.it schrieb: > Hi, > does anyone have experiences about putting a tablespace on ramdisk? Does it > work (and keep working after a restart of the server)? > Thanks in advance for any insight. > Yes it does work as long as you dont restart your server. Postgres does not appreciate disappearing cluster data. What are you trying to solve btw? ++Tino
I'd like to mimic MySQL's in-memory tables (engine=memory), which structure survives a server restart (data lost of course). I suspected that a server restart would be a problem in this case. Thank you anyway. Paolo Tino Wildenhain <tino@wildenhain.de> ha scritto > pmagnoli@systemevolution.it schrieb: > > Hi, > > does anyone have experiences about putting a tablespace on ramdisk? Does it > > work (and keep working after a restart of the server)? > > Thanks in advance for any insight. > > > Yes it does work as long as you dont restart your server. > Postgres does not appreciate disappearing cluster data. > > What are you trying to solve btw? > > ++Tino > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
pmagnoli@systemevolution.it schrieb: > I'd like to mimic MySQL's in-memory tables (engine=memory), which structure > survives a server restart (data lost of course). > I suspected that a server restart would be a problem in this case. > Thank you anyway. you could use temp tables... but usually it isnt worth the trouble. Adjust your cache mem and stuff and often used data will be in memory automatically. HTH Tino
On Sun, 20 Nov 2005, Tom Lane wrote: > Heikki Linnakangas <hlinnaka@iki.fi> writes: >> 5. Don't try to write buffers with commit hint modifications. Just discard >> them. > > The performance costs of that alone are astonishing (ie, repeated > verifications of commit status). You'd vacuum first to avoid that, of course. > I think what you are doing is a completely wrongheaded way to approach > it... it sounds incredibly invasive, messy, and fragile. A database or > tablespace that has been frozen (per VACUUM FREEZE) could sensibly be > put on read-only media, but I can't see doing the rest of this. Have > you thought about putting the more dynamic stuff onto a RAM disk? Invasive: Most of the changes are small modifications to startup and shutdown routines. I wouldn't call it too invasive. Probably less invasive than enforcing that a tablespace is read-only, which would be a nice feature of it's own. Messy: Well, I guess it's in the eye of the beholder. It depends a lot on the implementation details. Fragile: It's certainly something you have to keep in mind whenever you need to write something to disk. There's not that many places that do that, I enumerated them in the original mail. Using a RAM disk is harder for the user. You need to set up the RAM disk, figure out what to copy to RAM disk and what not, and then be careful not to change anything that's on the frozen tablespace. What would a script look like that setups a RAM disk and runs postgres from that? It's probably doable, but hard to do in a portable way. There's also this TODO: Allow a warm standby system to also allow read-only queries [pitr] In fact, I was originally thinking of that. I should've mentioned it. It has the same issues with transactions and WAL as running from read-only media, so I decided to start with the seemingly easier case. - Heikki
On Mon, 21 Nov 2005, Peter Eisentraut wrote: > Heikki Linnakangas wrote: >> I've been thinking about running postgres from read-only media. It's >> handy for creating demo CDs etc. > > I think that a read-only installation of PostgreSQL would be a very poor > demonstration of its capabilities. Better put the data in a RAM disk. RAM space is limited. I was thinking more of a demonstration CD of some other software that uses PostgreSQL as kind of an embedded database. Of course, there's other databases more suited for embedding. - Heikki
Heikki Linnakangas wrote: > > I think that a read-only installation of PostgreSQL would be a very > > poor demonstration of its capabilities. Better put the data in a > > RAM disk. > > RAM space is limited. Nowadays, CD space is often more limited than RAM. You could of course now talk about a DVD instead. I wonder what kind of performance you'd get out of that anyway. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Heikki Linnakangas <hlinnaka@iki.fi> writes:
> There's also this TODO:
> Allow a warm standby system to also allow read-only queries [pitr]
> In fact, I was originally thinking of that. I should've mentioned it.
> It has the same issues with transactions and WAL as running from
> read-only media, so I decided to start with the seemingly easier case.
No, it hasn't got anywhere near the same issues, primarily because you
don't have to have a 100% rock solid guarantee of no disk writes. Thus
for example there's no problem with updating hint bits, nor with
preventing use of temp files.
I concur with Peter's opinion that a PG demo that is hard-core read-only
is not going to be very useful. It certainly won't have a lot to do
with either the behavior or performance of the system in live use, which
would render it not only not very interesting but close to being false
advertising.
regards, tom lane