Postgresql：最好使用多个数据库，每个有1个模式，或者1个数据库有多个模式？ [英] Postgresql: is it better using multiple databases with 1 schema each, or 1 database with multiple schemas?

问题描述

此评论之后我的一个问题，我想如果更好地使用1数据库与X模式，反之亦然。

我的情况：我正在开发一个网络应用程序其中，当人们注册时，我创建（实际上）一个数据库（不，它不是一个社交网络：每个人都必须访问自己的数据，从来没有看到其他用户的数据）。

这是我用于上一个版本的应用程序（仍然运行在mysql上）的方式：通过plesk api，对于每个注册，我做：

1. 创建具有有限权限的数据库用户;


2. 创建只能由上一个创建的用户和超级用户访问的数据库


3. 填充数据库

现在，我需要对postgresql项目正在成熟，mysql ..不能满足所有的需要）

我需要所有的数据库/模式备份独立：pg_dump在两种方式完美工作

因此，假设你比我更有经验的potsgres用户，你认为是我的情况的最佳解决方案，为什么？

使用$ x db而不是$ x模式会有性能差异吗？

编辑：我几乎忘记了：我所有的数据库/ schemas将始终具有相同的结构！

Edit2 ：对于备份问题也许更好的使用1 db和许多模式，一次转储所有的模式：恢复将是相当简单的加载主转储在一个dev机器，然后转储和恢复只需要模式：有1个额外的步骤，但倾销所有的模式

ps：对不起，如果我在文本中忘记了一些W字符，我的键盘遭受该按钮）

UPDATE 2012

嗯，应用程序结构和设计在过去两年里改变了很多。
Im仍然使用 1 db with many schemas 方法，但是，我有一个数据库为我的应用程序的每个版本：

  Db myapp_01 
 \_ my_customer_foo_schema 
 \_ my_customer_bar_schema 
 Db myapp_02 
 \_ my_customer_foo_schema 
 \_ my_customer_bar_schema 
  

对于备份， ，然后移动dev服务器上的备份。

Im也使用PITR / WAL备份，但是，正如我之前说的，它不可能，我不得不恢复<

1-db（一个数据库）的所有数据库，所以它可能会在今年被取消（在我的情况不是最好的方法） -many-schema方法对我来说非常好，即使应用程序结构完全改变了：

我几乎忘记了：我的数据库/模式将总是具有相同的结构！

...现在，

解决方案

PostgreSQL的schema与MySQL的数据库大致相同。在PostgreSQL安装上有很多数据库可能会有问题;有很多模式将无故障工作。因此，您肯定希望使用该数据库中的一个数据库和多个模式。

After this comment to one of my question, I'm thinking if it is better using 1 database with X schemas or vice versa.

My situation: I'm developing a web-app where, when people register, I create (actually) a database (no, its not a social network: everyone must have access to his own data and never see the data of the other user).

That's the way I used for the previous version of my application (that is still running on mysql): through the plesk api, for every registration, I do:

1. Create a database user with limited privileges;
2. Create a database that can be accessed just by the previous created user and the superuser (for maintenance)
3. Populate the db

Now, I'll need to do the same with postgresql (the project is getting mature and mysql.. don't fulfill all the needs)

I need to have all the databases/schemas backups independent: pg_dump works perfectly in both ways, the same for the users that can be configured to access just 1 schema or 1 database.

So, assuming you are more experienced potsgres users than me, what do you think is the best solution for my situation, and why?

Will there be performance differences using $x db instead of $x schemas? And what solution will be better to maintain in future (reliability)?

Edit: I almost forgot: all of my databases/schemas will always have the same structure!

Edit2: For the backups issue (using pg_dump), is maybe better using 1 db and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a dev machine and then dump and restore just the schema needed: there is 1 additional step, but dumping all the schema seem faster then dumpin them one by one.

p.s: sorry if i forgot some 'W' char in the text, my keyboard suffer that button ;)

UPDATE 2012

Well, the application structure and design are changed so much dirung those last two years. Im still using the 1 db with many schemas approach, but still, I have 1 database for each version of my application:

Db myapp_01
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema
Db myapp_02
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema

For backups, im dumping each database regularly, then moving the backups on the dev server.

Im also using the PITR/WAL backup but, as I said before, its not likely i'll have to restore all database at once.. so it will probably be dismissed this year (in my situation is not the best approach).

The 1-db-many-schema approach worked very well for me since now, even if the app structure is totally changed:

i almost forgot: all of my databases/schemas will always have the same structure!

...now, every schema has its own structure that change dinamycally reacting to users data flow.

解决方案

A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.

这篇关于Postgresql：最好使用多个数据库，每个有1个模式，或者1个数据库有多个模式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！