On Tue, Jan 20, 2009 at 1:29 AM, Durumdara <durumdara@gmail.com> wrote:
> Hi!
>
> I came from SQLite, FireBird and DBISAM world.
> These database systems have a speciality that they stores one database in
> one file, or in one directory.
>
> Each of databases have separated file (or dir with files), and when I want
> to copy a database, I have MORE possibility to do this.
> First solution is the file-system based copy.
> Next is the embedded dump (FireBird).
> And the last is some SQL dump.
Sorry, but pgsql is built very differently from this. Each database
lives within a cluster, and the cluster owns the dbs, and gives them
coherence.
> But PGSQL have only one directory named data. All of the databases placed
> here.
> I cannot separate them.
> If the user send me a DB, I cannot copy it simply to anywhere, and register
> it. Because it have only SQL dump.
Yep, that's about the size of it.
> Ok, I saw that PGSQL have tablespace.
Those won't do what you want. Tablespaces exist as physical holding
layers that dbs can be put in. They aren't part of the namespace or
anything like that. They're an abstraction layer to allow for I/O
tuning by putting various parts of the db or dbs onto different drive
sets, etc...
> So: we want to search for a simplified way to place and separate our 200
> databases, and 200*8 applications without problems (to our development
> machine). And we wanna use them easier. If some of the customers want to
> resend a "real db" to us (for testing), we want to register easier this new
> db without rewrite our test context.
Then you are using the wrong db. That is not a strongsuit for pgsql.
> Have anybody an experience with PGSQL and many similar, but not same DB
> usage?
Sure, we used a single pgsql server for development, qa, integration
and customer test labs at my last shop. Before it got migrated to
oracle for non-performance reasons. Building a customer db in the
setup was pretty easy, but the tools to do it with are pg_dump /
createdb / psql / pg_restore.
> What is the easiest way to handle the question of the data transporting in
> the PGSQL?
You have pg_dump which allows you to transport the data between
various architectures and setups, and things like Point in Time
Recovery which is aimed more at backups of an active database within a
well defined environment.
There are some other tools to make loading lots of data easier.
pg_loader and pg_bulkload. pg_loader is like oracles sqlldr and
pg_bulkload is kinda a compromise between the whole copying the files
method you're using with firebird and postgresql's need to have
coherent data structures across all databases within a cluster.