On Fri, Jan 30, 2009 at 1:04 PM, Sam Mason <sam@xxxxxxxxxxxxx> wrote: > On Fri, Jan 30, 2009 at 08:37:02PM +0100, durumdara wrote: > >> - I can add/modify a table, or a field to a table without "full lock" >> on the table (like DBISAM restructure). Like in FireBird, where the "add >> field" change only the table description. I don't know that PG supports >> this way of the DB modifying. > > Yes; have a read about MVCC: > > http://www.postgresql.org/docs/current/static/mvcc.html Just because one uses MVCC doesn't mean it's free to add a new column to a table. Adding a nullable column in pgsql is trivial. Adding an index can be done concurrently. InnoDB supports MVCC, but MySQL's way of doing things results in innodb tables being rewritten from scratch for both of those for varying reasons, resulting in a locked table the whole time. Create index concurrently is a treasure to behold on large production systems. I can't stress how important it is that I can create an index on demand in the middle of the heaviest user activity on my system and nothing hangs or locks from the user side waiting on it. Same for adding a column. >> - Quick and easy backup/restore system > > pg_dump works well, how quick it is depends on the amount of data you > have obviously More important that quick and easy is reliable, predictable, and capable of being run on a live, working database (backup that is). pg_dump definitely is those things. >> This "one datadir" is seems to be not too good for us. We used DBISAM in >> our clients, and many times when we got some filesystem error, we can >> simply recover the tables - from the files. Do you get a lot of filesystem errors? Or corrupted table errors. Having table corruption be a common problem means something somewhere is broken, whether it be your hardware or the db you're running. I've been running pgsql for a long time, going back to the tailend of the 6.x era. I've never once had a corrupt table on a production server. I am not alone. > Use a proper backup system that takes consistent snapshots of your data, > anything else will come back and bite you when you really don't want it > to I feel like I'm bathed with light and getting religious, but yes, this. >> I very fear from to keep all databases in one place, because if they are >> corrupted, possible more of them injured (if they are not separated). >> I cannot make filesystem based (hard) copy from one db (only SQL dump >> enabled). You need to get out of the habit of playing in your databases files. Any database that requires you to routinely do that is broken by design. I get the feeling you've learned how to use a hammer (the file system level manipulation thing) to fix a problem that's very nail like (file corruption in mysql) and are looking for a place to use your hammer in pgsql. Pgsql has no such nails sticking out of it. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
