On Jan 30, 2009, at 2:37 PM, durumdara wrote:
Dear PG Users!
I don't want to make a
heated debate with this subject, but I wanna ask about your experiences because we need to make a new special site and we wanna know, which DB is the best for this...
This website will supports up to 200 corporations.
The functions are similar, but because of many differents of these corps (datas to keep, way to working them), we wanna make many databases (one DB for one corp).
The site uses one main database that handle all public, and shared services, and store the links to subdatabases.
The website will works with apache/mod_python, and the each of the corp's services are handled with another python module (from another (separated) database). The main structure of the source code already wroted.
But: we need to determine, which database we will use in the future for this project.
The main viewpoints:
- quick (re)connect - because mod_python basically not store the database connections persistently
I don`t know much about python except the animal but could use a SQL proxy to solve this issue
- fast queries
Define fast, but remember a DB was never designed to be fast. However it sounds like for the coupld of companies you wouldn't have to much problems there.
- easy IDE to use (like pgadmin)
- the db server can handle many databases (webdb_nnn where nnn is an integer)
I don't have experience with PG and hunderds of DB's, but I think it shouldn't be a problem
- 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.
How big are these tables and how frequently do they change? If they change really frequently then you have a problem with your design in general.
- Quick and easy backup/restore system
pg_dump/pg_restore are your friends. webmin makes this also easy if you want a point and click interface.
Another important thing that I don't understand (what as I saw) that the PostGreSQL is store the databases in one, unseparatable file set, in a directory named data.
In another databases, like DBISAM, FireBird, MySQL, the databases are separated to another directories/files.
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.
When we want to backup or restore one database, we can do it in two way: a.) archive all files b.) make sql dump from database.
If a file get corrupted in a database, then we can restore the datas from files, and this filesystem error causes problems only for this database, not for all.
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).
Why would just one file get corrupt?? You can store your tables(indexes in different places if you want to but if one get's corrupt you need to change/check/replace hardware anyways. PostgreSQL doesn't behave much like MySQL where all of a sudden tables get corrupted and you need to repair them.
Ok, I saw that pgsql supports "tablespaces", but as I saw, this function can hold only table datas in the another directory, and not the full database can separated with them.
Correct...
Because I don't used one PGSQL with many databases (up to 200), I don't know, what happening, and which cases possible. But I think you have many experience with it. Please share it with me!
Don't expect yourself to just set one table and/or DB back. This is plain wrong and you need to make appropriate backups. If you really want to set a DB back as a table then use sqlite, but that defeats your requirements of 'fast'.
Please help me, because we need to determine which DB to use.
I think PG will do very well in your situation, but you have to set your mind off using files
I started the usage of the PG in prev. month, and I liked it except the way of the data storage (one data dir).
I don't know any DB (except may be some Object DB's and sqlite) where you can do that properly, officially and ACID compliant.
I tried the MySQL before I tried PG. InnoDB is seems to be "forcing transaction system on MyISAM". And boolean data type is missing (solved with enum?).
I don't like it all, but it is seems to be fast with little tables, and it is separate the database files to another directories which thing I like. Possible it have many limitations what I don't saw in first time.
Don't want to debate MySQL but PG is a better option IMHO, it recovers VERY well from crashes where you are worried about (I know, I live in Ecuador where power goes down every week or so and it always recovered perfectly).
Please help me, which DB is good for us, and how to configure, and use PGSQL with these database-set which we need to use.
PostgreSQL is good for you as long as you set your mind away from restoring a DB by replacing a fileset. pg_dump/pg_restore are your friends. If you do care about restoring a DB up to a point in time you can do WAL shipping.
Thanks for your help:
dd
