PostgreSQL：使用多个数据库，每个数据库有一个模式，还是一个数据库有多个模式，更好？ [英] PostgreSQL: Is it better to use multiple databases with one schema each, or one database with multiple schemas?
问题描述
在此评论之后对于我的一个问题，我在考虑使用具有X模式的数据库是否更好，反之亦然。
After this comment to one of my question, I'm thinking if it is better using one database with X schemas or vice versa.
我的情况：我正在开发一个Web应用程序， ，当人们注册时，我（实际上）创建了一个数据库（不，这不是一个社交网络：每个人都必须有权访问自己的数据，并且永远都不会看到其他用户的数据）。
My situation: I'm developing a web application where, when people register, I create (actually) a database (no, it's not a social network: everyone must have access to his own data and never see the data of the other user).
这就是我在以前版本的应用程序（仍在MySQL上运行）中使用的方式：通过Plesk API，每次注册时，我都会这样做：
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:
- 创建具有有限特权的数据库用户；
- 创建仅可由先前创建的用户和超级用户（用于维护）访问的数据库
- 填充数据库
现在，我需要对PostgreSQL做同样的事情（项目
Now, I'll need to do the same with PostgreSQL (the project is getting mature and MySQL... don't fulfill all the needs).
我需要使所有数据库/方案备份独立：pg_dump在两种方式下均能完美运行，对于可以配置为仅访问一个模式或一个数据库的用户来说，也是相同的。
I need to have all the databases/schemas backups independent: pg_dump works perfectly in both ways, and the same for the users that can be configured to access just one schema or one database.
因此，假设您是比我更有经验的PostgreSQL用户，您怎么办？认为是针对我的情况的最佳解决方案，为什么？
So, assuming you are more experienced PostgreSQL users than me, what do you think is the best solution for my situation, and why?
使用$ x数据库而不是$ x模式会产生性能差异吗？以及哪种解决方案将来会更好地维护（可靠性）？
Will there be performance differences using $x database instead of $x schemas? And what solution will be better to maintain in the future (reliability)?
我所有的数据库/方案都将始终具有相同的结构！
All of my databases/schemas will always have the same structure!
对于备份问题（使用pg_dump），最好使用一个数据库和多个模式，一次转储所有模式：恢复将非常简单，加载主转储在开发机中，然后仅转储和还原所需的模式：还有一个步骤，但是转储所有模式似乎比逐个转储更快。
For the backups issue (using pg_dump), is maybe better using one database and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a development machine and then dump and restore just the schema needed: there is one additional step, but dumping all the schema seem faster than dumping them one by one.
在过去的两年中，应用程序的结构和设计发生了很大变化。我仍在使用具有许多模式的一个数据库方法，但仍然，我的应用程序的每个版本都有一个数据库：
Well, the application structure and design changed so much during those last two years. I'm still using the one db with many schemas approach, but still, I have one 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, I'm dumping each database regularly, and then moving the backups on the development server.
我也在使用PITR / WAL备份，但正如我之前所说，我不太可能拥有立即恢复所有数据库 ...因此它可能会在今年被淘汰（在我看来，这不是最好的方法）。
I'm also using the PITR/WAL backup but, as I said before, it's 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).
从现在起，即使应用程序结构已完全更改，一db-many-schema方法也对我非常有效：
The one-db-many-schema approach worked very well for me since now, even if the application structure is totally changed:
忘了：我所有的数据库/方案都会总是具有相同的结构！
...现在，每个模式都有其自己的结构，这些结构会动态响应用户数据流。
...now, every schema has its own structure that change dynamically reacting to users data flow.
推荐答案
PostgreSQL的模式与MySQL的数据库大致相同。在PostgreSQL上安装许多数据库会出现问题。具有许多模式将毫无问题。因此，您肯定要使用一个数据库和该数据库中的多个架构。
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：使用多个数据库，每个数据库有一个模式，还是一个数据库有多个模式，更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
