问题描述
在之后对于我的一个问题，我在考虑使用具有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：使用多个数据库，每个数据库有一个模式，还是一个数据库有多个模式，更好？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！