本文介绍了使用多个数据库和每个数据库一个模式更好,还是一个数据库有多个模式更好?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

此评论之后对于我的一个问题,我在考虑使用具有 X 模式的数据库是否更好,反之亦然.

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

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

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). 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. 创建一个权限有限的数据库用户;
  2. 创建一个只能由之前创建的用户和超级用户(用于维护)访问的数据库
  3. 填充数据库

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

Now, I'll need to do the same with PostgreSQL (the project is getting mature and MySQL don't fulfil all the needs). 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 用户,您认为最适合我的情况的解决方案是什么,为什么?使用 $x 数据库而不是 $x 模式会有性能差异吗?将来什么解决方案会更好地维护(可靠性)?我所有的数据库/模式将总是具有相同的结构!

So, assuming you are more experienced PostgreSQL users than me, what do you think is the best solution for my situation, and why? 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

对于备份,我定期转储每个数据库,然后在开发服务器上移动备份.我也在使用 PITR/WAL 备份,但正如我之前所说,我不太可能必须一次恢复所有数据库.所以今年可能会被驳回(在我的情况下不是最好的方法).

For backups, I'm dumping each database regularly, and then moving the backups on the development server. 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).

从现在起,一个数据库多模式方法对我来说非常有效,即使应用程序结构完全改变.我差点忘了:我所有的数据库/模式总是具有相同的结构!现在,每个模式都有自己的结构,可以根据用户数据流动态变化.

The one-db-many-schema approach worked very well for me since now, even if the application 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 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.

这篇关于使用多个数据库和每个数据库一个模式更好,还是一个数据库有多个模式更好?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-13 23:36