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
