Thread: Can I separate my Databases as in FireBird? Working with DataBases...

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.

Sometimes the customers have problems with the programs we wrote.
In this case sometimes we ask them that send me the DB fully.
They are using filesystem based copy. We open the DB, or the tables here simply, without special doing. Only we register the DB with the name "test", and we can test the applications simply.

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.

Ok, I saw that PGSQL have tablespace.

But: the databases need to be unique named. Previously I thought that tablespace higher object than database, and I can create more database with same name without collision if I use another tablespace for them.
But when I tried to do this, I got error.

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.

Have anybody an experience with PGSQL and many similar, but not same DB usage?
What is the easiest way to handle the question of the data transporting in the PGSQL?

Thanks for your help:
    dd

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.

http://www.postgresql.org/docs/8.3/interactive/sql-createtablespace.html
you probably look for that.

Durumdara wrote:
> 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.
>
> Sometimes the customers have problems with the programs we wrote.
> In this case sometimes we ask them that send me the DB fully.
> They are using filesystem based copy. We open the DB, or the
> tables here simply, without special doing. Only we register
> the DB with the name "test", and we can test the applications simply.
>
> 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.

Ok, here I start having trouble following.

There are two ways to backup a PostgreSQL database:
1) Stop the server, copy everything in the database directory.
   This can be restored on a computer with the same
   OS + hardware + address size.
2) Dump the database with pg_dump.
   This can be restored on any PostgreSQL installation
   with the same version.

After you restore the backup or dump, you can connect
to the cluster and simply rename the database to "test"
if you want.

> Ok, I saw that PGSQL have tablespace.

Yes, but you cannot simply copy the tablespace to copy
the database. Many metadata live outside this directory.

> But: the databases need to be unique named. Previously I
> thought that tablespace higher object than database, and I
> can create more database with same name without collision if
> I use another tablespace for them.
> But when I tried to do this, I got error.

Yes, the tablespace is not part of the database name.
It just says where tables will be stored.

> 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.
>
> Have anybody an experience with PGSQL and many similar, but not same DB usage?
> What is the easiest way to handle the question of the data transporting in the PGSQL?

I would teach the customer how to make a pg_dump of the
database and send that. You can then restore it into any
PostgreSQL cluster and rename the database to whatever you want.

Yours,
Laurenz Albe