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
regards, Ries van Twisk
-------------------------------------------------------------------------------------------------
Ries van Twisk
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS
WebORB PostgreSQL DB-Architect
email: r...@vantwisk.nl
web: http://www.rvantwisk.nl/
skype: callto://r.vantwisk
Phone: +1-810-476-4196
SIP: +1-747-690-5133
