是使用各有一个模式的多个数据库更好，还是使用一个数据库有多个模式更好?

在这样的评论到我的一个问题之后，我在想是否使用X模式的数据库更好，反之亦然。

我正在开发一个web应用程序，当人们注册时，我创建(实际上)一个数据库(不，这不是一个社交网络:每个人都必须访问自己的数据，永远不会看到其他用户的数据)。这就是我在我的应用程序的前一个版本(仍然在MySQL上运行)中使用的方式:通过Plesk API，对于每个注册，我做:

  1. 创建权限有限的数据库用户;
  2. 创建一个只能由之前创建的用户和超级用户访问的数据库(用于维护)
  3. 填充数据库

现在，我需要对PostgreSQL做同样的事情(项目越来越成熟，MySQL不能满足所有的需求)。我需要让所有的数据库/模式备份独立:pg_dump在两种方式下都能完美地工作，对于可以配置为只访问一个模式或一个数据库的用户也是如此。

那么，假设你是比我更有经验的PostgreSQL用户，你认为对我的情况最好的解决方案是什么，为什么?使用$x数据库而不是$x模式会有性能差异吗?将来维护哪种解决方案(可靠性)更好?我的所有数据库/模式将总是具有相同的结构!

对于备份问题(使用pg_dump)，可能更好的方法是使用一个数据库和多个模式，一次性转储所有模式:恢复将非常简单，在开发机器中加载主转储，然后转储和恢复所需的模式:有一个额外的步骤，但转储所有模式似乎比逐个转储更快。

更新2012

在过去的两年里，应用程序的结构和设计发生了很大的变化。我仍然在使用“一个数据库多模式”。-方法，但仍然，我有一个数据库对于每个版本我的应用程序:

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

对于备份，我定期转储每个数据库，然后将备份移动到开发服务器上。我也在使用PITR/WAL备份，但是，正如我之前所说，我不太可能必须立即恢复所有数据库。所以今年它可能会被取消(在我的情况下，这不是最好的方法)。

从现在起，即使应用程序结构完全改变，单数据库多模式方法也非常适合我。我几乎忘了:我所有的数据库/模式总是都具有相同的结构!现在，每个模式都有自己的结构，可以根据用户数据流动态变化。

120891 次浏览
小开

我会说，使用多个数据库和多个模式:)

PostgreSQL中的模式很像Oracle中的包，如果你熟悉它们的话。数据库用于区分整个数据集，而模式更像是数据实体。

例如，您可以为整个应用程序拥有一个数据库，其模式为“UserManagement”、“LongTermStorage”等等。“UserManagement”将包含“User”表，以及用户管理所需的所有存储过程、触发器、序列等。

数据库是整个程序，模式是组件。

小开

许多模式应该比许多数据库更轻量级，尽管我找不到证实这一点的参考文献。

但是如果你真的想让事情非常独立(而不是重构web应用程序，让一个“customer”列添加到你的表中)，你可能仍然想使用单独的数据库:我断言，你可以更容易地恢复一个特定客户的数据库——而不打扰其他客户。

小开
最佳答案

PostgreSQL的“模式”与MySQL的“数据库”大致相同。在PostgreSQL上安装许多数据库可能会出现问题;拥有许多模式可以毫无困难地工作。因此，您肯定希望使用一个数据库，并在该数据库中使用多个模式。

小开

当然，我将采用单数据库多模式的方法。这允许我转储所有的数据库，但只恢复一个非常容易，在许多方面:

  1. 转储数据库(所有的模式)，在一个新的db中加载转储，只转储我需要的模式，并恢复到主db中。
  2. 一个接一个地单独转储模式(但我认为这样机器会更痛苦——我预计会有500个模式!)

除此之外，我在谷歌上发现没有自动复制模式的过程(使用一个模式作为模板)，但很多人建议这样做:

  1. 创建一个模板模式
  2. 需要复制时，用新名称重命名
  3. 把它倾倒
  4. 重新命名
  5. 恢复转储
  6. 魔法完成了。

我用Python写了两行;我希望他们可以帮助别人(在2秒内编写代码，不要在生产中使用它):

import os
import sys
import pg


# Take the new schema name from the second cmd arguments (the first is the filename)
newSchema = sys.argv[1]


# Temperary folder for the dumps
dumpFile = '/test/dumps/' + str(newSchema) + '.sql'


# Settings
db_name = 'db_name'
db_user = 'db_user'
db_pass = 'db_pass'
schema_as_template = 'schema_name'


# Connection
pgConnect = pg.connect(dbname= db_name, host='localhost', user= db_user, passwd= db_pass)


# Rename schema with the new name
pgConnect.query("ALTER SCHEMA " + schema_as_template + " RENAME TO " + str(newSchema))


# Dump it
command = 'export PGPASSWORD="' + db_pass + '" && pg_dump -U ' + db_user + ' -n ' + str(newSchema) + ' ' + db_name + ' > ' + dumpFile
os.system(command)


# Rename back with its default name
pgConnect.query("ALTER SCHEMA " + str(newSchema) + " RENAME TO " + schema_as_template)


# Restore the previous dump to create the new schema
restore = 'export PGPASSWORD="' + db_pass + '" && psql -U ' + db_user + ' -d ' + db_name + ' < ' + dumpFile
os.system(restore)


# Want to delete the dump file?
os.remove(dumpFile)


# Close connection
pgConnect.close()
小开

在PostgreSQL上下文中，我建议使用一个数据库与多个模式，因为你可以(例如)跨模式的UNION ALL，但不能跨数据库。因此，一个数据库实际上完全与另一个数据库隔离，而模式与同一数据库中的其他模式并没有隔离。

如果您(出于某种原因)将来必须跨模式合并数据，那么在多个模式上这样做将很容易。对于多个数据库，您将需要多个数据库连接，并根据应用程序逻辑“手动”收集和合并来自每个数据库的数据。

后者在某些情况下具有优势，但在主要情况下，我认为单数据库多模式方法更有用。

小开

我不建议使用公认的答案——多个数据库而不是多个模式，原因如下:

  1. 如果你正在运行微服务，你想要在你的“模式”之间强制无法加入，这样数据就不会纠缠在一起，开发人员也不会最终加入其他微服务的模式，并想知道为什么当其他团队做出更改时，他们的东西不再工作。
  2. 如果负载需要，稍后可以迁移到单独的数据库机器。
  3. 如果您需要设置高可用性和/或复制，那么最好是拥有彼此完全独立的单独数据库。与整个数据库相比，不能只复制一个模式。
小开

使用单个数据库和多个schema是一个很好的方法 在postgres数据库中练习，因为:

  1. postgres中没有跨数据库共享任何数据。
  2. 到服务器的任何给定连接只能访问单个数据库中的数据，即连接请求中指定的数据库。

使用多个模式:

  1. 允许多个用户使用一个数据库而互不干扰。
  2. 将数据库对象组织到逻辑组中，使其更易于管理。
  3. 第三方应用程序可以放在单独的模式中，这样它们就不会与其他对象的名称冲突。

提交答案