PostgreSQL: Est-il préférable d'utiliser plusieurs bases de données avec un schéma de chacune, ou une base de données avec plusieurs schémas?

Après ce commentaire à une de mes question, je suis en train de penser si il est préférable d'utiliser une base de données avec X schémas ou vice versa.

Ma situation: je suis sur le développement d'une application web où, quand les gens vous inscrire, j'ai créer (en fait) une base de données (non, ce n'est pas un réseau social: tout le monde doit avoir accès à ses propres données et de ne jamais voir les données de l'utilisateur).

C'est la façon dont j'ai utilisé pour la précédente version de mon application (qui est toujours en cours sur MySQL): grâce à parallels Plesk panel de l'API, pour chaque enregistrement, je n':

Créer une base de données de l'utilisateur avec des privilèges limités;
Créer une base de données accessible seulement par le précédent créé d'utilisateur et le super utilisateur (pour l'entretien)
Remplir la base de données

Maintenant, je vais avoir besoin de faire la même chose avec PostgreSQL (le projet est d'arriver à maturité et MySQL... ne pas satisfaire tous les besoins).

J'ai besoin d'avoir toutes les bases de données/schemas sauvegardes indépendantes: pg_dump fonctionne parfaitement dans les deux sens, et de même pour les utilisateurs qui peut être configuré pour l'accès d'un schéma ou d'une base de données.

En supposant que vous êtes plus expérimenté des utilisateurs de PostgreSQL que moi, que pensez-vous est la meilleure solution pour ma situation, et pourquoi?

Aura-t-il des écarts de rendement à l'aide de $x base de données au lieu de $x schémas? Et quelle solution sera la mieux pour maintenir dans l'avenir (fiabilité)?

Toutes mes bases de données/schemas sera toujours ont la même structure!

Pour les sauvegardes question (à l'aide de pg_dump), est peut-être mieux à l'aide d'une base de données et de nombreux schémas, les déversements de tous les schémas à la fois: la récupération sera très simple de charger le principal dump dans une machine de développement, puis dump et restore juste le schéma nécessaire: il y a une étape supplémentaire, mais dumping tous le schéma semble plus rapide que de vider un par un.

Mise à JOUR 2012

Bien, la structure de l'application et de conception beaucoup changé au cours de ces deux dernières années. Je suis toujours à l'aide de la one db with many schemas approche, mais tout de même, j'ai une base de données pour chaque version de ma demande:

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

Pour les sauvegardes, je suis dumping chaque base de données régulièrement, et en déplaçant les sauvegardes sur le serveur de développement.

Je suis également en utilisant le PITR/WAL sauvegarde, mais, comme je l'ai dit avant, il n'est pas probable que je vais devoir restaurer de la base de données à la fois... donc il va probablement être rejeté cette année (dans ma situation n'est pas la meilleure approche).

Celui-db-nombreux-schéma approche a très bien fonctionné pour moi, car maintenant, même si la structure de l'application est totalement changé:

J'ai presque oublié: toutes mes bases de données/schemas sera toujours ont la même structure!

...maintenant, à chaque schéma a sa propre structure qui changent de manière dynamique réagissant aux utilisateurs de flux de données.

"toutes mes bases de données/schemas aura jamais la même structure!" vous voulez dire qu'ils ont tous la même structure? Ou jamais?
Désolé, oui, ils ont tous la même structure pour toujours: si je change, je vais changer tous 😉
Si vous avez 1000 clients, cela signifie que vous devez mettre à jour 1000 schéma?
oui, mais j'ai de la mise à jour de la structure des tables, pas les données.
Alors, qu'est ce que vous allez faire enfin? Une question, cependant, bien que les performances des requêtes, etc. peut être contrôlé par les tablespaces, les schémas résultant en un rendement équivalent de multi-db vs multi-schéma, aucun impact sur WAL journaux???
eh bien, le design de l'application a été radical changé au cours du temps... permettez-moi de mettre à jour mes question avec quelques détails
Comment vous assurer de la sécurité entre les deux schémas? Une connexion DB peut mettre à jour plusieurs Schémas - c'est une aubaine et nue comme bien

InformationsquelleAutor Strae | 2009-07-20

103

Un PostgreSQL "schéma" est à peu près le même comme une base de données MySQL "base de données". Ayant de nombreuses bases de données sur une installation de PostgreSQL pouvez obtenir problématique; avoir de nombreux schémas fonctionnera sans aucun problème. Si vous voulez absolument aller avec une base de données et plusieurs schémas dans la base de données.
- C'. Postgres ne vous permettra pas de requêtes dans les bases de données, qui peut être assez ennuyeux.
- "Avoir de nombreuses bases de données sur une installation de PostgreSQL pouvez obtenir problématique", veuillez préciser; est-il problématique en général ou dans ce cas précis, et pourquoi?
- "L'utilisation la plus courante de cas d'utilisation de plusieurs schémas de base de données est la construction d'un logiciel-comme-un-service de l'application dans laquelle chaque client a son propre schéma. Bien que cette technique semble convaincante, nous vous recommandons fortement contre elle, comme il a causé de nombreux cas de problèmes opérationnels. Par exemple, même avec un nombre modéré de schémas (> 50) peut avoir un grave impact sur la performance de la société Heroku est instantanés de base de données de l'outil" devcenter.heroku.com/articles/heroku-postgresql
- Fait intéressant, qui semble être à l'opposé de la conclusion de kquinn de l' (accepté) réponse.
- Pour ceux qui lisent à la fin de 2015. Il y a un dblink Postgres extension pour des requêtes dans les bases de données maintenant (c'est une réponse à @mattb commentaire).
- Pour ceux qui lisent après 2014, Pg a foreign data wrappers de départ à la v9.3, et en particulier la postgres_fdw permet l'interrogation sur Pg bases de données (OMI mieux que dblink).
- Avoir une base de données avec de nombreux schémas pour la rendre quasiment impossible de faire un dump d'un seul et unique schéma de ceux-ci, cependant. Je suis en cours d'exécution d'une seule base de données postgres avec plus de 3000 schémas et de pg_dump juste échoue avec une erreur de mémoire insuffisante si vous essayer de vider un seul schéma. Je me demande si ce serait différente si j'3000 bases de données à la place.
- Pourquoi ne peut-il obtenir problématique? Je peux voir comment plusieurs PostgreSQL clusters sur un serveur pouvez obtenir un problème, mais plusieurs bases de données (avec un schéma de chaque, habituellement juste de mettre des trucs dans public) dans un cluster doit être fine.
- Creuser dans ce, c'est plutôt un article intéressant à propos de la question influitive.io/... et rob.conery.io/2014/05/28/a-mieux-code-generator-pour-postgresql répond à certaines des questions que vous pouvez rencontrer. Le premier article a également un commentaire qui correspond à beaucoup de schéma-nombreuses tables isues de Josh Berkus (medium.com/@jberkus/...)
InformationsquelleAutor kquinn

Certainement, je vais y aller pour celui-db-nombre de schémas d'approche. Cela me permet de vider toutes les base de données, mais restaurer qu'un très facilement, à de nombreux égards:

Dump de la db (tous le schéma), charge le dump dans une nouvelle db, dump juste le schéma dont j'ai besoin, et de la restaurer dans les principaux db.
Dump le schéma séparément, un par un (mais je pense que la machine ne souffrent plus de cette façon - et je m'attends, comme 500 schémas!)

Sinon, googler autour, j'ai vu qu'il n'y a pas d'auto-procédure pour reproduire un schéma (à l'aide de l'un comme modèle), mais beaucoup d'entre eux suggèrent de cette façon:

Créer un modèle de schéma
Quand le besoin de dupliquer, renommer avec le nouveau nom
Dump
Le renommer en arrière
La restauration de la sauvegarde
La magie est fait.

J'ai écrit deux lignes en Python pour le faire; je l'espère, ils peuvent aider quelqu'un (en 2 secondes-écrit-code, ne pas utiliser en production):

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()

InformationsquelleAutor Strae

11

Je dirais, aller avec plusieurs bases de données ET plusieurs schémas 🙂

Schémas dans PostgreSQL sont un peu comme les paquets dans Oracle, dans le cas où vous êtes familier avec ceux-ci. Les bases de données sont destinées à différencier entre un ensemble de données, alors que les schémas sont plus comme des entités de données.

Par exemple, vous pourriez avoir une base de données pour une application entière avec les schémas "UserManagement", "LongTermStorage" et ainsi de suite. "UserManagement" serait alors contenir la table "User", ainsi que toutes les procédures stockées, des déclencheurs, des séquences, etc. qui sont nécessaires pour la gestion des utilisateurs.

Les bases de données sont des programmes complets, les schémas sont des composants.
- ... et donc je vais avoir 1 base de données, avec à l'intérieur les schémas: $customer1_user_schema, $customer2_user_schema, $customer3_user_schema, $customer1_documents_schema, $customer2_documents_schema, $customer3_documents_schema? Mh... ne semble pas un moyen fiable... et que dire de la performance? Et quel est le code de mon application (sera php et python)? donc, de nombreux schémas..
- Je suis en train de lire ce que: chaque client a sa base de données customer1_database, customer2_database et au sein de ces bases de données vous avez user_schema, documents_schema.
InformationsquelleAutor
4

Un certain nombre de schémas doivent être plus léger qu'un certain nombre de bases de données, bien que je ne peux pas trouver une référence qui confirme ce.

Mais si vous voulez vraiment garder les choses très distinctes (au lieu de refactoring de l'application web de sorte qu'un "client" de la colonne est ajoutée à vos tables), vous pouvez toujours utiliser des bases de données distinctes: j'affirme que vous pouvez plus facilement faire des restaurations d'un client de la base de données de cette façon-sans déranger les autres clients.

InformationsquelleAutor Troels Arvin
4

Dans un PostgreSQL contexte, je recommande d'utiliser une db avec plusieurs schémas, comme vous pouvez (par exemple) l'UNION de TOUS à travers des schémas, mais pas dans les bases de données. Pour cette raison, une base de données est vraiment complètement isolé à partir d'une autre base de données, tandis que les schémas ne sont pas isolés d'autres schémas dans la même base de données.

Si vous -pour une raison quelconque - ont pour consolider les données à travers des schémas dans l'avenir, il sera facile de le faire sur plusieurs schémas. Avec plusieurs bases de données que vous auriez besoin de plusieurs db-connexions, de recueillir et de fusionner les données de chaque base de données "à la main" par la logique de l'application.

Ces derniers ont des avantages dans certains cas, mais pour la majeure partie, je pense que celui-base de données-plusieurs schémas approche est plus utile.

InformationsquelleAutor emax

Vous devez vous connecter pour publier un commentaire.